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?
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.