CODEWITHSUNDEEP
oracle-databaseplsql
Jeremy Bourque
Jeremy Bourque

Reputation: 3543

How to determine a PL/SQL function's schema from within the function

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 that the package (and hence the function) is defined in. Anyone know how to do this?

Upvotes: 8

Views: 5236

Answers (3)

Tom W.
Tom W.

Reputation: 113

From Oracle 10g CURRENT_USER, as used in Gary Myers' answer, is deprecated. Oracle recommends that you use the SESSION_USER parameter instead, which:

For enterprises users, returns the schema. For other users, returns the database user name by which the current user is authenticated. This value remains the same throughout the duration of the session.

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

There's also no need to SELECT from dual; you can assign the return value of SYS_CONTEXT directly to the 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;

Upvotes: 7

Gary Myers
Gary Myers

Reputation: 35401

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 as long as the PL/SQL object doesn't have AUTHID CURRENT_USER.

Upvotes: 7

darreljnz
darreljnz

Reputation: 2380

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

Upvotes: 1

Related Questions