Subject: Re: HELP returning rows from stored proc

Marvin Frederickson (mfrederickson@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@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:                       <->
<->                                                               <->
<->              www.arikaplan.com                                <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page to Ari Kaplan's Home Page