Ora-31603 when calling dbms_metadata.get_ddl from Stored Procedure

I have a simple stored procedure that is calling dbms_metadata.get_ddl. The stored procedure is owned by UserA and it is trying to get the ddl for UserB.

create or replace PROCEDURE usera.get_ddl 
                    (p_table_owner VARCHAR2, 
                     p_table_name VARCHAR2)
IS
     l_ddl clob;
BEGIN
    SELECT dbms_metadata.get_ddl ('TABLE', 
                                  upper(p_table_name), 
                                  upper(p_table_owner))
        INTO l_ddl
        FROM dual;                 
END get_ddl;
/

  
Userb has the following table:


 create table userb.a ( a number, b varchar2(10));

Execute the stored procedure:


SQL> exec usera.get_ddl('userb','a');
BEGIN usera.get_ddl('userb','a'); END;

*
ERROR at line 1:
ORA-31603: object "a" of type TABLE not found in schema "userb"
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 1
ORA-06512: at "USERA.GET_DDL", line 7
ORA-06512: at line 1

So first off user a needs the following grant:

grant select_catalog_role to usera;

Even after this the execute still gives the same error.

This error is due to Oracle Bug: 3960099 - ORA-31603 WHEN CALLING DBMS_METADATA FROM PROCEDURE 

There are 2 work arounds given by Oracle:

Option 1:

1- Create the procedure with AUTHID CURRENT_USER
2- Grant SELECT ANY DICTIONARY privilege to any user calling the procedure

OR

Option 2:
1- Create the procedure in the SYS schema
2- Grant execute on the procedure to the chosen users.

I chose Option 1 as this way there is no need to create a SYS stored procedure:

create or replace PROCEDURE usera.get_ddl 
                    (p_table_owner VARCHAR2, 
                     p_table_name VARCHAR2)
  AUTHID CURRENT_USER
IS
     l_ddl clob;
BEGIN
    SELECT dbms_metadata.get_ddl (
                                'TABLE', 
                                upper(p_table_name), 
                                upper(p_table_owner))
        INTO l_ddl
        FROM dual;                 
END get_ddl;
/

Give the grant:

grant select any dictionary to usera;

Now execute the stored procedure:

SQL> exec usera.get_ddl('userb','a');

PL/SQL procedure successfully completed.