pl/sql table


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: pl/sql table

  1. #1
    Ervin Rodriguez Guest

    pl/sql table


    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

  2. #2
    Guy Smith Guest

    Re: pl/sql table


    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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center