0

I have 2 packages and a procedures that return a sysref cursor. Now i want to call either of the 2 packages and procedures in another package and procedure that will take the decision on an input column as to which one needs to be called.

however accordingly the output of the inner proc has to go as the output of the outer proc. Can you please throw some light as how to do this..

CREATE OR REPLACE PACKAGE DEDUPE_PACKAGE_abc IS
  TYPE refCursor IS REF CURSOR;
  PROCEDURE ADD_CAF_abc(
 IN_CUSTOMER_TYPE         IN VARCHAR2 ,
 retCursor_abc  IN OUT refCursor );

CREATE OR REPLACE PACKAGE DEDUPE_PACKAGE_pqr IS
  TYPE refCursor IS REF CURSOR;
  PROCEDURE ADD_CAF_prq(
 IN_CUSTOMER_TYPE         IN VARCHAR2 ,
 retCursor_pqr  IN OUT refCursor );


CREATE OR REPLACE PACKAGE DEDUPE_PACKAGE_main IS
  TYPE refCursor IS REF CURSOR;
  PROCEDURE ADD_CAF_main(
 IN_CUSTOMER_TYPE         IN VARCHAR2 ,
 retCursor_main  IN OUT refCursor );

BEGIN

if IN_CUSTOMER_TYPE == something then
   exec  dedupe_package_abc.ADD_CAF_abc ; 
   -- i need the output of the cursor here
    return ; 
else  IN_CUSTOMER_TYPE == something2 then
   exec  dedupe_package_pqr.ADD_CAF_pqr ; 
   -- i need the output of the cursor here
    return ; 

return -- i need to return the data from the retcursor_abc or the retcursor_pqr into the retrursor_main ...
myloginid
  • 1,463
  • 2
  • 22
  • 37
  • Is there a reason that you are defining three different types all named `refCursor` rather than just defining the parameter as a `sys_refcursor`? Is there a reason that your cursors are defined as `IN OUT` rather than `OUT`? Are you really expecting to be passing a cursor in to those procedures?? – Justin Cave Aug 21 '14 at 14:39
  • The type of the cursor is sysrefcursor. The name can be anything. All the cursors are out only. Just that the definition of the cursors was given as IN OUT in most documentation and i followed that.. – myloginid Aug 21 '14 at 17:56
  • In the code snippets you posted, you declare three different types (`DEDUPE_PACKAGE_abc.refCursor`, `DEDUPE_PACKAGE_pqr.refCursor`, and `DEDUPE_PACKAGE_main.refCursor`) rather than using `sys_refcursor`. Are you saying that in the actual code, those parameters are of type `sys_refcursor` rather than one of the three user-defined types? – Justin Cave Aug 21 '14 at 19:10
  • hi, i have edited the code to show what i need to do and changed the name of the retcusrors – myloginid Aug 22 '14 at 09:39
  • I got the answer here.. http://stackoverflow.com/questions/4644613/returning-a-cursor-from-an-inner-procedure-to-outer-procedure-in-oracle-pl-sql Thanks... Manish – myloginid Aug 22 '14 at 10:09

0 Answers0