execute immediate and insert - table or view does not exist
mariusApr 9 2008 — edited Apr 15 2008Hello,
I have a sqlplus script with an EXECUTE IMMEDIATE statement and an INSERT.
It fails with error ORA-00942: table or view does not exist at "INSERT INTO TBL_TEST" line.
It seems that it doesn't execute the "CREATE TABLE" statement and jumps directly to "INSERT".
I tried just the part with EXECUTE IMMEDIATE and it works. The user has the rights to create/insert tables.
begin
EXECUTE IMMEDIATE 'create table TBL_TEST
(
INFO VARCHAR2(4) not null
)';
DBMS_OUTPUT.PUT_LINE(1);
INSERT INTO TBL_TEST (INFO) VALUES ('1');
end;
I want to make a idempotent script for database migration.
For that I want to make a script like this:
begin
if ObjectExist('TBL_TEST') = 0 then
EXECUTE IMMEDIATE 'create table TBL_TEST...';
INSERT INTO TBL_TEST (...) VALUES (...);
end if;
end;