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

Home -> Community -> Usenet -> c.d.o.server -> Re: Using UPDATE with ROWNUM?

Re: Using UPDATE with ROWNUM?

From: Chris L. <diversos_at_uol.com.ar>
Date: Tue, 26 Jun 2007 11:57:52 -0700
Message-ID: <1182884272.614042.29040@n2g2000hse.googlegroups.com>


On 26 jun, 14:56, darr..._at_yahoo.com wrote:
> > But make sure to look up "bulkcollect" and "forall" updating
> > examples, which D. Morgan of this same newsgroup recently pointed me
> > to! Much higher performance.
>
> TYVM. Useful information for more that just this case!
>
> This post has an example of bulk collect from a sequence (through a
> dummy table):http://groups.google.com/group/comp.databases.oracle.server/browse_th...
> Is this a reasonably efficient method? I probably want to bulk collect
> about 1000 values at a time.

You don't need to bulk collect from the sequence. I suggest you bulk collect the primary key of the table being updated.

Then, using FORALL, you update the records with the sequence's nextval.

As follows (example modified from D. Morgan's page)

CREATE TABLE parent (part_num NUMBER, part_name VARCHAR2(10),seq_no number);
ALTER TABLE parent ADD PRIMARY KEY (part_num,part_name);

INSERT INTO parent (part_num,part_name)

SELECT 1,'a' FROM dual UNION
SELECT 2,'b' FROM dual UNION
SELECT 3,'c' FROM dual UNION
SELECT 4,'d' FROM dual ;

declare
TYPE array_one IS TABLE OF parent.part_num%TYPE; TYPE array_two IS TABLE OF parent.part_name%TYPE; tbl_num array_one;
tbl_name array_two;

CURSOR r IS
SELECT part_num,part_name
FROM parent;

BEGIN
  OPEN r;
  LOOP
    FETCH r BULK COLLECT INTO tbl_num,tbl_name LIMIT 1000;

    FORALL i IN 1..tbl_num.COUNT

      UPDATE parent SET seq_no=tempseq.NEXTVAL
      WHERE part_num=tbl_num(i)
      AND   part_name=tbl_name(i);

    EXIT WHEN r%NOTFOUND;
  END LOOP;
  COMMIT;
  CLOSE r;
END ;
/

(remember, I didn't know about bulk collect three days ago, so you might want to look it up some more in the manual) Received on Tue Jun 26 2007 - 13:57:52 CDT

Original text of this message

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