CODEWITHSUNDEEP
oracle-databaseamazon-web-servicesamazon-s3
Rakesh kumar
Rakesh kumar

Reputation: 41

RDSADMIN.RDS_FILE_UTIL.LISTDIR works from block and not in procedure

Today, I followed the instructions given to perform Oracle RDS integration with S3 to import files from S3 bucket into a database directory.

I was able to perform all the steps well and able to see the files imported from my S3 bucket in the DATA_PUMP_DIR directory on the RDS instance.

When I run the query

SELECT filename FROM table(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR')) order by mtime;

I get the output listing the files I imported.

Now, I am planning to get these files in a PLSQL block and the issue arises here. When I run something like this:

DECLARE
BEGIN
  FOR fn in (SELECT * FROM table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by mtime)
  LOOP
    dbms_output.put_line('File name is ' || fn.filename);
  END LOOP;  
END;

I can see the output in the dbms output window.

However, when I try to call this inside a procedure like the following: 

CREATE OR REPLACE PROCEDURE test1 IS
BEGIN
  FOR fn in (SELECT * FROM table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by mtime)
  LOOP
    dbms_output.put_line('File name is ' || fn.filename);
  END LOOP;  
END test1;

I receive the error:

3/14 PL/SQL: SQL Statement ignored
3/43 PL/SQL: ORA-01031: insufficient privileges

I searched online for this error and couldn't get any leads.

I tried writing the procedure with invoker rights 

CREATE OR REPLACE PROCEDURE test1 AUTHID CURRENT_USER IS

and it still gave me the same error.

Can someone please throw light on this?

Upvotes: 4

Views: 3457

Answers (2)

gerardo leyton
gerardo leyton

Reputation: 1

grant execute any procedure to admin WITH ADMIN OPTION;

grant create any procedure to admin WITH ADMIN OPTION;

Upvotes: 0

Nayan
Nayan

Reputation: 1

You will need following grants to owner of the procedure. Probably from your DBA.

Grant execute on rdsadmin.RDSADMIN_S3_TASKS to <procedure_Owner>;

Upvotes: 0

Related Questions