Solved

Select into a Global Temporary Table from a Procedure.

Posted on 2002-05-10
4
600 Views
Last Modified: 2008-02-01
Hi, I am trying to create a temporary table in oracle within a procedure and then insert records into that table within the same procedure.  Is this possible?

example

Create OR Replace Procedure TESTME AS

     BEGIN

     execute immediate  'Create Global Temporary Table TMP_MTMDayOne (
     Trade              Varchar2(8),
     Execution        Varchar2(32),
     TradeType        Varchar2(8),
     Counterparty     varchar2(32),
     Loadshape        varchar2(32),
        Valuationtime    date,
     Deliverytime      date,
     TSPeriod      varchar2(16),
     MarketArea      varchar2(32),
     NPVFactor      number(16,6),
     Volume           number(16,4),
     Value           number(16,6),
     MarketValue      number(16,6)) ON COMMIT PRESERVE';

    insert into tmp_mtmdayone
    select
     TRADE."TRADE",
     TRADE."EXECUTION",
     POSITION."TRADETYPE",
     POSITION."COUNTERPARTY",
     POWERPOSITION."LOADSHAPE",
     VALUATION."VALUATIONTIME",
     VALUATIONDETAIL."DELIVERYTIME",
     VALUATIONDETAIL."TSPERIOD",
     VALUATIONDETAIL."MARKETAREA",
     VALUATIONDETAIL."NPVFACTOR",
     VALUATIONDETAIL."VOLUME",
     VALUATIONDETAIL."VALUE",
     VALUATIONDETAIL."MARKETVALUE"
FROM
    VALUATIONDETAIL VALUATIONDETAIL,
    VALUATION VALUATION,
    POWERPOSITION POWERPOSITION,
    POSITION POSITION,
    TRADE TRADE    
WHERE
    VALUATIONDETAIL."VALUATION" = VALUATION."VALUATION" AND
    VALUATIONDETAIL."POSITION" = POWERPOSITION."POSITION" AND
    VALUATIONDETAIL."SEQ" = POWERPOSITION."SEQ" AND
    POWERPOSITION."POSITION" = POSITION."POSITION" AND
    POSITION."TRADE" = TRADE."TRADE" AND
    VALUATION."VALUATIONTIME" = to_date('2002-05-02 00:00:00','YYYY-MM-DD HH24:MI:SS');  
     
     END Rep_MTMCompare;
/


When I run this I get the following error


LINE/COL ERROR
-------- ---------------------------------------------------------
20/5     PL/SQL: SQL Statement ignored
20/17    PLS-00201: identifier 'TMP_MTMDAYONE' must be declared
SQL>

Any Ideas anyone.
0
Comment
Question by:mitai
  • 2
4 Comments
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 7002228
Remember, PL\SQL does not normally support "DML" statements (like: create table..., create index..., etc.).  You can use DML statements in PL\SQL if you use the "execute immediate" syntax or procedures in the dbms_sql package.

Another problem, you cannot compile a PL\SQL procedure if it refers to a table that does not exist at compile time (unless you only refer to the non-existant table in dynamic PL\SQL commands like those in the paragraph above.)

The usual procedure is to create the global temporary table first, then you can write, compile and run PL\SQL procedure(s) that refer to it with no problem.

I'm guessing that you have SQL Server experience.  "Temporary" tables are apparently much more common in SQL Server stored procedures than they are in Oracle, but the "global temporary tables" of Oracle8.1 and later seem to offer most of the features and advantages of SQL Server temporary tables.  You just need to create them outside of the PL\SQL procedure(s) that will use them.
0
 

Author Comment

by:mitai
ID: 7002242
Ok makrgeer, but if I create the Global temporary table first, and multiple copies of this procedure are going to be called by multiple users will I not get problems with the table create / drop.  I thought the whole idea behind Global Temporary tables was that it was only valid within the session, IE the one that created it used it.

Can you think of another way to do this?
0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 100 total points
ID: 7002281
The data in a global temporary table is only available to the session that inserted it.  This is not necessarily the same session (or user) who created it.  Security is the same as for standard tables, so if users other than the creator need to use it, you will have to issue grants and you may want to create synonym(s).  Global temporary tables do support multiple, concurrent use and each session will see only it's own data no matter how many other session use using the same global temporary table at the same time.

No there will be no problem with "creating" and "dropping" the temporary table, since the temporary table will just be created once (outside of PL\SQL typically) and it will stay in the database as a permanent object.  This allows you to compile stored procedures that refer to it.
0
 
LVL 7

Expert Comment

by:yoren
ID: 7002749
mitai,

A common way to do what you want is to have a system-wide "temporary" table that is not really temporary. Create the table once, but with an additional SESSION_ID column. Then create a sequence for populating that field. Your PL/SQL procedure should should select a session_id at the beginning of the procedure:

declare
  s_id number;
begin
  select tmp_mtm_id_s.nextval into s_id from dual;
...

and then add that column to all your inserts and selects to distinguish your rows from any other sessions.
 
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

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

743 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

11 Experts available now in Live!

Get 1:1 Help Now