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
PRAGMA EXCEPTION_INIT (does_not_exist, -942);
EXECUTE IMMEDIATE 'DROP TABLE foobar';
CREATE TABLE foobar (c1 INT);
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.