Number of rows UPDATED and INSERTED from the same procedure in PL/SQL

Question

I have a procedure that does the INSERT INTO and then the UPDATE of some fields (both in the same procedure), I'm using this answer from @Clive Number of rows affected by an UPDATE in PL/SQLto know the amount of data that has been updated to put in a log, but it brings me the total number of rows instead of just the records that have been updated.

Is that the right way to know?

What I need is to know how many rows were INSERTED from the INSERT STATEMENT and how many rows were UPDATED from the UPDATE STATEMENT.

My query:

CREATE OR REPLACE PROCEDURE OWNER.TABLE_NAME
AS

 -- VARIABLE
 v_qtd_regs  number := 0;  
 v_code      number;
 v_errm      VARCHAR2(500);
 start_time  pls_integer;
 end_time    pls_integer;
 elapse_time number;
 proc_name   varchar2(100);
 i NUMBER;

BEGIN
   proc_name := 'PRDWBI_CGA_D_COLUMNS';
   start_time := dbms_utility.get_time;
   DS_FUNCESP.PRDSBI_GRAVA_LOG( 'I', 'DataWarehouse', proc_name, 'Início Carga' );

   -- INSERT INTO TABLE:
   INSERT INTO OWNER.TABLE_NAME
   (COLUMN_ID, COLUMNS_NAME, COLUMN_NAME2)
   (SELECT 1 AS COLUMN_ID, 'TEST' AS COLUMN_NAME, SYSDATE AS COLUMN_NAME2 FROM DUAL);

   COMMIT;

  -- UPDATE SOME COLUMNS I NEED
   UPDATE OWNER.TABLE_NAME y
   SET (y.COLUMNS_NAME, y.COLUMN_NAME2) = 
               (SELECT 'TEST2' AS COLUMN_NAME, SYSDATE AS COLUMN_NAME2 FROM DUAL x WHERE x.COLUMN_ID = y.COLUMN_ID)
   WHERE EXISTS (SELECT 'TEST2' AS COLUMN_NAME, SYSDATE AS COLUMN_NAME2 FROM DUAL x WHERE x.COLUMN_ID = y.COLUMN_ID);

   -- TO KNOW HOW MANY ROWS WERE UPDATED
   i := SQL%rowcount;     

   COMMIT;  

   --dbms_output.Put_line(i);

   SELECT COUNT(1) INTO v_qtd_regs FROM OWNER.TABLE_NAME where LinData >= TRUNC(SYSDATE);
   end_time := dbms_utility.get_time;
   elapse_time := ((end_time - start_time)/100);
   v_errm := SUBSTR(SQLERRM, 1 , 500);
   DS_FUNCESP.PRDSBI_GRAVA_LOG('T', 'DataWarehouse', proc_name, v_errm, v_qtd_regs, elapse_time );

   COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      v_code := SQLCODE;
      v_errm := SUBSTR(SQLERRM, 1 , 500);
      DS_FUNCESP.PRDSBI_GRAVA_LOG('E', 'Error', proc_name, v_errm);

END;

---

QUESTION EDITED TO SHOW A REAL EXAMPLE:

