Date: Thu, 1 Mar 2001 16:40:07 -0500
Reply-To: Michael Davis <sas-l@BASSETTCONSULTING.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Michael Davis <sas-l@BASSETTCONSULTING.COM>
Subject: problem updating Oracle tables in-place
Content-Type: text/plain; charset="us-ascii"; format=flowed
The following problem occurs with SAS 8.1 run on an Windows NT server with
I'm working with a client that has an ERP (in this case, BaaN) whose data
is stored in Oracle databases. They would like to have the ability to use
SAS to update those tables outside the ERP with data that must come from
other (usually legacy) systems. Here is the problem.
Some of the Oracle tables to be updated have date or date-time
columns. These columns are usually specified in Oracle as being not
null. The ERP is apparently putting in a value that is not null but which
SAS interprets as an invalid date or date-time value.
So when we do something like:
data ora_devl.<table name> ;
modify ora_devl.<table name> <update table> ;
by <key vars> ;
SAS reads in the Oracle data and sets those variables to missing. When
Oracle attempts to update the table in-place, it refuses to perform the
update and reports an error. This occurs even when the update table omits
the troublesome columns. Why? When a MODIFY statement is processed, an
UPDATE statement is generated including all columns found on the database
We're looking at some cumbersome work-a-rounds and talking to the ERP
vendor but if anyone else has hit this type of problem and cares to offer a
better solution than we might have thought of, please share.
BTW, PROC SQL updating does work for us but is unacceptably slow when
updating a large table.
- Michael "Mad Doggy" Davis
Michael L. Davis
Bassett Consulting Services, Inc.
10 Pleasant Drive
North Haven CT 06473-3712