CODEWITHSUNDEEP
sqloracle-databaseplsql
user15746603
user15746603

Reputation:

Oracle function to drop table if exists

I have the following bit of code:

BEGIN
    SELECT
        COUNT(*)
    INTO table1_exists
    FROM
        sys.all_tables
    WHERE
        table1_name = upper('table1');

EXCEPTION
    WHEN no_data_found THEN
        table1_exists := 0;
END;

IF ( table1_exists ) = 1 THEN
    EXECUTE IMMEDIATE q'[Drop Table table1]';
END IF;

How could I create a SQL function to, given a table name as an input, drop if exists that table?

Upvotes: 0

Views: 972

Answers (2)

user15746603
user15746603

Reputation:

Posting the full procedure, following Sayan Malakshinov's answer.

CREATE OR REPLACE PROCEDURE drop_ifne (
    table_name IN VARCHAR2
) IS
    e_not_exists EXCEPTION;
    PRAGMA exception_init ( e_not_exists, -942 );
    vstr CLOB;
BEGIN
    vstr := q'[drop table ]' || table_name;
    EXECUTE IMMEDIATE ( vstr );
EXCEPTION
    WHEN e_not_exists THEN
        NULL;
END;

Upvotes: 1

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

Just try to drop it and suppress "ORA-00942: table or view does not exist" error:

declare
  e_not_exists exception;
  pragma exception_init(e_not_exists, -942);
begin
  execute immediate 'drop table blabla';
exception 
  when e_not_exists then null;
end;
/

Upvotes: 2

Related Questions