2 Replies Latest reply: May 22, 2013 9:51 PM by PRaju RSS

    DBMS_SCHEDULER, program argument not generating sysdate value as default

    PRaju
      Gurus,

      Recently we come across DBMS_SCHEDULER issue while passing SYSDATE as default value. Job executed with corect date value on creation time, but further schedule its not incrementing date value
      Let us know if any special job class need to use to achive this.

      Snap of the Program

      BEGIN
      SYS.DBMS_SCHEDULER.CREATE_JOB (
      job_name => '"OBIEE_ADM"."First_Job"',
      job_type => 'STORED_PROCEDURE',
      job_action => '"OBIEE_ADM"."SF_INTERFACE_PKG"."SF_EXTRACT"',
      number_of_arguments => 1,
      start_date => NULL,
      repeat_interval => 'FREQ=DAILY;BYHOUR=16;BYMINUTE=0;BYSECOND=0',
      end_date => NULL,
      job_class => '"SYS"."DEFAULT_JOB_CLASS"',
      enabled => FALSE,
      auto_drop => FALSE,
      comments => 'OBIEE Job FOR 4:00 PM');

      SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
      job_name => '"OBIEE_ADM"."First_Job"',
      argument_position => 1,
      argument_value => SYSDATE);

      SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
      name => '"OBIEE_ADM"."First_Job"',
      attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);

      SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
      name => '"OBIEE_ADM"."First_Job"',
      attribute => 'max_run_duration', value => INTERVAL '1' HOUR);

      SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
      name => '"OBIEE_ADM"."First_Job"',
      attribute => 'schedule_limit', value => INTERVAL '1' HOUR);

      SYS.DBMS_SCHEDULER.enable(
      name => '"OBIEE_ADM"."First_Job"');
      END;
      /

      Databsae Version

      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
      PL/SQL Release 10.2.0.4.0 - Production
      "CORE     10.2.0.4.0     Production"
      TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
      NLSRTL Version 10.2.0.4.0 - Production