Create Temporary Table in ORACLE Stored Procedure.

Queation :-  Create Temporary Table in ORACLE Stored Procedure.

I want to create Temporary Table, when SP is executed and then populate some data in it.
My Sample code is displaying error :- PL/SQL: ORA-00942: table or view does not exist.

Thx.


Sample Code :-

CREATE OR REPLACE PROCEDURE Temptabletest
AS
BEGIN      
EXECUTE IMMEDIATE       'CREATE global TEMPORARY TABLE G_Temp_1
      (
            CLIENT_CODE NUMBER(5)
      )
ON COMMIT DELETE ROWS';            
INSERT INTO G_Temp_1 VALUES (100);
END;
LVL 1
winsoftechAsked:
Who is Participating?
 
SujithData ArchitectCommented:
CREATE OR REPLACE PROCEDURE Temptabletest
AS
BEGIN    
EXECUTE IMMEDIATE      'CREATE global TEMPORARY TABLE G_Temp_1
     (
          CLIENT_CODE NUMBER(5)
     )
ON COMMIT DELETE ROWS';          
execute immediate 'INSERT INTO G_Temp_1 VALUES (100)';
END;
0
 
SujithData ArchitectCommented:
Since you are creating this temporary table dynamically; there is no object of name G_Temp_1 avaiable at compile time. Hence the error.
0
 
SujithData ArchitectCommented:
So you should do all the manipulations to the dynamically created table using dynamic SQL; use the code posted above.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
winsoftechAuthor Commented:
Amazing....
I tried to find out solution in Expert-Exchange all day....
not a single solution like this.

Solution within 2 minutes of submition.

Great.

Thx Sujit80
0
 
Mark GeerlingsDatabase AdministratorCommented:
"I want to create Temporary Table, when SP is executed..."

No you don't!  That is a SQL Server way of doing things.  That is *NOT* the best way to do things in Oracle!

If you need a temporary table at all (and remember this Oracle, not Sql Server, so the data concurrency and record-locking is very different than what you may be used to) you should create a "global temporary table" just once, outside of your stored procedure.  Then your stored procedure can simply use the existing global temporary table.

Oracle PL\SQL procedures do not directly support DDL statements (like: "create table...", etc.).  You can use them in PL\SQL if you need to, with either the "execute immediate" syntax, or the more complex DBMS_SQL package procedures, but either of these add a performance penalty, and they could add a security risk as well.  Oracle PL\SQL by default supports only these four basic SQL operators: select, insert, update, delete.
0
 
winsoftechAuthor Commented:
May i know what are the Performance and security risks involver...
0
 
SujithData ArchitectCommented:
It is always suggested to avoid dynamic sql as much as possible : But in certain cases you cannot live without.
Dynamic sql performes poorer compared to the embedded sql in pl/sql. For the reason that it forces a parsing each time executed.
If your code is executed much frequently; or if it is used in some online systems where performance is much critical; avoid the use of dynamic sql. If cannot; make sure to use bind variables in the dynaic piece of code.

Considering the quesion you have posted above: You should take care of the concurrency issues. I.e. Multiple sessions accessing the same table. Hope you are aware of the concepts of "Temporary Tables" in Oracle. If the purpose is just to store some temporary data; you can use a regular table instead.
0
 
SujithData ArchitectCommented:
One correction here:
"You should take care of the concurrency issues. I.e. Multiple sessions accessing the same table".
I meant multiple sessions accessing the same procedure. (Not sure whether you have a scenario like this at all.)
0
 
Mark GeerlingsDatabase AdministratorCommented:
Oracle PL\SQL normally uses "early binding" of object (table and column) names to their corresponding database objects at compile time.  This is part of what allows for the high scalability and performance that Oracle offers.  If you choose to use "dynamic SQL" instead, which means that the objects the procedures refers to either don't exist, or are unknown at compile time, then at runtime, Oracle has a lot more work to do (validating the object names, making sure the user is authorized to use them, etc.).  If you application only has to support a few dozen users, you may not notice the performance difference.

The security risk comes in through what you ask (or allow) the dynamic SQL to do.  If this is just processing "create table..." statements that are hard-coded into the procedure, then there is no security risk, just an additional performance penalty besides what I mentioned above.  If the procedure though accepts an input parameter, then uses that in a "create table..." or similar dynamic SQL statement, you are at risk.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.