Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to create PL/SQL table or temp_table
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
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;
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 );
8 l_tab mytable; 9 l_idx number := 0; 10 l_sum number := 0;
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;
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.