execute procedure Options

[mcp_ora]

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

[burleson]

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

[mcp_ora]

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.

[burleson]

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.

[burleson]

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!