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?
Add a comment
|
2 Answers
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.
Comments
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;
/
