5 Replies Latest reply on Nov 2, 2006 11:10 PM by Rnr-Oracle

    DBMS_SCHEDULER, scheduler program argument and simple sysdate problem


      I have a little problem with using Oracle Scheduler program. I've declared simple stored procedure PR_Test (p_date DATE) wchich outputs passed in date. Next definded Oracacle Scheduled program wchich action is to run PR_TEST:
      Dbms_Scheduler.Create_Program(program_name => 'TEST',
      program_type => 'STORED_PROCEDURE',
      program_action => 'OWNER.PR_TEST',
      number_of_arguments => 1,
      enabled => FALSE,
      comments => 'Test');

      Next defined program argument:
      Dbms_Scheduler.define_program_argument( program_name => 'TEST',
      argument_position => 1,
      argument_name => 'P_DATE',
      argument_type => DATE,
      default_value => 'SYSDATE',
      out_argument =>FALSE);

      Next enabled program, defined schedule and finally job:
      Dbms_Scheduler.Create_Job(p_job_name => 'SJOB_PR_TEST'
      ,p_program_name => 'TEST'
      ,p_schedule_name => 'SSCH_EVERY_MINUTE'
      ,p_comments => 'test');

      Job has been enabled, but of course FAILED with running PR_TEST with ORA-01841: (full) year must be between -4713 and +9999, and not be 0.

      Seem to fail on conversion default argument value into PR_TEST parameter p_date --> TO_DATE('SYSDATE')...

      The question is - how to define program arguments or built scheduler programs to achive the needed program behavior ? Have U got any ideas?

      Best regards,
        • 1. Re: DBMS_SCHEDULER, scheduler program argument and simple sysdate problem

          I assume you want to pass in the current date as the the default value but SQL cannot convert the string value 'SYSDATE' to a date value. There are two things you can try

          1) Instead of having
          default_value => 'SYSDATE',

          and then in your procedure have code that says "if p_date is NULL then use sysdate".

          2) You can use a metadata argument to pass into the program the date on which the job started.
          So instead of using define_program_argument, use

          define_metadata_argument('test', 'job_start',1) ;

          This will dynamically fill in the job start date whenever the job runs.

          Hope this helps,
          • 2. Re: DBMS_SCHEDULER, scheduler program argument and simple sysdate problem
            Thank U 4 your answer, but...

            1) First opttion - I has been thinking about that (DEFAULT_VALUE = NULL, when I would like to set any specific date I could do it by API, and parametr in PR_TEST (p_date DATE DEFAULT SYSDATE) would be a solution ) , but when I tried to Enable Job it fails...

            ORA-27457: argument 1of job "OWNER.SJOB_PR_TEST" has no value
            ORA-06512: on "SYS.DBMS_ISCHED", line 2751
            ORA-06512: on "SYS.DBMS_SCHEDULER", line1794
            ORA-06512: on line 2

            2) Maybe I don;t catch the idea of solution with matadata argument or my explenation wasn't clear.
            My need: I would like to schedule daiy executing for example load specific documents into Data Warehouse. Each day schould be loaded only documents created yestarday in source systems (in common loading). So I'd like to define it once, and each day procedure schould be run with appriopriate "yesterday" date without modyfication of program or argument definition..

            From your suggestion I undestand that matadata argument is some kind of placeholder when I could try to store some kind of dynamically changed values and use them as a job or program argument values - I have to read more about that, but honestly I'm not sure if this could be proper solution...
            • 3. Re: DBMS_SCHEDULER, scheduler program argument and simple sysdate problem

              1) This looks like it might be a Scheduler bug. What should work is instead having your procedure be
              PR_TEST(p_date DATE DEFAULT NULL)
              then for the program have number_of_arguments=>0 so it will use the stored procedure default (and don't do the define_program_argument) and then in your stored procedure convert NULL to SYSDATE.

              2) I still think this will work. using the job_start metadata argument will fill in the current date as that parameter (so p_date will see the current sysdate) . The Scheduler will fill in the current date every time it runs the job so you don't need to do anything,

              All you need to do is instead of using define_program_argument, use define_metadata_argument with metadata_attribute=>'JOB_START' .

              Hope this helps,
              • 4. Re: DBMS_SCHEDULER, scheduler program argument and simple sysdate problem

                1) I'll try to perform some more tests and if there is really bug I'll start a TAR.
                The proposed solution could be good for simple one procedure, but in fact I'd like to use scheduler with scheduler program for a few dozens of ETL packages and I can't afford for refractor all of them.

                2) As I said above, this could be a solution for a specific situation, but the problem is general. I'd like to load to Data Warehouse for example documents - every day should be (commonly) loaded documents from yesterday [=TRUNC(SYSDATE-1)], and settings metadata argument to 'JOB_START'. Of course different kinds of "documents" should be loaded with different combination of SYSDATE - n days...
                The aim is to have one definition of a program with argument (let's say p_date) commonly set default to current date, and could use this definition in different jobs in which I could override value of this parameter for another specific value. The basic job would execute program every day with common default program argument settings and auxiliary jobs would be used to run program with another settings (for example to reload some documents from a specific past date). And if for the specific, incidental jobs I could set job argument before start it for basic job I'd like not to do it - it should be set in program (argument) definition.

                Of course there are always possibilities to change philosophy of perform ETL processes (for example more use of DW metadata for control standard incremental loading) but at this point I'd like to avoid it - because budget was only for replacing unix crontab by Oracle Scheduler...

                - I'll take a look what aims could archive using metadata argument (and how to use it :-) )
                - unfortunately both above solutions still seems as a workaround...

                ...and thanks for your engage so far.

                Best regards,
                Marcin Kubec
                • 5. Re: DBMS_SCHEDULER, scheduler program argument and simple sysdate problem
                  Hi Marcin,

                  I agree that there is no good solution. The job_start metadata argument will only work for passing in sysdate not for the more general case of "sysdate-n" .

                  This problem is generic to PL/SQL stored procedures which cannot accept "sysdate" as a default, maybe pl/sql stored procedures have some solution or common workaround for this (you can ask on the PL/SQL forum) ?

                  One other way would be to accept a string value as p_date and use dynamic SQL to convert it into a date value (or cast it directly if it is a date format).

                  If setting default_value to NULL in define_program_argument doesn't work, try using define_anydata_argument with an anydata object containing NULL. This should work.

                  Hope this helps,