Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: ORA-01013 error

Re: ORA-01013 error

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 19 Jan 2006 13:06:04 -0800
Message-ID: <1137704754.430166@jetspin.drizzle.com>


smathews wrote:
> Hi,
> I have a funtion that bulk collects data into collection types and then
> loops thru each collection record to do some data manipulations. I am
> using the following select statement to assign some value to a
> collection item in the loop.
> "SELECT TRUNC((TRUNC((tt_process_end_time(i) -
> tt_process_start_time(i)),5) * 100000)/60) ||'.' ||
> LPAD(MOD((TRUNC((tt_process_end_time(i) - tt_process_start_time(i)),5)
> * 100000),60),2,0)
> INTO tt_elaspd_time(i)
> FROM dual;"
> When I am processing large number of records(say 20,000 +), I get
> ORA-01013 error(user requested cancel of current operation). I am not
> sure if it has to do something with the PL/sQL area size or in using
> the dual table. By the way this is in oracle 10g. Any help with this
> would be appreciated.
> Thanks.

I've never seen the behaviour you decribe.

What version of Oracle?
What platform and operating system?
What is in the alert log?
Do you really need to use TRUNC three times? Why not write tt_elaspd_time(i) := ....; ?

The above doesn't correspond with your statement that you: "... bulk collects data into collection types and then loops thru each collection record to do some data manipulations." What is this?

Doing what you describe might look more like this:

CREATE OR REPLACE PROCEDURE fast_way IS

TYPE parent_rec IS RECORD (
part_num dbms_sql.number_table,
part_name dbms_sql.varchar2_table);

p_rec parent_rec;

CURSOR c IS
SELECT part_num, part_name
FROM parent;

l_done BOOLEAN;

BEGIN
   OPEN c;
   LOOP

     FETCH c BULK COLLECT INTO p_rec.part_num, p_rec.part_name
     LIMIT 500;
     l_done := c%notfound;

     FOR i IN 1 .. p_rec.part_num.COUNT
     LOOP
       p_rec.part_num(i) := p_rec.part_num(i) * 10;
     END LOOP;

     FORALL i IN 1 .. p_rec.part_num.COUNT
     INSERT INTO child
     (part_num, part_name)
     VALUES
     (p_rec.part_num(i), p_rec.part_name(i));

     EXIT WHEN (l_done);

   END LOOP;
   COMMIT;
   CLOSE c;
END fast_way;
/

Note the FOR loop in the middle.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Thu Jan 19 2006 - 15:06:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US