Re: [sqr-users] Capturing SQL Errors in Oracle.

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

You can trap Oracle errors very well by using PL/SQL stored procedures
(preferably packaged) called from a Begin-SQL/End-SQL block. But is that
an option for you?

You write "our SQRs". Are these supplied by PeopleSoft, or do you mean
that your company has written them? If the former, you might want to
talk to PeopleSoft support.

I suppose that what I'd aim at is establishing the list of errors I
wanted to handle: key conflict, table or index out of space, etc. Then
I'd set up a test schema in DB2 that included tables of test data and a
table of error names and error codes. Then I could write a test program
with sections like

let $next-error = 'PRIMARY KEY CONFLICT
Begin-SQL ON-Error=record-error
INSERT INTO VICTIM1 (keycol)
VALUES (1);
End-SQL
....
Begin-Procedure
Begin-SQL
INSERT INTO standard-errors
VALUES(#sql-error, $error-description);
End-SQL
End-Procedure

Then you can accumulate the Oracle & DB2 error codes you want to handle.
Knowing them, you can create an SQC and you use #IFs to define your
variable names by which the error codes shall be known.



>>> manukoneru@yahoo.com 01/30/04 5:39 PM >>>
Hi folks,

We are converting from PeopleSoft 8SP2 on MVS/DB2 to
UNIX/ORACLE9i.

I don't have the environment to test yet, but am
assigned the task of handling the SQL Errors in our
SQRs so that the code will work both in an MVS/DB2 and
UNIX/ORACLE environments.

Has anyone come across a similar situation ? If so
could you please give me some insight into it.

I think more specifically I'm looking for what kind of
SQL Error messages would we get in the ORACLE database
for corresponding DB2 codes like +100, -803, -811, 
-903 etc.

Thanks in advance for your help.

Prasad Koneru.
Aquila Inc.

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/

_______________________________________________
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