CODEWITHSUNDEEP
sqloracle-databasestored-procedures
user2737223
user2737223

Reputation:

Oracle Sql : Procedure which can create temporary tables inside it

I am new to Oracle Sql and facing an issue :

I want to create a temporary table inside procedure .

LIKE: 

CREATE PROCEDURE P
  AS
  BEGIN
    CREATE TEMPORARY TABLE A(ID int);
  END P;

BUT THIS IS GIVING ME AN ERROR

How Can I Create a temporary table inside procedure.

I have seen other answers on stackoverflow but that doesn't answer my question properly Can you please help me out ?

Upvotes: 0

Views: 34357

Answers (3)

Joss
Joss

Reputation: 11

You must declare your procedure as:

create or replace PROCEDURE MYPROCEDURE AUTHID CURRENT_USER IS

script varchar(4000);

BEGIN

script:= 'CREATE GLOBAL TEMPORARY TABLE BNMCODIAGNOSTICASSOCIE_TEMP
ON COMMIT PRESERVE ROWS
as select ........';

EXECUTE IMMEDIATE script;

commit;

END;

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231651

Why do you want to create a temporary table in a stored procedure in the first place?

It is relatively common to create temporary tables in other databases (SQL Server and MySQL, for example). It is very, very rare to do the same thing in Oracle. In almost every case where you are tempted to create a temporary table in Oracle, there is a better architectural approach. There is a thread over on the DBA stack that discusses alternatives to temporary tables and why they are not commonly needed in Oracle.

Programmatically, you can create objects using dynamic SQL

CREATE OR REPLACE PROCEDURE dont_do_this
AS
BEGIN
  EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE a( id INTEGER )';
END;

If you create a temporary table dynamically, however, every reference to that table will also need to be via dynamic SQL-- you won't be able to write simple SELECT statements against the table. And the definition of a temporary table in Oracle is global so it is visible to every session. If you have two different sessions both trying to create the same table, the second session will get an error. If you expect the table to have a different definition in different sessions, you've got even more problems.

Upvotes: 8

Ollie
Ollie

Reputation: 17538

You could use Dynamic SQL with EXECUTE IMMEDIATE:

CREATE OR REPLACE
PROCEDURE p
AS
BEGIN
   EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE A(id NUMBER)...etc';
END p;

Edit: Obviously you'll have to ensure your syntax is correct within the EXECUTE IMMEDIATE statement.

Hope it helps.

Upvotes: 4

Related Questions