Proc Datasets Error

[DBailey]

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.

[RW9]

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

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

[DBailey]

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.

[Kurt_Bremser]

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.

[jakarman]

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.