0

Is there a way to count how many rows inserted into table by 

Create table tbl1 as select * from tbl2;

statement which performed from PL\SQL function (execute immediate)?

When I'm using SQL%ROWCOUNT the result is 1.

Thanks.

user2671057
  • 1,411
  • 2
  • 25
  • 43
  • Possible duplicate of [How to get number of rows inserted by a transaction](https://stackoverflow.com/questions/2272733/how-to-get-number-of-rows-inserted-by-a-transaction) – Ulug Toprak Jul 18 '17 at 08:37
  • I'm searching for a solution in Oracle not Sql server – user2671057 Jul 18 '17 at 08:43
  • `SQL%ROWCOUNT` will give the number of rows afftected by DML statement, When you executed `Execute immediate `, there was only 1 operation happened and inthat table is created so rowcount you are getting as 1. In the next step you can count it. in a variable and check it, – XING Jul 18 '17 at 08:46
  • Yes, I guess that that why I'm getting 1. What you mean "In the next step you can count it. in a variable and check it" ? select count(*) from tbl1? – user2671057 Jul 18 '17 at 08:51
  • @user2671057 You can see my answer what i meant to say. – XING Jul 18 '17 at 09:16

2 Answers2

1

You can try this. As mentioned in my comment you need to do it as followed. Remember that you need again to use Execute immediate else you get an issue that tab1 is undefined. 

DECLARE
   vsql   VARCHAR2 (200);

   cnt    NUMBER;
BEGIN
   vsql := 'create table tbl1 as select * from employee';

   EXECUTE IMMEDIATE vsql;

   vsql := 'select count(1) from tbl1';

   EXECUTE IMMEDIATE vsql INTO cnt;

   DBMS_OUTPUT.put_line (cnt);
END;
XING
  • 9,608
  • 4
  • 22
  • 38
1

You can do one thing. You can first create an empty table tbl1 from tbl2. Then insert data using SELECT and then use- SQL%ROWCOUNT. 

CREATE TABLE tbl1 AS SELECT * FROM tbl2 WHERE 1=2;
INSERT INTO tbl1 SELECT * FROM tbl2;
DBMS_OUTPUT.PUT_LINE ('No. of rows inserted in TBL2 from TBL1 = ' || SQL%ROWCOUNT);
Keyur Panchal
  • 1,382
  • 1
  • 10
  • 15