Re: PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 29 Aug 1998 13:26:42 GMT
Message-ID: <35ed011a.3152793@192.86.155.100>

A copy of this was sent to "gp" <gp_at_gp> (if that email address didn't require changing) On Sat, 29 Aug 1998 13:53:17 +0100, you wrote:

> Hi,
>
>I tried to print, from a PL/SQL procedure,
>in the screen the result from a query using
>the dbms_output.put_line. However, there
>is a limit of the bytes that I can store in the buffer.
>To overcome this, I put the result of the query in
>a temporary table and I printed the contents of
>this table from the SQL*PLUS.
>Is there a way to avoid the usage of a temporary table?
>
>Thanks in advance,
>greg
>
>
>

In short, it could look like this with cursor variables:

Here is an example:

create or replace package types
as

    type cursorType is ref cursor;
end;
/  

create or replace function sp_ListEmp return types.cursortype as

    l_cursor types.cursorType;
begin

    open l_cursor for select ename, empno from emp order by ename;  

    return l_cursor;
end;
/    

REM SQL*Plus commands to use a cursor variable  

SQL> variable c refcursor
SQL> exec :c := sp_ListEmp
SQL> print c

that way -- no temp tables and you can use all SQL*Plus formatting (subtotals, headings, etc) on the printed result set as well..

you only need to create the types package once per database -- all functions can use the same 'cursorType' after that..  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Aug 29 1998 - 08:26:42 CDT