CODEWITHSUNDEEP
sqloracle-databaseplsql
Satyaki
Satyaki

Reputation: 3

Execute Immediate on DDL Statements

I executed the following code in Oracle Database but faced with an error of the view name does not exist. Can anyone look into it ?

Declare 
Stmt varchar2(2000);
Var number;
Begin
Stmt:='create or replace view emp_dept_v as select * from emp';
Execute immediate stmt;
Select count(*) into var from emp_dept_v;
Dbms_output. Put_line(var);
End;

I know that DDL statements are auto commited, but in this case I have to add a commit statement after the execute immediate statement to resolve the issue.

Upvotes: 0

Views: 2765

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

What you get is a compilation error. The PL/SQL will be compiled before it is executed. So during compilation, the table you try to SELECT will not be available. And hence the error. Only if the view name already exists, this will work. Try your select too as dynamic.

Declare 
Stmt varchar2(2000);
Var number;
Begin
Stmt:='create or replace view emp_dept_v as select * from emp';
Execute immediate stmt;

Stmt:='Select count(*) from emp_dept_v';
Execute immediate stmt into var;
Dbms_output. Put_line(var);
End;
/

And by the way, DDLs do not need a COMMIT;

Upvotes: 4

Related Questions