16kb limit w/ CLOB and LIKE
- From: Herring Dave - dherri <Dave.Herring@xxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 1 Feb 2011 19:37:11 +0000
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@xxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
