Re: Stored Procedure: How to output entire buffer

On Jun 9, 2:56 pm, t..._at_panix.com (Dan Blum) wrote:
> In comp.databases.oracle.misc Bill Wordsworth <bill.wordswo..._at_gmail.com> wrote:
>
> > On Jun 9, 2:09?pm, Bill Wordsworth <bill.wordswo..._at_gmail.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 Received on Mon Jun 09 2008 - 14:34:44 CDT