Solved

Query a CLOB in Oracle using CF

Posted on 2002-03-21
7
503 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

815 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

12 Experts available now in Live!

Get 1:1 Help Now