Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2006
    Posts
    2

    Unanswered: Passing parameter to a cron job

    Hi,

    I need to create a report that runs every pay day for the payroll department. I have created my pl/sql procedure, but I need to pass the parameters for the specific payroll run (i.e. deduction code, payroll number, year, etc). Can I setup this as a cron job? How?

    Thanks for your help.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    How do you decide the values for these parameters?

    You could have a "parameter" table where you have a "status" column which indicates (apart from the status) which payroll run the cron job will execute; and your PL/SQL procedure could take it from there.

    Or, from that table, generate the next payroll parameters from the previous.

    Good Luck!

    PS: You can only pass "fixed" parameters to a cron job (unless you don't mind changing these in the crontab each payroll).
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Feb 2006
    Posts
    2
    Hi LKBrwn_DBA,

    I'd be glad to do the first option, because it seems to need very little human intervention. I'd need to create the parameter table, but still I'm clueless how to set it in cron. It would also be interesting (but not too good) to know how to pass fixed parameters to a cron job.

    Thanks for your help.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1

    start a script using crontab

    man crontab

    A crontab file consists of lines of six fields each.
    minute hour day_of_month month_of_year day_of_week command
    # (0-59) (0-23) (1-31) (1-12) (0-6 with 0=Sunday) command

    e.g.
    Code:
    13 18 * * * /scripts/killoldps 2>&1 | mailx -s "`uname -n`: killoldps" someone@somehost.com

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by anita09
    Hi LKBrwn_DBA,

    I'd be glad to do the first option, because it seems to need very little human intervention. I'd need to create the parameter table, but still I'm clueless how to set it in cron. It would also be interesting (but not too good) to know how to pass fixed parameters to a cron job.

    Thanks for your help.

    1) To use a parameter table (derfinition not included here), you will need to code the query in a PL/SQL procedure (or SQL script) and execute your pay-day report from there. Here is a simple example of SQL script:
    Code:
    #!/bin/ksh
    # Name: Pay_Report.ksh
    . /var/opt/oracle/oraenv #<- Your script to set oracle environment
    sqlplus /nolog <<EOF
    -- connect to database
    conn <uid>/<pw>
    -- Get parameters
    var deduction_code varchar2(3);
    var payroll_number number;
    var year_nr number;
    Begin
     Select deduct_cd, payroll_num, run_year
       Into :deduction_code, :payroll_number, :year_nr
       From Payroll_parameters
      Where Status = 'NOT  RUN';
    End;
    /
    -- Execute report:
    Spool /path/reports/pay_report.txt
    Exec Pay_Report(:deduction code, :payroll_number, :year_nr);
    Spool off
    Update Payroll_parameters
           Set Status = 'RUN'
           Where Status = 'NOT RUN';
    Commit;
    Exit;
    EOF
    2) Now if you want to execute on day 1 and 16 of every month at 5pm, you would add to crontab as:
    Code:
    00 17 1,16 * * * /path/to/scripts/Pay_Report.ksh >/path/to/logs/Pay_Report.log


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Assuming "pl/sql" in the original post actually refers to the Oracle environment I'd say the easiest way to accomplish the task would be to utilize an Oracle job (using DBMS_JOB package).

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by n_i
    Assuming "pl/sql" in the original post actually refers to the Oracle environment I'd say the easiest way to accomplish the task would be to utilize an Oracle job (using DBMS_JOB package).

    She still would need a means of "passing" (making available) the parameters, therefore the parameter table.
    Last edited by LKBrwn_DBA; 03-01-06 at 11:11.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Of course, I don't argue with that. I'm just trying to say that if you pass parameters via an Oracle table and then run a PL/SQL procedure it doesn't make sense to utilize an external scheduler when Oracle provides a facility with similar functions.

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Yes, but still crontab is easier to use.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •