Re: SQL*Plus Counting Updates

Michael Austin <maustin_at_firstdbasource.com> wrote in message news:<e9nAc.6891$TT1.1390_at_newssvr22.news.prodigy.com>...
> G Dahler wrote:
>
> > "mouse" <spamtrap_at_safe-mail.net> a écrit dans le message de
> > news:56ef365c.0406170312.208aa975_at_posting.google.com...
> >
> >>Hi
> >>
> >>I have several .sql scripts which are in the form of:
> >>
> >>update c_store_tb set class_lk_id = 14 where ccn = '010005';
> >>update c_store_tb set class_lk_id = 14 where ccn = '010008';
> >
> > <snip>
> >
> >>Each script can be up to 5000 lines long. On running the script I am
> >>told:
> >>'1 row updated' or '0 rows updated' for each of the rows. The output I
> >
> >
> > If you are un UNIX and really update either 1 or 0 row, you could spool the
> > file to a text file and use grep or awk to count the lines containing "0
> > rows updated" and "1 row updated"
> >
> > eg:
> >
> > updated=`grep "1 row updated" spool.lst | wc -l` and
> > notupdated=`grep "1 row updated" spool.lst | wc -l`
> >
> > Crude, not efficient, but better than excel. I would be better to write an
> > awk program and count all the same time.
> >
> > On Windoze, you could install cygwin and use a bash shell scipt to do it.
> >
> > just my 0.02
> >
> >
>
> how about creating a procedure that reads the file using UTL_FILE and
> read each statement executes in and counts the successes.
>
> Michael.

Thanks to all for the suggestions.

I'll definitely have a look at the UTL_FIL solution.

My temporary attempt (which works OK) is to encompass the updates in a procedure, delcaring variables for the updated and not updated rows like so:

declare
ud_count number;
tot_count number;
begin
tot_count := 0;
ud_count := 0;
update c_store_tb set net_selling_area_sqft = 2860 where ccn = '120962';
ud_count := ud_count + sql%rowcount;
tot_count := tot_count + 1;
update c_store_tb set net_selling_area_sqft = 1891 where ccn = '190598';
ud_count := ud_count + sql%rowcount;
tot_count := tot_count + 1;
update c_store_tb set net_selling_area_sqft = 2087 where ccn = '310054';
ud_count := ud_count + sql%rowcount;
tot_count := tot_count + 1;
dbms_output.put_line('Updated: ' || ud_count); dbms_output.put_line('Total: ' || tot_count); end;

It's just a pain to set the files up!

Cheers

Phil Received on Mon Jun 21 2004 - 15:34:44 CEST