| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
02-27-06, 12:40
|
|
Registered User
|
|
Join Date: Feb 2006
Posts: 2
|
|
|
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.
|
|
02-27-06, 16:34
|
|
Registered User
|
|
Join Date: Jun 2003
Location: West Palm Beach, FL
Posts: 2,456
|
|
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
|
|
02-28-06, 16:17
|
|
Registered User
|
|
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.
|
|
02-28-06, 17:36
|
|
Registered User
|
|
Join Date: May 2005
Location: South Africa
Posts: 1,268
|
|
|
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
|
|
03-01-06, 08:45
|
|
Registered User
|
|
Join Date: Jun 2003
Location: West Palm Beach, FL
Posts: 2,456
|
|
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
|
|
03-01-06, 08:52
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,454
|
|
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).
|
|
03-01-06, 10:07
|
|
Registered User
|
|
Join Date: Jun 2003
Location: West Palm Beach, FL
Posts: 2,456
|
|
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. 
__________________
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
|
Last edited by LKBrwn_DBA; 03-01-06 at 10:11.
|
03-01-06, 10:26
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,454
|
|
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.
|
|
03-01-06, 10:40
|
|
Registered User
|
|
Join Date: Jun 2003
Location: West Palm Beach, FL
Posts: 2,456
|
|
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
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Passing parameter to a cron job
