This content has been marked as final. Show 8 replies
Your Question has the answer.....what you want to do.....?
SQL > SET SERVEROUTPUT ON
SQL > EXEC proc();
it will display when dbms_output.put_line() ; if its there in proc.....if its for testing purpose.....
otherewise use OUT PARAMETER as SYS_REFCURSOR for the proc......to get the output of select statement.......
I have a select statement that I wanna print in a stored procedure. The only way is to create a cursor in the SP, which holds the result of the select, and then iterate through the lines and print each line using dbms_output.put_line?
Stored Procedure runs deep inside RDBMS engine.
So how exactly does results get back to the end user?
is this a 3-tier application?
I want to print the result of a select statement in a stored procedure.
Here's an example with a simple select:
create or replace procedure myFirstProc as
select * from myTable1;
This code yields: "an INTO clause is expected in this SELECT statement". So my question is wether the only alternative is to use a CURSOR that holds the result of the SELECT stmt and then print line by line the content of the cursor in the SP.
You can pass a ref cursor as an output parameter and print that.
SQL> create or replace procedure p (p_c out sys_refcursor) as 2 begin 3 open p_c for 4 with test_data as 5 ( 6 select 1 n, 'a' s from dual union all 7 select 2 n, 'b' s from dual union all 8 select 5 n, 'x' s from dual 9 ) 10 select n, s from test_data; 11 end; 12 / Procedure created. SQL> var c refcursor SQL> exec p(:c) PL/SQL procedure successfully completed. SQL> print c N S ---------- - 1 a 2 b 5 x