Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Posts
    53

    Unanswered: help! using 2 ref cursors result in error: ORA-04021?

    guys its me again, i have identified which part of my code that generates the error:
    here is my code:

    type mycursor is REF CURSOR;
    type mycursorattr is REF CURSOR;
    curs mycursor;
    cursattr mycursorattr;
    str varchar2(1000);
    str2 varchar2(1000);
    count number;
    str = "select * from....."
    str2 = "select * from....."

    OPEN curs for str;

    LOOP
    FETCH curs INTO ret;
    --just simple comparison
    IF (lastmidlog != ret.mid) THEN
    IF (lastmidlog IS NOT NULL) THEN
    count :=0;
    END IF;
    count :=110;
    ELSE
    count:=1;
    END IF;

    OPEN cursattr FOR str2;
    LOOP
    FETCH cursattr INTO retattr;
    --haven't even put my code
    exit when cursattr%NOTFOUND;
    END LOOP;
    CLOSE cursattr;


    exit when curs%NOTFOUND;
    END LOOP;
    CLOSE curs;


    see, if i only use 1 ref cursor here, the code runs just fine. but when i try to put another cursor onside the cursor, it goes hanged. well there are a lot of data there but i think it can be done given lots of time, but it jus cannot. it jus hanged there...is my code wrong? is it wrong to put 2 ref cursors in a code like this? pls help!

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    You forgot to post the Oracle version you are using.

    You also forgot to post the full text of your error.

    ORA_4021 is a timeout on a library cache object.

    While the session is hanging, check what it is waiting for in v$session_wait. You will need to format the output to make it readable.
    The wait events are documented in the Server Reference manual.

    There is a problem with some versions of Oracle9i where pl/sql clients hold onto library cache pins for long periods of time. But there isn't enough information here to determine if that is your problem.

  3. #3
    Join Date
    Nov 2003
    Posts
    53
    sorry,
    here's my oracle version:
    Oracle8i Enterprise Edition Release 8.1.7.3.0

    however when i am running the code it just hanged there, and its been 10 hours since i din't shut down my omputer. so my case now is no error displayed but hanged. then i am unable to compile or to do anything with the code...can someone tell me what i should do or where went wrong?

  4. #4
    Join Date
    Nov 2003
    Posts
    53
    btw this is my v$session_wait :
    but i don't know what does it means...hope it helps u guys to help me find my problem

    SQL> r
    1* select * from v$session_wait where sid='109'

    SID SEQ# EVENT P1TEXT P1 P1RAW
    ---------- ---------- --------------- ---------- ---------- ----------
    P2TEXT P2 P2RAW P3TEXT P3 P3RAW
    ---------- ---------- ---------- --------------- ---------- ----------------
    WAIT_TIME SECONDS_IN_WAIT STATE
    ---------- --------------- --------------------
    109 55966 db file sequent file# ########## 0000000000
    ial read 00003A
    block# 7965 0000000000 blocks ########## 0000000000000001
    001F1D
    -1 200 WAITED SHORT TIME

  5. #5
    Join Date
    Jan 2004
    Posts
    370
    Do a few queries (4 or 5) from v$session_wait, about 5 seconds apart.
    And try to format the output so we can read it.

  6. #6
    Join Date
    Nov 2003
    Posts
    53
    do u mean any query? can i put it as :
    select * from v$session_wait where sid='109';

    sorry for asking these simple questions.
    thanks

  7. #7
    Join Date
    Jan 2004
    Posts
    370
    If sid 109 is the hanging session then that is the one you want to look at.

    Try the following query.
    Run it 4 or 5 times, about 5 seconds apart.
    The format will probably be screwed but never mind.

    format col event a20
    select seq#, event, p1text, p1, p2text, p2, p3text, p3, wait_time, state
    from v$session_wait
    where sid=109;

  8. #8
    Join Date
    Nov 2003
    Posts
    53
    i ran ur code and i receive the same result. i run it 5 times.
    does it indicates something wrong?

    SEQ# EVENT P1TEXT P1 P2TEXT
    ---------- ------------------------ --------------- ---------- ---------------
    P2 P3TEXT P3 WAIT_TIME STATE
    ---------- --------------- ---------- ---------- -------------------
    1112 db file sequential read file# 58 block#
    7820 blocks 1 -1 WAITED SHORT TIME



    btw i have another question, referring to the code i first posted, i put the 'exit when curs%NOTFOUND;' statement after i put some code between.
    now i found out that it should be:
    1. open cursor
    2. fetch into ret
    3. exit when...
    4. code...
    is it a must to follow these procedure? does it means my problem of getting the code hanged is because of this?

  9. #9
    Join Date
    Jan 2004
    Posts
    370
    You got exactly the same result each time?
    Did seq# change?
    Did p1, p2, p3 change?

    I don't know if you the way you have coded this would cause a hang situation.
    I think it is best to discover what Oracle is waiting for and then looking to see what solutions are available.

  10. #10
    Join Date
    Nov 2003
    Posts
    53
    yeah i got the same result, as the same for seq#. but i ran the code just now and it is like this:
    SEQ# EVENT P1TEXT P1 P2TEXT
    ---------- ------------------------ --------------- ---------- ---------------
    P2 P3TEXT P3 WAIT_TIME STATE
    ---------- --------------- ---------- ---------- -------------------
    5434 latch free address 5.0440E+17 number
    66 tries 0 1 WAITED KNOWN TIME



    the seq# is different from just now but for the 5 time sthat i run it every 5 seconds, it is the same. so i presume it to change in a certain period.
    yeah this i got after i press ctrl+c when the compiling hanged. is it now free?

    i changed the 'exit when curs%NOTFOUND;' statement back to the correct way but it doesnt help.
    however, i tried my cursor again independantly(previously i ran 2 cursors). it seems that the second cursor will result the statement to hang:

    OPEN cursattr FOR str2;
    LOOP
    FETCH cursattr INTO retattr;
    --haven't even put my code
    exit when cursattr%NOTFOUND;
    END LOOP;
    CLOSE cursattr;

  11. #11
    Join Date
    Jan 2004
    Posts
    370
    There doesn't appear to be any consistency to the results - the wait event appears to change, so it can't be waiting for one single resource. I can't see your system, so I can't tell.

    Are you saying that the second cursor, when run on its own, causes the hang?

  12. #12
    Join Date
    Nov 2003
    Posts
    53
    yeah the 2nd cursor causes it i think....btw is it a correct way to put 2 cursors 2gether?and i doesnt see any problem the 2nd cursor...

  13. #13
    Join Date
    Jan 2004
    Posts
    370
    I don't know the answer to that question.
    If it is the second cursor causing the problem, maybe you just got to try coding round it somehow.

  14. #14
    Join Date
    Nov 2003
    Posts
    53
    well i'll try put it another way then, thanks for ur help skywriter!

Posting Permissions

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