use Perl

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?