CODEWITHSUNDEEP
oracle-databaseplsqlodp.net
TTCG
TTCG

Reputation: 9113

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.

Upvotes: 1

Views: 3763

Answers (3)

Kevin Burton
Kevin Burton

Reputation: 11936

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

Upvotes: 1

Codo
Codo

Reputation: 78865

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.

Upvotes: 2

vc 74
vc 74

Reputation: 38179

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.

Upvotes: 1

Related Questions