Path: text.usenetserver.com!out03b.usenetserver.com!news.usenetserver.com!in04.usenetserver.com!news.usenetserver.com!nx01.iad01.newshosting.com!newshosting.com!post01.iad01!not-for-mail Date: Mon, 09 Jun 2008 14:01:53 -0700 From: DA Morgan Organization: Puget Sound Oracle Users Group User-Agent: Thunderbird 2.0.0.14 (Windows/20080421) MIME-Version: 1.0 Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server Subject: Re: Stored Procedure: How to output entire buffer References: <477f0347-b4be-4b0b-abd9-b4c989d48daa@y21g2000hsf.googlegroups.com> In-Reply-To: <477f0347-b4be-4b0b-abd9-b4c989d48daa@y21g2000hsf.googlegroups.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Message-ID: <1213045310.82450@bubbleator.drizzle.com> Cache-Post-Path: bubbleator.drizzle.com!unknown@216.162.218.178 X-Cache: nntpcache 3.0.1 (see http://www.nntpcache.org/) Lines: 43 X-Complaints-To: abuse@csolutions.net Xref: usenetserver.com comp.databases.oracle.misc:252973 comp.databases.oracle.server:445458 X-Received-Date: Mon, 09 Jun 2008 17:01:50 EDT (text.usenetserver.com) Bill Wordsworth wrote: > On Jun 9, 2:56 pm, t...@panix.com (Dan Blum) wrote: >> In comp.databases.oracle.misc Bill Wordsworth wrote: >> >>> On Jun 9, 2:09?pm, Bill Wordsworth 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