Solved

Query a CLOB in Oracle using CF

Posted on 2002-03-21
7
505 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

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…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

752 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