SQL%ROWCOUNT

[masalachol]

I have multiple inserts and would like to know the total insert per job.....below with sql%rowcount I am only getting a count per insert not the total. Any suggestions?

CREATE OR REPLACE PACKAGE BODY dbcarrier_load_data_pkg IS
PROCEDURE invoice_load_pp
(p_orgid IN cc_invoice.org_id%TYPE,
p_vnbr IN cc_invoice.vendor_nbr%TYPE,
p_invnbr IN cc_invoice.invoice_nbr%TYPE,
p_invamt IN cc_invoice.invoice_amt%TYPE,
p_invdt IN cc_invoice.invoice_date%TYPE,
p_invpd IN cc_invoice.invoice_paid%TYPE)
IS
BEGIN

INSERT INTO cc_invoice
VALUES(p_orgid,
p_vnbr,
p_invnbr,
p_invamt,
p_invdt,
p_invpd);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END invoice_load_pp;

 

[carp]

Since you are only inserting one row, you should already know how many rows are inserted: 1!
If the real question is how many times is this procedure invoked, then you would have to create and maintain a counter in the invoking program. Every time you call invoice_load_pp, increment the counter.

 

[masalachol]

Ok i did what you suggested and it worked ...

1
2

PL/SQL procedure successfully completed.

ok now I only want to capture the total number which would be 2 ....how do I do that?

 

[johnherman]

You could insert that final value into an audit table containing the process, date, time and rows inserted count.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[masalachol]

ok thank you very much .... i figured it out.