execute immediate

>> How do i know what sql is being executed by EXECUTE IMMEDIATE

     you don't know when you write the code normally because execute immediate usually executes dynamic query so you have to output it in the runtime. you have a lot of choices to do it in the runtime:
     1. DBMS_OUTPUT.PUT_LINE
     2. write to a file using UTIL_FILE
     3. considering DBMS_OUTPUT.PUT_LINE has a limitation of 255 chars per line, you might insert into a temp table and you can query this table after your pl/sql finishes.

     Overall, agree with G and m :)

acton

Use SQL%ROWCOUNT to know number of rows affected.

You can check after EXECUTE IMMEDIATE that

if ( SQL%ROWCOUNT < 0 ) exit (-1);

Use of %ROWCOUNT
---------------
%ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. %ROWCOUNT yields 0 if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows.

If a SELECT INTO statement returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS and %ROWCOUNT yields 1, not the actual number of rows that satisfy the query.

%ROWCOUNT is applicable to most recently used statement. So in yur case it has to be used just after EXECUTE IMMEDIATE.

If it doesn't solve your problem, please get back.

SQL%ROWCOUNT will not coun sql inside "execute immediate".

it doesn't work.

actionwang:
>SQL%ROWCOUNT will not coun sql inside "execute immediate".
i have to disagree on that, because I use that all the time!

SQL%ROWCOUNT DOES work for a query run using EXECUTE IMMEDIATE in pl/sql.

>> SQL%ROWCOUNT will not coun sql inside "execute immediate".
>>it doesn't work.

     Yes. angelIII. SQL%ROWCOUNT WILL count rows being affected even in "execute immediate". You are right about it.

     The problem I tried to say is that if you have a wrong sql inside "execute immediate", you doesn't get any info only a SQL%ROWCOUNT of value 0 here, try this:


set serveroutput on

begin
   execute immediate 'no way it works!';
   dbms_output.put_line(SQL%ROWCOUNT);
end;
/

you get "0" value.  there is no way where it will return some values <0 as noted as biswaranjan_rath:

>>if ( SQL%ROWCOUNT < 0 ) exit (-1);

sorry for confusion. I should not use "count" and I should've used "show" or others...

>> How do i know what sql is being executed by EXECUTE IMMEDIATE
I think we are trying to resolve the  problem where SQL%ROWCOUNT can do about it.

then you have only option to print the string, you have in EXECUTE IMMEDIATE. But make sure it should not increase 256 chars. In that case take the length and do substr to print in a loop.

either you can insert the string into a temp table and check it. after verification remove the statement.