sas entreprise guide copy sas table to an oracle database

0
votes

i want to copy SAS table bcc_07_11_2016_ from '/sas/sasdata/test/test2' to a database oracle schema, i use this sas code in SAS entreprise guide, and i got this error

ERROR: Error attempting to CREATE a DBMS table. ERROR: ORACLE execute error: ORA-00911: invalid character..

LIBNAME LBSRC ORACLE PATH="xx" SCHEMA="xx" USER="xx" PASSWORD="xx"; LIBNAME GDC '/sas/sasdata/test/test2'; proc sql ; create table LBSRC.bcc_07_11_2016_ as select * from GDC.bcc_07_11_2016_; quit ;

Can you please help to correct this SAS CODE. thanks

2
oracle

2 Answers

0
votes

The SAS/ACCESS to ORACLE engine is probably generating INSERT statements that get executed in the ORACLE connection session. My recollection is that the engine does this internally with some form of a prepared statement. However, not knowing for sure, the data in you BCC table could contain some unusual quoting that is confounding the generated INSERT statement, or perhaps the data contains Unicode characters that your ORACLE session can not handle. You might also have character data in the BCC table that is so long it exceeds the engines ability to generate a valid string for insertion.

The SQL documentation can inform you about options for debugging and logging generated statements. Also, for maximum debugging detail you will need to look at SAS options sastrace= and sastraceloc=

0
votes

Speak to the admin of the SAS environment and/or Oracle and ask them to give you the details needed for a pass through query to do this. They may also have blocked the ability to write to the Oracle box.