pl/sql table

[Ervin Rodriguez]

I have a table that contains thousand of records. I am writing a program
that declares a pl/sql table as
TYPE temp_table IS TABLE OF s_department%ROWTYPE

the s_department table has 15 columns but my query is only getting 8 columns(different
data types). I want to take all the records return by my query and store
them in a pl/sql table at once (Bulk collect into). Is my table definition
ok or will i run into memory problems? Is there any other way i can get use
BULK COLLECT INTO statement without declaring a table of ROWTYPE? thanks

[Guy Smith]

The type definition will not work with BULK COLLECT INTO.

<SNIPPED FROM ORACLE DOCS>

You cannot bulk-fetch from a cursor into a collection of records, as the
following example shows:

DECLARE
TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT deptno, dname, loc FROM dept WHERE deptno > 10;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs; -- illegal
...
END;

</SNIPPED FROM ORACLE DOCS>

Note that SELECT BULK COLLECT INTO is also a cursor (an implicit one) and
hence, will not work for the same reason above. You will need to make a
s_department.columnX%TYPE collection for each column you wish to select into.

Guy



"Ervin Rodriguez" <ervin06@netscape.net> wrote:
>
>I have a table that contains thousand of records. I am writing a program
>that declares a pl/sql table as
>TYPE temp_table IS TABLE OF s_department%ROWTYPE
>
>the s_department table has 15 columns but my query is only getting 8 columns(different
>data types). I want to take all the records return by my query and store
>them in a pl/sql table at once (Bulk collect into). Is my table definition
>ok or will i run into memory problems? Is there any other way i can get
use
>BULK COLLECT INTO statement without declaring a table of ROWTYPE? thanks