Re: How to create PL/SQL table or temp

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 01 Feb 1999 19:20:35 GMT
Message-ID: <36b9f6e5.96838406@inet16.us.oracle.com>

On Mon, 01 Feb 1999 18:16:40 GMT, anilbatra_at_carewiseinc.com wrote:

>Can somebidy help with the following. I have a Stored Proc which has a loop
>in it, everytime I go through loop I get some rows which I need to insert in
>temp_table of PL/SQL table ( or if there is any other way). I need all rows
>for some other processing later on. How do I do that? I did create table
>temp_table (emp_name varchar(50), emp_add varchar (100), emp_id NUMBER); but
>it give me an error.
>
>
>Thanks
>Anil

Why not fetch the entire result into a pl/sql table in one call and then access it just like any other pl/sql table

Assuming you have Oracle8...

SQL> drop type empArrayType;
Type dropped.

SQL> create or replace type empType as object

  2  (   empno       number,
  3      mgr         number,
  4      ename       varchar2(10),
  5      sal         number

6 );
7 /
Type created.

SQL> create or replace type empArrayType as table of empType; 2 /
Type created.

SQL> declare
  2 l_x empArrayType;
  3 l_sum number := 0;
  4 begin
  5 select cast(

  6           multiset(select empno, mgr, ename, sal from emp) AS empArrayType)
  7      into l_x
  8      from dual;

9 dbms_output.put_line( 'Records fetched: ' || l_x.count ); 10 for i in 1 .. l_x.count loop
11 l_sum := l_sum + l_x(i).sal;
12 end loop;
13 dbms_output.put_line( 'Total Salaries: ' || l_sum ); 14 end;
15 /

Records fetched: 14
Total Salaries: 28025

If you are using Oralce7 then you'll have to loop over all row one at a time.

SQL> declare
2 type my_record is record(

  3      empno       number,
  4      mgr         number,
  5      ename       varchar2(10),
  6      sal         number );

7 type mytable is table of my_record index by binary_integer;

  8    l_tab mytable;
  9    l_idx number := 0;
 10    l_sum number := 0;

11 begin
12 for c in ( select empno, mgr, ename, sal

 13                 from emp )
 14    loop
 15      l_idx := l_idx + 1;
 16      l_tab(l_idx).empno := c.empno;
 17      l_tab(l_idx).mgr := c.mgr;
 18      l_tab(l_idx).ename := c.ename;
 19      l_tab(l_idx).sal := c.sal;

20 end loop;
21 dbms_output.put_line( 'Records fetched: ' || l_idx ); 22 for i in 1 .. l_tab.count loop
23 l_sum := l_sum + l_tab(i).sal; 24 end loop;
25 dbms_output.put_line( 'Total Salaries: ' || l_sum ); 26 end;
27 /

Records fetched: 14
Total Salaries: 28025

hope this helps.

chris.

>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Feb 01 1999 - 13:20:35 CST

Re: How to create PL/SQL table or temp_table