LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (March 2001, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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

Hello Friends,

The following problem occurs with SAS 8.1 run on an Windows NT server with Oracle 7.4.1.

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

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

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 Vice President Bassett Consulting Services, Inc. 10 Pleasant Drive North Haven CT 06473-3712 E-Mail: Web: Telephone: 203-562-0640 Facsimile: 203-498-1414 Messages: 888-477-1412

Back to: Top of message | Previous page | Main SAS-L page