CODEWITHSUNDEEP
oracle-database
user1970839
user1970839

Reputation: 90

Oracle EXECUTE IMMEDIATE Not doing what expected in stored procedure

I have a stored procedure which has several checks in them however the following section gives me problems with the execute immediate

for example when I try to compile the procedure with the below statements it complains that table or view does not exist.

...
...
...
EXECUTE IMMEDIATE 'CREATE TABLE mytable(col1 number, col2 number, col3 number)';
EXECUTE IMMEDIATE 'INSERT INTO mytable (col1,col2,col3)
   SELECT a,b,c FROM source_table' ;

SELECT COUNT(*) INTO c FROM mytable;

6:18:22 [CREATE - 0 row(s), 0.000 secs] {50:29} PL/SQL: ORA-00942: table or view does not exist

However,

If I remove the

SELECT COUNT(*) INTO c FROM mytable;

It compiles and works.

Please advise on what is probably a very simple colution.

Thanks

Upvotes: 0

Views: 598

Answers (1)

sstan
sstan

Reputation: 36483

When Oracle compiles the stored procedure, it checks for the existence of mytable. If mytable doesn't exist at compilation time, it will give you an error.

Your options:

  1. Create the table ahead of time.
  2. You are stuck performing your SELECT COUNT(*) ... using dynamic SQL (EXECUTE IMMEDIATE) like you are already doing for the INSERT statement.

Note that, if you decide to go with the 2nd option, it is possible to assign the result of the query to your c variable, like this:

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM mytable' INTO c;

However, it's generally not a good idea to mix DDL statements with queries in your stored procedures, because:

  1. It forces you down the path of dynamic SQL, which can easily break.
  2. You can't write a proper transactional stored procedure, because every DDL statement performs an implicit commit.

Upvotes: 2

Related Questions