Re: ksh scrip to include various sql commands and rownum

Hi Bilal,

try this. It should work and fulfill your requirement !!

#!/usr/bin/ksh
typeset -i num_of_rows
sqlplus username/password <<EOD
column num_rows format 9999999 noprint new_value num_of_rows REM do insert
insert into your_table (your select_statement from sub_table) /
commit;
REM do select
select count(*) num_rows
  from sub_table
   your where_clause from above
/
exit &num_of_rows
EOD
# echo $?
num_of_rows=$?
echo "Number of rows inserted = " $num_of_rows

Regards

Robert

"Bilal" <bjeewa_at_hotmail.com> wrote in message news:f9882c89.0401182350.5a543290_at_posting.google.com...
> Hi,
>
> 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.
> Thanks
> Bilal
Received on Mon Jan 19 2004 - 04:44:26 CST