Solved

Create Temporary Table in ORACLE Stored Procedure.

Posted on 2006-11-27
9
7,730 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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
 
LVL 35

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 35

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

730 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