Solved

Select into a Global Temporary Table from a Procedure.

Posted on 2002-05-10
4
602 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 35

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 35

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.

Question has a verified solution.

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

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

770 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