This discussion is archived
1 Reply Latest reply: Aug 28, 2013 11:44 PM by GPU RSS

Rowcount for multiple insert statements

SGUN Newbie
Currently Being Moderated

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 Explorer
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points