Oracle's dbms_metadata.get_ddl for type DIRECTORY: invalid input value for parameter SCHEMA

Question

When I try to call dbms_metadata.get_ddl('TABLE', 'MYTABLE', 'MYSCHEMA') either in the pl/sql block or in the package procedure it works fine.

When I try to call dbms_metadata.get_ddl('TABLE', 'MYTABLE') (without schema explicitely provided) either in the pl/sql block or in the package procedure it works fine also.

When I try to call dbms_metadata.get_ddl('DIRECTORY', 'MYDIR') (without schema explicitely provided) in the pl/sql block it works fine also.

But,

When I try to call dbms_metadata.get_ddl('DIRECTORY', 'MYDIR', 'MYSCHEMA') either in the pl/sql block or in the package procedure it raises the error:

ORA-31600: invalid input value MYSCHEMA for parameter SCHEMA in function GET_DDL

When I try to call dbms_metadata.get_ddl('DIRECTORY', 'MYDIR') (without schema explicitely provided) in the package procedure it raises the error:

ORA-31603: object "MYDIR" of type DIRECTORY not found in schema "MYSCHEMA"

What is the problem?

EXECUTE_CATALOG_ROLE=true
SELECT_CATALOG_ROLE=true
'CREATE ANY DIRECTORY'=true
PL/SQL Release 12.2.0.1.0 - Production

Answer 1

You need to add the AUTHID CURRENT_USER clause (Docs)

create or replace procedure dir_ddl  (dir_name in varchar2) AUTHID CURRENT_USER is
 x clob;
begin
 SELECT DBMS_METADATA.get_ddl ('DIRECTORY', dir_name) into x from dual;
 dbms_output.put_line(x);
end dir_ddl;
/

set serveroutput on
exec dir_ddl('PLSHPROF_DIR')

And my output is...

Procedure DIR_DDL compiled


   CREATE OR REPLACE DIRECTORY "PLSHPROF_DIR" AS '/home/oracle/profiler'


PL/SQL procedure successfully completed.

If I remove the AUTHID clause, I see the same error as you report.