Re: [sqr-users] TEMPORARY TABLES

[Christine Sessler <cms41@cornell.edu>]

Louise -

Been there, struggled with that as we moved from Informix (temp tables on 
the fly) to Oracle.

Our process is working pretty well for us.  The one thing that we have 
found is that you must remember that if you change the temp table 
structure, the SQR will NOT auto-build it if it already exists.  In DEV, 
we've gotten around this by putting a DROP TABLE statement into the 
SQR.  Also, as you migrate your SQRs through instances and build new 
instances, check to see if those temp tables already exist.  I'd suggest 
dropping them as you move from instance to instance.  This was made easier 
for us by our naming convention.

Here's what we did (a snippit of our whys and how tos).
Using Temp Tables in SQRs in Oracle
In the Oracle environment, temporary table structures do not automatically 
disappear, but the data does.  The approach to addressing this need will be 
to create the temporary table within the SQR, but only if the table does 
not already exist.
The following outlines the steps for remediation and maintenance of 
temporary table usage in Oracle.
Because the structure of the table will continue to exist after the program 
completes processing, names of all temporary tables must begin with 
CU_GT_(program name)_(table name) to allow easy recognition of them.
Ensure that all temporary table creations are performed in the BEGIN-SETUP 
procedure.
Only execute the CREATE statement if the temporary table does not exist, by 
checking for its existence in the ALL_TABLES table and executing the CREATE 
statement only if it does not.
In CREATE statements use the GLOBAL TEMPORARY syntax with the ON COMMIT 
PRESERVE ROWS (to handle programs that may perform intermittent commits) 
clause.
Indexes must be created on temporary tables while they are empty; 
therefore, CREATE INDEX statements must be executed right after the CREATE 
table statement within the conditional statement.
Begin-Setup
Begin-SQL
DECLARE t_count NUMBER;;
BEGIN
SELECT count(*) INTO t_count
FROM all_tables WHERE table_name = 'CU_GT_PAY604CU_FUT_PAY_CAL';;
if t_count = 0 THEN
EXECUTE IMMEDIATE
CREATE GLOBAL TEMPORARY TABLE CU_GT_PAY604CU_FUT_PAY_CAL
             (fpc_company       char(3),
              fpc_paygroup      char(3),
              fpc_pay_end_dt    char(11),
              fpc_pay_begin_dt  char(11),
              fpc_working_days  number)
        ON COMMIT PRESERVE ROWS;;
EXECUTE IMMEDIATE
CREATE INDEX &<index name>;;
END IF;;
END;;
End-SQL
End-Setup

Maintenance
Because the structure of a temporary table will continue to exist after the 
program completes processing, changes to the structure will require a 
request to the DBAs to drop the table before you can run the changed program.
Work Units created to migrate program changes that include temporary table 
structure changes must include a SQL file containing any DROP statements 
for the changed temporary tables.

Let me know if you have any questions,
Chris


At 12:14 PM 4/8/2003 -0400, you wrote:
>Hi All,
>
>Has anyone had any experience (or luck) with creating temporary tables that
>are unique for each user running a report?  I have an sqr  in which a
>temporary table is created in the setup section.  Within the program,
>calculations are performed and results stored in the table for later
>selection and manipulation.   The sqr works fine as long as someone else
>doesn't try to generate the report while another user has already kicked the
>program off (the table is already in use and not accessible since it doesn't
>have a unique name).
>
>We are using Oracle 8i and sqr 6.0.
>
>I searched the sqrug archives, finding references to CREATE GLOBAL TEMPORARY
>TABLE, which I also tried without success. It was referred to in a response
>for DB2 environment, so maybe Oracle doesn't recognize this?
>
>There is virtually nothing in the SQR reference manuals and user guide re:
>temp tables, so if anyone has been there before and conquered this problem,
>I'd appreciate your input.
>
>Thanks,
>Louise
>
>
>_______________________________________________
>sqr-users mailing list
>sqr-users@sqrug.org
>http://www.sqrug.org/mailman/listinfo/sqr-users

************************************************************
Christine Sessler
Cornell University
CIT/Business Information Systems
120 Maple Avenue
Ithaca, NY  14850

607.255.8149  - Office
607.255.6982  - Fax
cms41@cornell.edu
************************************************************
"If a dog jumps in your lap, it is because he is fond of you;
but if a cat does the same thing, it is because your lap is warmer." - 
Alfred North Whitehead
"The only normal people are the ones you don't know very well." - Joe Ancis

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users