Re: PL/SQL removes comments from SQL statements

From: joel garry <joel-garry_at_home.com>
Date: Tue, 31 Aug 2010 10:34:47 -0700 (PDT)
Message-ID: <aea8b63e-ac72-4b90-b8fa-366b2f41e319_at_h25g2000pri.googlegroups.com>

On Aug 31, 1:05 am, ca111026 <ca111..._at_gmail.com> wrote:
> 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.

Any hint that is not exactly correct will be ignored, there will only be confusion if your comment happens to be an exactly correct hint.

> - Use Dynamic SQL
>
> Anything else?

How exactly PL handles comments and white space does vary by version. There are also different compilation methods.

jg

--
_at_home.com is bogus.
http://www.signonsandiego.com/news/2010/aug/30/intel-moves-to-compete-with-qualcomm/

Received on Tue Aug 31 2010 - 12:34:47 CDT