Error pasing large CLOB to Oracle

[Ian Skinner]

I can't find anything that says there's a limit on the size of what I can pass
in. Can anyone help me here? Thanks.
<cfprocparam type="In" cfsqltype="CF_SQL_CLOB" value="#XML_Notam_Dist_Rules#"
null="No">

Have you checked the DSN configuration in the administrator? CLOB and
BLOB functionality must be turned on for any given DSN setup. They do
not exist by default.

It's under the 'advanced' control in the DSN configuration form.

[dianemk]

I'll check that, but I don't think it's the case, as my call works fine when passing smaller amounts of data. It's only when I pass large amounts that the error occurs.

[Ian Skinner]

Post by dianemk
I'll check that, but I don't think it's the case, as my call works fine when passing smaller amounts of data. It's only when I pass large amounts that the error occurs.

Well I presume the smaller amount of data is under the "CLOB" threshold
and therefor works correctly.

[SafariTECH]

yes - Ian is correct - if you do not check the boxes to enable large CLOB/BLOB processing then you will be limited to what you can process.

[dianemk]

First. thanks for your help so far.
I checked the CLOB box in the administrator, but still get the same error.
The Administrator help imples that the CLOB box and the associated Long Text
Buffer value are for data retrieved from the database, not sent to it. I don't
seem to see any parameter(s) which control how much data may be sent to the DB.
Can anyone clarify this for me?

Ps - the error I get says "setString can only process strings of less than
32766 characters"

Thanks.

[Ian Skinner]

Post by dianemk
Ps - the error I get says "setString can only process strings of less than
32766 characters"
Thanks.

This may not be a ColdFusion issue. That error rings to me as a DBMS
error with a function that resides inside the stored procedure you are
calling.

[JRock]

Not sure if this will help or not, but I had a similar problem and I was trying
to insert XML into a clob data type and it was too large. There is a size limit.

I broke it up using dbms_lob.append
http://www.psoug.org/reference/dbms_lob.html

[dianemk]

Thanks - I think you're talking about once you get into your PL/SQL code? My
problem seems to be that my object is too large for Cold Fusion to even pass on
to my Oracle Stored Procedure.

I'm still hoping to find some parameter I can tweak (or least a documented
limit) on the amount of data that CF can send to Oracle.

[paross1]

What version of Oracle? What version of ColdFusion (MX 7, 8, standard,
enterprise, etc.)? Something in the back of my mind sounds familiar, like
there was a problem with drivers and CLOB/BLOB sizes, but I can't remember
which versions it affected, and I haven't found anything in knowledgebase yet.
If I find anything that looks like it fits, I will forward it on.

Good luck,
Phil

[dianemk]

Sorry, I should have put the version info in my first post:

We're using Cold Fusion MX 6.1 and Oracle 10.1

Thanks.

[paross1]

I found the thread below, and it looks like the size limit applied to proc
calls but not to queries using cfquery. Sounds like a bug to me. Is there
anyway that you can test this in your situation by using a cfquery instead, and
seeing if it allows you to pass your large XML?


http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=1&catid=3&t
hreadid=1339303

Phil

[paross1]

Standard or enterprise? I believe that there are problems with CLOBs and using
Oracle thin client drivers, but if you are using the enterprise version of
ColdFusion, that shouldn't be a factor, at least that I am aware of.

Phil

[dianemk]

I'll try <cfquery> and see what happens. Unfortunately, that isn't a long-term solution for me since my actual stored proc is complex. I'll report back what happens. Thanks.

[Ian Skinner]

Post by dianemk
I'll try <cfquery> and see what happens. Unfortunately, that isn't a long-term solution for me since my actual stored proc is complex. I'll report back what happens. Thanks.

It is more effort on your part, but it is perfectly allowable to call a
stored procedure with <cfquery...> tags.

Just put the appropriate SQL to do so inside the tag.

[john85]

[q][i]Originally posted by: [b][b]dianemk[/b][/b][/i]
I'll try <cfquery> and see what happens. Unfortunately, that isn't a
long-term solution for me since my actual stored proc is complex. I'll report
back what happens. Thanks.
[/q]

If you use JDBC, then execute a call to store procedure via cfscript works.

Sample using Oracle Thin driver.

<!--- text to be insert into a clob field which is larger than 32,767 chars
long --->
<cfset someLargeText = "blah...."/>

<cfscript>
OracleTypes = CreateObject("java", "oracle.jdbc.driver.OracleTypes");
CLOB = CreateObject("java", "oracle.sql.CLOB");

ds = CreateObject("java", "oracle.jdbc.pool.OracleDataSource");
ds.setDriverType("thin");
ds.setServerName("HOSTNAME");
ds.setPortNumber("PORT");
ds.setDatabaseNAme("DBNAME");
ds.setUser("USERNAME");
ds.setPassword("PASSWORD");

con = ds.getConnection();

tempClob = CLOB.createTemporary(con, false, CLOB.DURATION_CALL);
tempClob.open(CLOB.MODE_READWRITE);

tempClobWriter = tempClob.getCharacterOutputStream();
tempClobWriter.write(someLargeText);
tempClobWriter.flush();
tempClobWriter.close();
tempClob.close();

cs = con.prepareCall("call pkg_clob_test.ins(?,?)");
cs.setClob(1, tempClob);
cs.registerOutParameter(2, OracleTypes.INTEGER);
cs.execute();

id = cs.getInt(2);
cs.close();
con.close();

WriteOuptut("The ID return by the insert operation is: " & id);
</cfscript>

It is a far from perfect but better then cfquery if your store procedure is
complex.

I did email the bug to Adobe's QA team, got a reply from them, then sent them
a test case which should fail when it attempt to send a clob via a stored
procedure. However, they did ran the test and it pass at their end so maybe the
next version (update?) of ColdFusion will fix the cf_sql_clob issue?