RE: [sqr-users] RE: TEMPORARY TABLES

["Hood Louise (app1lxh)" <app1lxh@ups.com>]

Denise,

Thanks--you've affirmed what I've found out since yesterday (like you said:
through trial and error).  Thanks to everyone for their suggestions and
work-arounds!!

What worked for us is getting the create table out of begin-setup (like you
described).  Now the create table is in the begin-program code stream:  


begin-program

  do GetParameters

  let $currdttm = datetostr(datenow(),'MMDDYYYYHHMISS') 
  DISPLAY '$currdttm = ' NOLINE
  DISPLAY $currdttm
  let $TMPTBL = 'SPCRPT'||$userid||$currdttm
  DISPLAY '$TMPTBL = ' NOLINE
  DISPLAY $TMPTBL 
 
begin-SQL 

create table [$TMPTBL]
  (SORT_SVCPVR_NA CHAR(35) NOT NULL,
  SORT_SVCPVR VARCHAR(10) NOT NULL,
  SORT_QTR_BEGIN INTEGER NOT NULL,
  SORT_YTD_REV_PCT DECIMAL(4,1) NOT NULL);

end-SQL

  . . . . 

end-program


Another thing that I found out is that since the table is not in the
begin-setup, you can't do DROP TABLE before CREATE TABLE or you get the
nasty error message:

(SQR 5528) ORACLE OCIStmtExecute error 942 in cursor 1:
   ORA-00942: table or view does not exist

Error on line 149:
   (SQR 3735) Could not execute SQL.

SQR: Program Aborting.


(Line 149 in this case is the begin-sql right before DROP TABLE.)


Something else that made me think the dynamic name wouldn't work is that I
made the unfortunate choice of creating a table name stringing
datenow('MMDDYYYYHHMISS') with _TBL.  ORACLE apparently does not accept
table names beginning with a number.  When I created the string as shown in
the above code, it works fine.  Also, planning for potential interruptions
in the SQR processing, the tables get prefixed with SPCRPT.  A cleanup
script will be run periodically to purge any tables beginning with SPCRPT
that get left behind due to the report not completing for whatever reason.


The only other problem we encountered was that the PDF file being created
did not have a unique name.  When we stress tested the SQR and everyone was
trying to grab the same PDF file there was an SQR error condition.  This was
eliminated by prefixing the PDF filename with the $userid, which should
virtually remove contention for the same file.

Thanks again to the group for all suggestions!
Louise


-----Original Message-----
From: White, Denise [mailto:DEWhite@vicr.com] 
Sent: Wednesday, April 09, 2003 4:20 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] RE: TEMPORARY TABLES


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

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