Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

execute immediate and insert - table or view does not exist

mariusApr 9 2008 — edited Apr 15 2008
Hello,
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;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 13 2008
Added on Apr 9 2008
19 comments
3,673 views