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

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 <adamt_at_hsltd.com> wrote in message news:7n7ojc$lmf$1_at_newsin-1.starnet.net...
> 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
>
>
Received on Fri Jul 23 1999 - 08:23:03 CDT