Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Mr. Muskrat (4572)

Mr. Muskrat
  reversethis-{moc ... ta} {tarksum.rm}

I'm married with 2 girls. I work as a full time Perl programmer for a Land Mobile Radio company in the Dallas/Fort Worth area.

I am enrolled at the Art Institute of Pittsburgh - Online working towards a Bachelor of Science in photography.

My other blog [blogspot.com]

Journal of Mr. Muskrat (4572)

Wednesday April 11, 2007
01:47 PM

Oracle Package Ownership

[ #32967 ]

In Oracle, everything in the database is an object with many properties. One of these properties is OWNER. To determine the owner of a particular object, we can query the DBA_SOURCE table. In this case, we are interested in the owner of the package in which a function is defined so we could run this query:

SQL> SELECT OWNER FROM DBA_SOURCE WHERE TYPE = 'PACKAGE' AND NAME = 'SOME_PACKAGE' GROUP BY OWNER ORDER BY OWNER;

OWNER
------------------------------
ABCDEV
XYZDEV

Here we see that you can have objects that share a name with different owners.

Alternately we could query against DBA_OBJECTS:

SQL> SELECT OWNER FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'PACKAGE' AND OBJECT_NAME = 'SOME_PACKAGE' GROUP BY OWNER ORDER BY OWNER;

OWNER
------------------------------
ABCDEV
XYZDEV

The problem with those approaches is that the owner of the package must be a DBA.

USER will always be the user who is running the query and therefore cannot be used to determine ownership of the package during the call to the function. Assuming the owner is the same as the schema (which it is in most cases) then the following query will get us the owner of a package:

SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;

Here is a short package to test it:

CREATE OR REPLACE PACKAGE FOO
AS
  FUNCTION BAR RETURN NUMBER;
END FOO;
/

CREATE OR REPLACE PACKAGE BODY FOO
IS
  FUNCTION BAR RETURN NUMBER
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'));
    RETURN 1;
  END;

END FOO;
/

Load the package as one user (XYZDEV for example), grant execute on the package to a second user (GRANT EXECUTE ON FOO TO ABCDEV), log in as the second user and run it.

SQL> SET SERVEROUTPUT ON
SQL> SELECT FOO.BAR() FROM DUAL;

FOO.BAR()
----------
         1

XYZDEV

Any questions?

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • What about using all_source or all_objects?
    • I had forgotten about those but most DBAs are reluctant to give grant select on any of the SYS owned stuff if they can help it.
      • The all_* virtual tables should give you access to everything you own (as with user_*), plus what you have been granted by other users.

        To be honest, I wasn't aware of SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'). Good to add to my bag o' tricks ;-)
        • I knew about SYS_CONTEXT because that's part of the magic behind Virtual Private Databases. I designed and coded one at work.
    • This doesn't work for the scenario for which I needed this. I have two identical packages in two schemas. Same name, same lines of code. I need the code in the package to determine the name of the package owner and query a table looking for rows where a field is set to the name of the owner.

      If I query one of the _SOURCE views (or better yet, one of the _OBJECT views), I'll find both packages and both owners, and still have no way of knowing which one I'm running in. :)

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers