java.sql.SQLException CLOB

[romain_soco]

I use a <CFSTOREDPROC with an Oracle 9i(R1) database and CFM MX7 standart
edition (So i use a jdbc driver, oracle driver is only available in a
enterprise version)

The procedure return a clob.
When the clob has a small len, the resultset is ok.
When the clob is more big, I have a cfm error : java.sql.SQLException (see as
under).
I dont' have this bug with an enterprise version with jdbc driver using.
I 've download the Updated DataDirect JDBC drivers (version 3.5) and the
option Enable long text retrieval (CLOB) is enabled in the cfide administrator


We can find the call as under :

<CFSTOREDPROC procedure="PS_FACADE" datasource="#cnxCimsaDB#">
<cfprocparam type = "inout" CFSQLType = "CF_SQL_CHAR" value="#strData#"
VARIABLE="xmlResult">
</CFSTOREDPROC>


java.sql.SQLException: ORA-06502: PL/SQL : erreur num?rique ou erreur sur une
valeur ORA-06512: ? ligne 1 at
oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134) at
oracle.jdbc.ttc7.TTIo

[jonwrob]

CF does not support connecting to Oracle with anything other than the
Enterprise version. It has to do with the way the Oracle thin client driver
defines CLOB columns.

You will get no help from technical support either. All the more reason to
use something other that CF if you ask me.

JR

[romain_soco]

You 're right. I 've read the article a guide to Using Stored Procedures with
ColdFusion.
[url]http://www.macromedia.com/devnet/coldfusion/articles/beg_storedproc_06.html[/url]

I have the same problem in cfm standart 6 and I use the cfquery and oracle
function to return the result set in a xml string. I'm going too do the same.
Thanks

[sdittbrenner]

<cfprocparam type = "inout" CFSQLType = "CF_SQL_CHAR" value="#strData#"
????????????????????

you want to gather a CLOB? up to 4000 Characters i would expect that there is
no problem - more then 4000 in a char type? Never!!
Try CFSQLType = "CF_SQL_CLOB"

[sdupre]

I think sdittbrenner is right about the 4000 chars and CF_SQL_CLOB.

>> CF does not support connecting to Oracle with anything other than the

Enterprise version<<
This statement is false. Thin, ODBC, alternative JDBC drivers like Inet are
all acceptable with Standard version.

Stephen Dupre
Macromedia QA

[jonwrob]

>> CF does not support connecting to Oracle with anything other than the
Enterprise version<<

This statement is not false. CF does not support drivers other than the one
that comes with enterprise. Try using one and reporting a problem to tech
support. You'll find out just how much they don't support them.

CF cannot use the thin client driver to return CLOB's greater than 64K. Talk
to tech support about this and they will tell you they do not support 3rd party
drivers. I have the email to prove it.

JR

[romain_soco]

In the documentation, cfm standart can connect to oracle throught a jdbc
driver. It less efficient than the entreprise cfm driver.
I use standart cfm with jdbc driver and an oracle function return a string
more than 4000 char with the cfquery
<cfquery name="query" datasource="#cnxCimsaDB#">
select FN_ENTREE_XML('#strData#') return_value from dual
</cfquery>
I don't read a contraindication about it. Right ?

[sdittbrenner]

to jonwrob

>>CF does not support drivers other than the one that comes with enterprise.

Try using one and reporting a problem to tech support. You'll find out just how
much they don't support them.

CF cannot use the thin client driver to return CLOB's greater than 64K. <<

CF supports the Oracle JDBC driver from 10g R2 and R1 and also CLOB greater
then 64K! We have used it to store base64-converted PDF (like Oracle
references with 12MB) and other files up to 85MB.
We have only a little problem to query a Oracle "date" column. CF doesn't
return the time component (Hello Macromedia support ;-))

[jonwrob]

I sense some difference of opinion on the meaning of the word "support." CF
Professional may work with the JDBC driver, but it is not supported. Talk to
Macromedia support about your date issue. If you tell them you're using CF
Professional, rather than CF Enterprise, and using an Oracle provided driver,
they will not help you. That is what not supported means.

What I said about the thin client JDBC driver was true a few months ago when
last I tried it. Perhaps there is a new version available from Oracle. I'll
give it a try.

JR