Re: Selective updates of an Oracle table

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?

>