Thanks for the question, Ranadhir Reddy.
Asked: May 19, 2017 - 2:54 pm UTC
Last updated: May 19, 2017 - 8:46 pm UTC
Version: 12c
Viewed 1000+ times
You Asked
Hi Tom,
I am using a multi-insert statement as below in a SQL script. There are around 50 insert all statements like this in my script for different tables.
INSERT ALL
WHEN FLAG='Y' THEN
INTO table1() values()
WHEN FLAG='N' THEN
INTO table2() values(a)
WHEN FLAG='N' THEN
INTO table2() values(b)
WHEN FLAG='N' THEN
INTO table2() values(c)
SELECT statement;
How can I get the insert record count for the two tables separately , table1 and table2.
Thanks
and Connor said...
You could embed a package in the insert commands - but honestly, I would not recommend it - a lot of overhead and complexity. You might be better off just querying the tables at the end of the proccess
SQL> CREATE OR REPLACE PACKAGE iall AS
2
3 c_t1 CONSTANT PLS_INTEGER := 1;
4 c_t2 CONSTANT PLS_INTEGER := 2;
5
6 FUNCTION iall_counter (
7 action_in IN PLS_INTEGER DEFAULT c_t1
8 ) RETURN PLS_INTEGER;
9
10 FUNCTION get_iall_t2_count RETURN PLS_INTEGER;
11
12 FUNCTION get_iall_t1_count RETURN PLS_INTEGER;
13
14 PROCEDURE reset_counters;
15
16 END iall;
17 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY iall AS
2
3 g_t2_counter PLS_INTEGER NOT NULL := 0;
4 g_t1_counter PLS_INTEGER NOT NULL := 0;
5
6 FUNCTION iall_counter (
7 action_in IN PLS_INTEGER DEFAULT c_t1
8 ) RETURN PLS_INTEGER IS
9 BEGIN
10 CASE action_in
11 WHEN c_t2
12 THEN g_t2_counter := g_t2_counter + 1;
13 WHEN c_t1
14 THEN g_t1_counter := g_t1_counter + 1;
15 ELSE
16 RAISE PROGRAM_ERROR;
17 END CASE;
18 RETURN 0;
19 END iall_counter;
20
21 FUNCTION get_iall_t2_count
22 RETURN PLS_INTEGER is
23 BEGIN
24 RETURN g_t2_counter;
25 END get_iall_t2_count;
26
27 FUNCTION get_iall_t1_count
28 RETURN PLS_INTEGER IS
29 BEGIN
30 RETURN g_t1_counter;
31 END get_iall_t1_count;
32
33 PROCEDURE reset_counters IS
34 BEGIN
35 g_t2_counter := 0;
36 g_t1_counter := 0;
37 END reset_counters;
38
39 END iall;
40 /
Package body created.
SQL>
SQL> @drop t1
Y1 Y2
----------------------- -------------------------
TABLE cascade constraints purge
1 row selected.
Table dropped.
SQL> @drop t2
Y1 Y2
----------------------- -------------------------
TABLE cascade constraints purge
1 row selected.
Table dropped.
SQL>
SQL> create table t1 (x int );
Table created.
SQL> create table t2 (x int );
Table created.
SQL>
SQL>
SQL> INSERT ALL
2 WHEN FLAG='Y' THEN
3 INTO t1 values( case iall.iall_counter(1) when 0 then 10 end)
4 WHEN FLAG='N' THEN
5 INTO t2 values(case iall.iall_counter(2) when 0 then 20 end)
6 WHEN FLAG='N' THEN
7 INTO t2 values(case iall.iall_counter(2) when 0 then 30 end)
8 SELECT case when rownum < 10 then 'Y' else 'N' end flag
9 from dual connect by level <= 30;
51 rows created.
SQL>
SQL> set serverout on
SQL> exec dbms_output.put_line(iall.get_iall_t1_count);
9
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line(iall.get_iall_t2_count);
42
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select count(*) from t1;
COUNT(*)
----------
9
1 row selected.
SQL> select count(*) from t2;
COUNT(*)
----------
42
1 row selected.
SQL>