Date:         Mon, 2 Jul 2007 14:54:39 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: Reject records while loading Oracle from SAS!
Comments: To: Ankit <ankit_gangal79@yahoo.com>
In-Reply-To:  <1183401677.033639.221670@a26g2000pre.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

Ankit:
See the recent SAS archives for a discussion of the SQL Server 'bulk
load' BCP program. Oracle bulk loads work similarly and have a similar
SAS/Access connection string syntax.
S

-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
On Behalf Of Ankit
Sent: Monday, July 02, 2007 2:41 PM
To: sas-l@uga.edu
Subject: Reject records while loading Oracle from SAS!


Hello,

I would like to know if someone has handled the case where data is being
loaded from SAS to Oracle database using SAS/ACCESS for Oracle and the
rejected records (due to whatever reason -. integrity constraint, null
value etc) are being output to a separate table/ dataset.

By default SAS throws up an error if a record insertion/ upate in Oracle
fails. This can be taken care off by setting the ERRLIMIT= option while
writing to the databse. This would ensure that SAS inserts/updates all
the records while leaving the rejected records and not throwing an error
on encountering the reject records.

However, is there anyway of capturing these rejected records into a
separate oracle table or SAS dataset? In SAS/ACCESS for Oracle I could
find a option of "BL_BADFILE= path anf filename"  which can be used in
DATA & PROC steps while accessing RDBMS using SAS/ACCESS. This option
identifies a file that contains records that were rejected during a bulk
load.

Any idea on how rejected records at the load time can be captured into a
separate table/ dataset?

Thanks!
Ankit