oracle sql transcation block proper syntax?

Question

If there is a ParentTable table1 and child-table table2 and I want to make sure either both get created (in proper order!) or none get created, is this the correct syntax?

begin    
  insert into table1 values (seq.nextvalue, 'test') ;
  insert into table2 values (seq.currvalue, 'test3');
  commit;
end;

INSERT INTO t1 SELECT a, b FROM dual OR INSERT INTO t1 VALUES (a, b) Your code kinda slips between the two :) — MatBailie
– MatBailie, Commented Apr 26, 2012 at 10:51
I'd put a field list into the INSERT statements - e.g. INSERT INTO T1 (FIELDNAME_1, FIELDNAME_2) VALUES (seq.NEXTVALUE, 'test'). I like to offer the database the fewest number of chances possible to fold, spindle, mutilate, or misinterpret what I meant. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Apr 26, 2012 at 14:10

Ollie · Accepted Answer · 2012-04-26 10:53:04Z

1

If you are worried about different values in your sequence assignment then fetch it into a variable before your insertion. If any exceptions are raised it'll rollback the insertions, otherwise it'll commit them.

DECLARE
   v_seq_id NUMBER;
BEGIN
   SELECT seq.nextval
     INTO v_seq_id
     FROM dual;
   --
   INSERT INTO table1
   VALUES (
      v_seq_id,
      'test'
   );
   --
   INSERT INTO table2
   VALUES (
      v_seq_id,
      'test3'
   );
   --
   COMMIT;
EXCEPTION
   WHEN others
   THEN
      <log_error>
      ROLLBACK;
END;

Hope it helps...

answered Apr 26, 2012 at 10:53

Ollie

17.6k8 gold badges50 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

TPR Over a year ago

I'm not just worried about different seq values, but only first insert taking place and not the other - for whatever reason. I'm guessing that's where exception thing comes in. Btw, am I right in being worried about different seq values?

Ollie Over a year ago

@progtick, it depends on whether you really need the sequence values to match (i.e. for a foreign key relationship). As for the inserts and one happening without the other, you are correct, the exception section would ensure that if there was an error in one of the inserts, a rollback would occur ensuring nothing was inserted into either table.

Bob Jarvis - Слава Україні Over a year ago

In the EXCEPTION block you might want to replace <log_error> with DBMS_OUTPUT.PUT_LINE('Error - ' || SQLCODE || ' : ' || SQLERRM).

Ollie Over a year ago

@BobJarvis, I was hoping the OP had their own custom error logging to insert there :-)

hkutluay · Accepted Answer · 2012-04-26 10:41:36Z

0

Not tested but must be like that

begin    
  insert into table1 values(seq.nextval, 'test') ;
  insert into table2 values(seq.currval, 'test3');
  commit;
end;

answered Apr 26, 2012 at 10:41

hkutluay

6,9742 gold badges37 silver badges57 bronze badges

Collectives™ on Stack Overflow

oracle sql transcation block proper syntax?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related