To return the no of rows affected in oracle for complex queries

I have so many complex join queries, for which I want to return the nu开发者_运维技巧mber of rows affected. Say for select 100, insert 50, etc..

I dont want to change the query for this operation. I tried SQL%ROWCOUNT. But it doesnt work. Please help me in this.

To use sql%rowcount, you'll need to use it directly after the select/insert/update/delete. For example:

declare
  v_date date;
  v_cnt pls_integer := 0;
  v_cnt2 pls_integer := 0;
begin

  select sysdate into v_date from dual;

  v_cnt := sql%rowcount;
  dbms_output.put_line('rowcount: ' || v_cnt);

  commit;

  v_cnt2 := sql%rowcount;
  dbms_output.put_line('rowcount2: ' || v_cnt2);

end;

Output:

rowcount: 1
rowcount2: 0

Hope that helps.

If I understood correctly, you want to examine the inner workings of your query (cardinality of joined tables etc.) - is that right? If yes, you need to look at the execution plan Oracle generates for your query. Try a command like this:

EXPLAIN PLAN FOR SELECT * FROM ...; // <-- insert your query here
SELECT * FROM TABLE(dbms_xplan.display);

(See SQL*Plus FAQ for other options and more information.)