I'd like to check weather a table exists before creating one, in an Oracle database. Though, the following statement is not working throwing error ORA-06550 on line 7 (CREATE).
DECLARE cnt NUMBER;
BEGIN
SELECT COUNT(*) INTO cnt FROM ALL_TABLES WHERE lower(table_name) = lower('TestTable');
IF( cnt = 0 )
THEN
CREATE TABLE TestTable
(
TestFlag NUMBER(1) NOT NULL
);
END IF;
END;
Can anyone help me out with this one?
Thanks in advance!
creating tables on the fly in Oracle is a big no-no, so if this is real code you're running then, stop. use temp tables instead. but the reason for failure is that DDL has to be run in SQL, not PL/SQL.
DECLARE cnt NUMBER;
BEGIN
SELECT COUNT(*) INTO cnt FROM ALL_TABLES WHERE lower(table_name) = lower('TestTable');
IF( cnt = 0 )
THEN
execute immediate 'CREATE TABLE TestTable (testFlag NUMBER(1) NOT NULL)';
END IF;
END;