[sqr-users] RE: TEMPORARY TABLES

["White, Denise" <DEWhite@vicr.com>]

Hi Louise,

I have encountered the same problem with multiple users running the same job 
and having conflicts.  To create a unique name for the temporary table, I 
appended the $prcs_process_instance to the end of the table name, making it 
dynamic.  However, I found that after I did this, I could not create the table 
in the BEGIN-SETUP procedure, where I had always created temp tables before.  I 
had to create a regular procedure, in which I did the usual drop and then 
create.  Of course, because it was not in BEGIN-SETUP, I couldn't use the 
special ON-ERROR options (I usually use WARN for the drop), so I had to use my 
regular ON-ERROR procedure.  I also found that using a dynamic table name 
caused a few other glitches, such as having to qualify numeric columns when 
selecting from it (ex. "t.amt  &t.amt=number").  I can't remember all the 
specific glitches, so it may take a bit of trial and error on your part.  The 
only real problem to this approach is that every table name is unique, so if 
there is an abnormal stop to the program, it will not get dropped next time it 
is run (so I guess that initial drop before the create is really unnecessary).

We are on 4.3.4, so this is all assuming that this will still work the same for 
you.

HTH,

Denise M. White
Sr. Software Engineer
Vicor 

--__--__--

Message: 5
From: "Hood Louise (app1lxh)" <app1lxh@ups.com>
To: sqr-users@sqrug.org
Date: Tue, 8 Apr 2003 12:14:49 -0400 
Subject: [sqr-users] TEMPORARY TABLES
Reply-To: sqr-users@sqrug.org

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