PL/SQL print out ref cursor returned by a stored procedure
How can I fetch from a ref cursor that is returned from a stored procedure (OUT variable) and print the resulting rows to STDOUT in SQL*PLUS?
ORACLE stored procedure:
PROCEDURE GetGrantListByPI(p_firstname IN VARCHAR2, p_lastname IN VARCHAR2,
p_o开发者_开发问答rderby IN VARCHAR2, p_cursor OUT grantcur);
PL/SQL:
SET SERVEROUTPUT ON;
DECLARE
TYPE r_cursor IS REF CURSOR;
refCursor r_cursor;
CURSOR grantCursor IS
SELECT last_name, first_name
FROM ten_year_pis
WHERE year_added = 2010;
last_name VARCHAR2(100);
first_name VARCHAR2(100);
BEGIN
OPEN grantCursor;
FETCH grantCursor INTO last_name, first_name;
WHILE grantCursor%FOUND LOOP
PMAWEB_PKG.GetGrantListByPI(last_name, first_name, 'last_name', refCursor);
--HOW DO I LOOP THROUGH THE RETURNED REF CURSOR (refCursor)
--AND PRINT THE RESULTING ROWS TO STDOUT?
FETCH grantCursor into last_name, first_name;
END LOOP;
CLOSE grantCursor;
END;
/
Note: This code is untested
Define a record for your refCursor return type, call it rec. For example:
TYPE MyRec IS RECORD (col1 VARCHAR2(10), col2 VARCHAR2(20), ...); --define the record
rec MyRec; -- instantiate the record
Once you have the refcursor returned from your procedure, you can add the following code where your comments are now:
LOOP
FETCH refCursor INTO rec;
EXIT WHEN refCursor%NOTFOUND;
dbms_output.put_line(rec.col1||','||rec.col2||','||...);
END LOOP;
You can use a bind variable at the SQLPlus level to do this. Of course you have little control over the formatting of the output.
VAR x REFCURSOR;
EXEC GetGrantListByPI(args, :x);
PRINT x;
If you want to print all the columns in your select clause you can go with the autoprint command.
CREATE OR REPLACE PROCEDURE sps_detail_dtest(v_refcur OUT sys_refcursor)
AS
BEGIN
OPEN v_refcur FOR 'select * from dummy_table';
END;
SET autoprint on;
--calling the procedure
VAR vcur refcursor;
DECLARE
BEGIN
sps_detail_dtest(vrefcur=>:vcur);
END;
Hope this gives you an alternate solution
More easier option is to use DBMS_SQL.return_result();
Lets say your package / procedure / cursor spec is as below.
create or replace PACKAGE my_package IS
TYPE my_ref_cursor_type IS REF CURSOR;
PROCEDURE my_procedure (
p_in_param1 IN VARCHAR2,
p_in_param2 IN VARCHAR2,
p_in_param3 IN VARCHAR2,
p_my_ref_cursor OUT my_ref_cursor_type,
p_err_code OUT NUMBER,
p_err_msg OUT VARCHAR2
);
END my_package;
Try this to invoke the procedure from your sql developer WORKSHEET
SET SERVEROUTPUT ON;
DECLARE
P_MY_REF_CURSOR my_schema.my_package.my_ref_cursor_type;
P_ERR_CODE NUMBER;
P_ERR_MSG VARCHAR2(200);
BEGIN
my_package.my_procedure(
'VALUE1',
'VALUE2',
'VALUE3',
P_MY_REF_CURSOR => P_MY_REF_CURSOR,
P_ERR_CODE => P_ERR_CODE,
P_ERR_MSG => P_ERR_MSG
);
DBMS_OUTPUT.PUT_LINE(P_ERR_MSG);
DBMS_OUTPUT.PUT_LINE(P_ERR_CODE);
DBMS_SQL.return_result(P_MY_REF_CURSOR);
END;
Hope this helps !
There are many ways for displaying the sys_refcursor result set and one of them that is so easy is using SQL Developer to fetch sys_refcursor and print output which are:
- Create a test function to print its result
- Execute the function
- View the output
- Verify the result set
精彩评论