Oracle EXECUTE IMMEDIATE Not doing what expected in stored procedure -

i have stored procedure has several checks in them following section gives me problems execute immediate

for example when try compile procedure below statements complains table or view not exist.

... ... ... execute immediate 'create table mytable(col1 number, col2 number, col3 number)'; execute immediate 'insert mytable (col1,col2,col3)    select a,b,c source_table' ;  select count(*) c mytable; 

6:18:22 [create - 0 row(s), 0.000 secs] {50:29} pl/sql: ora-00942: table or view not exist

however,

if remove

select count(*) c mytable;

it compiles , works.

please advise on simple colution.

thanks

when oracle compiles stored procedure, checks existence of mytable. if mytable doesn't exist @ compilation time, give error.

your options:

1. create table ahead of time.
2. you stuck performing select count(*) ... using dynamic sql (execute immediate) doing insert statement.

note that, if decide go 2nd option, possible assign result of query c variable, this:

execute immediate 'select count(*) mytable' c; 

however, it's not idea mix ddl statements queries in stored procedures, because:

1. it forces down path of dynamic sql, can break.
2. you can't write proper transactional stored procedure, because every ddl statement performs implicit commit.