Re: [sqr-users] Oracle Begin-Setup SQR data Errors

["George Jansen" <GJANSEN@aflcio.org>]

Might I suggest using Oracle Global Temporary tables? No need to setup, drop, 
or truncate.

>>> PeterG.Clark@vac-acc.gc.ca 09/30/05 12:44 PM >>>
If you are not ensuring elsewhere that your temp table is being dropped (after 
initial run), you would need to put a DROP TABLE in your setup section prior to 
your create table in order to ensure that the CREATE does not error out because 
the table already exists.

begin-SQL On-Error=SKIP
   drop table PS_UWW_FA_TRM_BKUP
end-SQL
Begin-SQL       on-error=stop      
   CREATE TABLE PS_UWW_FA_TRM_BKUP
   tablespace uwwtmp
   AS SELECT * 
   FROM PS_STDNT_FA_TERM
End-SQL


>>> pickj@uww.edu 2005/09/30 12:13:54 >>>
Hi,

 

I am a programmer at UW-Whitewater we have. 

PeopleSoft 8.21.4

Oracle 9.2

Unix HP 11

 

(This is for a user who wants to be able to run a process that will
delete older rows on an effective dated table.)

 

I am setting up an SQR program that will create temporary tables in the
begin-setup, and later use one of those tables in a regular procedure.
What control can be used to be sure each step in begin-setup is
completed successfully?  For example.

 

In begin-setup

 

Begin-SQL       on-error=stop      

CREATE TABLE PS_UWW_FA_TRM_BKUP

tablespace uwwtmp

AS SELECT * 

FROM PS_STDNT_FA_TERM

End-SQL

 

Only syntax errors will stop the program - with an oracle statement.

A data error, such as not enough disk space, stops the immediate
statement with the program continuing to whatever is next. 

Such as...

 

In a regular procedure

!           Let $SQL-Error-Text = 'Truncate table PS_STDNT_FA_TERM    '


!

!           Begin-SQL       on-error=Db-Error        

!           TRUNCATE TABLE PS_STDNT_FA_TERM

!           End-SQL

 

In a regular procedure Db-Error can stop the program.  

But what can stop the program with a data error when going between
begin-setup to a regular procedure?

Currently I am putting counters into the program ... 

 

Thanks for any ideas

 

Jim 

_______________________________________________
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


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