dbms_datapump.get_dumpfile_info can't read directory when compiled in stored procedure

Question

I'm creating a stored procedure to load (impdp) a Datapump database dump.

I am trying to get the dump file's creation date (to compare with the date of a previously loaded dump), using DBMS_DATAPUMP.GET_DUMPFILE_INFO, like in this example.

When running in an anonymous block (like below), it runs fine, outputting the dump file's creation date. However, when this same block is adapted and compiled in a stored procedure, I get the ORA-39087 error (Directory name is invalid).

DECLARE 
  dumpfile VARCHAR2(256) := 'my_file.dp';
  dir VARCHAR2(30) := 'MY_DIR';
  info ku$_dumpfile_info;
  ft NUMBER;
BEGIN
  sys.dbms_datapump.get_dumpfile_info(dumpfile, dir, info, ft);
  FOR rec IN (SELECT * FROM TABLE (info) WHERE item_code = 6 ) LOOP 
    dbms_output.put_line(rec.value);
  END LOOP;
END;

The directory exists. The name is valid. When I run

SELECT * FROM datapump_dir_objs;

with the same user, I can see that the user has READ and WRITE privileges on the directory. Oracle version is 11g Release 11.2.0.4.0.

Any light on what I am doing wrong?

Thanks in advance.

Answer 1

The problem was that the READ and WRITE privileges on the directory were added via a role. By default, anonymous blocks are executed with the current user's privileges, but stored procedures are not.

I added AUTHID CURRENT_USER to the procedure's header and managed to access my directory.

Thanks to Alex Poole for the insight.