I created a table that takes data from "SYS.DBA_TAB_COLUMNS" just to use as an example, as shown below:

  CREATE TABLE "DW_FUNCESP"."D_TEST"
    (
        "ID_COLUMN" NUMBER(10,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1
        START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
        "NM_OWNER"  VARCHAR2(500 CHAR) NOT NULL ENABLE                       ,
        "NM_TABLE"  VARCHAR2(500 CHAR) NOT NULL ENABLE                       ,
        "CD_COLUMN" NUMBER(20,0) NOT NULL ENABLE                             ,
        "NM_COLUMN" VARCHAR2(500 CHAR) NOT NULL ENABLE                       ,
        "DS_COLUMN" VARCHAR2(500 CHAR) NOT NULL ENABLE                       ,
        "LINDATE"   DATE DEFAULT SYSDATE NOT NULL ENABLE                     ,
        "LINORIGIN" VARCHAR2(100 CHAR) NOT NULL ENABLE
    )

Then I created a procedure to identify the inserted and updated records, as below:

CREATE OR REPLACE PROCEDURE DW_FUNCESP.PRDWBI_CGA_D_TEST
AS

 -- variaveis de suporte as informações que deve gravar
 rows_inserted integer;
 rows_updated  integer;

BEGIN

   -- Insert Into table
   INSERT INTO DW_Funcesp.D_TEST
   (NM_OWNER, NM_TABLE, CD_COLUMN, NM_COLUMN, DS_COLUMN, LINDATE, LINORIGIN)
   (SELECT
      NVL(x.NM_OWNER ,'NOT FOUND')      AS NM_OWNER     ,
      NVL(x.NM_TABLE ,'NOT FOUND')      AS NM_TABLE     ,
      NVL(x.CD_COLUMN ,-1)              AS CD_COLUMN    ,
      NVL(x.NM_COLUMN ,'NOT FOUND')     AS NM_COLUMN    ,
      NVL(x.DS_COLUMN ,x.NM_COLUMN)     AS DS_COLUMN    ,
      SYSDATE                           AS LINDATE      ,
      'SYS.DBA_TAB_COLUMNS'             AS LINORIGIN
    FROM
      (
        SELECT
          d.OWNER       AS NM_OWNER ,
          d.TABLE_NAME  AS NM_TABLE ,
          d.COLUMN_ID   AS CD_COLUMN,
          d.COLUMN_NAME AS NM_COLUMN,
          e.COMMENTS    AS DS_COLUMN
        FROM SYS.DBA_TAB_COLUMNS d
        LEFT JOIN SYS.DBA_COL_COMMENTS e
          ON e.OWNER           = d.OWNER
          AND e.TABLE_NAME  = d.TABLE_NAME
          AND e.COLUMN_NAME = d.COLUMN_NAME
        WHERE d.OWNER = 'DW_FUNCESP'
       ) x
    LEFT JOIN DW_FUNCESP.D_TEST y
      ON  y.NM_OWNER = x.NM_OWNER
      AND y.NM_TABLE = x.NM_TABLE
      AND y.NM_COLUMN = x.NM_COLUMN
    WHERE y.ID_COLUMN IS NULL);

    rows_inserted := sql%rowcount;

  -- Update the table
   UPDATE DW_FUNCESP.D_TEST z
   SET (z.NM_COLUMN, z.DS_COLUMN, z.LINDATE) = 
                  (SELECT
                      NVL(x.NM_COLUMN ,'NOT FOUND')     AS NM_COLUMN    ,
                      NVL(x.DS_COLUMN ,x.NM_COLUMN)     AS DS_COLUMN    ,
                      SYSDATE                           AS LINDATE      
                    FROM
                      (
                        SELECT
                          d.OWNER       AS NM_OWNER ,
                          d.TABLE_NAME  AS NM_TABLE ,
                          d.COLUMN_ID   AS CD_COLUMN,
                          d.COLUMN_NAME AS NM_COLUMN,
                          e.COMMENTS    AS DS_COLUMN
                        FROM SYS.DBA_TAB_COLUMNS d
                        LEFT JOIN SYS.DBA_COL_COMMENTS e
                          ON e.OWNER           = d.OWNER
                          AND e.TABLE_NAME  = d.TABLE_NAME
                          AND e.COLUMN_NAME = d.COLUMN_NAME
                        WHERE d.OWNER = 'DW_FUNCESP'
                       ) x
                    WHERE z.NM_OWNER = x.NM_OWNER
                      AND z.NM_TABLE = x.NM_TABLE
                      AND z.CD_COLUMN = x.CD_COLUMN)
   WHERE EXISTS (SELECT
                      NVL(x.NM_COLUMN ,'NOT FOUND')     AS NM_COLUMN    ,
                      NVL(x.DS_COLUMN ,x.NM_COLUMN)     AS DS_COLUMN    ,
                      SYSDATE                           AS LINDATE      
                    FROM
                      (
                        SELECT
                          d.OWNER       AS NM_OWNER ,
                          d.TABLE_NAME  AS NM_TABLE ,
                          d.COLUMN_ID   AS CD_COLUMN,
                          d.COLUMN_NAME AS NM_COLUMN,
                          e.COMMENTS    AS DS_COLUMN
                        FROM SYS.DBA_TAB_COLUMNS d
                        LEFT JOIN SYS.DBA_COL_COMMENTS e
                          ON e.OWNER           = d.OWNER
                          AND e.TABLE_NAME  = d.TABLE_NAME
                          AND e.COLUMN_NAME = d.COLUMN_NAME
                        WHERE d.OWNER = 'DW_FUNCESP'
                       ) x
                    WHERE z.NM_OWNER = x.NM_OWNER
                      AND z.NM_TABLE = x.NM_TABLE
                      AND z.CD_COLUMN = x.CD_COLUMN);

   rows_updated := sql%rowcount; 

   dbms_output.Put_line('inserted=>' || to_char(rows_inserted) || ', updated=>' || to_char(rows_updated));

   COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      RAISE;

END;

So my first insert output was:

inserted=>2821, updated=>2821

So I chose a data to be changed and it was updated, I made the following select to choose which data should be updated to bring in the DBMS output again:

SELECT * FROM DW_FUNCESP.D_TEST WHERE NM_TABLE = 'D_TEST';

I commented in a column as shown in the image, to bring in the update:

COMMENT ON COLUMN DW_FUNCESP.D_TEST.LINORIGIN IS 'The origin of the data';

I ran the procedure again, and the output was:

inserted=>0, updated=>2821

The result for that update:

Shouldn't you have brought just 1 updated data in the output, as only 1 updated? And not all the rows?

e.g.: inserted=>0, updated=>1

So my question remains, am I asking it correctly? Is it possible to obtain this result in the same procedure? Is it the update that is incorrect (despite having updated the data)?

Answer 1

Try to add the instruction i := SQL%rowcount; after each DML:

1. after INSERT to have the number of inserted rows
2. after UPDATE to have the number of updated rows

Answer 2

You are not getting the rows inserted and rows updated. SQL%rowcount contains ONLY the number rows from the last select or DML statement. Since you set your variable only after the Update your only get the number of updates. If you want both then you need a separate variable for each.
Hint: There is no need to commit after each DML, actually that is ofter considered a very poor practice. You need to study as bit on transactions. The basic idea being that all operations complete successfully or none of them complete successfully. Look up ATOMIC and Atomicity.

So your revised procedure becomes:

create or replace procedure owner.table_name
as

 -- VARIABLE
 v_qtd_regs  number := 0;  
 v_code      number;
 v_errm      varchar2(500);
 start_time  pls_integer;
 end_time    pls_integer;
 elapse_time number;
 proc_name   varchar2(100);
 rows_inserted integer;
 rows_updated  integer;
begin
   proc_name := 'PRDWBI_CGA_D_COLUMNS';
   start_time := dbms_utility.get_time;
   ds_funcesp.prdsbi_grava_log( 'I', 'DataWarehouse', proc_name, 'Início Carga' );


   insert into owner.table_name
   (column_id, columns_name, column_name2)
   (select 1 as column_id, 'TEST' as column_name, sysdate as column_name2 from dual);

   rows_inserted := sql%rowcount;

   update owner.table_name y
   set (y.columns_name, y.column_name2) = 
               (select 'TEST2' as column_name, sysdate as column_name2 from dual x where x.column_id = y.column_id)
   where exists (select 'TEST2' as column_name, sysdate as column_name2 from dual x where x.column_id = y.column_id);

   rows_updated := sql%rowcount;     


   dbms_output.Put_line('inserted=>' || to_char(rows_inserted) || ', updated=>' || tp_char(rows_updated));

   select count(1) into v_qtd_regs from owner.table_name where lindata >= trunc(sysdate);
   end_time := dbms_utility.get_time;
   elapse_time := ((end_time - start_time)/100);
   v_errm := substr(sqlerrm, 1 , 500);
   ds_funcesp.prdsbi_grava_log('T', 'DataWarehouse', proc_name, v_errm, v_qtd_regs, elapse_time );

   commit;

exception
   when others then
      v_code := sqlcode;
      v_errm := substr(sqlerrm, 1 , 500);
      ds_funcesp.prdsbi_grava_log('E', 'Error', proc_name, v_errm);

end;

Answer 3

I would use ORA_SCN as the other answers suggest if you are interested what rows have been inserted or updated. But you want only to know how many, so I would leave the counting to Oracle (might be timeconsuming for larger number of rows).

Please have a look at the data dictionary view USER_TAB_MODIFICATIONS (or ALL_TAB_MODIFICATIONS if the table is in another schema than the procedure.

CREATE TABLE d (
  id  NUMBER GENERATED ALWAYS AS IDENTITY,
  dt  DATE DEFAULT SYSDATE,
  foo VARCHAR2(128 BYTE)
);

Gathering the table statistics will reset the modifications view:

EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL,'D');

Now after your INSERT, the modifications view will have the number of inserted rows:

INSERT INTO d(foo) SELECT object_name FROM all_objects;
67,141 rows inserted.

SELECT inserts, updates, deletes FROM user_tab_modifications WHERE table_name='D';
INSERTS UPDATES DELETES
  67141       0       0

Likewise, after the UPDATE, the updated rows:

UPDATE d SET foo=lower(foo),dt=SYSDATE WHERE mod(id,10)=0; 
6,714 rows updated.

SELECT inserts, updates, deletes FROM user_tab_modifications WHERE table_name='D';
INSERTS UPDATES DELETES
  67141    6714       0

For clarity, I've used SQL instead of PL/SQL. You might have to grant some special privs to the schema containing the procedure. Add a comment with my name if you run into problems with that.