DDL statements in PL/SQL?

Question

I am trying the code below to create a table in PL/SQL:

DECLARE
    V_NAME 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;
/

The SELECT statement fails with this error:

PL/SQL: ORA-00942: table or view does not exist

Is it possible to CREATE, INSERT and SELECT all in a single PL/SQL Block one after other?

Answer 1

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.

I can recommend René Nyffenegger's post "Why is dynamic SQL bad?" for reasons why you should avoid dynamic SQL, if at all possible, from a performance standpoint. Please also be aware that you are much more open to SQL injection and should use bind variables and DBMS_ASSERT to help guard against it.