Oracle/Unix shell script question...'INSERT'

[modfather]

Hi. I'm hoping someone can help me out with an Oracle/Unix question I have. I have a Unix script (RS/6000, Oracle 8i) that adds a record to an Oracle table. This script is in production and a cron job runs it weekly, on Saturdays. But I have to change this script EVERY week to do two things: Increment the "jobno" field (in the script, it is
toward the bottom - "33&quot and change the "datetime" field to the date for "this Saturday". This is a bit cumbersome and is subject to forgetfulness. Is there some way I can automate this? Can I change it within the script, at runtime? Or should I create a seperate file to keep the last "jobno" and "datetime" and update them each week with an automated script? But I'm very new to Oracle (have
Progress background) and wouldn't know how to change the hardcoded values into something like "expr $jobno" or something similar.

If someone could spell it out for me, I'd add you to my Christmas card list!

Thanks a bunch in advance. My script is below.

Thanks again!
Steve


#!/bin/ksh
#
#Script : archive_db.ksh
#
#
#Usage : archive_db.ksh <dbname> <usrname> <pwd>
#

ORACLE_SID=o21p

if [[ $# != 3 ]]
then
print &quot;Invalid number of parameters&quot;
print &quot;Usage:&quot;
print &quot; $0 db user password&quot;
exit 1
fi
if [[ $ORACLE_SID != $1 ]]
then
print &quot;Database name does not match your current SID&quot;
exit 1;
fi

sqlplus $2/$3 << SQLEND >$WINALOGS/archive_it.log
set term on
set pagesize 54
set verify off
clear columns
clear breaks
clear computes
set transaction read only;
commit;

insert into aque
(
jobno
,who
,corr_acc_no
,group_code
,agent_code
,currency
,started
,finished
,datetime
,filename
,lock_flg
,status
,priority
,modes
)
values
(
33, <--------Want to increment this by one each time it is run
'AUTO',
0,
'ALL',
' ',
' ',
'01-JAN-1900',
'01-JAN-1900',
'08-DEC-2001', <----------------Want this to always be &quot;Next&quot;
Saturday
' ',
0,
0,
0,
4
);
commit;
SQLEND
exit

 

[Guest_imported]

I can't think of any off hand for the date but for the jobno , you could create a sequence on the database and insert sequence.nextval each time, this will autoincrement when you use the nextval maybe for the date you could select max date and add 7 ?