Get the Affected Row from PL-SQL

I am using ODP.Net and run the PL/SQL Command to merge the table in the Oracle 10G database. My command is as follow:

MERGE INTO TestTable t 
USING (SELECT 2911 AS AR_ID FROM dual) s 
ON (t.AR_ID = s.AR_ID) 
WHEN MATCHED THEN 
  UPDATE SET t.AR_VIUAL_IMPAIRMENT = 1                                               
WHEN NOT MATCHED THEN 
  INSERT (AR_S_REF) 
  VALUES ('abcdef'); 

SELECT sql%ROWCOUNT FROM dual; 

The Merge command runs successfully and update/insert as I want. The problem is I want to know how many records are updated.

When I run the above statement, "ORA-00911: invalid character error".

Please advise me how I could get the affected rows开发者_运维百科 back. Thanks million.

You're mixing up a few things: a MERGE statement is a plain SQL command while PL/SQL code is always delimited by BEGIN/END (and optional DECLARE). Furthermore, SQL%ROWCOUNT is a PL/SQL variable that cannot occur outside of PL/SQL.

And I don't quite understand whether you ran the MERGE and the SELECT statement with two separate or a common ODP.NET call.

Anyway, the solution is straightfowrad with ODP.NET: Execute the MERGE command with OracleCommand.ExecuteNonQuery(). This method returns the number of affected rows.

One thing you could do is put your code in a PLSQL function that returns %ROWCOUNT.

Then call this function from ODP.net setting the command type to stored procedure and using the ExecuteLiteral method which is going to return you the row count as an object instance you can cast as an int.

It is not possible to return just the "updated" row count.

(as already mentioned the row count is the number of affected (inserted and updated) rows)

there is a good discusion on ask tom: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:122741200346595110