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

Home -> Community -> Usenet -> c.d.o.misc -> Re: ksh scrip to include various sql commands and rownum

Re: ksh scrip to include various sql commands and rownum

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 19 Jan 2004 09:25:03 -0600
Message-ID: <uad4krmj3.fsf@standardandpoors.com>


> I'm very new to ksh, unix and oracle (bad combination for this
> task!!). I'm trying to include several insertion sql commands in a ksh
> script and after each insertion I would like to store the number of
> lines inserted into a variable. e.g
> This is my attempt so far but its not working. I doubt it is efficient
> and I'm not too sure about the syntax:
>
> sqlplus - silent "$USERNAME/$PASSWORD@$DATASTORE" <<EOF
> set pagesize 0 feedback off verify off heading off echo off
>
> insert into ALARM_ARC (select * from ALARM where ID_ALARM = 6);
> row_num_6=`SQL%ROWCOUNT`;
> insert into ALARM_ARC (select * from ALARM where ID_ALARM = 105);
> row_num_105=`SQL%ROWCOUNT`;
> insert into ALARM_ARC (select * from ALARM where ID_ALARM = 96);
> row_num_96=`SQL%ROWCOUNT`;
> echo "Number of rows inserted is for ID 6 is "$row_num_6;
> echo "Number of rows inserted is for ID 96 is "$row_num_96;
> echo "Number of rows inserted is for ID 105 is "$row_num_105;
> EXIT;
> EOF;
>
> My constraint is that the script must not call any other sql files or
> ksh scripts. It must be completely self contained. I thank you and
> appreciate any help.

You need to understand what the two tools and the syntax of how to make them do so. KSH variable syntax is the bottom three echo lines but look at where you put it, inside a sqlplus session. SQLPlus won't understand the ksh `$' character to mean variable.

But, to accomplish what you want just ignore the ksh echo statement and look up dbms_output and try to get it to replace the echo statement. Your sort of close.

-- 
Galen Boyer
Received on Mon Jan 19 2004 - 09:25:03 CST

Original text of this message

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