Re: HELP returning rows from stored proc

Marvin Frederickson (mfrederickson_at_kpmg.com) wrote:
: I'm trying to write a stored procedure to return rows, but not having
: any luck.
: In SQL Server, the rows from the last SELECT stmt in the stored proc is
: returned as data from the stored procedure. How do I do this in Oracle?
:
: Thanks.
: mfrederickson_at_kpmg.com

Marvin,

You will need to do two things. First, start the serveroutput:

ALTER SYSTEM SET SERVEROUTPUT ON SIZE 100k;

This will display the output buffer of stored procedures up to 100k. You can change the 100k to suit your needs.

Next, in the stored procedure, use the DBMS_OUTPUT command:

DMBS_OUTPUT.PUT_LINE('The row is ...'||column_a||', '||column_b|| ... );

Note that no output will occur until the stored procedure has completed.

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 80+ Oracle tips, visit my Web Page:                       <->
<->                                                               <->
<->              http://homepage.interaccess.com/~akaplan         <->
<->                                                               <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->