I am enrolled at the Art Institute of Pittsburgh - Online working towards a Bachelor of Science in photography.
My other blog [blogspot.com]
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;
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;
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
FUNCTION BAR RETURN NUMBER;
CREATE OR REPLACE PACKAGE BODY FOO
FUNCTION BAR RETURN NUMBER
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;