Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

performance Tuning 10g reference poster
 
Oracle training in Linux 

commands
 
Oracle training 

Excel
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> execute procedure
mcp_ora
post Apr 12 2012, 09:28 AM
Post #1


Newbie
*

Group: Members
Posts: 2
Joined: 12-April 12
Member No.: 47,073



How do i execute and display results of a procedure declared with ref cursor OUT parameter.

I am using SQL Developer and Oracle 10.2

Thanks
MCP
Go to the top of the page
 
+Quote Post
burleson
post Apr 12 2012, 10:14 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,089
Joined: 26-January 04
Member No.: 13



Hi MCP,

>> REf cursor as an OUT parameter

Good question! Becaue a ref cursor is a ;pointer to a array, you must have an equivalent data structure ready to accept the ref cursor and de-reference the ref cursor and accept the PL/SQL collection (array).

See below, cursor variables as parameters:

http://www.dba-oracle.com/plsql/t_plsql_cu..._parameters.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
mcp_ora
post Apr 18 2012, 08:40 AM
Post #3


Newbie
*

Group: Members
Posts: 2
Joined: 12-April 12
Member No.: 47,073



Thanks for the reply.

I have my procedure definition as below

PROCEDURE PRC_TRENDREPORT_PROMPT(po_Project OUT SYS_REFCURSOR,
po_riskfactor OUT SYS_REFCURSOR,
po_trial out sys_refcursor,
po_errnum OUT NUMBER,
po_errmsg OUT VARCHAR2) AS

And i am using below batch to fetch records and see in SQL developer

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_cursor sys_refcursor;
row_project DDIM_MV_Development_Project%ROWTYPE;
l_rf sys_refcursor;
row_riskfactor dtap_ref_risk_factor%ROWTYPE;
l_tr sys_refcursor;
row_trail DTAP_Prompt_TRIAL%ROWTYPE;
errnum number;
errmsg varchar2(10);
BEGIN
PKG_REPORT.PRC_TRENDREPORT_PROMPT (
po_Project => l_cursor,
po_riskfactor => l_rf,
po_trial => l_tr,
po_errnum => errnum,
po_errmsg => errmsg);

LOOP
FETCH l_cursor
INTO row_project;
EXIT WHEN l_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(row_project.project_sk || ' : ' || row_project.al_project_nm);
END LOOP;
CLOSE l_cursor;
END;


When i execute, below error being shown

Error report:
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
ORA-06512: at line 19
06504. 00000 - "PL/SQL: Return types of Result Set variables or query do not match"
*Cause: Number and/or types of columns in a query does not match declared
return type of a result set variable, or declared types of two Result
Set variables do not match.
*Action: Change the program statement or declaration. Verify what query the variable
actually refers to during execution.

Could you please help in fix the issue.
Go to the top of the page
 
+Quote Post
burleson
post Apr 18 2012, 09:12 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,089
Joined: 26-January 04
Member No.: 13



Hi,

A REF CURSOR or cursor variable is just a reference or a handle to a static cursor. The ref cursor is a pointer to a structure, so you need to "match" the sys_refcursor type in your code.

Make your receiving end code be a refcursor also?

Please read:

http://sql-plsql.blogspot.com/2007/05/orac...ef-cursors.html

I'm not a PL/SQL expert, but I found this for you:

http://www.google.com/search?sourceid=ie7&...1I7SNYR_enUS316

https://forums.oracle.com/forums/thread.jsp...90&tstart=0



PLease post the funal solution once you get it working.


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
burleson
post Apr 18 2012, 09:48 AM
Post #5


Advanced Member
***

Group: Members
Posts: 11,089
Joined: 26-January 04
Member No.: 13



Hi MCP,

Good Niew! I found you a working example:

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


Just note:

The number of values (3) match, and the OUT and IN are both of type sys_refcursor.

When you get yours working, please post the solution!


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
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: 19th April 2014 - 01:52 AM