This discussion is archived
8 Replies Latest reply: Dec 5, 2012 8:03 AM by LPS RSS

display select in a stored procedure

968875 Newbie
Currently Being Moderated
Hello,

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?

Thanks.
  • 1. Re: display select in a stored procedure
    LPS Journeyer
    Currently Being Moderated
    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.......
  • 2. Re: display select in a stored procedure
    sb92075 Guru
    Currently Being Moderated
    questioningq12 wrote:
    Hello,

    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?

    Thanks.
    PRINT?
    Print where?
    Stored Procedure runs deep inside RDBMS engine.
    So how exactly does results get back to the end user?

    is this a 3-tier application?
  • 3. Re: display select in a stored procedure
    968875 Newbie
    Currently Being Moderated
    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
    begin
    select * from myTable1;
    end;
    /

    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.

    Thanks
  • 4. Re: display select in a stored procedure
    6363 Guru
    Currently Being Moderated
    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
  • 5. Re: display select in a stored procedure
    LPS Journeyer
    Currently Being Moderated
    if the select returns single record then go for into clause with select.....if the select returns more than one row go for cursor......
  • 6. Re: display select in a stored procedure
    968875 Newbie
    Currently Being Moderated
    Ok, but do i go for the cursor because it's the only way to do it? And yes, the SELECT returns multiple records.
  • 7. Re: display select in a stored procedure
    LPS Journeyer
    Currently Being Moderated
    If select returns more than one record you need go for cursor ....
  • 8. Re: display select in a stored procedure
    968875 Newbie
    Currently Being Moderated
    This is different than what I had in mind. Thanks for the suggestion.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points