Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2350
  • Last Modified:

execute immediate

How do i know what sql is being executed by EXECUTE IMMEDIATE
2 Solutions
Maybe You can DBMS_OUTPUT.PUT_LINE before EXECUTE IMMEDIATE or put it into some table?
Why do you want to know what SQL is being executed?

As GGuzdziol has said I've always found the easiest way to put the SQL into a table.  If you are trying to understand wha will be executed, you can store the SQL as a VARCHAR2 variable and  then execute immediate the variable and insert the variable into a table at the same time.

If you have a specific reason for wanting to do this we might be able to help?
>> 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:
     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 :)

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Use SQL%ROWCOUNT to know number of rows affected.

You can check after EXECUTE IMMEDIATE that

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

%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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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

   execute immediate 'no way it works!';

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.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now