Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: ORA-01013 error
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);
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