Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Create Temporary Table in ORACLE Stored Procedure.

Posted on 2006-11-27
9
Medium Priority
?
9,828 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.

618 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