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.
Google search result 1: https://community.oracle.com/thread/551583
Apparently you can only remove tables within the schema you are logged into.
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.
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.