CODEWITHSUNDEEP
oracle-database
vikky
vikky

Reputation: 171

Executing a procedure in oracle with multiple cursors as output

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?

Upvotes: 0

Views: 520

Answers (1)

vc 74
vc 74

Reputation: 38179

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

Upvotes: 1

Related Questions