I tried by this
select module from v$sqlarea where sql_fulltext LIKE '%begin ORACLE_PKG%'
Any help would be appreciated.
Add a comment
|
2 Answers
You can get the module name by your current sql provided the module name is set by using dbms_application_info.set_module method within the related application :
declare
v_module_name varchar2(150);
begin
v_module_name := get_module_name; -- a presumed function that brings the module name
dbms_application_info.set_module( module_name => v_module_name, action_name => 'some duty');
end;
Comments
If you want the module that has already been set via a call to DBMS_APPLICATION_INFO.SET_MODULE, you can get that with this expression:
SYS_CONTEXT('USERENV','MODULE')
If you want the current PL/SQL package name, you can get that with this expression:
$$PLSQL_UNIT
Example:
CREATE OR REPLACE PACKAGE my_pkg AS
PROCEDURE p;
END my_pkg;
/
CREATE OR REPLACE PACKAGE BODY my_pkg AS
PROCEDURE p IS
BEGIN
dbms_application_info.set_module ( module_name => 'MY_MODULE', action_name => 'MY_ACTION' );
dbms_output.put_line('Current module = ' || SYS_CONTEXT('USERENV','MODULE'));
dbms_output.put_line('Current program unit = ' || $$PLSQL_UNIT);
END p;
END my_pkg;
exec my_pkg.p;
Current module = MY_MODULE Current program unit = MY_PKG
