Job fails with:
ORA-29481: Implicit results cannot be returned to client.
ORA-06512: at "SYS.DBMS_SQL", line 2832
ORA-06512: at "SYS.DBMS_SQL", line 2826
ORA-06512: at "owner.SEND_TO_S3", line 8
ORA-06512: at line 1
I can run the procedure manually(BEGIN SEND_TO_S3; END;) without errors and the db exports show up in the s3 bucket.
****Oracle Job
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SEND_EXP_TO_S3_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN SEND_TO_S3; END;',
start_date => SYSTIMESTAMP,
enabled => TRUE,
repeat_interval => 'freq=weekly; byday=mon; byhour=20; byminute=40; bysecond=0;');
END;
****Oracle procedure
(If there some other way than to use a refcursor that might solve this issue as well)
CREATE OR REPLACE PROCEDURE send_to_s3
AS
rc sys_refcursor;
BEGIN
open rc for
SELECT
rdsadmin.rdsadmin_s3_tasks.upload_to_s3(
p_bucket_name => 'bucket/name',
p_prefix => 'EXP',
p_s3_prefix => '',
p_directory_name => 'DATA_PUMP_DIR') `your text`
AS TASK_ID FROM DUAL;
DBMS_SQL.RETURN_RESULT(rc);
END send_to_s3;
****The oracle error points to an outdated client but I have the latest client and I'm
not sure the scheduler uses a client.
Is there a way to get this job running correctly?
AWS customer support was able to help me with this job. So for sake of completeness here is a workable answer to whoever else may need this.
BEGIN
DBMS_SCHEDULER.create_job (
job_name => ' SEND_EXP_TO_S3_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE TASK_ID VARCHAR2(242);BEGIN
SELECT
rdsadmin.rdsadmin_s3_tasks.upload_to_s3(
p_bucket_name => '' bucket/name'',
p_prefix => '' EXP'',
p_s3_prefix => '''',
p_directory_name => ''DATA_PUMP_DIR'')
into TASK_ID FROM DUAL;END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=weekly; byday=mon; byhour=20; byminute=40; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB procedure.');
END;
/