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.1 person found this helpful
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
if the select returns single record then go for into clause with select.....if the select returns more than one row go for cursor......
Ok, but do i go for the cursor because it's the only way to do it? And yes, the SELECT returns multiple records.
If select returns more than one record you need go for cursor ....
This is different than what I had in mind. Thanks for the suggestion.