2

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!

Alexander Mills
  • 90,741
  • 139
  • 482
  • 817
  • It won't compile, or it won't run? What error message do you get? ('barf' is not an error message, though perhaps should be). – Alex Poole Sep 04 '13 at 15:10
  • 1
    That's the whole error stack? You don't get something more specific saying what `DBMS_ISCHED` tripped over? – Alex Poole Sep 04 '13 at 15:24
  • 1
    That can't be the entire error message. What is the error code of the error. You are jsut showing the call stack. – OldProgrammer Sep 04 '13 at 15:24
  • I am running the proc from Java, so let me work to get more of the error stack. – Alexander Mills Sep 04 '13 at 15:26
  • ok so the more specific error stack is: ORA-22370: incorrect usage of method originated from line 19 in my procedure. – Alexander Mills Sep 04 '13 at 15:30
  • 2
    See this link - https://forums.oracle.com/thread/482151 – OldProgrammer Sep 04 '13 at 15:49
  • thanks, it looks like I will just create a table to store the array of varchar2s, instead of passing the array, and just pass the table name, oh well, thanks anyway – Alexander Mills Sep 04 '13 at 16:01
  • 1
    Bug 4531589 is reported as fixed in 10.2.0.4 and 11.1.0.6 (Oracle Support doc ID 4531589.8). What patch level are you running in your 10g instance? It seems a bit odd for your dev/test environments to be on a different version to live, and at least to not have live patched up. – Alex Poole Sep 04 '13 at 16:01
  • ok thanks Alex, as for 10G then, I will have to use a work around. We are using Oracle 10.2.0.3.0 – Alexander Mills Sep 04 '13 at 16:02

1 Answers1

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

Community
  • 1
  • 1
MeyerRJ
  • 792
  • 4
  • 8