Oracle stored procedure - difference between 10g and 11g perhaps?

Question

The following stored procedure code works in our DEV and TEST environments which run with Oracle 11G but will not work in our 10G PROD environment:

first, I created my own data structure in Oracle to hold any array of VARCHAR2:

create or replace
type MAT_MULTIPLES_ARRAY as table of VARCHAR2(100);

here is the procedure code:

    create or replace PROCEDURE MAT_SUBMIT_JOB (v_multiples_columns_to_add IN our_schema.MAT_MULTIPLES_ARRAY)

    v_jobno number;
    v_job_name VARCHAR2(100);
    v_error_message VARCHAR2(32000);

    begin

    v_job_name := 'doesnt matter right now';

    dbms_scheduler.create_job(v_job_name,program_name=>'MAT_JOB_PROGRAM');
    dbms_scheduler.set_job_anydata_value(v_job_name,1,sys.anydata.convertCollection(v_multiples_columns_to_add));

    dbms_scheduler.enable(v_job_name);

    end;

again, this same code works in 11G in our DEV and TEST environments, and it compiles in our 10G environment, but then it appears to barf during runtime, on the second dbms_scheduler line (in bold).

Does dbms_scheduler work in 10G? Or perhaps there is a problem with 'sys.anydata.convertCollection(v_multiples_columns_to_add)'

Here is the error message:

ORA-22370: incorrect usage of method originated from line 19 in my procedure.

line 19 is the line with convertCollection() call.

Please help!

Answer 1

I found this in the documentation:

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm#i1000820

SET_JOB_ANYDATA_VALUE requires that you be the owner of the job or have ALTER privileges on that job. You can also set a job argument value if you have the CREATE ANY JOB privilege.

This might also be related as well: ANYDATA with Collections based on rowtype