CODEWITHSUNDEEP
oracle-databaseexceptionplsqlddl
user9892866
user9892866

Reputation:

Exception in Dynamic exception

I have been give a situation .if table exist we have to drop the table inside the procedure. if it doesnt exist we have to create the table.

so we will write

execute immediate 'Drop table emp';

if emp table doesn't exist then above statement should give exception and then we will create emp table, How Can we do that without checking in user_tables or all_tables

Upvotes: 1

Views: 127

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

I think you may use such a way :

begin
  execute immediate 'Drop table emp';
 exception when others then
   begin
    if ( sqlcode = -942 ) then
    execute immediate 'Create table emp(id int,name varchar2(50),surname varchar2(50))';
    end if;
   end; 
end;

when run drops the table if exists, if don't exist drops the table.

We have some exception names spesific to the error codes, rather than using the generalized one OTHERS, returning from the keyword sqlcode such as :

ZERO_DIVIDE(-1476), TOO_MANY_ROWS(-1422), VALUE_ERROR(-6502), INVALID_NUMBER(-1722) .. etc.

You may refer Exception Handling and Related Codes

Upvotes: 3

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59602

Is this a homework?

If no, then this would be a rather poor design. You should not create table dynamically, better truncate the data.

If yes, then a solution with exception names would be this one:

DECLARE
  TABLE_DOES_NOT_EXIST EXCEPTION;
  PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST, -942);
begin
  execute immediate 'Drop table emp';
exception 
   when TABLE_DOES_NOT_EXIST then
      execute immediate 'Create table emp(id int,name varchar2(50),surname varchar2(50))';
end;

Upvotes: 2

Related Questions