• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6777
  • Last Modified:

Oracle Stored Proc with temp table

Hello
Below is my stored procedure which works - but, what I would now like is to create a table (see commented out two lines below IS) and then use this table later in my procedure then drop the table at the end of the procedure
How do I do this and what is the syntax please as I have never done this before.

CREATE OR REPLACE procedure PHILPKGPROC
(
 startdate in varchar2,
 enddate in varchar2,
 p_cursorr in out PHIL.PHILCURSOR
)
IS    
--CREATE TABLE PIP(REF_ID VARCHAR2(25),OPENTIME DATE);
--INSERT INTO PIP SELECT INCIDENT_ID,OPEN_TIME FROM INCIDENTSM1;
begin
open p_cursorr for
select 'INDIA' COUNTRY,
         '1 - Incidents' REFERENCE,
         t01.contact_name,
         t01.contact_first,
         t01.contact_last,
         t2.name grp,
         t3.full_name analyst,
         t1.numberprgn ref_id,
         t1.category category,
         t1.product_type type,
         t1.cause_code cause_code,
         t1.close_time close_time                                 
from          incidentsm1          t01
       left outer join screlationm1         t02 on t01.incident_id = t02.source
       left outer join probsummarym1        t1 on t02.depend = t1.numberprgn
       left outer join assignmenta1      tt1 on t1.closed_by = tt1.operators       
       left outer join assignmentm1         t2 on tt1.name = t2.name
       left outer join operatorm1             t3 on t1.closed_by  = t3.name
       where t2.name in('ACIS IT SUPPORT','REACTIVE OPERATIONS')
       and substr(t01.contact_name,1,1) = 'I'
       and t1.close_time between to_date(startdate,'dd/mm/yy hh24:mi:ss') and to_date(enddate,'dd/mm/yy hh24:mi:ss')
;       
end PHILPKGPROC;
/
0
philsivyer
Asked:
philsivyer
  • 3
  • 2
1 Solution
 
Stephen LappinSenior TechnologistCommented:
A better solution is to create a Global Temporary Table - it is far more efficent in terms of performance, and it very easy to use. This table definition is held in the data dictionary, but no space is allocated to it until it is used. When you populate/query the table, you only see data put there by your session - no other session can see what data you have in the table.

The table will automatically delete all rows in it (for your session) when you commit a transaction. If you omit the ON COMMIT DELETE ROWS clause, the data will remain for the session, and will be deleted at the end of your session.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT DELETE ROWS;
0
 
Stephen LappinSenior TechnologistCommented:
So, create the temp table like so:

CREATE GLOBAL TEMPORARY TABLE TABLE PIP(
  REF_ID VARCHAR2(25),
  OPENTIME DATE
) ON COMMIT DELETE ROWS;

Then all you need in yours procedure is this line:

INSERT INTO PIP SELECT INCIDENT_ID,OPEN_TIME FROM INCIDENTSM1;

Once you commit your transaction, the data in PIP will be automatically deleted.
0
 
slightwv (䄆 Netminder) Commented:
I agree with lappins that a global temporary table is best here.

I just wanted to add a little:
To perform DDL in PL/SQL you need to use 'execute immediate'.
...
execute immediate 'create table ... ';
...

Now why this is bad (just 1 of many reasons):  What if this procedure gets executed twice at the same time?
0
 
Stephen LappinSenior TechnologistCommented:
Do not create the TEMP Table on  the fly - it should be created once, and once only, before the stored procedures is complied (because you will not be able to compile it if the TEMP tables does not exist).

Doing DDL in a transaction is very expensive, and should be avoided if at all possible.
0
 
slightwv (䄆 Netminder) Commented:
Agreed.  I believed you had already covered that so I didn't.

I just wanted to let philsivyer know how to do it in case they ever needed to do something similar in the future.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now