Which row is the error in an Insert-Select using on-error

[Michael Lee <homestoremike@YAHOO.COM>]

Hello,
I want to be able to catch orphan records being
inserted into a child table.  I have constraints
enabled in the database.  When it finds a row that
violates the parent key constraint, it goes into the
"on-error" procedure, which I am currently displaying
the $SQL-Error.

However, I would like to be able to see the exact keys
that it is failing on.  I have a generic Insert-Select
procedure that I feed the tablename to so that I can
reuse it for all of my tables.  I do not want to have
a separate Select statement with the columns
identified and then perform an Insert.  I know that
will work, and I'll be able to capture the fields that
it is dying on.  But I would like to be able to
preserve my generic Insert-Select procedure and feed
in the tablename for maintenance purposes.

I've attached some of the code below as well as the
log.  Any ideas?  Thanks in advance,

Michael

------------------------------------------------------
begin-procedure Process-Email-Addresses

   let $Target_Table_Root = 'EMAIL_ADDRESSES'
   let $Source_Table = 'PS_EMAIL_ADDRESSES'
   do Get-Target-Name
   do Insert-Select

begin-SELECT
COUNT(*) &COUNT_EMAIL_ADDRESSES
move &COUNT_EMAIL_ADDRESSES to #COUNT
FROM [$Target_Table]
end-SELECT

   do Get-Current-DateTime
   show $AsOfToday ' ' $AsOfNow ' ' $Target_Table '
table load completed with ' #COUNT ' rows'

end-procedure

begin-procedure Get-Target-Name

   let $Target_Table = $Target_Table_Root || '@' ||
$Database_Link_Name
   !show 'Target_Table = ' $Target_Table
end-procedure


begin-procedure Insert-Select

begin-SQL on-error=Capture-SQL-Error

INSERT INTO [$Target_Table]
SELECT * FROM [$Source_Table]

end-SQL

end-procedure


begin-procedure Capture-SQL-Error
   show 'Oracle SQL Error: ' $SQL-Error
end-procedure

----------------------------------------------------
Database Name (Optional, Press ENTER to continue):
Process Instance (Optional, Press ENTER to continue):
Report ID   : STAGING_HR
Report Title: Populate HR Staging Area

Process Begin Date : 03-JUN-2002
Process Begin Time : 09:56:08_AM
No Process Instance generated.
03-JUN-2002 09:56:08_AM Begin - Deletion of Staging
Area Tables
03-JUN-2002 09:56:14_AM End - Table deletion
completed.
03-JUN-2002 09:56:14_AM COUNTRY_TBL@TESTA table load
completed with 238.000000 rows
03-JUN-2002 09:56:14_AM PERSON@TESTA table load
completed with 439.000000 rows
03-JUN-2002 09:56:14_AM PERSONAL_INFO@TESTA table load
completed with 439.000000 rows
03-JUN-2002 09:56:14_AM NATIONAL_ID@TESTA table load
completed with 432.000000 rows
03-JUN-2002 09:56:14_AM EMAIL_ADDR_TYPE_TBL@TESTA
table load completed with 5.000000 rows
Oracle SQL Error: ORA-02291: integrity constraint
(SYSADM.REFPERSON93) violated - parent key not found
ORA-02063: preceding line from TESTA
03-JUN-2002 09:56:14_AM EMAIL_ADDRESSES@TESTA table
load completed with 0.000000 rows



__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com