Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
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
rakeshpgowda
post Jan 3 2017, 06:03 AM
Post #1


Newbie
*

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
boobal_ganesan
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,

CODE
DECLARE
l_rc_var1 sys_refcursor;
l_rc_var2 sys_refcursor;
l_vc_var3 VARCHAR2(1);
BEGIN
OPEN l_rc_var1 FOR SELECT * FROM dual;
l_rc_var2:=l_rc_var1;
FETCH l_rc_var2 INTO l_vc_var3;
dbms_output.put_line(l_vc_var3);
CLOSE l_rc_var2;
dbms_output.put_line(l_rc_var1%rowcount);
END;
/

Result:
X

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,

http://www.dba-oracle.com/t_adv_plsql_impl...result_sets.htm

http://www.dba-oracle.com/t_adv_plsql_get_next_result.htm


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