Stored Procedure: How to output entire buffer

#21

Bill Wordsworth wrote:

Quote:

On Jun 9, 2:56 pm, t... (AT) panix (DOT) com (Dan Blum) wrote:
In comp.databases.oracle.misc Bill Wordsworth <bill.wordswo... (AT) gmail (DOT) com> wrote:

On Jun 9, 2:09?pm, Bill Wordsworth <bill.wordswo... (AT) gmail (DOT) com> wrote:
I wrote a Stored Procedure wherein I use a Cursor to extract multiple
rows and columns. I then write them into the buffer
(dbms_output.put_line). But when I try to capture the entire result
into an OUT variable, I only get the last buffered line.
So how do I output the entire buffer- all rows and columns?
Cheers, Bill
In other words (maybe), how do I use dbms_output.get_lines() to assign
value to an OUT variable?
Cheers, Bill
I'm sure exactly what you're trying to do here, but I doubt this is the correct
approach. Most likely you just want to make your OUT variable a ref cursor.
If there's some reason you can't do that, create a record collection type, make
the variable that type, and put the cursor results into the variable.

DBMS_OUTPUT is designed to display results - it's not meant to be used like
this.

OK, I added the following to "CREATE OR REPLACE PROCEDURE ... ()":
cursor_out_test OUT cursor_test

But when I try:
DEFINE CURSOR TYPE cursor_test IS REF CURSOR RETURN table%ROWTYPE;
...or...
DECLARE TYPE cursor_test IS REF CURSOR RETURN table%ROWTYPE;
I get errors.

Cheers, Bill

Go to the demos of DBMS_OUTPUT in Morgan's Library at www.psoug.org.
The code examples there demonstrate how to use this built in package.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Stored Procedure: How to output entire buffer

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Stored Procedure: How to output entire buffer - 06-09-2008 , 04:01 PM