How can I pass a BLOB value as a DBMS job parameter?

Question

While using DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE and passing a BLOB encapsulated in ANYDATA value the assignment fails with error:

ORA-22370: incorrect usage of method 
ORA-06512: at "SYS.DBMS_ISCHED", line 278
ORA-06512: at "SYS.DBMS_SCHEDULER", line 880
ORA-06512: at line 9
22370. 00000 -  "incorrect usage of method %s"
*Cause:    This method of SYS.AnyType or SYS.AnyData or SYS.AnyDataSet is
           being used inappropriately.
*Action:   Check the documentation for correct usage.

If I pass a VARCHAR2 encapsulated in ANYDATA it works, but a BLOB isn't working. Here is an example:

PL/SQL PROCEDURE

CREATE OR REPLACE PROCEDURE BLOB_TEST (
    p_blob  BLOB
) AS
BEGIN
    COMMIT;
END;
/

DBMS PROGRAM

BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM (
        program_name        => 'prog_blob_test',
        program_type        => 'STORED_PROCEDURE',
        program_action      => 'BLOB_TEST',
        number_of_arguments => 1,
        enabled             => FALSE
    );

    DBMS_SCHEDULER.DEFINE_ANYDATA_ARGUMENT (
        program_name        => 'prog_blob_test',
        argument_name       => 'p_blob',
        argument_position   => 1,
        argument_type       => 'BLOB',
        default_value       => NULL
    );

    DBMS_SCHEDULER.ENABLE (name => 'prog_blob_test');
END;
/

DBMS JOB

DECLARE
    l_blob  BLOB    := UTL_RAW.cast_to_raw('This is some BLOB data');
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'job_blob_test',
        program_name    => 'prog_blob_test'
    );

    DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE (
        job_name        => 'job_blob_test',
        argument_name   => 'p_blob',
        argument_value  => SYS.ANYDATA.ConvertBlob(l_blob) -- DON'T WORK
        -- argument_value  => SYS.ANYDATA.convertvarchar2('test') -- WORKS!
    );

    -- DBMS_SCHEDULER.ENABLE (name => 'job_blob_test');
END;
/

old question but did you ever figure this out? – MrE Dec 12 '21 at 01:48 — MrE, Dec 12 '21 at 01:48

score 1 · Answer 1 · answered Jul 02 '19 at 18:26

Try simply using DEFINE_PROGRAM_ARGUMENT instead of SET_JOB_ANYDATA_VALUE:

    declare
      l_blob  BLOB    := UTL_RAW.cast_to_raw('This is some BLOB data');      
    begin
        dbms_scheduler.CREATE_PROGRAM
        (
            program_name        => 'prog_blob_test',
            program_action      => 'BLOB_TEST',
            program_type        => 'STORED_PROCEDURE',
            number_of_arguments => 1,
            enabled             => FALSE
        ) ;

        dbms_scheduler.DEFINE_PROGRAM_ARGUMENT
        (
            program_name        => 'prog_blob_test',
            argument_position   => 1,
            argument_type       => 'BLOB',
            default_value       => l_blob
        );

        DBMS_SCHEDULER.ENABLE(name => 'prog_blob_test');
    end;
    /

This should work with any SQL datatype.

this defines a program and a blob argument, but it's not running the job, and it's not passing the blob as a param to the job. I tried this and I have the same issue as the OP. — MrE, Dec 12 '21 at 01:50

How can I pass a BLOB value as a DBMS job parameter?

1 Answers1