PL/SQL embedded insert into table that may not exist

Question

Hi,

I much prefer using this 'embedded' style inserts in a pl/sql block (opposed to the execute immediate style dynamic sql - where you have to delimit quotes etc).

-- a contrived example
PROCEDURE CreateReport( customer IN VARCHAR2, reportdate IN DATE )
BEGIN

   -- drop table, create table with explicit column list
   CreateReportTableForCustomer;

   INSERT INTO TEMP_TABLE 
   VALUES ( customer, reportdate );
END;
/

The problem here is that oracle checks if 'temp_table' exists and that it has the correct number of colunms and throws a compile error if it doesn't exist.

So I was wondering if theres any way round that?! Essentially I want to use a placeholder for the table name to trick oracle into not checking if the table exists.

EDIT:

I should have mentioned that a user is able to execute any 'report' (as above). A mechanism that will execute an arbitrary query but always write to the temp_table ( in the user's schema). Thus each time the report proc is run it drops the temp_table and recreates it with, most probably, a different column list.

Answer 1

You could use a dynamic SQL statement to insert into the maybe-existent temp_table, and then catch and handle the exception that occurs when the table doesn't exist.

Example:

execute immediate 'INSERT INTO '||TEMP_TABLE_NAME||'  VALUES ( :customer, :reportdate )' using customer, reportdate;

Note that having the table name vary in a dynamic SQL statement is not very good, so if you ensure the table names stay the same, that would be best.

Answer 2

Maybe you should be using a global temporary table (GTT). These are permanent table structures that hold temporary data for an Oracle session. Many different sessions can insert data into the same GTT, and each will only be able to see their own data. The data is automatically deleted either on COMMIT or when the session ends, according to the GTT's definition.

You create the GTT (once only) like this:

create globabal temporary table my_gtt
(customer number, report_date date) 
on commit delete/preserve* rows;

* delete as applicable

Then your programs can just use it like any other table - the only difference being it always begins empty for your session.

Answer 3

Using GTTs are much preferable to dropping/recreating tables on the fly - if your application needs a different structure for each report, I strongly suggest you work out all the different structures that each report needs, and create separate GTTs as needed by each, instead of creating ordinary tables at runtime.

That said, if this is just not feasible (and I've seen good examples when it's not, e.g. in a system that supports a wide range of ad-hoc requests from users), you'll have to go with the EXECUTE IMMEDIATE approach.