5 Replies Latest reply: May 23, 2008 3:47 PM by 641120 RSS

    multiple insert using union all

    641120
      Hello

      i'm trying to make a multiple insert using union all.
      i have a stored procedure to do this but.. i can't make commit into a block begin .. exception.. end

      what can i do to solve this?

      i have something like this...
      begin
      insert into tableX(F1, F2)
      select 'A' , 2 from dual
      union all
      select 'B' , 2 from dual
      ....
      commit;
      exception
      WHEN OTHERS THEN
      rollback;
      end;

      thaks for your help...

      Rose
        • 1. Re: multiple insert using union all
          589667
          ...but.. i can't make commit into a block begin .. exception.. end
          What exactly does this mean ?

          You have put a COMMIT after the INSERT statement, so what's the holdup ?

          pratz
          • 2. Re: multiple insert using union all
            641120
            well..
            the thing is.. when i execute the procedure.. never do commit or rollback..it jump to next step
            • 3. Re: multiple insert using union all
              589667
              Are you sure the problem isn't somewhere else ?
              test@ORA10G>
              test@ORA10G> -- ===================================================
              test@ORA10G> -- test commit
              test@ORA10G> -- ===================================================
              test@ORA10G>
              test@ORA10G> drop table tableX;
              
              Table dropped.
              
              test@ORA10G> create table tableX (
                2    F1 varchar2(1),
                3    F2 number
                4  );
              
              Table created.
              
              test@ORA10G>
              test@ORA10G> create or replace procedure my_proc
                2  as
                3  begin
                4    insert into tableX (F1, F2)
                5    select 'A', 2 from dual
                6    union all
                7    select 'B', 2 from dual
                8    union all
                9    select 'C', 2 from dual;
               10    commit;
               11  exception
               12    when others then
               13      rollback;
               14  end;
               15  /
              
              Procedure created.
              
              test@ORA10G>
              test@ORA10G> exec my_proc;
              
              PL/SQL procedure successfully completed.
              
              test@ORA10G>
              test@ORA10G> select * from tableX;
              
              F         F2
              - ----------
              A          2
              B          2
              C          2
              
              test@ORA10G> rollback;
              
              Rollback complete.
              
              test@ORA10G> select * from tableX;
              
              F         F2
              - ----------
              A          2
              B          2
              C          2
              
              test@ORA10G> -- the INSERT has been committed, so ROLLBACK is ineffective
              test@ORA10G>
              test@ORA10G>
              test@ORA10G> -- ===================================================
              test@ORA10G> -- with no commit
              test@ORA10G> -- ===================================================
              test@ORA10G>
              test@ORA10G> drop table tableX;
              
              Table dropped.
              
              test@ORA10G> create table tableX (
                2    F1 varchar2(1),
                3    F2 number
                4  );
              
              Table created.
              
              test@ORA10G>
              test@ORA10G> create or replace procedure my_proc
                2  as
                3  begin
                4    insert into tableX (F1, F2)
                5    select 'A', 2 from dual
                6    union all
                7    select 'B', 2 from dual
                8    union all
                9    select 'C', 2 from dual;
               10  --  commit;
               11  exception
               12    when others then
               13      rollback;
               14  end;
               15  /
              
              Procedure created.
              
              test@ORA10G>
              test@ORA10G> exec my_proc;
              
              PL/SQL procedure successfully completed.
              
              test@ORA10G>
              test@ORA10G> select * from tableX;
              
              F         F2
              - ----------
              A          2
              B          2
              C          2
              
              test@ORA10G> rollback;
              
              Rollback complete.
              
              test@ORA10G> select * from tableX;
              
              no rows selected
              
              test@ORA10G> -- the INSERT was never committed, hence ROLLBACK was successful
              test@ORA10G> -- ===================================================
              test@ORA10G> -- test rollback
              test@ORA10G> -- ===================================================
              test@ORA10G>
              test@ORA10G> drop table tableX;
              
              Table dropped.
              
              test@ORA10G> create table tableX (
                2    F1 varchar2(1),
                3    F2 number
                4  );
              
              Table created.
              
              test@ORA10G>
              test@ORA10G> alter table tableX add constraint pk1 primary key (F2);
              
              Table altered.
              
              test@ORA10G>
              test@ORA10G>
              test@ORA10G> create or replace procedure my_proc
                2  as
                3  begin
                4    insert into tableX (F1, F2) values ('X', 0);
                5    insert into tableX (F1, F2)
                6    select 'A', 2 from dual
                7    union all
                8    select 'B', 2 from dual
                9    union all
               10    select 'C', 2 from dual;
               11    commit;
               12  exception
               13    when others then
               14      dbms_output.put_line('In exception block, right before rollback...');
               15      rollback;
               16      dbms_output.put_line('In exception block, right after rollback...');
               17  end;
               18  /
              
              Procedure created.
              
              test@ORA10G>
              test@ORA10G> exec my_proc;
              In exception block, right before rollback...
              In exception block, right after rollback...
              
              PL/SQL procedure successfully completed.
              
              test@ORA10G>
              test@ORA10G> select * from tableX;
              
              no rows selected
              
              test@ORA10G>
              test@ORA10G> -- the 2nd INSERT was unsuccessful due to the primary key violation, hence the 1st INSERT was rolled back
              test@ORA10G>
              test@ORA10G>
              pratz
              • 4. Re: multiple insert using union all
                641120
                Hello..
                i have my proc like you do..
                but when i execute it with a trace into.. (using sql developer) it just pass it..
                i'm starting to use oracle..
                so.. i will try with your example..
                • 5. Re: multiple insert using union all
                  641120
                  I found my error...
                  i missed the ';' in the last select...
                  thaks a lot!!!

                  Rose