oracle-databaseamazon-s3plsqlamazon-rdsdatapump

Procedure to move oracledb export to s3 bucket fails when ran in a job


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?


Solution

  • 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;
    /