tracker issue : CF-4165262

select a category, or use search below
(searches all categories and all time range)

null/blank clob value returned as reference cursor from select statement in oracle causes No More Data Available To Read

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/Fixed

Reporter/Name(from Bugbase): Eric Harris / Eric Harris (Eric Harris)

Created: 06/16/2016

Components: Database

Versions: 11.0

Failure Type: Non Functioning

Found In Build/Fixed In Build: CF11_Final / 303100

Priority/Frequency: Critical / All users will encounter

Locale/System: English / Win 2012 Server x64

Vote Count: 0

Problem Description: Coldfusion 11, Patch 9 looks to have introduced a bug where if you select a clob column that has blank text in it ( dbms_lob.getlength() returns null/nothing ) and this select statement is returned as a referenced cursor coldfusion is returning "[Macromedia][Oracle JDBC Driver]No more data available to read."

Steps to Reproduce:
create a table that has a clob in it.  Insert a record and for the clob text use '' ( 2 single quotes right next to each other ).  
create a database procedure that returns a referenced cursor
use dbms_sql.parse() to select the clob column.
use dbms_sql.execute() and then dbms_sql.to_refcursor() to create the referenced cursor.  Return value as out parameter to CF
Coldfusion will throw JDBC Driver error.

Actual Result:
Coldfusion 11 Patch 9 returns JDBC Driver error

Expected Result:
Coldfusion 11 Patch 8 does not return error condition and processes properly.

Any Workarounds:
Yes.  Instead of selecting the column, use a case statement to determine if their is a value in the clob.  If not, return empty_clob():

          replace( case when dbms_lob.getlength( table_text) >= 0 then table_text else empty_clob() end,chr(10), '<br>') table_text,

instead of:
         replace( table_text, chr(10), '<br>') table_text,

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	4165262

Reason:	PRHaveInfo

External Customer Info:
External Company:  
External Customer Name: Eric Harris
External Customer Email:  
External Test Config: My Hardware and Environment details:

Windows 2012 R2 64 bit

Coldfusion 11 Patch 9 64 bit



Thanks Eric for reporting this issue. While we are investigating this issue. Please follow the steps mentioned below to resolve this issue: 1. Stop ColdFusion application service. 2. Take backup and remove macromedia_drivers.jar file located at <cf_install_dir>/cfusion/lib 3. Copy macromedia_drivers.jar available inside backup folder (Ex. <cf_install_dir>\cfusion\hf-updates\hf-11-00009\backup\lib) and move it to <cf_install_dir>/cfusion/lib location. 4. Start ColdFusion application service. Thanks, Nimit
Comment by Nimit S.
2379 | June 17, 2016 12:59:50 AM GMT
This issue exist in ColdFusion 2016 as well... The solution posted by Nimit works.
Comment by External U.
2380 | July 18, 2016 12:55:22 PM GMT
we implemented if() check in source code to work around this issue.
Comment by External U.
2381 | September 09, 2016 11:43:35 AM GMT
Hi All, This issue is fixed now. The fix will be included as part of an upcoming update of ColdFusion. You can access QA verified macromedia_drivers.jar hosted at below given location: Location: MD5 Checksum: e171251f7c26af91db17b569f503adcc Steps to apply this jar: 1. Stop ColdFusion service 2. Navigate to <cf_install_root>/<instance_name>/lib 3. Take backup of macromedia_drivers.jar 4. Replace it with the latest driver jar. 5. Start ColdFusion service Thanks, Nimit
Comment by Nimit S.
2382 | July 07, 2017 12:26:34 PM GMT
The new macromedia drivers solve the problem. Can these drivers by used in production as you seem to indicate that they are "QA verified"? Is that macromedia_drivers.jar the version that's going out with the next patch? If not, when will the next patch be? Currently, we are testing against 2016,0,04,302561 and I'm not seeing new updates available. Thanks, Steve Johnson Comment by Stephen Johnson
Comment by Stephen J.
2383 | July 18, 2017 06:09:41 PM GMT
Steve, Yes, you can use this driver in your production environment. This driver will also be included in the upcoming update of ColdFusion.
Comment by Nimit S.
2384 | July 18, 2017 06:23:26 PM GMT