Oracle PL / SQL Drop Table Exception Handling

[Joe San]

Guys,

I would like to handle exceptions using PL SQL for the drop able and create table scripts. This would happen when I initially set up the db scripts, I would have a drop table statement preceeding the create table statement. But when the script is being run for the first time, the drop table would result in an error and I would like to handle this error and proceed with the create table. Any suggestions or pointers on how to do this.

[Joe San]

Found out a way to do this. Wrote a Procedure that would take a varchar as parameter and check if this parameter is a table and if yes, it will drop the table and if the table does not exist, the thrown exception will be handled properly and the procedure returns.

[Ireneusz Kordal]

Hi,

use nested begin-exception-end blocks to handle exceptions in PL/SQL

declare 
  table_does_not_exist exception;
  PRAGMA EXCEPTION_INIT(table_does_not_exist, -942); -- ORA-00942
  
  table_name varchar2(100) := 'tt';
begin

  begin
      execute immediate 'drop table ' || table_name;
  exception
      when table_does_not_exist then null;
  end;
  
  execute immediate 
          'create table ' || table_name || '(  
              xx number     
          )';
end;
/

[Avi Abrami]

declare CNT number; begin select count(*) into CNT from USER_TABLES where TABLE_NAME = 'whatever'; -- if CNT = 1 then drop table WHATEVER; end if; -- create table WHATEVER ... end;

[Joe San]

Thanks guys!