Solved

Query a CLOB in Oracle using CF

Posted on 2002-03-21
7
500 Views
Last Modified: 2013-12-24
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.
0
Comment
Question by:ck969
7 Comments
 
LVL 19

Expert Comment

by:cheekycj
ID: 6886560
from previous messages two approaches:
(1) turn "CLOB_column" data into readable character strings using ToBase64;
(2) save "CLOB_column" data to a file, then manipulate the file.
0
 
LVL 6

Expert Comment

by:dash420
ID: 6887913
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.




0
 
LVL 2

Expert Comment

by:Cyril_H
ID: 6888026
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="#GetDirectoryFromPath(GetCurrentTemplatePath())#/tmp.doc" output="#toBinary(A.CV)#" addnewline="No">

<cflocation url="result.cfm">

<!--- result.cfm --->
<HTML>

<BODY>
<cfcontent type="application/msword" file="#GetDirectoryFromPath(GetCurrentTemplatePath())#/tmp.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="#GetDirectoryFromPath(GetCurrentTemplatePath())#/tmp.txt" output="#toBinary(A.CV)#" addnewline="No">

<cflocation url="result.cfm">

<!--- result.cfm --->
<HTML>

<BODY>
<cfcontent type="text/plain" file="#GetDirectoryFromPath(GetCurrentTemplatePath())#/tmp.txt" deletefile="No">
</BODY>

</HTML>



Hope it will help,
Cyril
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:ck969
ID: 6888078
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.
0
 
LVL 11

Accepted Solution

by:
jimmy282 earned 50 total points
ID: 6891966
You could create a FUll text catalog of the table and search it using CONTAINS instead of LIKE

Here is an example

This example finds all products with a price of $15.00 that contain the word "bottles."

SELECT ProductName
FROM Products
WHERE UnitPrice = 15.00
AND CONTAINS(QuantityPerUnit, 'bottles')

jimmy

0
 
LVL 6

Expert Comment

by:dash420
ID: 7250921
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>

0
 

Expert Comment

by:SpideyMod
ID: 8096559
Force Accepted

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now