-- =================================================================
-- Minimal Correct Way for AWS RDS Oracle (no error)
-- =================================================================
DECLARE
-- Input Parameters
p_action VARCHAR2(20) := 'DOWNLOAD'; -- DOWNLOAD, UPLOAD, or READ_LOG
p_bucket_name VARCHAR2(100) := 'your-bucket-name';
p_s3_prefix VARCHAR2(500) := 'folder-name/';
p_directory VARCHAR2(100) := 'DATA_PUMP_DIR';
p_file_prefix VARCHAR2(100) := 'optional-prefix'; -- For uploads
p_task_id NUMBER := NULL; -- Use only for READ_LOG
-- Working Variables
v_task_id NUMBER;
v_log_filename VARCHAR2(500);
v_task_status VARCHAR2(100);
v_start_time TIMESTAMP;
v_timeout_seconds NUMBER := 600;
v_check_interval NUMBER := 5;
BEGIN
-- --- Step 1: Fire download or upload SQL first ---
IF UPPER(p_action) = 'DOWNLOAD' THEN
DBMS_OUTPUT.PUT_LINE('Submitting download from S3...');
SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
p_bucket_name => p_bucket_name,
p_s3_prefix => p_s3_prefix,
p_directory => p_directory
)
INTO v_task_id
FROM dual;
ELSIF UPPER(p_action) = 'UPLOAD' THEN
DBMS_OUTPUT.PUT_LINE('Submitting upload to S3...');
SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3(
p_bucket_name => p_bucket_name,
p_prefix => p_s3_prefix,
p_directory => p_directory,
p_file_prefix => p_file_prefix
)
INTO v_task_id
FROM dual;
ELSIF UPPER(p_action) = 'READ_LOG' THEN
IF p_task_id IS NULL THEN
RAISE_APPLICATION_ERROR(-20003, 'Task ID must be provided for READ_LOG.');
END IF;
v_task_id := p_task_id; -- Use provided task_id directly
ELSE
RAISE_APPLICATION_ERROR(-20004, 'Invalid action specified: ' || p_action);
END IF;
-- --- Step 2: Now wait for task if needed ---
IF UPPER(p_action) IN ('DOWNLOAD', 'UPLOAD') THEN
v_start_time := SYSTIMESTAMP;
v_task_status := 'RUNNING';
LOOP
BEGIN
SELECT task_status
INTO v_task_status
FROM rdsadmin.rdsadmin_s3_tasks
WHERE task_id = v_task_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_task_status := 'UNKNOWN';
END;
EXIT WHEN v_task_status IN ('SUCCESS', 'FAILED');
IF SYSTIMESTAMP > v_start_time + NUMTODSINTERVAL(v_timeout_seconds, 'SECOND') THEN
RAISE_APPLICATION_ERROR(-20001, 'Timeout waiting for task to complete.');
END IF;
DBMS_LOCK.SLEEP(v_check_interval);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Task finished with status: ' || v_task_status);
END IF;
-- --- Step 3: Always read task log ---
v_log_filename := 'dbtask-' || v_task_id || '.log';
DBMS_OUTPUT.PUT_LINE('Reading log file: ' || v_log_filename);
FOR rec IN (
SELECT text
FROM TABLE(
rdsadmin.rds_file_util.read_text_file(
p_directory => 'BDUMP',
p_filename => v_log_filename
)
)
) LOOP
IF UPPER(rec.text) NOT LIKE '%STARTED%' AND
UPPER(rec.text) NOT LIKE '%ENDED%' AND
rec.text IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(rec.text);
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
-- =================================================================
-- PLAIN SIMPLE PL/SQL BLOCK for S3 Download
-- =================================================================
DECLARE
v_task_id NUMBER;
v_log_filename VARCHAR2(500);
BEGIN
-- Submit the S3 Download Task
SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
p_bucket_name => 'your-bucket-name', -- <<< your S3 bucket name
p_s3_prefix => 'your-folder-or-file/', -- <<< your S3 object key (prefix/file name)
p_directory => 'DATA_PUMP_DIR' -- <<< your Oracle directory
)
INTO v_task_id
FROM dual;
DBMS_OUTPUT.PUT_LINE('Download submitted successfully.');
DBMS_OUTPUT.PUT_LINE('Task ID: ' || v_task_id);
-- (Optional) Read the dbtask log file immediately
v_log_filename := 'dbtask-' || v_task_id || '.log';
DBMS_OUTPUT.PUT_LINE('Reading log file: ' || v_log_filename);
FOR rec IN (
SELECT text
FROM TABLE(
rdsadmin.rds_file_util.read_text_file(
p_directory => 'BDUMP',
p_filename => v_log_filename
)
)
) LOOP
-- Print only meaningful lines
IF UPPER(rec.text) NOT LIKE '%STARTED%' AND
UPPER(rec.text) NOT LIKE '%ENDED%' AND
rec.text IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(rec.text);
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
END;
/
-- =================================================================
-- Download from S3 + Read Log + Directory Check (Safe Version)
-- =================================================================
DECLARE
v_task_id VARCHAR2(50);
v_log_filename VARCHAR2(500);
v_dir1_exists NUMBER;
v_dir2_exists NUMBER;
v_directory1 VARCHAR2(100) := 'DATA_PUMP_DIR'; -- <<< Directory 1 (required for download)
v_directory2 VARCHAR2(100) := 'BDUMP'; -- <<< Directory 2 (required for log reading)
BEGIN
-- === Step 1: Check if both required directories exist ===
SELECT COUNT(*) INTO v_dir1_exists
FROM dba_directories
WHERE directory_name = UPPER(v_directory1);
SELECT COUNT(*) INTO v_dir2_exists
FROM dba_directories
WHERE directory_name = UPPER(v_directory2);
IF v_dir1_exists = 0 THEN
DBMS_OUTPUT.PUT_LINE('Error: Required directory ' || v_directory1 || ' does not exist.');
RETURN;
END IF;
IF v_dir2_exists = 0 THEN
DBMS_OUTPUT.PUT_LINE('Error: Required directory ' || v_directory2 || ' does not exist.');
RETURN;
END IF;
DBMS_OUTPUT.PUT_LINE('Both directories exist. Proceeding with download.');
-- === Step 2: Submit the S3 Download Task ===
SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
p_bucket_name => 'your-bucket-name',
p_s3_prefix => 'your-folder-or-file/',
p_directory => v_directory1
)
INTO v_task_id
FROM dual;
DBMS_OUTPUT.PUT_LINE('Download submitted successfully.');
DBMS_OUTPUT.PUT_LINE('Task ID: ' || v_task_id);
-- === Step 3: Wait a few seconds to allow log to be created ===
DBMS_LOCK.SLEEP(10);
-- === Step 4: Read and Print the Task Log ===
v_log_filename := 'dbtask-' || v_task_id || '.log';
DBMS_OUTPUT.PUT_LINE('Reading log file: ' || v_log_filename);
FOR rec IN (
SELECT text
FROM TABLE(
rdsadmin.rds_file_util.read_text_file(
p_directory => v_directory2,
p_filename => v_log_filename
)
)
) LOOP
IF UPPER(rec.text) NOT LIKE '%STARTED%' AND
UPPER(rec.text) NOT LIKE '%ENDED%' AND
rec.text IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(rec.text);
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
END;
/
No comments:
Post a Comment