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

[Michael Lee <homestoremike@YAHOO.COM>]

Ashish,
Thanks for your reply, but what would removing the
on-error statement buy me?  If I remove the on-error,
I just get the database error, which is basically what
is in $SQL-Error.

I'm using the on-error because I would like to capture
the exact key values that the SQL dies on due to
constraint violations.

Has anyone done this before?  The only other way that
I thought of was to complete the table load, then do a
SELECT <blah> FROM TARGET_TABLE WHERE <PRIMARY KEY>
NOT IN SELECT <BLAH> FROM SOURCE_TABLE.

I am running Oracle 8.1.6, and I'm using a database
link to connect between the source database and the
target database.

Any help is greatly appreciated.

Mike


--- Ashish Bhatt <ashish_bhatt@HOTMAIL.COM> wrote:
> 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
> >


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