Error pasing large CLOB to Oracle

Report · Mar 04, 2008

I am using <cfprocparam> within <cfstoredproc> to pass a large XML string to an Oracle stored procedure.

The procedure gets called and executes properly when a small amount of XML is passed, but when the string is larger, I get the following error:

Error Executing Database Query.
setString can only process strings of less than 32766 characters. The offending line is below:

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.

Report · Mar 04, 2008

dianemk wrote:
> 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.

Report · Mar 04, 2008

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.

Report · Mar 04, 2008

dianemk wrote:
> 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.

Report · Mar 04, 2008

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.

Report · Mar 05, 2008

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.

Report · Mar 05, 2008

dianemk wrote:
> 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.

Report · Mar 04, 2008

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

Report · Mar 05, 2008

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.

Report · Mar 05, 2008

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

Report · Mar 05, 2008

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.

Report · Mar 05, 2008

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?

ORA-01460 cf_sql_clob for cfstoredproc param failed

Phil

Report · Mar 05, 2008

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

Report · Mar 05, 2008

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.

Report · Apr 01, 2009

quote:

Originally posted 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.

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

Sample using Oracle Thin driver.


<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?

Report · Mar 05, 2008

dianemk wrote:
> 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.