Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query a CLOB in Oracle using CF

Posted on 2002-03-21
7
Medium Priority
?
510 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
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 

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 200 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

886 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