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!
Content-Type: text/plain; charset="us-ascii"
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.
From: email@example.com [mailto:firstname.lastname@example.org]
On Behalf Of Ankit
Sent: Monday, July 02, 2007 2:41 PM
Subject: Reject records while loading Oracle from SAS!
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
Any idea on how rejected records at the load time can be captured into a
separate table/ dataset?