SELECT Multiple Rows of a Table INTO a PL/Sql TableRecord Without Using Cursor

[Adam Tadjkarimi]

I wonder if it is possible to SELECT directly multiple rows of a table into
a PL/Sql table record without using cursor? Here is a simple example:

DECLARE
TYPE PeriodRecTabType IS TABLE OF Period%ROWTYPE
INDEX BY BINARY_INTEGER;
PeriodRecTab PeriodRecTabType;
IndexVar BINARY_INTEGER := 1;
BEGIN
SELECT * INTO PeriodRecTab(IndexVar)
FROM Period;
EXCEPTION
......
......
END;

When I run it I get the ORA-01422, exact fetch returns more than requested
number of rows.

Is it possible at all? any workaround?

Thanks,
Adam Tadj

[Chris Weiss]

Since you must explicitly set the index for each row in the PL/SQL table,
you may not populate the table without a cursor. The binary index
requirement does not allow for an autoincrement of the index for each row.

PL/SQL tables can actually impede performance for a code segment. Make sure
you have a real need before you go through the trouble of creating one. In
the right context, a PL/SQL table can be a tremendous savings in both raw
performance and I/O (depending on your cache size and LRU swap rate). For
example, if you have a record set for a package that must be accessed
repeatedly without an actual update of the data, a global PL/SQL table for a
package can truly improve performance. However, if you can pin the original
table in memory, performance will be nearly identical, or if you have a
large area in RAM for Oracle, the data should remain in memory.

I have not yet worked with 8i, but I believe the rules are similar.

Christopher Weiss
Professional Services Division
Compuware Corporation

Adam Tadjkarimi <ad...@hsltd.com> wrote in message
news:7n7ojc$lmf$1...@newsin-1.starnet.net...

[Jonathan Lewis]

You can do all sorts of daft, or exciting (depends on your
view point), things with Pl/SQL arrays and cursors in 8i.

There are also options for 'array fetches' in 8.0 using
the dbms_sql package. The main limitation is that the
arrays you fetch into are limited to simple types specified
by the package.

There are a few examples of the different possibilities on
my web site.

--

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

Chris Weiss wrote in message <7n9qci$h0h$1...@msunews.cl.msu.edu>...

>
>I have not yet worked with 8i, but I believe the rules are similar.
>
>

[Thomas Kyte]

A copy of this was sent to "Adam Tadjkarimi" <ad...@hsltd.com>
(if that email address didn't require changing)

On Thu, 22 Jul 1999 13:40:28 -0500, you wrote:

>I wonder if it is possible to SELECT directly multiple rows of a table into
>a PL/Sql table record without using cursor? Here is a simple example:
>
>DECLARE
> TYPE PeriodRecTabType IS TABLE OF Period%ROWTYPE
> INDEX BY BINARY_INTEGER;
> PeriodRecTab PeriodRecTabType;
> IndexVar BINARY_INTEGER := 1;
>BEGIN
> SELECT * INTO PeriodRecTab(IndexVar)
> FROM Period;
> EXCEPTION
> ......
> ......
>END;
>

>When I run it I get the ORA-01422, exact fetch returns more than requested
>number of rows.
>
>Is it possible at all? any workaround?
>
>Thanks,
>Adam Tadj
>

In 7.x -- No. You must loop and fill it up.

In 8.0 -- if the plsql table is really an Oracle8 object type -- YES. For
example:

SQL> create or replace type myTableType as table of number
2 /

Type created.

SQL>
SQL> declare
2 l_x myTableType;
3 begin
4
4 select cast( multiset( select user_id from all_users where rownum <
5 )
5 AS myTableType )
6 into l_x
7 from dual;
8
8 for i in 1 .. l_x.count loop
9 dbms_output.put_line( l_x(i) );
10 end loop;
11 end;
12 /
0
5
17
19

PL/SQL procedure successfully completed.

In 8.1 -- yes, array fetching has been added. For example:

SQL> DECLARE
2 TYPE enameType IS TABLE OF emp.ename%type;
3 TYPE empnoType IS TABLE OF emp.empno%type;
4
5 enames enameType;
6 empnos empnoType;
7 BEGIN
8
9 SELECT ename, empno BULK COLLECT INTO enames, empnos FROM emp;
10
11 for i in 1 .. enames.count loop
12 dbms_output.put_line( empnos(i) );
13 end loop;
14 END;
15 /
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

PL/SQL procedure successfully completed.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte tk...@us.oracle.com
Oracle Service Industries Reston, VA USA

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