Error pasing large CLOB to Oracle

Forum|Forum|18 years ago
March 4, 2008
4 replies
4348 views

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.

This topic has been closed for replies.

P

paross1

Participating Frequently

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

P

paross1

Participating Frequently

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

J

JRock

Participant

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

D

dianemkAuthor

Participating Frequently

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.

P

paross1

Participating Frequently

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

S

SafariTECH

Inspiring

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.

D

dianemkAuthor

Participating Frequently

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.

N

Newsgroup_User

Inspiring

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.

D

dianemkAuthor

Participating Frequently

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded