[SOLVED] Sending PL/SQL block to db using OCI always return rows

Sending PL/SQL block to db using OCI always return rows_affected=1 (ORACLE DB)

I am writing a client in C which sends a query to Oracle DB, and when I try to sample the number of rows affected by it, I always get value of 1. This is the query:

char * query =    "BEGIN "\
                  "INSERT INTO table_name ( field_1, field_2, id, field_3 ) VALUES ( :1, :2, :3, 1 );"\
                  "EXCEPTION "\
                  "when dup_val_on_index then "\
                  "UPDATE table_name SET field_1 =:4, field_2 =:5, field_3 = 1 where id = :6 and field_4 <= :7;"\
                  "END; ";

I execute it using OCIStmtExecute function of OCI with OCI_COMMIT_ON_SUCCESS, and in order to sample the affected rows I do:

ub4 update_count;
OCIAttrGet(stmthp, OCI_HTYPE_STMT, &update_count, 0, OCI_ATTR_ROW_COUNT, ctx->hp);

For some weird reason update_count is always 1. It's odd because if I send a simple Update or Insert query exatcly the same way, I get the correct result in update_count.

Is there anything that should be done diffrently when executing the query in this form?

Solution

It is unfortunately how things work. Your block could be deleting 20 rows, then updating 10, etc. SQL%ROWCOUNT always returns 1 after successful execution of a PL/SQL block run via execute immediate. You might consider calling SQL%ROWCOUNT inside the dynamic block and then return that value and use it.

You can see this behavior in my trivial LiveSQL script: https://livesql.oracle.com/apex/livesql/s/kdh6dang21mt8eumn4x6wv0ct

begin 
   execute immediate 'begin null; end;'; 
   dbms_output.put_line ('count = ' || sql%rowcount); 
end;   
/


count = 1