Re: ksh scrip to include various sql commands and rownum

"Robert" <Robert.Ansperger_at_shs.de> wrote in message news:<bugce9$9b2$1_at_news.f.de.plusline.net>...
> 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

Thanks a lot everyone ... its exactly what I needed. I appreciate all your help.

Kind regards
Bilal Received on Tue Jan 20 2004 - 03:24:33 CST