Reputation: 607

Why doesn't a PL/SQL block create a temporary table?

I would like to create and populate temporary table with data to process it inside loop statement like this:

DECLARE 
  cnt NUMBER;
BEGIN


  SELECT COUNT(tname) INTO cnt from tab where tname = 'MY_TEMP';
  IF (cnt > 0) THEN
    EXECUTE IMMEDIATE 'DROP TABLE MY_TEMP';
  END IF;

  EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE MY_TEMP (G NVARCHAR2(128), F NVARCHAR2(128), V NVARCHAR2(128)) ON COMMIT DELETE ROWS';  

  INSERT INTO MY_TEMP VALUES (N'G-value1', N'F-value1', N'V-value1');  
  INSERT INTO MY_TEMP VALUES (N'G-value2', N'F-value2', N'V-value2');  
  ...

  FOR record IN (SELECT G,F,V FROM MY_TEMP)
  LOOP

     ... Do something sophisticated with record.G, record.F, record.V 
  END LOOP;

  COMMIT;
END;

When I run this script inside PL-SQL Developer it tells me for the very first INSERT that MY_TEMP table or view doesn't exist even though my EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE ... ' statement seems to be executed without errors. I checked there is no MY_TEMP table inside tables list after script execution

When I run EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE ... ' alone it runs ok and MY_TEMP table is really created. After this the whole scripts runs ok.

How do I use this script without manually precreating MY_TEMP table ?

Upvotes: 0

Answers (5)

Mathias Magnusson

Reputation: 197

The probloem is not wityh your first insert. It is with the compile of your block. The table does not exist, but you are referencing it. Try creating it beforhand so it is there such as it will be once the bloick finishes. Now the code is likely to compile as the reference to the table exists when you run it.

However, then you'll get into trouble with the drop as your code has a share lock on the table so you are not allowed to drop it.

You either have to make your selects dynamic, or make sure the table is created and dropped outrside the execution of your block.

Upvotes: 0

Avrajit Roy

Reputation: 3303

The only way around for your problem is to make the whole INSERT INTO temp_table statements into EXECUTE IMMEDIATE in this way you can BYPASS the TABLE check during COMPILE Time first.
But this way in my opinion is not good at all. There are some questions in my mind which has be answred before answering this question.

1) Why Temp Table is created evertime and Dropped. 
We have option in GTT to keep or Remove Data after one Oracle Session.
2) Is this script a one time job ? If Yes then we can go for once GTT creation and the rest script will work fine.

Upvotes: 0

Luke Woodward

Reputation: 64969

How do I use this script without manually precreating MY_TEMP table ?

You can't. Unless of course you run everything after the creation of the temporary table using EXECUTE IMMEDIATE. But I cannot for a second recommend that approach.

The point is not that your script fails to run, but that it fails to compile. Oracle won't start running your block if it can't compile it first. At the point Oracle tries to compile your PL/SQL block, the table doesn't exist. You have a compilation error, not a runtime error.

I suspect that you are more familiar with temporary tables in SQL Server and are trying to use temporary tables in Oracle in the same way. If this is the case, then you will need to know that there are differences between temporary tables in Oracle and in SQL Server.

Firstly, there's no such thing as a local temporary table (i.e. a table visible to only one connected user) in Oracle. Oracle does have global temporary tables, but only the data in a global temporary table is temporary. The table itself is permanent, so once it has been created it will only be dropped if you explicitly drop it. Compare this with SQL Server temporary tables, which are dropped once all relevant users disconnect.

I really don't think you need to be creating the temporary table in your block. It should be sufficient to create it once beforehand.

Upvotes: 4