-
I created a table in which one of the fields was of CLOB datatype.
When I run select statements from SQL prompt the table is accessible for me. However, when the developer tries in her application to access the table it comes up with an error . The same statements which run perfectly do not run in the Oracle ODBC Test environment.
I tried changing the CLOB field to a varchar2 and the whole thing works.
What is to be done for using the table with CLOB datatype?Putting that field in another tablespace- will that help?
thanks
manjunath
-
I guess u can't acees the CLOB object directy in the front end.As the data is going to be large the front end(ASP or Developer2000 or VB) should read the data in chunks ie in 1024 bytes or so.Also same the case for appending data to the table.Hope i answered say 10%
Set OraDynasetForLong = OraDatabase.DbCreateDynaset("select
product_id,product_html,publish_status_id from PRODUCT where Product_id = " &
cint(lnProdId), &H2&)
OraDynasetForLong.DbEdit
OraDynasetForLong.Fields("product_html").Value = ""
lnTotalSize = Len(trim(Request.Form("prodhtml")))
lnChunkSize =1024
LongData = cStr(Request.Form("prodhtml"))
'Set number of chunks.
lnNumChunks = lnTotalSize \ lnChunkSize
if lnNumChunks < lnChunkSize then
OraDynasetForLong.Fields("product_html").DbAppendChunk(LongData)
else
'Set number of remaining bytes.
lnRemChunkSize = lnTotalSize Mod lnChunkSize
For I = 1 To lnNumChunks
if I = lnNumChunks then
lnChunkSize = lnRemChunkSize
end if
if Request.Form("txtLongDesc") <> "" then
lsCurChunk = mid(Request.Form("prodhtml"),i*lnChunkSize,lnChunkSize)
OraDynasetForLong.Fields("product_html").DbAppendChunk (trim(lsCurChunk))
end if
next
end if
---selecting fields---
if Request.QueryString ("MODE") = 1 then ' to modify
lnProdId = Request.QueryString("ProductID")
Set OraDynasetForLong = OraDatabase.DbCreateDynaset("select
product_id,product_name,product_brief_description,product_html,product_html,publish_status_id,produc t_type_id
from PRODUCT where Product_id = " & cint(lnProdId), &H2&)
lsProdDesc=OraDynasetForLong.Fields("product_brief_description")
lsPrdName = OraDynasetForLong.Fields("PRODUCT_NAME")
lsPrdId = OraDynasetForLong.Fields("product_type_id")
ChunkSize = 256
liCounter = 0
lsLongDesc=""
Do
CurChunk = OraDynasetForLong.Fields("Product_html").DbGetChunk(liCounter *
ChunkSize, ChunkSize)
CurSize = Len(CurChunk) 'Get the length of the current chunk.
lsLongDesc = lsLongDesc & (Cstr(CurChunk)) 'Write chunk to Page.
liCounter = liCounter + 1
Loop Until CurSize < ChunkSize
