Downloading file from S3 bucket - AWS Oracle RDS

Question

I'm new to Oracle AWS RDS, we have a RDS deployed and an S3 bucket. The download from S3 works fine, however I wanted to write a script which checks if the file download has been completed.

DECLARE 
V_TASKID VARCHAR2(100);
V_CTR integer := 0;
V_CMD VARCHAR2(4000) := NULL;

BEGIN
SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(p_bucket_name => 'humm-cards-dev',  p_directory_name => 'DATA_PUMP_DIR') INTO V_TASKID FROM DUAL;
dbms_output.put_line( V_TASKID ) ;

WHILE V_CTR = 0 LOOP
    dbms_output.put_line(V_CMD);
    V_CMD := 'SELECT count(*) FROM table(rdsadmin.rds_file_util.read_text_file(''BDUMP'', ''dbtask-' || V_TASKID || '.log'')) WHERE text LIKE ''%finished successfully%''';
    dbms_output.put_line(V_CMD);
    execute immediate V_CMD INTO V_CTR;
    IF V_CTR > 0 THEN EXIT; END IF;
END LOOP;
END;
/

The script kicks off the download - I get the TaskID and the V_CMD output in the DBMS Output. However the script fails on

execute immediate V_CMD INTO V_CTR

with this error

ORA-06512: at "RDSADMIN.RDS_FILE_UTIL", line 90 ORA-06512: at line 14 29283. 00000 - "invalid file operation%s" *Cause: An attempt was made to read from a file or directory that does not exist, or file or directory access was denied by the operating system. *Action: Verify file and directory access privileges on the file system, and if reading, verify that the file exists.

Interesting thing is, that if I run the DBMS output of V_CMD separately, it works just fine. DBMS output is below.

SELECT count(*) FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-1636692191529-641.log')) WHERE text LIKE '%finished successfully%';

Does anyone have any idea what I'm doing wrong?

are you sure you have write privileges on the directory BDUMP ? Because it looks like you have only read privileges — Roberto Hernandez, Nov 12 '21 at 06:48
Yeah I don't need write privilege, I only need read privilege so that I can read from the s3 bucket's log file associated with the Task ID I'm passing as a variable. — Hammad Mansoor, Nov 12 '21 at 08:29
your use has read privilege over the db directory, but what about the operating system location of the BDUMP ? — Roberto Hernandez, Nov 12 '21 at 13:18
The problem seems to be related to 'execute immediate' somehow ... This runs fine even within the loop (if it was permission issue on BDUMP this should have failed too?) SELECT count(*) FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-1636692191529-641.log')) WHERE text LIKE '%finished successfully%'; But execute immediate fails, and that's what I need to work as I need the TASKID from variable when the S3 download starts. — Hammad Mansoor, Nov 12 '21 at 16:40
Anyone with suggestions please? Is there other way I can read from a dynamic table other than 'execute immediate' ? — Hammad Mansoor, Nov 16 '21 at 00:55

score 1 · Accepted Answer · edited Feb 12 '22 at 11:34

Not sure if your issue was resolved, but I faced same problem and I think I found the reason for this. Once you download from s3 bucket, you need to wait till the file download is complete. What happens with your code (And my old code too), was that the file download operation was started, but not completed and hence I believe the log is still not available when you immediately try to read it.
What I did was after the download operation, put a while loop to find if the log file dbtask-taskid.log exists in the BDUMP directory.

v_logfilename:='dbtask-'|| taskid ||'.log';

v_logs_created:=0;

WHILE v_logs_created<1)
    LOOP 
        Select Count(1) 
        INTO v_logs_created
        FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory=>'BDUMP')) where filename=logfilename;
    END LOOP;

Once it was, I would proceed to the next step that is read if there is a success message in that.

score 0 · Answer 2 · answered Apr 19 '22 at 13:53

Solution @Yogesh Sati works well also in the same way with upload to S3:

set serveroutput on format wrapped;
DECLARE 
V_TASKID VARCHAR2(100);
V_LOGFILENAME VARCHAR2(100);
V_CTR integer := 0;
V_LOGS_CREATED integer := 0;
V_CMD VARCHAR2(4000) := NULL;

BEGIN
-- DOWNLOAD
SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(p_bucket_name => 'humm-cards-dev',  p_directory_name => 'DATA_PUMP_DIR') INTO V_TASKID FROM DUAL;
-- UPLOAD
-- SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3(p_bucket_name => 'humm-cards-dev', p_prefix => 'mydump.dmp', p_s3_prefix => 'DEV/', p_directory_name => 'DATA_PUMP_DIR') INTO V_TASKID FROM DUAL;
dbms_output.put_line('TASKID: ' || V_TASKID);

V_LOGFILENAME:='dbtask-'|| V_TASKID ||'.log';

WHILE V_LOGS_CREATED < 1
    LOOP 
        Select Count(1) 
        INTO V_LOGS_CREATED
        FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory=>'BDUMP')) where filename=V_LOGFILENAME;
    END LOOP;
    
WHILE V_CTR = 0 LOOP
    V_CMD := 'SELECT count(*) FROM table(rdsadmin.rds_file_util.read_text_file(''BDUMP'', ''dbtask-' || V_TASKID || '.log'')) WHERE text LIKE ''%finished successfully%''';
    execute immediate V_CMD INTO V_CTR;
    IF V_CTR > 0 THEN 
        dbms_output.put_line('The task finished successfully.');
        EXIT; 
    END IF;
END LOOP;
END;
/

score 0 · Answer 3 · edited Jul 25 '22 at 17:51

I just had this issue right now:

ORA-00904: "RDSADMIN"."RDSADMIN_S3_TASKS"."DOWNLOAD_FROM_S3": invalid identifier.

All my RDS have the same configurations working fine. Just one brand new created was giving the error when using rdsadmin.rdsadmin_s3_tasks.download_from_s3. Reading the AWS page on RDS oracle S3 integration, I decided to remove the option S3_INTEGRATION, then I removed the IAM role and rebooted the RDS. Then, I added the same IAM and then the option S3_INTEGRATION back. Works just fine now.

Downloading file from S3 bucket - AWS Oracle RDS

3 Answers3