From: "Powell, Mark" <mark.powell2@hp.com>
To: "'oracle-l@freelists.org'" <oracle-l@freelists.org>
Date: Wed, 2 Feb 2011 20:45:05 +0000
Subject: RE: 16kb limit w/ CLOB and LIKE
Thread-Topic: 16kb limit w/ CLOB and LIKE
Message-ID: <7C4BF3B32B80CC44AE37D31B172415937456191B82@GVW1337EXC.americas.hpqcorp.net>
References: <BD475CE0B3EE894DA0CAB36CE2F7DEB4019A59@LITIGMBCRP02.Corp.Acxiom.net> <C5533BD628A9524496D63801704AE56D68C9B95DC0@SPOBMEXC14.adprod.directory>
In-Reply-To: <C5533BD628A9524496D63801704AE56D68C9B95DC0@SPOBMEXC14.adprod.directory>
Accept-Language: en-US
Content-Language: en-US
acceptlanguage: en-US
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: mark.powell2@hp.com


"I would rather it didn't work at all then partially work."

I have to agree with that statement.  A reference to a clob column with a LIKE operator should either work or result in a syntax error!  Someone needs to open a bug report with Oracle.
 

-----Original Message-----
From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Taylor, Chris David
Sent: Wednesday, February 02, 2011 1:20 PM
To: 'Dave.Herring@acxiom.com'; 'oracle-l@freelists.org'
Subject: RE: 16kb limit w/ CLOB and LIKE

I hadn't considered that LIKE might work on only parts of a field - I would rather it didn't work at all then partially work.
So, I learned something there.

I think you can use some of the DBMS_LOB functions to get at what you want though.

Dbms_lob.instr(clob_field, 'text to search',1,1) > 0, or dbms_lob.substr maybe.


Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-663-1673
Email: chris.taylor@ingrambarge.com
 
CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.


-----Original Message-----
From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Herring Dave - dherri
Sent: Tuesday, February 01, 2011 1:37 PM
To: oracle-l@freelists.org
Subject: 16kb limit w/ CLOB and LIKE

This may have been raised before and if so, I apologize for the duplication, but I found what appears to be a 16KB limit when filtering CLOBs off a LIKE comparison (10.2.0.2 on Linux RHEL 4.x).  So if you were to filter on a CLOB column with something similar to "... AND sql_text LIKE '%<blah blah>%' ...", you'll get false matches if "blah" doesn't show up until after 16KB of the CLOB.

I ran into this searching DBA_HIST_SQLTEXT, checking for certain statement patterns.  To prove to me what was going on, I picked a statement that DOES NOT include the string DAVE.BOGUS_TABLE and is over 16KB in length (actually around 27KB).

Here's my test:

CREATE TABLE dch_test_tb (stmt_len NUMBER, sql_text CLOB); TRUNCATE TABLE dch_test_tb;

DECLARE
   v_line_length NUMBER := 100;
   v_offset      NUMBER := 1;
   v_temp_clob   CLOB;
   v_insert_clob CLOB;
BEGIN
   SELECT sql_text INTO v_temp_clob
     FROM dba_hist_sqltext
    WHERE sql_id = '<known SQL_ID mentioned above>';

   WHILE v_offset <= 28000
   LOOP
      v_insert_clob := v_insert_clob || TO_CLOB(DBMS_LOB.SUBSTR(v_temp_clob, v_line_length, v_offset));
      INSERT INTO dch_test_tb VALUES (v_line_length, v_insert_clob);
      COMMIT;
      v_offset := v_offset + 100;
   END LOOP;
   COMMIT;
END;
/

SELECT LENGTH(sql_text)
  FROM dch_test_tb
 WHERE UPPER(sql_text) LIKE 'SELECT%DAVE.BOGUS_TABLE%'
 ORDER BY 1;

-------------------Output--------------------------

16400
16500
16600
16700
	...
27203

This isn't a huge issue now for me, as REGEXP_LIKE works but is slower.  I just thought others should know about this in case they didn't already.

DAVID HERRING
DBA
Acxiom Corporation
EML   dave.herring@acxiom.com
TEL    630.944.4762
MBL   630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM  

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l