RE: 16kb limit w/ CLOB and LIKE
Date: Wed, 2 Feb 2011 12:20:18 -0600
Message-ID: <C5533BD628A9524496D63801704AE56D68C9B95DC0_at_SPOBMEXC14.adprod.directory>
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_at_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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Herring Dave - dherri
Sent: Tuesday, February 01, 2011 1:37 PM
To: oracle-l_at_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_at_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
Received on Wed Feb 02 2011 - 12:20:18 CST