Re: how to write table changes in a log file

Add
dbms_output.put_line(SQL%ROWCOUNT) at appropiate places (*before* the commit)

Hth,

Sybrand Bakker, Oracle DBA

"Maggie" <maggiezhou_at_hotmail.com> wrote in message news:8jiip3$55m$1_at_web1.cup.hp.com...
> I would like to capture in a text log file for how many rows are affected
> after each call is made in the procedure. so, how many rows were inserted,
> deleted, etc.
>
> For example, here is a procedure, I don't know proper plsql statement I
 can
> use in PLSQL to catch these affected rows.
>
> Procedure CTN ( v_vMinDate IN VARCHAR2, v_vMaxDate IN
 VARCHAR2,v_nDateMonth
> IN NUMBER) IS
>
> v_dMinDate DATE := TO_DATE(v_vMinDate,'MM/DD/YY');
> v_dMaxDate DATE := TO_DATE(v_vMaxDate,'MM/DD/YY');
>
> BEGIN
>
> DELETE from hpo_cm_ctn_trans_temp;
> commit;
>
> INSERT INTO hpo_cm_ctn_trans_temp
> (
> SELECT ctn.seq_pk,
> ctn.source_site_cd,
> ctn.product_line,
> ctn.reference_dt,
> mapi.cm_transaction_code
> FROM ctn,mapi
> WHERE ctn.trans_date >= v_dMinDate
> and ctn.trans_date < v_dMaxDate
> and ctn.debit_acc = mapi.debit_acct
> and ctn.location_cd = mapi.location_code
> and ctn.trans_code = mapi.cm_transaction_code);
> commit;
>
> END;
>
>
>
Received on Fri Jun 30 2000 - 00:00:00 CDT