9.2.0.5 SQL-s converted to UPPER case & comments are truncated.

From: <J.Velikanovs_at_alise.lv>
Date: Thu, 8 Jul 2004 19:17:28 +0300
Message-ID: <OF82780D86.80150BB1-ONC2256ECB.00594835-C2256ECB.005A2F01@alise.lv>

I would like to share my observation.
May be only I was unaware ;)
Is it known feature?
Take a look:

SYS:IKS01> @ver

BANNER

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for Linux: Version 9.2.0.5.0 - Production NLSRTL Version 9.2.0.5.0 - Production

SYS:IKS01> select executions, hash_value, sql_text from v$sql
SYS:IKS01> where upper(sql_text) like upper('%dual where 0=4%')
SYS:IKS01> and upper(sql_text) not like upper('%declare%')
SYS:IKS01> and upper(sql_text) not like upper('%from v$sql%')
SYS:IKS01> /

no rows selected

SYS:IKS01> save bu rep
Wrote file bu.sql

SYS:IKS01> declare v_n number;
SYS:IKS01> begin
SYS:IKS01> select /* Please don't take this comment away ;) */ count(*)

into v_n from Dual where 0=4;
SYS:IKS01> end;
SYS:IKS01> / PL/SQL procedure successfully completed.

SYS:IKS01> @bu

EXECUTIONS HASH_VALUE SQL_TEXT
---------- ----------

1 2534927737 SELECT COUNT(*) FROM DUAL WHERE 0=4 1 row selected.

SYS:IKS01> declare v_n number;
SYS:IKS01> begin
SYS:IKS01> SeLeCt /* YOU CAN WRITE SQL IN ANY CASE YOU'LL GET THE SAME ;)

*/ count(*) into v_n from Dual where 0=4; SYS:IKS01> end;
SYS:IKS01> / PL/SQL procedure successfully completed.

SYS:IKS01> @bu

EXECUTIONS HASH_VALUE SQL_TEXT
---------- ----------

2 2534927737 SELECT COUNT(*) FROM DUAL WHERE 0=4 1 row selected.

SYS:IKS01> declare v_n number;
SYS:IKS01> begin
SYS:IKS01> select /*+ Please don't take this comment away ;) */ count(*)

into v_n from Dual where 0=4;
SYS:IKS01> end;
SYS:IKS01> / PL/SQL procedure successfully completed.

SYS:IKS01> @bu

EXECUTIONS HASH_VALUE SQL_TEXT
---------- ----------

1 1805064747 SELECT /*+ Please don't take this comment away ;) */ COUNT(*) FROM DUAL WHERE 0=4
2 2534927737 SELECT COUNT(*) FROM DUAL WHERE 0=4 2 rows selected.

SYS:IKS01> sElEcT /* For NON-REC SQL case is still IMPORTANT */ count(*) FROM DUAL WHERE 0=4; COUNT(*)

SYS:IKS01> @bu

EXECUTIONS HASH_VALUE SQL_TEXT
---------- ----------

1 1805064747 SELECT /*+ Please don't take this comment away ;) */ COUNT(*) FROM DUAL WHERE 0=4

         2 2534927737 SELECT COUNT(*) FROM DUAL WHERE 0=4
         1 1182738150 sElEcT /* For NON-REC SQL case is still IMPORTANT */

count(*) FROM DUAL WHERE 0=4

3 rows selected.

Jurijs
9268222

http://otn.oracle.com/ocm/jvelikanovs.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Thu Jul 08 2004 - 11:22:00 CDT