ORA-27465: invalid value 1 for attribute ARGUMENT_POSITION

Question

I have procedure like this inside a package

PROCEDURE prepBillInfoforAccrualIntr (p_date in date);

-- created a program

BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM(PROGRAM_NAME        => 'MY_PROGRAM',
                                PROGRAM_TYPE        => 'STORED_PROCEDURE',
                                PROGRAM_ACTION      => 'GENEVA_ADMIN.IPGTIBCOAPIS_LMS_GENERIC_FIXED.PREPBILLINFOFORACCRUALINTR',
                                NUMBER_OF_ARGUMENTS => 1,
                                ENABLED             => FALSE,
                                COMMENTS            => 'MY PROGRAM');
END;

-- defined argument

BEGIN
  DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(PROGRAM_NAME      => 'MY_PROGRAM',
                                         ARGUMENT_NAME     => 'P_DATE',
                                         ARGUMENT_POSITION => 1,
                                         ARGUMENT_TYPE     => 'DATE',
                                         DEFAULT_VALUE     => '');
END;

-- enabled

BEGIN
  DBMS_SCHEDULER.ENABLE(NAME => 'MY_PROGRAM');
END;

-- created a job

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(JOB_NAME   => 'MY_TEST_JOB',
                           -- PROGRAM_NAME => 'MY_PROGRAM',
                            JOB_TYPE   => 'STORED_PROCEDURE',
                            JOB_ACTION => 'GENEVA_ADMIN.IPGTIBCOAPIS_LMS_GENERIC_FIXED.PREPBILLINFOFORACCRUALINTR',
                            START_DATE => SYSDATE,
                            AUTO_DROP  => TRUE,
                            COMMENTS   => 'MY NEW JOB');
END;

-- and passing arguments

BEGIN
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(JOB_NAME          => 'MY_TEST_JOB',
                                        ARGUMENT_POSITION => 1,
                                        ARGUMENT_VALUE    => '06-JUL-2017');
END;

It is giving below error.

ORA-27465: invalid value 1 for attribute ARGUMENT_POSITION

I want to pass static date as 06-JUL-2017. I also tried with

dbms_scheduler.set_job_anydata_value

but getting same error.

Could you please help.

Thanks,

Answer 1

From the docs, SET_JOB_ARGUMENT_VALUE only works for VARCHAR2 arguments. If you need to set a non-VARCHAR2 value (in your case a date), you have to use SET_JOB_ANYDATA_VALUE instead. I think this should work.

BEGIN
  DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE(JOB_NAME          => 'MY_TEST_JOB',
          ARGUMENT_POSITION => 1,
          ARGUMENT_VALUE => SYS.ANYDATA.convertDate(TO_DATE('06-JUL-2017','DD-MON-YYYY')));
END;

Answer 2

I got where is the problem.

in DBMS_SCHEDULER.CREATE_JOB we need to pass NUMBER_OF_ARGUMENTS so that DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE or DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE won't give error.

Thanks every one for supporting.

Answer 3

The error also occurs when you add new arguments to the procedure AFTER you had created the job. Then you have to recreate the job in order to accept the new arguments.