Oracle Tips and Tricks — David Fitzjarrell

September 16, 2008

Execute This!

Filed under: General,pl/sql — dfitzjarrell @ 17:54

A while back I wrote on the causes and solutions for apparently missing tables and views, but I didn’t address the problem of ‘missing’ packages/procedures/functions. Silly me. Let’s correct that.

Oracle newsgroups and blogs are filled with suggestions, tips, techniques and scripts intended to help the DBA with his or her chores, and many of these offerings utilize ‘standard’ packages and procedures installed by Oracle at database creation. Of course some of these packages/procedures/functions aren’t meant for the common, every-day user to utilize, and the privileges on those objects are limited to specific types of accounts. Some of these are even restricted to use by SYS as SYSDBA and no one else. There are many, though, that are suitable for any user to execute, provided that user has the requisite privileges. And, unfortunately, such privileges may not have been granted to the user desiring access; calling or attempting to describe such procedures/packages/functions then results in the following undesired output:

SQL> desc dbms_lock
ERROR:
ORA-04043: object dbms_lock does not exist


SQL>

And, from a PL/SQL block you can get the following unnerving message:

PLS-00201: identifier 'dbms_lock' must be declared

But, hey, you KNOW it’s there, because all of these wonderful scripts can’t be wrong. And they’re not; the user account in use simply has not been granted execute privilege on that package. And the same rules apply here that I listed in my prior post:

* the user has no execute privilege on the package/procedure/function
* a synonym is missing and the user is attempting to access the object by name

How to fix this glaring omission? Either grant execute on the desired object to the requesting user, or create a synonym to allow access by name. How can you tell which is required? If this:

SQL> desc dbms_lock
ERROR:
ORA-04043: object dbms_lock does not exist


SQL>

and this:

SQL> desc sys.dbms_lock
ERROR:
ORA-04043: object sys.dbms_lock does not exist


SQL>

are the end results then the user has no execute privilege on the package/procedure/function. If, however, access by name fails:

SQL> desc dbms_lock
ERROR:
ORA-04043: object dbms_lock does not exist


SQL>

but access by owner.name succeeds:

SQL> desc sys.dbms_lock
PROCEDURE ALLOCATE_UNIQUE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKNAME                       VARCHAR2                IN
 LOCKHANDLE                     VARCHAR2                OUT
 EXPIRATION_SECS                NUMBER(38)              IN     DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
 LOCKMODE                       NUMBER(38)              IN
 TIMEOUT                        NUMBER                  IN     DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
 LOCKMODE                       NUMBER(38)              IN
 TIMEOUT                        NUMBER                  IN     DEFAULT
FUNCTION RELEASE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
FUNCTION RELEASE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
FUNCTION REQUEST RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
 LOCKMODE                       NUMBER(38)              IN     DEFAULT
 TIMEOUT                        NUMBER(38)              IN     DEFAULT
 RELEASE_ON_COMMIT              BOOLEAN                 IN     DEFAULT
FUNCTION REQUEST RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
 LOCKMODE                       NUMBER(38)              IN     DEFAULT
 TIMEOUT                        NUMBER(38)              IN     DEFAULT
 RELEASE_ON_COMMIT              BOOLEAN                 IN     DEFAULT
PROCEDURE SLEEP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SECONDS                        NUMBER                  IN

SQL>

then the issue is a missing synonym. Knowing the corrective action required (and, of course, taking that action) will allow the user to access the desired package/procedure/function.

Knowing what packages/procedures/functions you CAN access is information which is fairly easy to obtain:

select owner, object_name
from all_objects
where object_type in ('PACKAGE','FUNCTION','PROCEDURE');

You’ll get a list (possibly a LONG list) of packages, procedures and functions (and the assiciated owners) which you’re allowed to execute:

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SYS                            STANDARD
SYS                            DBMS_STANDARD
SYS                            DBMS_REGISTRY
SYS                            DBMS_REGISTRY_SERVER
SYS                            XML_SCHEMA_NAME_PRESENT
SYS                            UTL_RAW
SYS                            PLITBLM
SYS                            SYS_STUB_FOR_PURITY_ANALYSIS
SYS                            PIDL
SYS                            DIANA
SYS                            DIUTIL


OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SYS                            SUBPTXT2
SYS                            SUBPTXT
SYS                            DBMS_PICKLER
SYS                            DBMS_JAVA_TEST
SYS                            DBMS_SPACE_ADMIN
SYS                            DBMS_LOB
SYS                            UTL_SYS_COMPRESS
SYS                            UTL_TCP
SYS                            UTL_HTTP
SYS                            DBMS_TRANSACTION_INTERNAL_SYS
SYS                            DBMS_SQL


OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SYS                            DBMS_SYS_SQL
SYS                            DBMS_OUTPUT
SYS                            DBMS_LOGSTDBY
SYS                            DBMS_SESSION
SYS                            DBMS_LOCK
SYS                            UTL_FILE
SYS                            DBMS_TYPES
SYS                            GETTVOID
SYS                            XMLSEQUENCEFROMXMLTYPE
SYS                            XQSEQUENCEFROMXMLTYPE
SYS                            XMLSEQUENCEFROMREFCURSOR
...

If the package/procedure/function is in that list, but you still can’t access it by name you’re simply missing a synonym. And, if it’s not in that list you have no access to that object so you’ll need to discuss that issue with your DBA.

[ A special case can exist through PL/SQL where a user has access to a table/view/package/procedure/function by virtue of a role but PL/SQL can’t ‘see’ it. Many packages/procedures/functions are compiled with AUTHID DEFINER (the default) and, as such, won’t use privileges granted through a role. Modifying the procedure to be AUTHID CURRENT_USER can fix that problem and allow PL/SQL to traverse the entire privilege tree. If the procedure cannot be modified (such as Oracle-supplied packages, procedures and functions) the only way to ‘fix’ that is to have privileges directly on the object in question. If you find yourself in this boat talk with your DBA to see if he/she can accomodate you.]

I’ll state again in this post that not all Oracle users are destined to access or use all of the installed packages/procedures/functions Oracle supplies. There may be very good reasons in your organization for not having access to a specific package, procedure or function, so don’t be surprised if your request is met with a glorious

“Nope, sorry, can’t do that.”

Security is the watchword of late, and some organizations may frown upon just any user having privilege to execute certain code, because granting such access may open security holes in the database. Pete Finnigan has an excellent website listing the security issues with Oracle releases; it’s worth the time to peruse his site to get a feel for what could disrupt an Oracle installation and give you a ‘heads up’ on why, possibly, you can’t use a certain package or procedure.

It never hurts to ask. Just don’t be surprised if the answer is “No” because there is probably a very good reason for that response.

Advertisements

4 Comments »

  1. hey..Great info.Thanks a lot for a very useful post. it helped me a lot.–Harsh–

    Comment by Harsh — April 15, 2009 @ 12:37 | Reply

  2. Still confusedcheck my below code Declare l_count integer := 0; l_count_t integer := 0; error_msg varchar2(4000);begin select count(*) into l_count from user_objects where object_name='PAKAGE_NAME' and object_type='PACKAGE'; if l_count=0 then DBMS_OUTPUT.PUT_LINE('ERROR: Package PAKAGE_NAME does not exist in the database'); else select count(*) into l_count_t from user_objects where object_name='PAKAGE_NAME' and status='VALID' and object_type in ('PACKAGE','PACKAGE BODY'); if l_count_t<>2 then DBMS_OUTPUT.PUT_LINE('ERROR: Package PAKAGE_NAME or its body is in INVALID state'); begin select text into error_msg from user_errors where name='PAKAGE_NAME' and rownum=1; DBMS_OUTPUT.PUT_LINE(substr(error_msg,1,250)); exception when no_data_found then null; end; end if; end if; end; / ERROR: Package PAKAGE_NAME or its body is in INVALID statePLS-00201: identifier 'DBMS_LOCK' must be declaredPL/SQL procedure successfully completed.I have a DBA privilege

    Comment by Anonymous — December 9, 2011 @ 10:30 | Reply

  3. Declare l_count integer := 0; l_count_t integer := 0; error_msg varchar2(4000);begin select count(*) into l_count from user_objects where object_name='PAKAGE_NAME' and object_type='PACKAGE'; if l_count=0 then DBMS_OUTPUT.PUT_LINE('ERROR: Package PAKAGE_NAME does not exist in the database'); else select count(*) into l_count_t from user_objects where object_name='PAKAGE_NAME' and status='VALID' and object_type in ('PACKAGE','PACKAGE BODY'); if l_count_t<>2 then DBMS_OUTPUT.PUT_LINE('ERROR: Package PAKAGE_NAME or its body is in INVALID state'); begin select text into error_msg from user_errors where name='PAKAGE_NAME' and rownum=1; DBMS_OUTPUT.PUT_LINE(substr(error_msg,1,250)); exception when no_data_found then null; end; end if; end if; end; / ERROR: Package PAKAGE_NAME or its body is in INVALID statePLS-00201: identifier 'DBMS_LOCK' must be declaredPL/SQL procedure successfully completed.

    Comment by Anonymous — December 9, 2011 @ 10:30 | Reply

  4. DBA privilege doesn't provide direct grants on objects so you'll need to provide such a grant on DBMS_LOCK to your account:grant execute on sys.dbms_lock to ;Do this as SYS then connect as your user and recompile the package.

    Comment by d_d_f — December 16, 2011 @ 22:45 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: