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;

Answer 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...

Answer 2

Not tested but must be like that

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