Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle/Unix shell script question...

Re: Oracle/Unix shell script question...

From: <Stan.Milam_at_metronet.com>
Date: Thu, 13 Dec 2001 20:04:49 GMT
Message-ID: <BR7S7.4534$Oh1.40535@insync>

: On 11 Dec 2001 11:12:01 -0800, ocsfan_at_yahoo.com (Steve) wrote:  

:>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") 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. If you wouldn't mind, please email replies
:>to ocsfan_at_PLEASEREMOVEyahoo.com or post here. My script is below.
:>
:>Thanks again!
:>Steve
:>ocsfan_at_PLEASEREMOVEyahoo.com
:>
:>
:>#!/bin/ksh
:>#
:>#Script : archive_db.ksh
:>#
:>#
:>#Usage : archive_db.ksh <dbname> <usrname> <pwd>
:>#
:>
:>ORACLE_SID=o21p
:>
:>if [[ $# != 3 ]]
:>then
:> print "Invalid number of parameters"
:> print "Usage:"
:> print " $0 db user password"
:> exit 1
:>fi
:>if [[ $ORACLE_SID != $1 ]]
:>then
:> print "Database name does not match your current SID"
:> 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 "Next"
:>Saturday
:>' ',
:>0,
:>0,
:>0,
:>4
:>);
:>commit;
:>SQLEND
:>exit

: 1 create a sequence like this
: create sequence yourseq start with 1 increment 1  -- etc
: replace the 33 by yourseq.nextval, after having this statement once
: before the next run of the job
: 2 replace the hardcoded date by 
: next_day(sysdate,'SAT')

Use the above example and put the code in PL/SQL stored procedure and schedule it with DBMS_JOB. This get CRON out of the way.

Regards,
Stan Milam.

-- 
==========================================================================
"He deserves death."  "Deserves it!  I daresay he does.  Many that live
deserve death.  And some that die deserve life.  Can you give it to them?
Then do not be be too eager to deal out death in judgement.  For even the
very wise cannot see all ends."  J.R.R. Tolkien, "The Fellowship of the
Ring". 

For we know him who said, "It is mine to avenge; I will repay", and again,
"The Lord will judge his people."  It is a dreadful thing to fall into the
hands of the living God.  Hebrews 10:30

"Now the dwelling of God is with men, and he will live with them.  They
will be his people, and God himself will be with them and be their God.  He
will wipe every tear from their eyes.  There will be no more death or
mourning or crying or pain, for the old order of things has passed away."
Revelations 21:3-4
Received on Thu Dec 13 2001 - 14:04:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US