CODEWITHSUNDEEP
oracle-databaseschedulerjobsdbms-scheduler
SK Yakub
SK Yakub

Reputation: 105

ORA-27465: invalid value 1 for attribute ARGUMENT_POSITION

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,

Upvotes: 4

Views: 4773

Answers (3)

Falco Preiseni
Falco Preiseni

Reputation: 476

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.

Upvotes: 0

SK Yakub
SK Yakub

Reputation: 105

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.

Upvotes: 6

kfinity
kfinity

Reputation: 9091

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;

Upvotes: 0

Related Questions