Home » SQL & PL/SQL » SQL & PL/SQL » Declare a cursor from the return of a procedure?
Declare a cursor from the return of a procedure? [message #660584] Tue, 21 February 2017 09:53 Go to next message
sdsmaster
Messages: 2
Registered: February 2017
Junior Member
I'm writing a query that needs to be used in two ways:
1 it needs to be a ref cursor output of a procedure (passed to a .NET procedure, I can't change this requirement) and
2 I need to check the results and determine the output of a field based on the results.

What I have now is a procedure that accepts inputs and puts out a ref cursor. I then declare a cursor with the same columns as the ref cursor that I create so I can just hold on to the data.

Is there some more efficient way to accomplish this? I'm thinking like "curosr x is package.procedure(input => value, output => cursorvariable);", but that doesn't seem plausible. I've read about declaring a cursor at the package level and then calling it in two different procedures. Will that work for me? Can I open the cursor into a ref cursor and have _all_ the rows, not just the first row populate?

simplified code that I have now is below:
---------------------------------------------------------------------------
  PROCEDURE MATCH_APPNO (  INPUT1          IN     VARCHAR2 DEFAULT NULL
                          ,INPUT2          IN     VARCHAR2 DEFAULT NULL
                          ,C_Table            OUT sp_cursor)
                          
   IS
   BEGIN
     OPEN C_Table FOR
       SELECT
		 PERSON 
         APPNO
		 TERM
		 PROGRAM
      FROM
         TABLE1
      WHERE 
	     TABLE1_COL1 = INPUT1
		 TABLE1_COL2 = INPUT2
	  ORDER BY APPNO DESC
       
       UNION ALL

       SELECT
         NULL              as PERSON
        ,'New Application' as APPNO
        ,null              as TERM
        ,null              as PROGRAM
      FROM
       DUAL
       ;
     
   END  MATCH_APPNO;    
------------------------------------------------------------------------------------------ 
  procedure FILL_MATCHED_APPNO (INPUT1_IN        IN     VARCHAR2 DEFAULT NULL
                               ,INPUT2_IN        IN     VARCHAR2 DEFAULT NULL
                               ,APPNO               OUT VARCHAR2)
  IS
  
  APPROW sp_cursor;
  
  -- Can I declare this cursor as the output of the above procedure?
  cursor abeyance is
  
       SELECT
         NULL as PERSON
        ,null as APPNO
        ,null as TERM
        ,null as PROGRAM
      FROM dual;
  
  APPROWhold abeyance%rowtype;
  
  varrowcount number;
  varappno varchar2(2);
  
  PERSON_OUT varchar2(4000);
  TERM_OUT varchar2(4000);
  PROGRAM_OUT varchar2(4000);

  
  BEGIN
    TRANSFORMS_SCSU.MATCH_APPNO (  	 INPUT1 => INPUT1_IN
				    ,INPUT2 => INPUT2_IN 
				 ,C_Table   => APPROW);
    --the first iteration is data that I want      
    FETCH APPROW 
    INTO PERSON_OUT
        ,APPNO
        ,TERM_OUT
        ,PROGRAM_OUT;
    
    FETCH APPROW 
    INTO APPROWhold;
    
	--if I have a third result set, I know that my query had more than two returns
	--and in that case, I want to put a flag out in the Appno field.
    FETCH APPROW 
    INTO APPROWhold;
    
     IF APPROW%FOUND THEN
       APPNO := 'Flag';
     ELSE
       APPNO := APPNO;
     END IF;
    
    CLOSE APPROW;
	
      
  END FILL_MATCHED_APPNO;
---------------------------------------------------------------------------
end code

My goal here is to make things really modular, so I could change the query in one place and not have to maintain the code in the other procedure(s?).

All help is appreciated. Thanks!


[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Tue, 21 February 2017 16:41] by Moderator

Report message to a moderator

Re: Declare a cursor from the return of a procedure? [message #660586 is a reply to message #660584] Tue, 21 February 2017 09:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Re: Declare a cursor from the return of a procedure? [message #660588 is a reply to message #660586] Tue, 21 February 2017 09:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Declare a cursor from the return of a procedure? [message #660593 is a reply to message #660588] Tue, 21 February 2017 10:28 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is the appno parameter for the use of the .net code?
Cause if it is the the .net code should just check the contents of the ref cursor itself.
Re: Declare a cursor from the return of a procedure? [message #660596 is a reply to message #660584] Tue, 21 February 2017 20:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The following demonstration represents my best understanding of what you want.

-- table and data for demonstration:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE table1
  2    ( table1_col1  VARCHAR2(15)
  3    , table1_col2  VARCHAR2(15)
  4    , appno	      VARCHAR2(15)
  5    , person       VARCHAR2(10)
  6    , term	      VARCHAR2(10)
  7    , program      VARCHAR2(10))
  8  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO table1 VALUES ('A', 'B', 'APP1', 'person1', 'term1', 'program1')
  3  INTO table1 VALUES ('C', 'D', 'APP2', 'person2', 'term2', 'program2')
  4  INTO table1 VALUES ('C', 'D', 'APP3', 'person3', 'term3', 'program3')
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM table1
  2  /

TABLE1_COL1     TABLE1_COL2     APPNO           PERSON     TERM       PROGRAM
--------------- --------------- --------------- ---------- ---------- ----------
A               B               APP1            person1    term1      program1
C               D               APP2            person2    term2      program2
C               D               APP3            person3    term3      program3

3 rows selected.

-- package specification:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE transforms_scsu
  2  AS
  3    TYPE sp_row IS RECORD
  4  	 ( person   table1.person%TYPE
  5  	 , appno    table1.appno%TYPE
  6  	 , term     table1.term%TYPE
  7  	 , program  table1.program%TYPE);
  8    TYPE sp_cursor IS REF CURSOR RETURN sp_row;
  9    PROCEDURE match_appno
 10  	 ( input1	   IN	  VARCHAR2 DEFAULT NULL
 11  	 , input2	   IN	  VARCHAR2 DEFAULT NULL
 12  	 , c_table	   OUT	  sp_cursor);
 13    PROCEDURE fill_matched_appno
 14  	 ( input1_in	    IN	   VARCHAR2 DEFAULT NULL
 15  	 , input2_in	    IN	   VARCHAR2 DEFAULT NULL
 16  	 , appno	    OUT    VARCHAR2);
 17  END TRANSFORMS_SCSU;
 18  /

Package created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.

-- package body:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE BODY transforms_scsu
  2  AS
  3    PROCEDURE match_appno
  4  	 ( input1	   IN	  VARCHAR2 DEFAULT NULL
  5  	 , input2	   IN	  VARCHAR2 DEFAULT NULL
  6  	 , c_table	   OUT	  sp_cursor)
  7    IS
  8    BEGIN
  9  	 OPEN c_table FOR
 10  	   SELECT person, appno, term, program
 11  	   FROM   table1
 12  	   WHERE  table1_col1 = input1
 13  	   AND	  table1_col2 = input2
 14  	   UNION ALL
 15  	   SELECT NULL			      AS person
 16  		, 'New Application'	      AS appno
 17  		, NULL			      AS term
 18  		, NULL			      AS program
 19  	   FROM   DUAL
 20  	   ORDER  BY appno DESC;
 21    END match_appno;
 22    --
 23    PROCEDURE fill_matched_appno
 24  	 ( input1_in	    IN	   VARCHAR2 DEFAULT NULL
 25  	 , input2_in	    IN	   VARCHAR2 DEFAULT NULL
 26  	 , appno	    OUT    VARCHAR2)
 27    IS
 28  	 abeyance		   sp_cursor;
 29  	 approwhold		   abeyance%ROWTYPE;
 30  	 person_out		   table1.person%TYPE;
 31  	 term_out		   table1.term%TYPE;
 32  	 program_out		   table1.program%TYPE;
 33  	 v_count		   NUMBER := 0;
 34    BEGIN
 35  	 match_appno
 36  	   ( input1  => input1_in
 37  	   , input2  => input2_in
 38  	   , c_table => abeyance);
 39  	 FETCH abeyance INTO person_out, appno, term_out, program_out;
 40  	 v_count := v_count + 1;
 41  	 LOOP
 42  	   FETCH abeyance INTO approwhold;
 43  	   EXIT WHEN abeyance%NOTFOUND OR v_count >= 3;
 44  	   v_count := v_count + 1;
 45  	 END LOOP;
 46  	 CLOSE abeyance;
 47  	 DBMS_OUTPUT.PUT_LINE (v_count);
 48  	 IF v_count >= 3 THEN appno := 'Flag';
 49  	 END IF;
 50    END FILL_MATCHED_appno;
 51  END TRANSFORMS_SCSU;
 52  /

Package body created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.

-- sample executions of packaged procedure that returns ref cursor:
SCOTT@orcl_12.1.0.2.0> VARIABLE g_refcur REFCURSOR
SCOTT@orcl_12.1.0.2.0> EXEC transforms_scsu.match_appno ('A', 'B', :g_refcur)

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT g_refcur

PERSON     APPNO           TERM       PROGRAM
---------- --------------- ---------- ----------
           New Application
person1    APP1            term1      program1

2 rows selected.

SCOTT@orcl_12.1.0.2.0> EXEC transforms_scsu.match_appno ('C', 'D', :g_refcur)

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT g_refcur

PERSON     APPNO           TERM       PROGRAM
---------- --------------- ---------- ----------
           New Application
person3    APP3            term3      program3
person2    APP2            term2      program2

3 rows selected.

-- sample executions of packaged procedure that executes above procedure,
-- then fetches some values of the returned cursor:
SCOTT@orcl_12.1.0.2.0> VARIABLE g_appno VARCHAR2(15)
SCOTT@orcl_12.1.0.2.0> EXEC transforms_scsu.fill_matched_appno ('A', 'B', :g_appno)
2

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT g_appno

G_APPNO
--------------------------------------------------------------------------------
New Application

SCOTT@orcl_12.1.0.2.0> EXEC transforms_scsu.fill_matched_appno ('C', 'D', :g_appno)
3

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT g_appno

G_APPNO
--------------------------------------------------------------------------------
Flag

SCOTT@orcl_12.1.0.2.0> 
Re: Declare a cursor from the return of a procedure? [message #660650 is a reply to message #660596] Thu, 23 February 2017 11:02 Go to previous messageGo to next message
sdsmaster
Messages: 2
Registered: February 2017
Junior Member
Thanks, Barbara!!

So what I'm taking away from this is,
1 I really should declare a rowtype for the return in the fill_matched_appno procedure, no matter how I solve the puzzle.
2 When a sp_cursor is strongly defined (am I using the right vocab?), I can declare a rowtype of that cursor. But without that definition, I can't declare a rowtype of that cursor.
3 The rowtype I create has to be hard coded, it can't be variable, or based on the result of a procedure.

Am I getting anything wrong there?

Is there a more elegant way to pull just the appno value out of the return? If I define the rowtype of the sp_cursor, can I tell it to "just pull the value for appno from the open cursor"?

Thanks again!!
Re: Declare a cursor from the return of a procedure? [message #660659 is a reply to message #660650] Thu, 23 February 2017 13:45 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Read up on REF_CURSOR. It's what you want
Re: Declare a cursor from the return of a procedure? [message #660664 is a reply to message #660650] Thu, 23 February 2017 14:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
sdsmaster wrote on Thu, 23 February 2017 09:02
Thanks, Barbara!!

So what I'm taking away from this is,
1 I really should declare a rowtype for the return in the fill_matched_appno procedure, no matter how I solve the puzzle.
2 When a sp_cursor is strongly defined (am I using the right vocab?), I can declare a rowtype of that cursor. But without that definition, I can't declare a rowtype of that cursor.
3 The rowtype I create has to be hard coded, it can't be variable, or based on the result of a procedure.

Am I getting anything wrong there?

Is there a more elegant way to pull just the appno value out of the return? If I define the rowtype of the sp_cursor, can I tell it to "just pull the value for appno from the open cursor"?

Thanks again!!
You could return a ref cursor by just using SYS_REFCURSOR, without declaring a record type or rowtype and you could even fetch into individual column variables, but to fetch into other things from the same cursor, you need a strongly typed ref cursor, based on a record type or table%rowtype or some such thing. If your type returned will always be the same as some table, then you can just use table%rowtype with the appropriate table name. If your type returned will always have the same number of columns, then you could use a static record declaration. Otherwise, every time that you change the query, you will need to change the corresponding columns in the record type declaration. If all you need from the fill_matched_appno procedure is the first appno and the flag if there are 3 or more rows in the cursor, then there are more elegant ways to do that. Please see the revised demonstration that fetches bulk collect into a table of the rowtype of the strong ref cursor. This allows you to then select just the first appno and the count from that. Just to be complete, I should probably mention that there are some complex generic methods using dbms_sql that might be able to use to extract things from a sys_refcursor or weak type ref cursor. Also, I am an Oracle programmer, not a .net programmer, so there may be ways to get what you want from .net instead of Oracle.

SCOTT@orcl_12.1.0.2.0> -- environment:
SCOTT@orcl_12.1.0.2.0> SELECT banner FROM v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

5 rows selected.

SCOTT@orcl_12.1.0.2.0> -- table and data for demonstration:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE table1
  2    ( table1_col1  VARCHAR2(15)
  3    , table1_col2  VARCHAR2(15)
  4    , appno	      VARCHAR2(15)
  5    , person       VARCHAR2(10)
  6    , term	      VARCHAR2(10)
  7    , program      VARCHAR2(10))
  8  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO table1 VALUES ('A', 'B', 'APP1', 'person1', 'term1', 'program1')
  3  INTO table1 VALUES ('C', 'D', 'APP2', 'person2', 'term2', 'program2')
  4  INTO table1 VALUES ('C', 'D', 'APP3', 'person3', 'term3', 'program3')
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM table1
  2  /

TABLE1_COL1     TABLE1_COL2     APPNO           PERSON     TERM       PROGRAM
--------------- --------------- --------------- ---------- ---------- ----------
A               B               APP1            person1    term1      program1
C               D               APP2            person2    term2      program2
C               D               APP3            person3    term3      program3

3 rows selected.

SCOTT@orcl_12.1.0.2.0> -- package specification:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE transforms_scsu
  2  AS
  3  -- make changes to record type here:
  4    TYPE sp_row IS RECORD
  5  	 ( person   table1.person%TYPE
  6  	 , appno    table1.appno%TYPE
  7  	 , term     table1.term%TYPE
  8  	 , program  table1.program%TYPE);
  9    TYPE sp_cursor IS REF CURSOR RETURN sp_row;
 10    PROCEDURE match_appno
 11  	 ( input1	   IN	  VARCHAR2 DEFAULT NULL
 12  	 , input2	   IN	  VARCHAR2 DEFAULT NULL
 13  	 , c_table	   OUT	  sp_cursor);
 14    PROCEDURE fill_matched_appno
 15  	 ( input1_in	    IN	   VARCHAR2 DEFAULT NULL
 16  	 , input2_in	    IN	   VARCHAR2 DEFAULT NULL
 17  	 , appno	    OUT    VARCHAR2);
 18  END TRANSFORMS_SCSU;
 19  /

Package created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> -- package body:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE BODY transforms_scsu
  2  AS
  3    PROCEDURE match_appno
  4  	 ( input1	   IN	  VARCHAR2 DEFAULT NULL
  5  	 , input2	   IN	  VARCHAR2 DEFAULT NULL
  6  	 , c_table	   OUT	  sp_cursor)
  7    IS
  8    BEGIN
  9  	 OPEN c_table FOR
 10  -- make changes to query here:
 11  	   SELECT person, appno, term, program
 12  	   FROM   table1
 13  	   WHERE  table1_col1 = input1
 14  	   AND	  table1_col2 = input2
 15  	   UNION ALL
 16  	   SELECT NULL			      AS person
 17  		, 'New Application'	      AS appno
 18  		, NULL			      AS term
 19  		, NULL			      AS program
 20  	   FROM   DUAL
 21  	   ORDER  BY appno DESC;
 22    END match_appno;
 23    --
 24    PROCEDURE fill_matched_appno
 25  	 ( input1_in	    IN	   VARCHAR2 DEFAULT NULL
 26  	 , input2_in	    IN	   VARCHAR2 DEFAULT NULL
 27  	 , appno	    OUT    VARCHAR2)
 28    IS
 29  	 abeyance		   sp_cursor;
 30  	 TYPE approwtab IS TABLE OF abeyance%ROWTYPE;
 31  	 approwhold		   approwtab;
 32    BEGIN
 33  	 match_appno
 34  	   ( input1  => input1_in
 35  	   , input2  => input2_in
 36  	   , c_table => abeyance);
 37  	 FETCH abeyance BULK COLLECT INTO approwhold;
 38  	 appno := approwhold(1).appno;
 39  	 IF approwhold.COUNT >= 3 THEN appno := 'Flag';
 40  	 END IF;
 41  	 CLOSE abeyance;
 42    END FILL_MATCHED_appno;
 43  END TRANSFORMS_SCSU;
 44  /

Package body created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> -- execution of packaged procedure that returns ref cursor:
SCOTT@orcl_12.1.0.2.0> VARIABLE g_refcur REFCURSOR
SCOTT@orcl_12.1.0.2.0> EXEC transforms_scsu.match_appno ('A', 'B', :g_refcur)

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT g_refcur

PERSON     APPNO           TERM       PROGRAM
---------- --------------- ---------- ----------
           New Application
person1    APP1            term1      program1

2 rows selected.

SCOTT@orcl_12.1.0.2.0> EXEC transforms_scsu.match_appno ('C', 'D', :g_refcur)

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT g_refcur

PERSON     APPNO           TERM       PROGRAM
---------- --------------- ---------- ----------
           New Application
person3    APP3            term3      program3
person2    APP2            term2      program2

3 rows selected.

SCOTT@orcl_12.1.0.2.0> -- execution of packaged procedure that executes above procedure,
SCOTT@orcl_12.1.0.2.0> -- then fetches some values of the returned cursor:
SCOTT@orcl_12.1.0.2.0> VARIABLE g_appno VARCHAR2(15)
SCOTT@orcl_12.1.0.2.0> EXEC transforms_scsu.fill_matched_appno ('A', 'B', :g_appno)

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT g_appno

G_APPNO
--------------------------------------------------------------------------------
New Application

SCOTT@orcl_12.1.0.2.0> EXEC transforms_scsu.fill_matched_appno ('C', 'D', :g_appno)

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT g_appno

G_APPNO
--------------------------------------------------------------------------------
Flag

SCOTT@orcl_12.1.0.2.0> 



[Updated on: Thu, 23 February 2017 14:24]

Report message to a moderator

Re: Declare a cursor from the return of a procedure? [message #660669 is a reply to message #660664] Thu, 23 February 2017 15:07 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Here is another example using SYS_REFCURSOR with no record declaration. So, you could change the query without changing anything else, as long as there is still an APPNO amongst the column names in the returned rows of the cursor. You don't even need a package, although you could put the two procedures in a package. I think this may be closer to what you are looking for. There may also be others who can suggest other methods.

SCOTT@orcl_12.1.0.2.0> -- environment:
SCOTT@orcl_12.1.0.2.0> SELECT banner FROM v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

5 rows selected.

SCOTT@orcl_12.1.0.2.0> -- table and data for demonstration:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE table1
  2    ( table1_col1  VARCHAR2(15)
  3    , table1_col2  VARCHAR2(15)
  4    , appno	      VARCHAR2(15)
  5    , person       VARCHAR2(10)
  6    , term	      VARCHAR2(10)
  7    , program      VARCHAR2(10))
  8  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO table1 VALUES ('A', 'B', 'APP1', 'person1', 'term1', 'program1')
  3  INTO table1 VALUES ('C', 'D', 'APP2', 'person2', 'term2', 'program2')
  4  INTO table1 VALUES ('C', 'D', 'APP3', 'person3', 'term3', 'program3')
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM table1
  2  /

TABLE1_COL1     TABLE1_COL2     APPNO           PERSON     TERM       PROGRAM
--------------- --------------- --------------- ---------- ---------- ----------
A               B               APP1            person1    term1      program1
C               D               APP2            person2    term2      program2
C               D               APP3            person3    term3      program3

3 rows selected.

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE match_appno
  2  	 ( input1	   IN	  VARCHAR2 DEFAULT NULL
  3  	 , input2	   IN	  VARCHAR2 DEFAULT NULL
  4  	 , c_table	   OUT	  SYS_REFCURSOR)
  5    IS
  6    BEGIN
  7  	 OPEN c_table FOR
  8  	   SELECT person, appno, term, program
  9  	   FROM   table1
 10  	   WHERE  table1_col1 = input1
 11  	   AND	  table1_col2 = input2
 12  	   UNION ALL
 13  	   SELECT NULL			      AS person
 14  		, 'New Application'	      AS appno
 15  		, NULL			      AS term
 16  		, NULL			      AS program
 17  	   FROM   DUAL
 18  	   ORDER  BY appno DESC;
 19    END match_appno;
 20  /

Procedure created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE fill_matched_appno
  2  	 ( input1_in	    IN	   VARCHAR2 DEFAULT NULL
  3  	 , input2_in	    IN	   VARCHAR2 DEFAULT NULL
  4  	 , appno	    OUT    VARCHAR2)
  5    IS
  6  	 abeyance		   SYS_REFCURSOR;
  7  	 v_count		   NUMBER;
  8    BEGIN
  9  	 match_appno
 10  	   ( input1  => input1_in
 11  	   , input2  => input2_in
 12  	   , c_table => abeyance);
 13  	 SELECT appnum, cnt
 14  	 INTO	appno, v_count
 15  	 FROM	(SELECT TRIM (COLUMN_VALUE) appnum, COUNT (*) OVER (PARTITION BY NULL) cnt
 16  		 FROM	XMLTABLE ('//APPNO/text()' PASSING XMLTYPE (abeyance)))
 17  	 WHERE	ROWNUM = 1;
 18  	 IF v_count >= 3 THEN appno := 'Flag'; END IF;
 19  	 CLOSE abeyance;
 20    END FILL_MATCHED_appno;
 21  /

Procedure created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> -- execution of procedure that returns ref cursor:
SCOTT@orcl_12.1.0.2.0> VARIABLE g_refcur REFCURSOR
SCOTT@orcl_12.1.0.2.0> EXEC match_appno ('A', 'B', :g_refcur)

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT g_refcur

PERSON     APPNO           TERM       PROGRAM
---------- --------------- ---------- ----------
           New Application
person1    APP1            term1      program1

2 rows selected.

SCOTT@orcl_12.1.0.2.0> EXEC match_appno ('C', 'D', :g_refcur)

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT g_refcur

PERSON     APPNO           TERM       PROGRAM
---------- --------------- ---------- ----------
           New Application
person3    APP3            term3      program3
person2    APP2            term2      program2

3 rows selected.

SCOTT@orcl_12.1.0.2.0> -- execution of procedure that executes above procedure,
SCOTT@orcl_12.1.0.2.0> -- then fetches some values of the returned cursor:
SCOTT@orcl_12.1.0.2.0> VARIABLE g_appno VARCHAR2(15)
SCOTT@orcl_12.1.0.2.0> EXEC fill_matched_appno ('A', 'B', :g_appno)

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT g_appno

G_APPNO
--------------------------------------------------------------------------------
New Application

SCOTT@orcl_12.1.0.2.0> EXEC fill_matched_appno ('C', 'D', :g_appno)

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT g_appno

G_APPNO
--------------------------------------------------------------------------------
Flag

SCOTT@orcl_12.1.0.2.0> 

[Updated on: Thu, 23 February 2017 15:12]

Report message to a moderator

Previous Topic: Update based on grouping
Next Topic: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Goto Forum:
  


Current Time: Fri Mar 29 06:22:29 CDT 2024