kuldarim kuldarim - 1 month ago 17
SQL Question

Create or replace table in Oracle pl/sql

I need a script which creates table or if it already exist drops it, and when recreates table. After some research I have found out that

CREATE OR REPLACE TABLE
in pl/sql doesn't exist. So I come up with this script :

DECLARE
does_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT (does_not_exist, -942);
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE foobar';
EXCEPTION
WHEN does_not_exist
THEN
NULL;
END;
/

CREATE TABLE foobar (c1 INT);


Is there any proper way to achieve this functionality?

Answer

Using a global temporary table would seem to be a better option. However, if you insist on dropping and re-adding tables at runtime you could query one of the _TABLES views (i.e. USER_TABLES, DBA_TABLES, ALL_TABLES) to determine if the table exists, drop it if it does, then create it:

SELECT COUNT(*)
  INTO nCount
  FROM USER_TABLES
  WHERE TABLE_NAME = 'FOOBAR';

IF nCount <> 0 THEN
  EXECUTE IMMEDIATE 'DROP TABLE FOOBAR';
END IF;

EXECUTE IMMEDIATE 'CREATE TABLE FOOBAR(...)';

Share and enjoy.

Comments