Re: Selective updates of an Oracle table

Douglas,

That worked nicely for me.
Thank you very much.

Michael

Douglas Hawthorne wrote:
> Michael,
>
> One suggestion would be:
> #!/bin/ksh -u
> sqlplus -S "/ as sysdba" <<END
> WHENEVER SQLERROR EXIT FAILURE
> UPDATE messengerlog
> SET stepstatus = ${1:-stepstatus},
> failreason = ${2:-failreason}
> /
> COMMIT;
> EXIT
> END
>
> To invoke this script, you will have to use the following syntax:
> ./your_script "'parm_1'" "'parm_2'"
> (The apostrophes are enclosed in quotes). This works for Oracle
10.1.0.2 on
> SuSe Linux 9.0 Pro.
>
> The secret is the operator ${name:-word} which evaluates to 'word' if
$name
> is not defined or does not have at least one character.
>
> My preference would be to be precede the sqlplus by the following
logic:
> if [[ -z "$1" ]]
> then
> new_stepstatus_value=stepstatus
> else
> new_stepstatus_value="'$1'"
> fi
> ...
> UPDATE messengerlog
> SET stepstatus = $new_stepstatus_value
> ...
> This avoids the messy quoting on the script invocation by putting
inside the
> script.
>
> Douglas Hawthorne
>
> <voichekm_at_yahoo.com> wrote in message
> news:1102488013.250961.93040_at_f14g2000cwb.googlegroups.com...
> > Hi,
> > I'm using Oracle 8.17 on Unix.
> > Need to write a ksh script whose input is a few parameters and it
> > updates Oracle table with the parameter values using SQLPLUS.
> > The catch is that some of the parameters might be empty strings
("").
> > It that case, the existent values in the Oracle table should not be
> > updated.
> > The script should look something like:
> > -------------------
> > #!/bin/ksh
> > sqlplus -silent "/ as sysdba" << END
> > UPDATE messengerLog SET stepstatus = IF ($1 = "", stepstatus, '$1')
> > SET failreason = IF ($2 = "", failreason, '$2')
> > ...
> > commit;
> > exit;
> > END
> > ------------------
> > Can you help me to formalize such a script?
> >
> > Thank you,
> > Michael
> >
Received on Wed Dec 08 2004 - 07:58:04 CST