Test Window SQL reported differently in V$SQL

[Ivan C.]

SELECT statements (I haven't checked other SQL statements) get reported as all upper-case in V$SQL.

When I execute the same statements from SQL Window, Command Window, or from SQL*Plus the statements get reported in V$SQL "as is". No case changes.

Also, when executed from Test Window, comments from SELECT statements are removed (not hints) in V$SQL.

My DB's cursor_sharing parameter is set to EXACT.

Is it possible that Test Window is setting this parameter to FORCE for the session, internally (I didn't find a Preference for it)?

I can't find another explanation for such behavior.

 

[Marco Kalter]

I cannot reproduce this. The v$sql view shows the SQL exactly as entered in the Test Window, including case and optimizer hints.

 

[Ivan C.]

Here are the steps to reproduce it:

1. New Test Window
2.

declare
  l_number number;
begin
  select /* comment_for_v$sql_identification */ count(*)
    into l_number
    from dual
   where 'A' = 'B';
end;

3. Execute Test Window
4. New SQL Window
5.

select * from v$sql
 where lower(sql_text) like '%comment_for_v$sql_identification%';

Query in step 5 only returns itself and the PL/SQL block from step 2. The query from PL/SQL block was not in result set.

6.

select * from v$sql
 where lower(sql_text) like '%where ''a'' = ''b''%';

Query in step 6 returns the PL/SQL block from step 2 and the following:

SELECT COUNT(*) FROM DUAL WHERE 'A' = 'B'

I'm doing this in PL/SQL Developer 9.0.6.1665, running on a Windows 7 Pro, 64-bit, while connected to an Oracle Database 11g Enterprise Edition Release 11.2.0.3.0, or to an Oracle Database 10g Enterprise Edition Release 10.2.0.5.0

We witnessed the same behavior on a different machine (also Windows 7 Pro, 64-bit), running PL/SQL Developer 9.0.5.1648, while connected to an Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Please let me know what other info I can supply you with, if any.

This issue is somewhat concerning (at least for us), since code run from Test Window is generating a SQL with different signature (hash, sql_id, etc.)
than the one we're trying to debug/test.

 

[Marco Kalter]

This is apparently standard Oracle Server behavior. I get the exact same results when executing the PL/SQL Block from SQL*Plus.

 

[Ivan C.]

You're absolutely right, Marco.
This is happening for any code executed from an anonymous PL/SQL block, even if the code being executed is a pre-compiled PL/SQL object (such as function or procedure).

Thank you for looking into this.