ck969
asked on
Query a CLOB in Oracle using CF
I have an Oracle table using a CLOB field in storing big chunk of data. When I tried to code CFQUERY like the following, it does not work reason being I have this field as CLOB. Anyone has overcome this problem before ?
<cfquery datasource="icl">
SELECT * FROM library
WHERE texts LIKE '%#form.freetext#%'
</cfquery>
texts is a CLOB field.
<cfquery datasource="icl">
SELECT * FROM library
WHERE texts LIKE '%#form.freetext#%'
</cfquery>
texts is a CLOB field.
If u have any clob feilds, always fetch in separate query,
in query you are selecting * from library, then all the fields oracle assume as clob. so record fetching always not correct.
so always use separate query for clob feilds. if u have two clob fields in ur table u want both to select. don't select both in same query rather use separate query.
u can fetch clob fields within 32000 character if that clob fields has more than 32000 character then coldfusion fails to displays. so u need service pack that.
in query you are selecting * from library, then all the fields oracle assume as clob. so record fetching always not correct.
so always use separate query for clob feilds. if u have two clob fields in ur table u want both to select. don't select both in same query rather use separate query.
u can fetch clob fields within 32000 character if that clob fields has more than 32000 character then coldfusion fails to displays. so u need service pack that.
To retrieve content of the CLOB, you have to put the resultset of the select in a file and then read the file by specifying the type. The best way is to store file type/extension in the DB is your CLOB Field store different kind of files.
Exemple :
If your CLOB contains a word document
-------------------------- ---------- -
<CFQUERY name="A" datasource="tst8">
SELECT CV
FROM EXPERTS
WHERE CODE_EXPERT = 3
</CFQUERY>
<cffile action="WRITE" file="#GetDirectoryFromPat h(GetCurre ntTemplate Path())#/t mp.doc" output="#toBinary(A.CV)#" addnewline="No">
<cflocation url="result.cfm">
<!--- result.cfm --->
<HTML>
<BODY>
<cfcontent type="application/msword" file="#GetDirectoryFromPat h(GetCurre ntTemplate Path())#/t mp.doc" deletefile="No">
</BODY>
</HTML>
If your CLOB contains a text document
-------------------------- ---------- -
<CFQUERY name="A" datasource="tst8">
SELECT CV
FROM EXPERTS
WHERE CODE_EXPERT = 3
</CFQUERY>
<cffile action="WRITE" file="#GetDirectoryFromPat h(GetCurre ntTemplate Path())#/t mp.txt" output="#toBinary(A.CV)#" addnewline="No">
<cflocation url="result.cfm">
<!--- result.cfm --->
<HTML>
<BODY>
<cfcontent type="text/plain" file="#GetDirectoryFromPat h(GetCurre ntTemplate Path())#/t mp.txt" deletefile="No">
</BODY>
</HTML>
Hope it will help,
Cyril
Exemple :
If your CLOB contains a word document
--------------------------
<CFQUERY name="A" datasource="tst8">
SELECT CV
FROM EXPERTS
WHERE CODE_EXPERT = 3
</CFQUERY>
<cffile action="WRITE" file="#GetDirectoryFromPat
<cflocation url="result.cfm">
<!--- result.cfm --->
<HTML>
<BODY>
<cfcontent type="application/msword" file="#GetDirectoryFromPat
</BODY>
</HTML>
If your CLOB contains a text document
--------------------------
<CFQUERY name="A" datasource="tst8">
SELECT CV
FROM EXPERTS
WHERE CODE_EXPERT = 3
</CFQUERY>
<cffile action="WRITE" file="#GetDirectoryFromPat
<cflocation url="result.cfm">
<!--- result.cfm --->
<HTML>
<BODY>
<cfcontent type="text/plain" file="#GetDirectoryFromPat
</BODY>
</HTML>
Hope it will help,
Cyril
ASKER
Thanks for the suggestion so far, but that seems like not meeting what I required. My CLOB field is storing plain texts (more than 4000 characters, that's why I don't use varchar2 field).
My objective from the query is to get a list of the records which match the "form.freetext" entered by user on the page, that's why I use 'LIKE' in the query.
My objective from the query is to get a list of the records which match the "form.freetext" entered by user on the page, that's why I use 'LIKE' in the query.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
it is only workaround solution it will match only upto 4000 character of the clob fields as beyond 4000 i.e. 4001 it will gives error. as function dbms_lob.substr will fails to fetch more than 4000 characters.
<cfquery datasource="icl">
SELECT * FROM library
WHERE dbms_lob.substr(texts,4000 ,1) LIKE '%#form.freetext#%'
</cfquery>
<cfquery datasource="icl">
SELECT * FROM library
WHERE dbms_lob.substr(texts,4000
</cfquery>
Force Accepted
SpideyMod
Community Support Moderator @Experts Exchange
SpideyMod
Community Support Moderator @Experts Exchange
(1) turn "CLOB_column" data into readable character strings using ToBase64;
(2) save "CLOB_column" data to a file, then manipulate the file.