SQL> drop user user1 cascade;

User dropped.

SQL> drop user user2 cascade;

User dropped.

SQL> drop user user3 cascade;

User dropped.

SQL>
SQL> grant connect to user1 identified by user1;

Grant succeeded.

SQL> grant create procedure to user1;

Grant succeeded.

SQL>
SQL> grant connect to user2 identified by user2;

Grant succeeded.

SQL> grant create procedure to user2;

Grant succeeded.

SQL>
SQL> grant connect to user3 identified by user3;

Grant succeeded.

SQL>
SQL> conn user2/user2
Connected.
SQL>
SQL> create or replace package pkg_name
  2  is
  3    procedure p;
  4  end;
  5  /

Package created.

SQL>
SQL> create or replace package body pkg_name
  2  is
  3    procedure p
  4    is
  5    begin
  6      null;
  7    end;
  8  end;
  9  /

Package body created.

SQL>
SQL> conn user1/user1
Connected.
SQL>
SQL> create or replace function USER1.test(rc out sys_refcursor) return number
  2    authid current_user
  3  is
  4    Result number;
  5  begin
  6    open rc for
  7    select dbms_metadata.get_ddl('PACKAGE_BODY','PKG_NAME', 'USER2') ddl from dual;
  8
  9    return 1;
 10  end test;
 11  /

Function created.

SQL>
SQL> grant execute on test to user3;

Grant succeeded.

SQL>
SQL> conn user3/user3
Connected.
SQL>
SQL> select * from session_roles;

ROLE
--------------------------------------------------------------------------------
CONNECT

SQL> select authid from all_procedures where owner='USER1' and object_name='TEST';

AUTHID
------------
CURRENT_USER

SQL>
SQL> var rc refcursor
SQL>
SQL> set autop on long 100000
SQL>
SQL> exec dbms_output.put_line( user1.test(:rc))

PL/SQL procedure successfully completed.

ERROR:
ORA-31603: object "PKG_NAME" of type PACKAGE_BODY not found in schema "USER2"
ORA-06512: at "SYS.DBMS_METADATA", line 6069
ORA-06512: at "SYS.DBMS_METADATA", line 8666
ORA-06512: at line 1



no rows selected

SQL> drop user user1 cascade;

User dropped.

SQL> drop user user2 cascade;

User dropped.

SQL> drop user user3 cascade;

User dropped.

SQL>
SQL> grant connect to user1 identified by user1;

Grant succeeded.

SQL> grant create procedure to user1;

Grant succeeded.

SQL> grant connect to user2 identified by user2;

Grant succeeded.

SQL> grant create procedure to user2;

Grant succeeded.

SQL>
SQL> grant connect to user3 identified by user3;

Grant succeeded.

SQL> grant select_catalog_role to user3;

Grant succeeded.

SQL>
SQL> conn user2/user2
Connected.
SQL>
SQL> create or replace package pkg_name
  2  is
  3    procedure p;
  4  end;
  5  /

Package created.

SQL>
SQL> create or replace package body pkg_name
  2  is
  3    procedure p
  4    is
  5    begin
  6      null;
  7    end;
  8  end;
  9  /

Package body created.

SQL>
SQL> conn user1/user1
Connected.
SQL>
SQL> create or replace function USER1.test(rc out sys_refcursor) return number
  2    authid current_user
  3  is
  4    Result number;
  5  begin
  6    open rc for
  7    select dbms_metadata.get_ddl('PACKAGE_BODY','PKG_NAME', 'USER2') ddl from dual;
  8
  9    return 1;
 10  end test;
 11  /

Function created.

SQL>
SQL> grant execute on test to user3;

Grant succeeded.

SQL>
SQL> conn user3/user3
Connected.
SQL>
SQL> select * from session_roles;

ROLE
--------------------------------------------------------------------------------
CONNECT
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE

SQL> select authid from all_procedures where owner='USER1' and object_name='TEST';

AUTHID
------------
CURRENT_USER

SQL>
SQL> var rc refcursor
SQL>
SQL> set autop on long 100000
SQL>
SQL> exec dbms_output.put_line( user1.test(:rc))

PL/SQL procedure successfully completed.


DDL
--------------------------------------------------------------------------------

  CREATE OR REPLACE EDITIONABLE PACKAGE BODY "USER2"."PKG_NAME"
is
  procedure p
  is
  begin
    null;
  end;
end

SQL> drop user user1 cascade;

User dropped.

SQL> drop user user2 cascade;

User dropped.

SQL> drop user user3 cascade;

User dropped.

SQL>
SQL> grant connect to user1 identified by user1;

Grant succeeded.

SQL> grant create procedure to user1;

Grant succeeded.

SQL> grant select_catalog_role to user1 with delegate option;

Grant succeeded.

SQL>
SQL> grant connect to user2 identified by user2;

Grant succeeded.

SQL> grant create procedure to user2;

Grant succeeded.

SQL>
SQL> grant connect to user3 identified by user3;

Grant succeeded.

SQL>
SQL> conn user2/user2
Connected.
SQL>
SQL> create or replace package pkg_name
  2  is
  3    procedure p;
  4  end;
  5  /

Package created.

SQL>
SQL> create or replace package body pkg_name
  2  is
  3    procedure p
  4    is
  5    begin
  6      null;
  7    end;
  8  end;
  9  /

Package body created.

SQL>
SQL> conn user1/user1
Connected.
SQL>
SQL> create or replace function USER1.test(rc out sys_refcursor) return number
  2    --authid current_user
  3  is
  4    Result number;
  5  begin
  6    open rc for
  7    select dbms_metadata.get_ddl('PACKAGE_BODY','PKG_NAME', 'USER2') ddl from dual;
  8
  9    return 1;
 10  end test;
 11  /

Function created.

SQL>
SQL> grant select_catalog_role to function test;

Grant succeeded.

SQL>
SQL> grant execute on test to user3;

Grant succeeded.

SQL>
SQL> conn user3/user3
Connected.
SQL>
SQL> select * from session_roles;

ROLE
--------------------------------------------------------------------------------
CONNECT

SQL> select authid from all_procedures where owner='USER1' and object_name='TEST';

AUTHID
------------
DEFINER

SQL>
SQL> var rc refcursor
SQL>
SQL> set autop on long 100000
SQL>
SQL> exec dbms_output.put_line( user1.test(:rc))

PL/SQL procedure successfully completed.


DDL
--------------------------------------------------------------------------------

  CREATE OR REPLACE EDITIONABLE PACKAGE BODY "USER2"."PKG_NAME"
is
  procedure p
  is
  begin
    null;
  end;
end;