Re: Stored Procedure: How to output entire buffer

In comp.databases.oracle.misc Bill Wordsworth <bill.wordsworth_at_gmail.com> wrote:
> 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.

Where are you declaring the type? You need to do it outside the procedure. Presumably the procedure is in a package, in which case you would declare the type in the package header. (Note that the DECLARE is not necessary and the DEFINE version is just incorrect.)

If the procedure is not in a package, I would recommend putting it in one. Alternatively, you can always use the system-defined type SYS_REFCURSOR, if you don't mind it being weakly-typed.

In any case I would strongly recommend reading the Oracle documentation on how to do this sort of thing, which has lots of examples. You don't say which version you are using - in the 10.2 documentation, this is in Chapter 6 ("Performing SQL Operations from PL/SQL") of the PL/SQL User's Guide and Reference.

-- 
_______________________________________________________________________
Dan Blum					         tool_at_panix.com	
"I wouldn't have believed it myself if I hadn't just made it up."