Oracle SQL Developer accessing SYS packages with a different schema

Question

Using Oracle SQL Developer I'm trying to access a given package that has been created by SYS and in which the, say, FOO schema has EXECUTE and DEBUG permissions, in fact executing a desc will produce the following output:

desc SYS.PKG_MY_PACKAGE

PROCEDURE      Argument Name Type     IN/OUT Default 
-------------- ------------- -------- ------ ------- 
PRC_MY_PROCESS P_MY_ARGUMENT VARCHAR2 IN     unknown

The problem is such package is not visible under Connections > My Connection > Packages.

Any feedback will be sincerely appreciated. Thanks much in advance.

score 4 · Accepted Answer · answered Apr 01 '11 at 22:23

4

The only way I've found to do this is to select "Other Users" under My Connection, expand the SYS user, and go to Packages in there.

IOW,

Connections > MyConnection > Other Users > SYS > Packages

answered Apr 01 '11 at 22:23

DCookie

42,630
11
83
92

thanks much for your reply. This definitely solves my problem. I really appreciate your contribution given that I was being pressed to install a different product just to accomplish such task. – Nano Taboada Apr 04 '11 at 14:19

score 1 · Answer 2 · answered Apr 02 '11 at 08:51

Well you could start by creating an alias to the package your are trying to access under the schema you are using, but you will need to have all permissions on all objects this package is accessing/modifying and on the execute permission on package itself for everything to work correctly

score -1 · Answer 3 · edited Jul 15 '14 at 05:05

Is this is a genuine example? That is, have you created a package called PKG_MY_PACKAGE in the SYS schema?

This is very bad practice. The SYS schema is key to the successful running of the database. Building objects in that schema can make exports, etc difficult. Even worse is when we use CREATE OR REPLACE to apply our code and overwrite an existing PL/SQL package.

So, please, create new schemas for all your code. If the schema needs additional powerful privileges like SYS then grant them. But don't contaminate SYS. This also applies to SYSTEM and indeed any of the other schemas which Oracle installs.

Changing the SYS schema from the Oracle-supplied state is bad practice.

Oracle SQL Developer accessing SYS packages with a different schema

3 Answers3