RE: 16kb limit w/ CLOB and LIKE
- From: "Powell, Mark" <mark.powell2@xxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 2 Feb 2011 20:45:05 +0000
"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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Taylor, Chris David
Sent: Wednesday, February 02, 2011 1:20 PM
To: 'Dave.Herring@xxxxxxxxxx'; 'oracle-l@xxxxxxxxxxxxx'
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@xxxxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Herring Dave - dherri
Sent: Tuesday, February 01, 2011 1:37 PM
To: oracle-l@xxxxxxxxxxxxx
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@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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
