PL/SQL removes comments from SQL statements

From: ca111026 <ca111026_at_gmail.com>
Date: Tue, 31 Aug 2010 01:05:56 -0700 (PDT)
Message-ID: <b7c6958a-aa09-48c0-9e8d-24a0c85ed784_at_v35g2000prn.googlegroups.com>

I've notices that PL/SQL removes comments from SQL statements

For example if I execute folloing SQL

select /* MyComment */ count(*) from big_table;

and while it is running try to find it in V$SQL then the comment is visible

select hash_value, sql_text from V$SQL where sql_text like '%MyComment %'

However if I create stored procedure

create or replace procedure commenttest as l_count number;
begin
select /* MyComment */ count(*) into l_count from big_table; end;
/

then execute it

exec commenttest;

and check V$SQL from another session then the comment isn't visible, I get

select count(*) from big_table;

It is still visible in DBA_SOURCE, but this isn't what I need.

It seems that there are only two ways to keep comments in SQL statements executed from PL/SQL:
- Trick PL/SQL into thinking that the comment is a hint by adding "+".
Howedver this is kind of dirty method as it could confuse the Optimizer.
- Use Dynamic SQL

Anything else? Received on Tue Aug 31 2010 - 03:05:56 CDT