Hi,
I am unable to pass a plsql table(collection) as a parameter to a function which is initiated from DBMS job. Below are more details
---Created package below
create or replace package p1 as
Type rec_type is Record (col1 number,
col2 varchar2(100));
TYPE tab_type IS TABLE OF rec_type;
l_tab p1.tab_type := p1.tab_type();
Function f1(param1 in tab_type) Return NUmber;
end p1;
/
create or replace package body p1 as
Function f1(param1 in tab_type) Return NUmber is
Begin
IF param1.count > 0 Then
For I in 1..param1.Last Loop
Dbms_Output.Put_Line('P1.F1'||param1(i).col1||param1(i).col2);
End Loop;
Else
Dbms_Output.Put_Line('F1','Count0');
End if;
return 0;
Exception
when Others Then
Dbms_Output.Put_Line('F1 Error',sqlerrm);
End f1;
end p1;
I am unable to create a job which calls function f1 because its input parameter is an collection variable.
Tried creating job(s) using DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE procedure which allows to define and pass parameters with complex datatypes but compiler throws error "wrong number or types of arguments in call to 'SET_JOB_ANYDATA_VALUE'".
DBMS_SCHEDULER.CREATE_JOB(job_name => 'JOB_Test_Var' ,
job_type => 'STORED_PROCEDURE',
job_action => 'p1.f1',
number_of_arguments => 1,
start_date => SYSDATE,
enabled => TRUE);
DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE (
job_name => 'JOB_Test_Var',
argument_position => 1,
argument_value => l_tab);
Please help!
--Imran.