Re: 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:18:44 +0300
Message-ID: <OFD5E93EC3.30DAACC3-ONC2256ECB.00599027-C2256ECB.005A4CEB@alise.lv>

At the same time.
SYS:jozh> @ver

BANNER

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for 32-bit Windows: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production

SYS:jozh> @bu

no rows selected

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

into v_n from Dual where 0=4;
SYS:jozh> end;
SYS:jozh> /

PL/SQL procedure successfully completed.

SYS:jozh> @bu

EXECUTIONS HASH_VALUE
---------- ----------

SQL_TEXT

1 3783200825
SELECT /* Please don't take this comment away ;) */ count(*) from Dual where 0=4

1 row selected.

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

*/ count(*) into v_n from Dual where 0=4; SYS:jozh> end;
SYS:jozh> /

PL/SQL procedure successfully completed.

SYS:jozh> @bu

EXECUTIONS HASH_VALUE
---------- ----------

SQL_TEXT

1 3783200825
SELECT /* Please don't take this comment away ;) */ count(*) from Dual where 0=4

1 1978626701
SELECT /* YOU CAN WRITE SQL IN ANY CASE YOU'LL GET THE SAME ;) */ count(*) from Dual where 0=4

2 rows selected.

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

into v_n from Dual where 0=4;
SYS:jozh> end;
SYS:jozh> /

PL/SQL procedure successfully completed.

SYS:jozh> @bu

EXECUTIONS HASH_VALUE
---------- ----------

SQL_TEXT

1 3783200825
SELECT /* Please don't take this comment away ;) */ count(*) from Dual where 0=4

1 1978626701
SELECT /* YOU CAN WRITE SQL IN ANY CASE YOU'LL GET THE SAME ;) */ count(*) from Dual where 0=4

1 4008834265
SELECT /*+ Please don't take this comment away ;) */ count(*) from Dual where 0=4

3 rows selected.

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

1 row selected.

SYS:jozh> @bu

EXECUTIONS HASH_VALUE
---------- ----------

SQL_TEXT

1 3783200825
SELECT /* Please don't take this comment away ;) */ count(*) from Dual where 0=4

1 1182738150
sElEcT /* For NON-REC SQL case is still IMPORTANT */ count(*) FROM DUAL WHERE 0=4 1 1978626701
SELECT /* YOU CAN WRITE SQL IN ANY CASE YOU'LL GET THE SAME ;) */ count(*) from Dual where 0=4

1 4008834265
SELECT /*+ Please don't take this comment away ;) */ count(*) from Dual where 0=4

4 rows selected.

SYS:jozh>

Jurijs
9268222

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

J.Velikanovs_at_alise.lv
Sent by: oracle-l-bounce_at_freelists.org
08.07.2004 19:17
Please respond to oracle-l

        To:     oracle-l_at_freelists.org
        cc: 
        Subject:        9.2.0.5 SQL-s converted to UPPER case & comments

are truncated.

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


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






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

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:58 CDT