asked on

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;
/

Stephen Lappin

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;

Stephen Lappin

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.

slightwv (䄆 Netminder)

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?

ASKER CERTIFIED SOLUTION

Stephen Lappin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

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.