How to define a PL / SQL function schema from within a function

Question

I have a PL / SQL package in an Oracle 10g database, and I want to write a function that returns the name of the schema in which the package is defined (and therefore the function). Does anyone know how to do this?

Answer 1

create function xcurr return varchar2 is v_curr varchar2(32); begin SELECT SYS_CONTEXT ('USERENV', 'CURRENT_USER') into v_curr from dual; return v_curr; end; 

This will work until the PL / SQL object has AUTHID CURRENT_USER.

Answer 2

From Oracle 10g, CURRENT_USER , as used in response to Gary Myers , is not recommended. Oracle recommends using the SESSION_USER parameter instead, which:

For enterprise users, returns a schema. For other users, the database username with which the current user is authenticated is returned. This value remains unchanged throughout the session.

I would use CURRENT_SCHEMA. There are subtle differences between the two, because CURRENT_SCHEMA changes if an ALTER SESSION SET CURRENT_SCHEMA statement is issued.

There is also no need to choose from double; You can assign the return value of SYS_CONTEXT directly to a variable.

 DECLARE v_current_schema varchar2(30); BEGIN v_current_schema := SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'); dbms_output.put_line('I am using the schema [' || v_current_schema || ']'); END; 

Answer 3

There is probably an easier way, but you can use dbms_utility.format_call_stack and dbms_utility.format_call_stack results to get the schema name. This works in Oracle 9i.