Unable to create table in Oracle database via SAS, but works within Oracle database

Reply
N/A
Posts: 1

Unable to create table in Oracle database via SAS, but works within Oracle database

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

test_input;

myora.AC_HSPBYMO30ILQ1_2012_Q4_2012;

run;

PROC SQL;

Create table myora.test_write_to_oracle_apr24_2PM

as select *

from test_input;

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:
      Engine:        ORACLE
      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;
62   run;

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


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

Super User
Posts: 9,878

Re: Unable to create table in Oracle database via SAS, but works within Oracle database

It is right problem .

ERROR: ERROR: ERROR: ORACLE execute error: ORA-01031: insufficient privileges.   

Trusted Advisor
Posts: 3,208

Re: Unable to create table in Oracle database via SAS, but works within Oracle database

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. 

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 2 replies
  • 650 views
  • 3 likes
  • 3 in conversation