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

[Ashish Bhatt <ashish_bhatt@HOTMAIL.COM>]

Remove on-error=Capture-SQL-Error as below:



begin-procedure Insert-Select

begin-SQL

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

end-SQL

end-procedure



----- Original Message -----
From: "Michael Lee" <homestoremike@YAHOO.COM>
To: <SQR-USERS@list.iex.net>
Sent: Monday, June 03, 2002 10:06 AM
Subject: Which row is the error in an Insert-Select using on-error


> 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
>