Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Location
    India
    Posts
    2

    Question Unanswered: BULK COLLECT problem

    Hello Friends,
    I have a procedure which selects about 5 million records from a master table and inserts into an intermediate table. TO start with I used a cursor to fetch the records and commited every 10,000 records. But then I was getting the ORA-01555 error. So I reverted back and implemented the BULK COLLECT facility. Now I have a single SELECT statement(NOT a CURSOR) doing a BULK COLLECT. But then my problem now is how do I limit my select to a particular value. Meaning my BULK COLLECT should collect not more than 1,00,000 records at a time....Something like a LIMIT clause as in BULK COLLECT for a CURSOR.

    A sample code will be of great help. Am using Oracle 9.0.1.0.0 version

    Thanks in Advance
    Subramaniam.R

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: BULK COLLECT problem

    Originally posted by SRamaiah
    Hello Friends,
    I have a procedure which selects about 5 million records from a master table and inserts into an intermediate table. TO start with I used a cursor to fetch the records and commited every 10,000 records. But then I was getting the ORA-01555 error. So I reverted back and implemented the BULK COLLECT facility. Now I have a single SELECT statement(NOT a CURSOR) doing a BULK COLLECT. But then my problem now is how do I limit my select to a particular value. Meaning my BULK COLLECT should collect not more than 1,00,000 records at a time....Something like a LIMIT clause as in BULK COLLECT for a CURSOR.

    A sample code will be of great help. Am using Oracle 9.0.1.0.0 version

    Thanks in Advance
    Subramaniam.R
    The simple answer is: you can't. If you could:

    SELECT a,b,c BULK COLLECT INTO x,y,z LIMIT 100000 FROM t; /* Can't do this */

    OK, you would have the first 100,000 records in your arrays. How would you get the NEXT 100,000? If you run the same SELECT again you will get the same 100,000 as the first time!

    You would have to find some columns that partition your data into sub-100,000 record chunks. For example, if table t had a column deptno and SELECT deptno, COUNT(*) FROM t GROUP BY deptno HAVING COUNT(*) > 100000 returned no rows, then you could do:

    FOR d IN (SELECT deptno FROM dept)
    LOOP
    INSERT INTO t2
    SELECT a,b,c FROM t1 WHERE deptno = d;
    END LOOP;

    (And then you have no need for BULK COLLECT anyway!)

    Of course, the BEST answer is to get the DBA to set up a rollback segment big enough to do this as a single INSERT..SELECT followed by a single COMMIT.

  3. #3
    Join Date
    Sep 2002
    Location
    ITALY
    Posts
    53

    Re: BULK COLLECT problem

    Hi.

    Have you considered the INSERT /*+ APPEND */ feature ?
    Maybe in this kind of bulk insert you can afford (seems like a DWH) not being in transaction.

    want to kill ora-1555 ?
    try not to comit, you can if you size rbs correctly.

    try to do the bulk insert, instead of the bulk collect.

    hope this ideas will serve to you.
    Best Regards.

    Originally posted by SRamaiah
    Hello Friends,
    I have a procedure which selects about 5 million records from a master table and inserts into an intermediate table. TO start with I used a cursor to fetch the records and commited every 10,000 records. But then I was getting the ORA-01555 error. So I reverted back and implemented the BULK COLLECT facility. Now I have a single SELECT statement(NOT a CURSOR) doing a BULK COLLECT. But then my problem now is how do I limit my select to a particular value. Meaning my BULK COLLECT should collect not more than 1,00,000 records at a time....Something like a LIMIT clause as in BULK COLLECT for a CURSOR.

    A sample code will be of great help. Am using Oracle 9.0.1.0.0 version

    Thanks in Advance
    Subramaniam.R
    Franco Ceotto
    SIPTI srl
    OnSite Services
    9i OCP DBA, Performance Engineer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •