CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Phate
Phate

Reputation: 6602

Get number of inserted rows by just plain sql

is there a way to get the number of inserted rows inside of the same transaction? I see that PL/SQL command:

SQL%ROWCOUNT

does the job, however I don't want to create a procedure just for that! I tried to simply call

insert into T ...
select SQL%ROWCOUNT;

but it gives me "invalid character". If I remember well mysql actually had a way to obtain this information, does oracle really not provide any means for that?

Upvotes: 1

Views: 3230

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

I don't want to create a procedure just for that

No need to create any procedure, you could simply use an anonymous PL/SQL block.

For example,

SQL> SET serveroutput ON
SQL> DECLARE
  2  var_cnt NUMBER;
  3  BEGIN
  4  var_cnt :=0;
  5  FOR i IN(SELECT empno FROM emp)
  6  LOOP
  7  INSERT INTO emp(empno) VALUES(i.empno);
  8  var_cnt := var_cnt + SQL%ROWCOUNT;
  9  END loop;
 10  DBMS_OUTPUT.PUT_LINE(TO_CHAR(var_cnt)||' rows inserted');
 11  END;
 12  /
14 rows inserted

PL/SQL procedure successfully completed.

SQL>

Update If you cannot use PL/SQL, and just plain SQL, then you cannot use SQL%ROWCOUNT.

The only option that comes to my mind is to have a timestamp column in your table, and query the count based on the timestamp to know the number of rows inserted.

Upvotes: 1

Emipro Technologies Pvt. Ltd.
Emipro Technologies Pvt. Ltd.

Reputation: 14746

Try following,

DBMS_OUTPUT.put_line(TO_CHAR(SQL%ROWCOUNT)||' rows inserted');

Upvotes: 0

Related Questions