Create Oracle Procedure with rdsadmin.rds_file_util.listdir

0
votes

How do you create a procedure in Oracle with a select from rdsadmin?

Environment: Oracle Database 11g 11.2.0.4.0 AWS RDS

SQL query 

 `SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'DATA_PUMP_DIR'));`

Returns

enter image description here (fig.1)

Store Procedure:

create or replace PROCEDURE SP_LIST_DIR
IS
BEGIN

  SELECT *
  FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'DATA_PUMP_DIR'));

END SP_LIST_DIR;

How can I get the procedure to return the results from (fig.1)?

Results:

enter image description here

1
oraclestored-proceduresprocedure

1 Answers

0
votes

Turns out that Oracle does not have permissions to run RDSADMIN in a procedure. Running this resolved the issue:

grant execute on rdsadmin.rds_file_util to < procedure owner >;

reference: https://forums.aws.amazon.com/thread.jspa?messageID=893048