Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> 9.2.0.5 SQL-s converted to UPPER case & comments are truncated.

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(*)


         0

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US