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

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

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

From: <J.Velikanovs_at_alise.lv>
Date: Fri, 9 Jul 2004 07:29:56 +0300
Message-ID: <OF45ECADF8.45157CC6-ONC2256ECC.001871FD-C2256ECC.00196872@alise.lv> 





The same happens with the spaces.


Oracle truncates (in 9.2.0.5, 10g) unnecessary spaces before parse the 
SQL.



Jurijs



SYS:IKS01> @bu



no rows selected


SYS:IKS01> declare v_n number;
SYS:IKS01> begin
SYS:IKS01> select 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




SYS:IKS01> declare v_n number;
SYS:IKS01> begin
SYS:IKS01> .
SYS:IKS01> declare v_v varchar2(100);
SYS:IKS01> begin
SYS:IKS01> select decode (count(*),0,'1   2   3', 'ops') into v_v from 


Dual where 0=4;


SYS:IKS01> end;


SYS:IKS01> /


PL/SQL procedure successfully completed.



SYS:IKS01> @bu



EXECUTIONS HASH_VALUE



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



SQL_TEXT





         1  775652456


SELECT DECODE (COUNT(*),0,'1   2   3', 'ops') FROM DUAL WHERE 0=4



         1 2534927737


SELECT COUNT(*) FROM DUAL WHERE 0=4




SYS:IKS01> declare v_v varchar2(100);
SYS:IKS01> begin
SYS:IKS01> select decode (count(*),0,'1   2   3','ops') into v_v from Dual 


where 0=4;


SYS:IKS01> end;


SYS:IKS01> /


PL/SQL procedure successfully completed.



SYS:IKS01> @bu



EXECUTIONS HASH_VALUE



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



SQL_TEXT





         1 1348236570


SELECT DECODE (COUNT(*),0,'1   2   3','ops') FROM DUAL WHERE 0=4



         1  775652456


SELECT DECODE (COUNT(*),0,'1   2   3', 'ops') FROM DUAL WHERE 0=4



         1 2534927737


SELECT COUNT(*) FROM DUAL WHERE 0=4







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





         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


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






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

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 - 23:34:26 CDT












Original text of this message













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