I am trying to print the output of the ref cursor and I used
variable x refcursor
execute sp_create_portal_dummy_users(100025,10,:x);
print x;
Can I use dbms_output.put_line instead of print x here ?
Kindly suggest
I am trying to print the output of the ref cursor and I used
…
Can I use dbms_output.put_line instead of print x here ?
Hello;
DBMS_OUTPUT.Put_Line was not built to be able to handle the more complex data
types let alone a pointer to a cursor.
If you wish to use Put_Line, you’ll need to form the code in a PLSQL
block, loop through the records, and display the values you’d like
displayed. At least, that’s the method I can think of.
Roger S.
PRINT is a SQL*Plus command.
Toad has support for bringing cursors back into data grids, search the help for
ref cursor
Declare
variable x refcursor
begin
sp_create_portal_dummy_users(100025,10,:x);
dbms_output.put_line( x);
end;
Does this work in TOAD?
You can put that in the editor and run it via F5 to find out. Make sure you
enable DBMS_OUTPUT first.
Afternoon,
Declare
variable x refcursorbegin
sp_create_portal_dummy_users(100025,10,:x);
dbms_output.put_line( x);
end;Does this work in TOAD?
I doubt it! You can't output an entire ref cursor with DBMS_OUTPUT even
in TOAD.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
begin
sp_create_portal_dummy_users(100025,10,:x);
dbms_output.put_line( x);
Not quite what I meant. Here, I’ll provide a simple example. Drop it in
Toad, hit the formatter, Yahoo is not friendly to white space.
DECLARE
TYPE type_rec_tmp IS RECORD
(id NUMBER,
description VARCHAR2(50));
rec_tmp type_rec_tmp;
BEGIN
rec_tmp.id := 1;
rec_tmp.description := ‘Example Record Type’;
DBMS_OUTPUT.Put_Line(rec_tmp); -- will not work, this is a “more complex
datatype”
DBMS_OUTPUT.Put_Line(rec_tmp.id); -- will work, this is a basic datatype of
number
END;
In short: if you want to use DBMS_OUTPUT to produce the data from a ref cursor,
you’ll need to form a proper loop, identify the columns in each record,
and within the loop output the contents of the records by specifically
referencing the columns. When you try and run the above, you should run into an
error along the lines “PLS-00306: wrong number or types of arguments in
call to 'PUT_LINE'”. You need to comment out the line that does not work
in order to run the block.
In short: what you suggest won’t even compile.
Roger S.
/*
Create Sample Package in HR Schema
*/
CREATE OR REPLACE PACKAGE PRINT_REF_CURSOR
AS
PROCEDURE SP_S_EMPLOYEES_BY_DEPT (
p_DEPARTMENT_ID IN INTEGER,
Out_Cur OUT SYS_REFCURSOR);
END PRINT_REF_CURSOR;
CREATE OR REPLACE PACKAGE BODY PRINT_REF_CURSOR
AS
PROCEDURE SP_S_EMPLOYEES_BY_DEPT (
p_DEPARTMENT_ID IN INTEGER,
Out_Cur OUT SYS_REFCURSOR)
AS
BEGIN
OPEN Out_Cur FOR
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID = p_DEPARTMENT_ID;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.Put_Line(‘SP_S_EMPLOYEES_BY_DEPT’ || ‘,’ || ‘-20000’ || ‘,’ );
WHEN OTHERS
THEN
DBMS_OUTPUT.Put_Line(‘SP_S_EMPLOYEES_BY_DEPT’ || ‘,’ || ‘-20001’ || ‘,’ );
END SP_S_EMPLOYEES_BY_DEPT;
END PRINT_REF_CURSOR;
/*
Fetch values using Ref Cursor and display it in grid.
*/
var RC refcursor;
DECLARE
p_DEPARTMENT_ID NUMBER;
OUT_CUR SYS_REFCURSOR;
BEGIN
p_DEPARTMENT_ID := 90;
OUT_CUR := NULL;
PRINT_REF_CURSOR.SP_S_EMPLOYEES_BY_DEPT ( p_DEPARTMENT_ID, OUT_CUR);
:RC := OUT_CUR;
END;
/
PRINT RC;
/**************/