CODEWITHSUNDEEP
oracle-databaseplsql
Sonic Soul
Sonic Soul

Reputation: 24909

oracle drop table if doesn't exist (in one line)

I am trying to do something on Oracle that is done on Sql Server like this: 

IF EXISTS(SELECT * FROM   dbo.Scores) DROP TABLE dbo.Scores

Note that i want to avoid the multi line pl sql since this can be repeated many times in a single script.

Initially wrote it as a proc, but then realized in Oracle the proc belongs to a schema and that schema would need elevated rights which is not desirable.

as plan B I am trying to execute output of a function call like this 

execute immediate FN_DROP_TABLE_IF_EXISTS('mySchema','myTable');

again my goal is to have a way to do this in one line. above should return a table drop statement, or some void type statement if table does not exist?

Upvotes: 0

Views: 6458

Answers (2)

Alex Poole
Alex Poole

Reputation: 191265

You can create your function - or more properly, in this context, a procedure - with invoker's rights, which means that the commands in it will run as the calling user, with their privileges - rather than as the procedure owner. You wouldn't then need to pass in the schema.

create procedure drop_table_if_exists(p_table_name varchar2)
authid current_user
as
  e_does_not_exist exception;
  pragma exception_init(e_does_not_exist, -942);
begin
  execute immediate 'drop table ' || p_table_name;
exception
  when e_does_not_exist then
    null; -- could report if you want
end drop_table_if_exists;
/

When you can call it as:

exec drop_table_if_exists('my_table');

For example, if you create a table and then try to drop it twice, you don't see the exception form the second attempt:

create table my_table (id number);

Table MY_TABLE created.

exec drop_table_if_exists('my_table');

PL/SQL procedure successfully completed.

exec drop_table_if_exists('my_table');

PL/SQL procedure successfully completed.

Your procedure could also query the data dictionary (user_tables) to see whether it exists, and then only drop it if it does, rather than try to drop and handle the exception.

You also need to be aware of case-sensitive table names and referential integrity which will need tables to be dropped in a specific order.

Upvotes: 2

Baltazar
Baltazar

Reputation: 11

You have to handle exception when there is no such table:

create or replace procedure drop_it(pv_name in varchar2)
is
begin
 execute immediate 'drop table schema.' || pv_name ||'  purge';
exception when others then null; end;

Upvotes: 0

Related Questions