Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    4

    Question Unanswered: ORA-01002 with out parameter cursor

    Hi,

    can you help me on this problem :

    I'm working with Oracle 8.1.6, and I have stored procedures.
    In "liste_dm_pour_om" function, I have a cursor as out parameter.
    The problem is that the execution returns the error "ORA-01002 : fetch out of sequence", although I know there are rows in the SELECT I make.

    Here is the code :

    __________________________________________________ _________
    FUNCTION liste_dm_pour_om (NUMERO in RESULT_OM.NUMERO%type,
    OBJET in PONT_SUMO.ID_OBJET%type,
    APPLICATION in PONT_SUMO.APP%type,
    ROWS out NUMBER,
    INFODM_CUR out INFODMSCURTYP) IS

    recdm RECINFODMSTYP;

    BEGIN
    rows:=0;
    IF NOT INFODM_CUR%ISOPEN THEN
    OPEN INFODM_CUR FOR
    select distinct r.numero, r.titre_dm, r.libelle_ss_ensemble
    from result_dm r, PONT_SUMO c
    where c.ID_OBJET =OBJET
    and r.NUMERO_om=NUMERO
    and c.APPLICATION = APPLICATION;
    END IF;
    LOOP
    FETCH INFODM_CUR into recdm;
    EXIT when INFODM_CUR%NOTFOUND;
    rows:=rows+1;
    END LOOP;


    END liste_dm_pour_om;
    __________________________________________________ ________

    And the definition of the cursor type (which is defined in the specification package) is :
    __________________________________________________ ________
    TYPE RECINFODMSTYP is
    record(numero result_dm.numero%type,
    titre_dm result_dm.titre_dm%type,
    libelle_ss_ensemble result_dm.libelle_ss_ensemble%type);
    TYPE INFODMSCURTYP is REF CURSOR return RECINFODMSTYP;
    __________________________________________________ _________


    Remark :
    ---------

    When I don't use this kind of cursor, but a cursor declared before the "BEGIN" of the function, I have no error. But the problem with this method is that I don't manage to make this cursor be an out parameter of my function.

    Here is the code related to this second part :
    __________________________________________________ _________
    FUNCTION liste_dm_pour_om (NUMERO in RESULT_OM.NUMERO%type,
    OBJET in PONT_SUMO.ID_OBJET%type,
    APPLICATION in PONT_SUMO.APP%type,
    ROWS out NUMBER,
    INFODM_CUR out INFODMSCURTYP) IS

    recdm RECINFODMSTYP;

    CURSOR get_info_dm (numom IN RESULT_OM.NUMERO%type,
    obj IN PONT_SUMO.ID_OBJET%type,
    app IN PONT_SUMO.APP%type) IS
    select distinct r.numero, r.titre_dm, r.LIBELLE_SS_ENSEMBLE
    from result_dm r, PONT_SUMO c
    where c.ID_OBJET = obj
    and r.NUMERO_om = numom
    and c.APPLICATION = app
    and c.ID_OUTIL = r.ID_OUTIL;

    BEGIN
    rows:=0;
    IF NOT get_info_dm%ISOPEN THEN
    OPEN get_info_dm(NUMERO, OBJET, APPLICATION);
    END IF;
    LOOP
    FETCH get_info_dm into recdm;
    EXIT when get_info_dm%notfound;
    rows:=rows+1;
    END LOOP;

    END liste_dm_pour_om;
    __________________________________________________ ___


    Thank U to help me !!!

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Exclamation

    First, a FUNCTION must RETURN some value and I don't seem to see where you coded the function as:

    FUNCTION ...(...) RETURN <variable> IS ...

    Also you can write your cursor as:

    BEGIN
    OPEN INFODM_CUR FOR
    select distinct r.numero, r.titre_dm, r.libelle_ss_ensemble
    from result_dm r, PONT_SUMO c
    where c.ID_OBJET =OBJET
    and r.NUMERO_om=NUMERO
    and c.APPLICATION = APPLICATION;
    rows:=SQL%ROWCOUNT;
    END IF;
    END;
    /
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2003
    Posts
    4

    Unhappy

    Hi, excuse me for answering so late...

    Indeed, my function contains "RETURN", but I've just forgotten to write it in the example ...

    Concerning my cursor, the sample you gave me didn't help me : my cursor still doesn't give anything (rows = 0), although I know it contains something...

    Any other idea??

    Can someone explain me why I've got a difference whether I use a cursor defined before "BEGIN" or inside? and how can I manage to get back the content of my cursor in a parameter.

    Thank U very much!

    Nadi

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    Originally posted by Nadi
    Hi, excuse me for answering so late...

    Indeed, my function contains "RETURN", but I've just forgotten to write it in the example ...

    Concerning my cursor, the sample you gave me didn't help me : my cursor still doesn't give anything (rows = 0), although I know it contains something...

    Any other idea??

    Can someone explain me why I've got a difference whether I use a cursor defined before "BEGIN" or inside? and how can I manage to get back the content of my cursor in a parameter.

    Thank U very much!

    Nadi
    If you notice the parameters of your function, you are expecting 'INFODM_CUR' as output type INFODMSCURTYP (I suspect a reference cursor?).

    Therefore the cursor as written in my example will return the result in this reference cursor.

    Also your loop:
    FETCH INFODM_CUR into recdm;
    EXIT when INFODM_CUR%NOTFOUND;
    rows:=rows+1;
    END LOOP;
    Is not necesary because the 'OPEN INFODM_CUR CURSOR' already assign the result set to the reference cursor.

    And INFODM_CUR%ROWCOUNT will give you the number of rows fetched.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Aug 2003
    Posts
    4
    Hi!

    I've found that my problem came from the fact that the number in my "WHERE" clause wasn't recognized.

    Indeed, when I write the following, it is OK :

    OPEN INFODM_CUR FOR
    select distinct r.numero, r.titre_dm, r.libelle_ss_ensemble
    from result_dm r, PONT_SUMO c
    where c.ID_OBJET =OBJET
    and r.NUMERO_om=43
    and c.APPLICATION = APPLICATION;

    and when I write the following, it is KO :

    OPEN INFODM_CUR FOR
    select distinct r.numero, r.titre_dm, r.libelle_ss_ensemble
    from result_dm r, PONT_SUMO c
    where c.ID_OBJET =OBJET
    and r.NUMERO_om=NUMERO
    and c.APPLICATION = APPLICATION;

    So I tried to change "NUMBER" into "to_number(NUMBER,'99999')", because NUMERO is of NUMBER(5) type, but it didn't work.

    Can you help me a last time on this subject?

    Thank you very much for helping me, it's very kind of you.

    Nadi

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Question

    Did you get any error?

    If r.NUMERO_om and NUMERO are numbers, no conversion is needed.

    If any of the two is NOT a number, Oracle will do automatic conversion.

    The one case where the query could produce incorrect results is when either value is NULL.

    You could try this:

    and NVL(r.NUMERO_om,0)=NVL(NUMERO,0)

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Aug 2003
    Posts
    4

    Smile

    Hi, I've just found a way to avoid my problem :

    instead of doing "and r.NUMERO_om=NUMERO" in my where clause, I do "and r.NUMERO_om=num", where num is defined as following :

    num RESULT_OM.NUMERO%type;
    num:=NUMERO;

    I don't know why, but it works now.

    Thank you for having accorded this time to me!

    Nadi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •