DDL statements in PL/SQL?

Answer

I assume you're doing something like the following:

declare
   v_temp varchar2(20);
begin
   execute immediate 'create table temp(name varchar(20))';
   execute immediate 'insert into temp values(''XYZ'')';

   select name into v_name from temp;
end;

At compile time the table, TEMP, does not exist. It hasn't been created yet. As it doesn't exist you can't select from it; you therefore also have to do the SELECT dynamically. There isn't actually any need to do a SELECT in this particular situation though you can use the returning into syntax.

declare
   v_temp varchar2(20)
begin
   execute immediate 'create table temp(name varchar2(20))';
   execute immediate 'insert into temp
                      values(''XYZ'')
                      returning name into :1'
                returning into v_temp;
end;

However, needing to dynamically create tables is normally an indication of a badly designed schema. It shouldn't really be necessary.

All plsql Questions

Ask your interview questions on plsql

Write Your comment or Questions if you want the answers on plsql from plsql Experts
Name* :
Email Id* :
Mob no* :
Question
Or
Comment* :
 





Disclimer: PCDS.CO.IN not responsible for any content, information, data or any feature of website. If you are using this website then its your own responsibility to understand the content of the website

--------- Tutorials ---