Executing a procedure in oracle with multiple cursors as output

Question

How to execute a procedure having multiple cursors as output in oracle.

CREATE OR REPLACE PROCEDURE TC_OWNER.usp_GetGEGAllDataBySecurityID(
p_SecurityID NUMBER,
cur1 OUT sys_refcursor,
cur2 OUT sys_refcursor,
cur3 OUT sys_refcursor,
cur4 OUT sys_refcursor,
cur5 OUT sys_refcursor)
AS 

v_EffectiveDate TIMESTAMP(3);
v_CompanyID NUMBER(10);
BEGIN

SELECT MAX(EffectiveStartDate) INTO v_EffectiveDate
FROM    tblGEGSecurityDtls
WHERE    SecurityId = p_SecurityID AND SYSDATE BETWEEN EffectiveStartDate 
and EffectiveEndDate;


SELECT  CompanyID INTO v_CompanyID
FROM    tblGEGSecurityDtls
WHERE    SecurityId = p_SecurityID AND EffectiveStartDate = 
v_EffectiveDate;

usp_GetGEGSecurityDtls(p_SecurityID,cur1);
usp_GetGEGRecommendations(p_SecurityID,cur2);
usp_GetGEGCompanyDtls(v_CompanyID,cur3);
usp_GetGEGSectorRegionData(v_CompanyID,null,cur4);
usp_GetGEGCompanyDivisionData(v_CompanyID,null,cur5);

END;

Here anything starting with usp_ denotes a procedure. Each of the procedure returns a table.

The same can easily be achieved in sql but i am unable to execute this in oracle.

Edit : As per the answer given by vc74 i tried the below code to print all the five tables but it is throwing error:

declare
lcur1 sys_refcursor;
lcur2 sys_refcursor; 
lcur3 sys_refcursor; 
lcur4 sys_refcursor; 
lcur5 sys_refcursor; 
begin
usp_GetGEGAllDataBySecurityID(
    p_SecurityID => 457, 
    cur1 => lcur1, 
    cur2 => lcur2,
    cur3 => lcur3,
    cur4 => lcur4,
    cur5 => lcur5
);
end;
print lcur1;
print lcur2;
print lcur3;
print lcur4;
print lcur5;

How to print all the tables in the output window?

Execute form where - are you just running/testing it from a client like SQL\*Plus or SQL Developer (and if so which one)? [Possibly related, if so](https://stackoverflow.com/q/8610495/266304). Or calling it from an application - over JDBC, .net, something else? — Alex Poole, Jan 25 '19 at 13:37

vc 74 · Accepted Answer · 2019-01-25T14:02:06.150

Things are less trivial in Oracle, you have to explicitly declare the cursors. To display the cursors in grids in Toad.

variable outer_cur1 refcursor
variable outer_cur2 refcursor
variable outer_cur3 refcursor
variable outer_cur4 refcursor
variable outer_cur5 refcursor

declare
    inner_cur1 sys_refcursor;
    inner_cur2 sys_refcursor; 
    inner_cur3 sys_refcursor; 
    inner_cur4 sys_refcursor; 
    inner_cur5 sys_refcursor; 
begin
    pkg_cur1s.get(
        pnum_scen_id => 671, 
        pcsr_cur1 => inner_cur1, 
        pcsr_cur2 => inner_cur2,
        pcsr_cur3 => inner_cur3,
        pcsr_cur4 => inner_cur4,
        pcsr_cur5 => inner_cur5
    );

    :outer_cur1 := inner_cur1;
    :outer_cur2 := inner_cur2;
    :outer_cur3 := inner_cur3;
    :outer_cur4 := inner_cur4;
    :outer_cur5 := inner_cur5;
end;

print outer_cur1
print outer_cur2
print outer_cur3
print outer_cur4
print outer_cur5

But how to see the five tables data in the output window.Is there any command for that?Could you please edit the code for printing the output in the output window. — vikky, Jan 25 '19 at 13:53

Executing a procedure in oracle with multiple cursors as output

1 Answers1