It doesn't work. At least not on Oracle 11g.1.
First off, I had the bright idea to begin using records (ROWTYPE or Objects) as parameters for procedures. This has many benefits, of which I will not go into detail here.
So after this bright idea (which works if not using DBMS_SCHEDULER), I had the need to call one of these procedures using DBMS_SCHEDULER. DBMS_SCHEDULER does not allow you to pass objects/rowtypes directly. Instead it requires you to encapsulate these complex types in anydata.
Ok, thats fine.
As such, I created an object that I could encapsulate in anydata.
Then i wrote an anonymous block to set some variables before scheduling the job with the anydata parameter. That went as follows:
I'd populate the object with values.
Create a job with one argument, disabled.
Use DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE in position 1, to set the anydata argument.
DBMS_JOB.ENABLE(MYJOBNAME)
Each time I've run, I've hit the error: ORA-06553: PLS-306: wrong number or types of arguments in call to 'YOUR_PROCEDURE'.
I can call my procedure passing in an anydata variable just fine, when NOT using dbms_scheduler.set_job_anydata_value.
Does anyone else out there have an example of this working? I'd sure appreciate a code example.
I even tried to create my user defined type (object) using OID as suggested here. No beans.
** Also, in addition to the above awesomeness **
- be advised that anydata does not yet seem to allow for objects with CLOBs as a field.
So for instance, if your object type has fields of varchar2 and numbers, etc. Fine. DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE works fine (or at least it lets you call it - but it still doesnt 'work' as per above).
If your object type has CLOB fields, SET_JOB_ANYDATA_VALUE errors with "ORA-22370: incorrect usage of method".
Hi, you got any fix for this? I also want to pass a nested table as parameter to a procedure and should call in dbms_scheduler. If you have something in hand please share. thanks Prabhakar
ReplyDeleteIn Oracle 11, it works. To do this: the program Argument must be of type object, and the set job argument must be anydata.
ReplyDeleteSQL> begin
2 dbms_scheduler.create_program (
3 program_name => 'pgm_load_t',
4 program_type => 'stored_procedure',
5 program_action => 'load_t',
6 number_of_arguments => 1,
7 enabled => false,
8 comments => 'program to run a stored procedure.');
9
10 dbms_scheduler.define_program_argument (
11 program_name => 'pgm_load_t',
12 argument_name => 'v',
13 argument_position => 1,
14 argument_type => 'my_varray');
15
16 dbms_scheduler.enable (name => 'pgm_load_t');
17 END;
SQL> declare
2 v my_varray := my_varray(1,2,3,4);
3 a sys.anydata := SYS.ANYDATA.ConvertCollection(v);
4 begin
5 dbms_scheduler.create_job (
6 job_name => 'job_load_t',
7 program_name => 'pgm_load_t',
8 start_date => SYSTIMESTAMP,
9 enabled => false,
10 comments => 'Job to run pgm');
11
12 dbms_scheduler.set_job_anydata_value (
13 job_name => 'job_load_t',
14 argument_position => 1,
15 argument_value => a
16 );
17
18 dbms_scheduler.enable ('job_load_t');