BookmarkSubscribeRSS Feed
DBailey
Lapis Lazuli | Level 10

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.

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

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

DBailey
Lapis Lazuli | Level 10

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
Super User

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
Barite | Level 11

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.

---->-- ja karman --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1062 views
  • 0 likes
  • 4 in conversation