How to create and insert into a table in an anonymous block

oracle-11g-r2plsql

I have PL/SQL anonymous block that creates a table (using execute immediate) and then inserts data into the table.

When the block compiles, it gives me an ORA-00942: table or view does not exist error.

Well, no, it doesn't exist, but it will be created before the insert occurs. If I create the table before compiling, it will work.

How can I handle this?

Best Answer

Generally, it doesn't make sense to create tables in anonymous blocks let alone to then insert data into those tables in the same anonymous block. Creating new objects at runtime is a bad idea (hopefully you're not trying to create a temporary table like you would with other databases).

If you really do need to create a new table at runtime, you can insert data in the same anonymous block only by using dynamic SQL for the INSERT operation as well. You would need to use dynamic SQL every time you referenced the newly created table in the same anonymous block. You could also create the table in one anonymous PL/SQL block and then insert the data in a second anonymous PL/SQL block. That would not require that the INSERT statement use dynamic SQL.

Since it sounds from the comments like you are trying to create a temporary table like you would in other databases, you probably want to take a look at this StackOverflow thread that discusses alternatives to creating temporary tables in Oracle.

Related Solutions

Is Using Execute Immediate Bad Practice in Oracle

Well, it may seem kludgy, but compared to using DBMS_SQL, it's downright elegant.

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

It wouldn't perform as well, but to meet your requirements given your limitations, you could use a pipelined result set. Both your original procedure and your new procedure could then use the function. The original one would return the entire results, and the new procedure would wrap the function in a larger query that does the GROUP BY. In this way the core query code would not be duplicated. Here is a pipelined result set example along with two queries using it:

create or replace PACKAGE Example AUTHID Definer AS

Type tRow Is Record (
     F1 Date
   , F2 Date
   );
Type tTable Is Table of tRow;
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined;

END Example;
/


create or replace PACKAGE BODY Example AS

Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined AS
   vYesterday Date := trunc(sysdate-1);
Begin
  For vRow IN (
      SELECT pDate, vYesterday FROM DUAL
      UNION ALL
      SELECT pDate, vYesterday-1 FROM DUAL
      UNION ALL
      SELECT pDate+1, vYesterday FROM DUAL
  )
  Loop
    Pipe Row(vRow);
  End Loop;
End;

END Example;
/


SELECT * FROM TABLE(Example.PipelineResult);

SELECT F1, count(*) FROM TABLE(Example.PipelineResult) GROUP BY F1;

Best Answer

Related Solutions

Is Using Execute Immediate Bad Practice in Oracle

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

Related Question