Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: REF cursor and PL/SQL table

Re: REF cursor and PL/SQL table

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 8 Jul 1999 08:43:22 +0100
Message-ID: <931421114.27533.0.nnrp-06.9e984b29@news.demon.co.uk>


I may have to write this one up and put it on my web site. I'm assuming that you mean Oracle 7 (Solutions for 8.0 and 8.1 are mentioned on the web site).

Oracle 7 strategy - which is very inefficient, highly CPU intensive, and should not be used for high-performance systems:

  1. You need a 'table' which returns a list of numbers Create table ints(n number) or select rownum from {something large like all_objects}
  2. Create a function for each PL/SQL array item that you want to turn into a column create function_x (i in integer) return {table_x_type} as begin return table_x(i); end; if packaged, these function will need the restrict references pragma
  3. Open your cursor (using the all_objects approach) as: select function_x(rownum), function_y(rownum), function_z(rownum) from all_objects ;

        You get error 1403 (no data) when you overshoot the end of the array.

d) Optional : to avoid 1403 and/or use a simple select:

        create a function returning the number of rows in one of the arrays:
            function count_x return integer is
            begin
                return table_x.count;  --  needs pl/sql 2.3
            end;

        then
            select ...
            from all_objects
            where rownum <= (select count_x from dual)
            ;


You will appreciate that all that function calling costs a fortune in CPU, though, hence the warning.

Using object types in Oracle 8 is much more efficient.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

parkstate_at_my-deja.com wrote in message <7m0umj$ti8$1_at_nnrp1.deja.com>...
>Does anyone has any idea(I am sure some of you do) on how one can read
>from a PL/SQL table using a REF cursor?
Received on Thu Jul 08 1999 - 02:43:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US