Highlighted

CF 11 errors when inserting, updating or selecting Oracle 12c Varchar2 columns that are over 4000 characters

New Here ,
Jun 07, 2017

Copy link to clipboard

Copied

Oracle 12c can now store over 32k of data in a Varchar2 column instead of the previous limitation of 4000 and we are trying to utilize that option but are receiving ColdFusion errors when trying to do so. Below is my dev specs but this is also occurring on our productions systems with similar specs (CF 11 Enterprise and Oracle 12c).

ColdFusion Version: 11,0,12,302575

Edition: Developer 

Operating System: Windows 7

Adobe Driver Version: 5.1.3 (Build 000094) 

Java Version: 1.8.0_25 

Oracle Version: Personal Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

**Local install on Windows 7

After increasing the ability for Oracle 12c VarChar2 fields to hold 32k of data, inserting into, updating or selecting breaks ColdFusion statements for data over 4000. Statements under 4001 works fine.

Strangely, when I remove the cfquerparam on the insert or update it works (but that's not an option).

The select breaks either way.

Insert Statement / Error:

-------------------------------------------

<cfquery datasource="#application.datasource#">

  update project

  set description = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.description#">

  where projectId = <cfqueryparam cfsqltype="cf_sql_integer" value="62605" />

</cfquery>

Error Code: 1461

Message: [Macromedia][Oracle JDBC Driver][Oracle]ORA-01461: can bind a LONG value only for insert into a LONG column

***NOTE: The db column is a varchar2 of length 10,000.

***NOTE: I get the same error message for an insert statement

Select Statement / Error:

-------------------------------------------

<cfquery datasource="#application.datasource#" name="qProject">

  select description

  from project

  where projectId = <cfqueryparam cfsqltype="cf_sql_integer" value="62605" />

</cfquery>

Error Code: 24920

Message: [Macromedia][Oracle JDBC Driver][Oracle]ORA-24920: column size too large for client

Any thoughts / ideas?? Thanks!!

TOPICS
Database access

Views

814

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

CF 11 errors when inserting, updating or selecting Oracle 12c Varchar2 columns that are over 4000 characters

New Here ,
Jun 07, 2017

Copy link to clipboard

Copied

Oracle 12c can now store over 32k of data in a Varchar2 column instead of the previous limitation of 4000 and we are trying to utilize that option but are receiving ColdFusion errors when trying to do so. Below is my dev specs but this is also occurring on our productions systems with similar specs (CF 11 Enterprise and Oracle 12c).

ColdFusion Version: 11,0,12,302575

Edition: Developer 

Operating System: Windows 7

Adobe Driver Version: 5.1.3 (Build 000094) 

Java Version: 1.8.0_25 

Oracle Version: Personal Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

**Local install on Windows 7

After increasing the ability for Oracle 12c VarChar2 fields to hold 32k of data, inserting into, updating or selecting breaks ColdFusion statements for data over 4000. Statements under 4001 works fine.

Strangely, when I remove the cfquerparam on the insert or update it works (but that's not an option).

The select breaks either way.

Insert Statement / Error:

-------------------------------------------

<cfquery datasource="#application.datasource#">

  update project

  set description = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.description#">

  where projectId = <cfqueryparam cfsqltype="cf_sql_integer" value="62605" />

</cfquery>

Error Code: 1461

Message: [Macromedia][Oracle JDBC Driver][Oracle]ORA-01461: can bind a LONG value only for insert into a LONG column

***NOTE: The db column is a varchar2 of length 10,000.

***NOTE: I get the same error message for an insert statement

Select Statement / Error:

-------------------------------------------

<cfquery datasource="#application.datasource#" name="qProject">

  select description

  from project

  where projectId = <cfqueryparam cfsqltype="cf_sql_integer" value="62605" />

</cfquery>

Error Code: 24920

Message: [Macromedia][Oracle JDBC Driver][Oracle]ORA-24920: column size too large for client

Any thoughts / ideas?? Thanks!!

TOPICS
Database access

Views

815

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jun 07, 2017 0
LEGEND ,
Nov 01, 2017

Copy link to clipboard

Copied

Even though this post is several months old at the time of my reply, I think an answer would be a good idea.

From the quick Google search that I did, I came across a blog that mentions this exact same problem.  The whole thing is worth reading, but you can scan down and find the solution easily enough.

HTH,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 01, 2017 0