Solved

Query a CLOB in Oracle using CF

Posted on 2002-03-21
7
501 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
Give your grad a cloud of their own!

With up to 8TB of storage, give your favorite graduate their own personal cloud to centralize all their photos, videos and music in one safe place. They can save, sync and share all their stuff, and automatic photo backup helps free up space on their smartphone and tablet.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

864 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