Solved

Create Temporary Table in ORACLE Stored Procedure.

Posted on 2006-11-27
9
6,130 Views
Last Modified: 2008-01-09
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;
0
Comment
Question by:winsoftech
  • 5
  • 2
  • 2
9 Comments
 
LVL 27

Accepted Solution

by:
sujith80 earned 500 total points
ID: 18019419
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
 
LVL 27

Expert Comment

by:sujith80
ID: 18019436
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
 
LVL 27

Expert Comment

by:sujith80
ID: 18019443
So you should do all the manipulations to the dynamically created table using dynamic SQL; use the code posted above.
0
 
LVL 1

Author Comment

by:winsoftech
ID: 18019445
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 18019462
"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
 
LVL 1

Author Comment

by:winsoftech
ID: 18019492
May i know what are the Performance and security risks involver...
0
 
LVL 27

Expert Comment

by:sujith80
ID: 18019624
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
 
LVL 27

Expert Comment

by:sujith80
ID: 18019635
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
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 18019666
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now