8 Replies Latest reply: Dec 5, 2012 10:04 AM by 968875 RSS

    display select in a stored procedure

    968875
      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
          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
            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
              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
                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
                  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
                    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
                      If select returns more than one record you need go for cursor ....
                      • 8. Re: display select in a stored procedure
                        968875
                        This is different than what I had in mind. Thanks for the suggestion.