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 |
|
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 #660596 is a reply to message #660584] |
Tue, 21 February 2017 20:03 |
|
Barbara Boehmer
Messages: 9097 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 #660664 is a reply to message #660650] |
Thu, 23 February 2017 14:16 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
sdsmaster wrote on Thu, 23 February 2017 09:02Thanks, 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 |
|
Barbara Boehmer
Messages: 9097 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
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:38:00 CDT 2024
|