04-24-2015 03:03 PM
I am attempting to create a table on an Oracle11GR2 database using an Oracle database user that has the "Create ANY table" , Create Session, and Unlimited tablespace Oracle system privileges.
From within Oracle itself the Oracle database user can create a table in its own and other schemas.
myora ORACLE USER='test_user_dggroup'
PASSWORD='XXXXX' path='ORADEV' schema='ILIP';
Create table myora.test_write_to_oracle_apr24_2PM
as select *
Execution Results are Below:
58 LIBNAME myora ORACLE USER='test_user_dggroup'
59 PASSWORD=XXXXXXXXX path='ORADEV' schema='ILIP';
NOTE: Libref MYORA was successfully assigned as follows:
Physical Name: ORADEV
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 29.45 seconds
cpu time 0.39 seconds
60 data test_input;
61 set myora.AC_HSPBYMO30ILQ1_2012_Q4_2012;
NOTE: There were 2421 observations read from the data set MYORA.AC_HSPBYMO30ILQ1_2012_Q4_2012.
NOTE: The data set WORK.TEST_INPUT has 2421 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
64 PROC SQL;
65 create table myora.test_write_to_oracle_apr24_2PM
66 as select *
67 from test_input;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
ERROR: ERROR: ERROR: ORACLE execute error: ORA-01031: insufficient privileges. With the
occurrence of the above ERROR, the error limit of 1 set by the ERRLIMIT= option has been
reached. ROLLBACK has been issued(Any Rows processed after the last COMMIT are lost).
Total rows processed: 1
Rows failed : 1
WARNING: File deletion failed for MYORA.TEST_WRITE_TO_ORACLE_APR24_2PM.DATA.
04-25-2015 07:06 AM
Define the sastrace option SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition to see what is happening and what CLI command is causing the trouble.
With you action it is bad performance issue and possible misunderstanding in what is going on.
You are copying an oracle table to the SAS machine (sas work) that is transferring all the data. The information of conversion is delivering some definitions by the sas formats.
Then you are transferring all data back and hopefully it creates a new table with same content. The trigger of Oracle columns are the sas formats.
Than you get multiple ERROR: ERROR: ..... that could be an indication of a wrong translation or other privilege problem.