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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 23 Jul 1999 14:19:47 GMT
Message-ID: <37a077cf.98899349@newshost.us.oracle.com>

A copy of this was sent to "Adam Tadjkarimi" <adamt_at_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                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jul 23 1999 - 09:19:47 CDT