1 Reply Latest reply: Aug 29, 2013 1:44 AM by GPU RSS

    Rowcount for multiple insert statements

    SGUN

      Hi all,

       

      Is there a way I can get separate rowcount in multi table insert?

      My sql is written as below:

       

          INSERT ALL

            INTO TAB1 (COL1, COL2....)  VALUES          (VAL1, VAL2....)

            INTO TAB2 (COL1, COL2....)  VALUES          (VAL1, VAL2....)

              (SELECT * FROM TABLE);

       

      rowcount of tab1?

      rowcount of tab2?

       

      Thank you,

      Niroop

        • 1. Re: Rowcount for multiple insert statements
          GPU

          Here is the work around to get the multi-table insert counts.

           

          SQL>

          SQL> select * from v$version;

           

          BANNER                                                                         

          --------------------------------------------------------------------------------

          Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production   

          PL/SQL Release 11.2.0.1.0 - Production                                         

          CORE    11.2.0.1.0    Production                                                     

          TNS for 64-bit Windows: Version 11.2.0.1.0 - Production                        

          NLSRTL Version 11.2.0.1.0 - Production                                         

           

          SQL>

          SQL> DROP PACKAGE utils_pkg;

           

          Package dropped.

           

          SQL> DROP TABLE src_tab;

           

          Table dropped.

           

          SQL> DROP TABLE tab1;

           

          Table dropped.

           

          SQL> DROP TABLE tab2;

           

          Table dropped.

           

          SQL>

          SQL> CREATE OR REPLACE

            2  PACKAGE utils_pkg

            3  IS

            4    v_tab1_rec_count pls_integer :=0;

            5    v_tab2_rec_count pls_integer :=0;

            6    FUNCTION fCounter(

            7        pTab_Name IN VARCHAR2)

            8      RETURN NUMBER;

            9    FUNCTION fGetInsCounts(

          10        pTab_Name IN VARCHAR2)

          11      RETURN NUMBER;

          12    FUNCTION fResetCounts

          13      RETURN NUMBER;

          14  END utils_pkg;

          15  /

           

          Package created.

           

          SQL> CREATE OR REPLACE

            2  PACKAGE body utils_pkg

            3  IS

            4  FUNCTION fcounter(

            5      pTab_Name IN VARCHAR2)

            6    RETURN NUMBER

            7  IS

            8  BEGIN

            9    IF pTab_Name        = 'TAB1' THEN

          10      v_tab1_rec_count := v_tab1_rec_count +1;

          11    ELSE

          12      v_tab2_rec_count := v_tab2_rec_count +1;

          13    END IF;

          14    RETURN 0;

          15  END fcounter;

          16  FUNCTION fGetInsCounts(

          17      pTab_Name IN VARCHAR2)

          18    RETURN NUMBER

          19  IS

          20  BEGIN

          21    IF pTab_Name = 'TAB1' THEN

          22      RETURN v_tab1_rec_count;

          23    ELSE

          24      RETURN v_tab2_rec_count;

          25    END IF;

          26  END fGetInsCounts;

          27  FUNCTION fResetCounts

          28    RETURN NUMBER

          29  IS

          30  BEGIN

          31    v_tab1_rec_count :=0;

          32    v_tab2_rec_count :=0;

          33    RETURN NULL;

          34  END fResetCounts;

          35  END utils_pkg;

          36  /

           

          Package body created.

           

          SQL> CREATE TABLE src_tab AS

            2  SELECT *

            3  FROM

            4    (SELECT 1 col1, 'TEST' col2, 'TAB2_DATA' col3 FROM dual

            5    UNION ALL

            6    SELECT 2, 'TESTING', 'NO_TAB2_DATA' FROM dual

            7    )temp;

           

          Table created.

           

          SQL>

          SQL> CREATE TABLE tab1 AS

            2  SELECT col1, col2 FROM src_tab WHERE 1=2;

           

          Table created.

           

          SQL>

          SQL> CREATE TABLE tab2 AS

            2  SELECT col3 FROM src_tab WHERE 1=2;

           

          Table created.

           

          SQL>

          SQL> SELECT utils_pkg.fGetInsCounts('TAB1') AS tab1_inserts,

            2    utils_pkg.fGetInsCounts('TAB2')      AS tab2_inserts,

            3    utils_pkg.fresetcounts    AS reset_done

            4  FROM dual;

           

          TAB1_INSERTS TAB2_INSERTS RESET_DONE                                           

          ------------ ------------ ----------                                           

                     0            0                                                      

           

          SQL>

          SQL> INSERT ALL

            2  INTO tab1

            3    (

            4      col1,

            5      col2

            6    )

            7    VALUES

            8    (

            9      DECODE(utils_pkg.fcounter('TAB1'),0,col1),

          10      col2

          11    )

          12  INTO tab2

          13    (

          14      col3

          15    )

          16    VALUES

          17    (

          18      DECODE(utils_pkg.fcounter('TAB2'),0,col3)

          19    )

          20  SELECT col1, col2, col3 FROM src_tab;

           

          4 rows created.

           

          SQL>

          SQL>

          SQL> SELECT utils_pkg.fGetInsCounts('TAB1') AS tab1_inserts,

            2    utils_pkg.fGetInsCounts('TAB2')      AS tab2_inserts,

            3    utils_pkg.fresetcounts    AS reset_done

            4  FROM dual;

           

          TAB1_INSERTS TAB2_INSERTS RESET_DONE                                           

          ------------ ------------ ----------                                           

                     2            2                                                      

           

          SQL>

          SQL> rollback;

           

          Rollback complete.

           

          SQL>

          SQL> INSERT  WHEN 1=1 THEN

            2  INTO tab1

            3    (

            4      col1,

            5      col2

            6    )

            7    VALUES

            8    (

            9      DECODE(utils_pkg.fcounter('TAB1'),0,col1),

          10      col2

          11    )

          12    WHEN col3 = 'TAB2_DATA' THEN

          13  INTO tab2

          14    (

          15      col3

          16    )

          17    VALUES

          18    (

          19      DECODE(utils_pkg.fcounter('TAB2'),0,col3)

          20    )

          21  SELECT col1, col2, col3 FROM src_tab;

           

          3 rows created.

           

          SQL>

          SQL>

          SQL> SELECT utils_pkg.fGetInsCounts('TAB1') AS tab1_inserts,

            2    utils_pkg.fGetInsCounts('TAB2')      AS tab2_inserts,

            3    utils_pkg.fresetcounts    AS reset_done

            4  FROM dual;

           

          TAB1_INSERTS TAB2_INSERTS RESET_DONE                                           

          ------------ ------------ ----------                                           

                     2            1                                                      

           

          SQL>

          SQL> rollback;

           

          Rollback complete.

           

           

          Thanks,

          GPU