topic Re: Proc Datasets Error in SAS Procedures

Proc Datasets Error

DBailey — Thu, 16 Apr 2015 15:32:42 GMT

Using Proc datasets against oracle:

proc datasets lib=ldbmr memtype=data nolist nowarn;

delete tmpzippsycle:;

delete psycle_zip4_xref;

change stage_psycle_zip4_xref=psycle_zip4_xref;

run;

produces an error:

ERROR: ORACLE execute error: ORA-01765: specifying owner's name of the table is not allowed.

any ideas?

dat

log below

23 proc datasets lib=ldbmr memtype=data nolist nowarn;

24 delete tmpzippsycle:;

25 delete psycle_zip4_xref;

26 change stage_psycle_zip4_xref=psycle_zip4_xref;

27 run;

NOTE: Deleting LDBMR.TMPZIPPSYCLE (memtype=DATA).

ORACLE_7: Prepared: on connection 4

SELECT * FROM DBMR.TMPZIPPSYCLE

ORACLE_8: Executed: on connection 4

DROP TABLE DBMR.TMPZIPPSYCLE

NOTE: Deleting LDBMR.TMPZIPPSYCLE_2 (memtype=DATA).

ORACLE_9: Prepared: on connection 4

SELECT * FROM DBMR.TMPZIPPSYCLE_2

ORACLE_10: Executed: on connection 4

DROP TABLE DBMR.TMPZIPPSYCLE_2

NOTE: Changing the name LDBMR.STAGE_PSYCLE_ZIP4_XREF to LDBMR.PSYCLE_ZIP4_XREF (memtype=DATA).

ORACLE_11: Prepared: on connection 4

SELECT * FROM DBMR.PSYCLE_ZIP4_XREF

ORACLE_12: Prepared: on connection 4

SELECT * FROM DBMR.STAGE_PSYCLE_ZIP4_XREF

ORACLE_13: Executed: on connection 4

RENAME DBMR.STAGE_PSYCLE_ZIP4_XREF TO DBMR.PSYCLE_ZIP4_XREF

ERROR: ORACLE execute error: ORA-01765: specifying owner's name of the table is not allowed.

Re: Proc Datasets Error

RW9 — Thu, 16 Apr 2015 15:50:07 GMT

Google search result 1: https://community.oracle.com/thread/551583

Apparently you can only remove tables within the schema you are logged into.

Re: Proc Datasets Error

DBailey — Thu, 16 Apr 2015 17:43:02 GMT

true...but that's what I'm trying to do.

It looks like SAS is sending invalid syntax to oracle for the name change as you can't include the schema as part of the "to" name.

Re: Proc Datasets Error

Kurt_Bremser — Fri, 17 Apr 2015 06:34:49 GMT

From the gut, I'd say that

"specifying owner's name of the table is not allowed"

signifies that someone else "owns" the table and that the operation would implictly change the ownership, which you are not allowed to do,

OTOH, I would suggest doing database maintenance directly with a proper database client. Use the screwdriver for screws, not the hammer.

Re: Proc Datasets Error

jakarman — Fri, 17 Apr 2015 15:13:58 GMT

It is still a problem in the SAS software the combination of RW9 and DBailey post are covering the issue. The translation by the SAS/ACCESS to oracle is having a fail with this.

As SAS is selling that software with all support, it is advicable to have SAS-TS go for that.

The DDL DML MDL differences are far most SAS programmers something they are not aware of as the DBA is not aware of a SAS environment. Closing the gaps is another goal.