CODEWITHSUNDEEP
sqloracle-database
cometta
cometta

Reputation: 35759

Oracle insert script with condition

I have sql insert script like below

insert into table......from table2.;

drop table2.....;
drop table3....;

commit;

is it possible to do condition "insert" must be success before proceed with "drop table2 and table3" in oracle?

Upvotes: 0

Views: 385

Answers (2)

Rene
Rene

Reputation: 10551

In sqlplus you can specify "whenever sqlerror exit". So your script will terminate after an error. Alternatively you may want to put you code in a plsql block for more control. Quick example:

begin
   begin
      insert  ...
   exception
      when others then 
         dbms_output.put_line(sqlerrm);   -- print the error
         rais application_error(-20000,'Error in insert statement');
   end;

   drop 1
   drop 2
end;
/

Upvotes: 0

Benoit
Benoit

Reputation: 79243

If you are running under SQLplus :

WHENEVER SQLERROR EXIT SQL.SQLCODE

Also note that the commit operation is optional, as DDL statements (like DROP instructions) will always commit before processing.

Upvotes: 2

Related Questions