Welcome Guest ( Log In | Register )



Performance Tuning Reference poster
Oracle training in Linux 

Oracle training Weblogic Book
Easy Oracle Jumpstart
Oracle training & performance tuning books
Burleson Consulting Remote DB Administration
Reply to this topicStart new topic
> Fetch out of sequence error
post Jan 3 2017, 06:03 AM
Post #1


Group: Members
Posts: 1
Joined: 3-January 17
Member No.: 51,725

I have a procedure with 5 sysrefcursors as out parameter.
I am now trying to add 6th sys recursor and assign the first one to 6th one.

Is it possible to assign one sysrefcursor to other directly.

Any other alternative for this?
Go to the top of the page
+Quote Post
post Jan 3 2017, 05:00 PM
Post #2

Advanced Member

Group: Members
Posts: 85
Joined: 6-June 16
From: India
Member No.: 51,370

Hello Rakesh,

Your question "Fetch out of sequence error" does not have anything to do with your clarification.

You can assign one Ref Cursor to another, but why do you have to do that?

Consider the below example,

l_rc_var1 sys_refcursor;
l_rc_var2 sys_refcursor;
l_vc_var3 VARCHAR2(1);
OPEN l_rc_var1 FOR SELECT * FROM dual;
FETCH l_rc_var2 INTO l_vc_var3;
CLOSE l_rc_var2;


Error report -
ORA-01001: invalid cursor
ORA-06512: at line 11
01001. 00000 - "invalid cursor"

Here, I have assigned the sys_refcursor L_RC_VAR1 to L_RC_VAR2 variable. Now, both has the same cursor ID internally.

You can open L_RC_VAR2 and internally L_RC_VAR1 also gets opened.

If you close L_RC_VAR2, internally L_RC_VAR1 also gets closed. So, its just a mirror copy, which I dont feel of any use.

Please post your requirement for adding a new sys ref cursor OUT parameter, so that we can see what is the best way to do it.

Also, if you are unsure about the number of Ref cursor OUT parameters, you can very well use the implicit result sets technique (12c and later only). Excerpts given below,



Thank you,
Boobal Ganesan

Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:


Lo-Fi Version Time is now: 20th October 2017 - 09:13 AM