Oracle error ORA-01461when trying to insert into an ORACLE BLOB field

[SucramLegiew]

I am getting Oracle error ‘ORA-01461: can bind a LONG value only  for insert into a LONG column' when trying to insert into an ORACLE BLOB field. The error occurs when trying to insert a large BLOB (JPG), but does not occur when inserting a small (<1K) picture BLOB.(JPG). Any ideas?

BTW, when using a SQL Server datasource using the same code.... everything works with no problems.

ORACLE version is 11.2.0.1

The ORACLE datasource is JDBC using Oracle's JDBC driver ojdbc6.jar v11.2.0.1 (I also have tried ojdbc5.jar v11.2.0.1; ojdbc5.jar v11.2.0.4; and ojdbc6.jar v11.2.0.4 with the same error result.)

Here is my code:

<cfset file_mime = Lcase(Right(postedXMLRoot.objname.XmlText, 3))>

<cfif file_mime EQ 'jpg'><cfset file_mime = 'jpeg'></cfif>

<cfset file_mime = 'data:image/' & file_mime & ';base64,'>

<cfset image64 = ImageReadBase64("#file_mime##postedXMLRoot.objbase64.XmlText#")>

<cfset ramfile = "ram://" & postedXMLRoot.objname.XmlText>

<cfimage action="write" source="#image64#" destination="#ramfile#" overwrite="true">

<cffile action="readbinary" file="#ramfile#" variable="image_bin">

<cffile action="delete" file="#ramfile#">

<cfquery name="InsertImage" datasource="#datasource#">

INSERT INTO test_images

(

image_blob

)

SELECT

<cfqueryparam value="#image_bin#" cfsqltype="CF_SQL_BLOB">

FROM          dual

</cfquery>

[Matthew C. Parks]

It seems to me the the OJDBC6.jar does not support INSERT INTO () SELECT .  Does not matter if it is a BLOB or not.  Test it out but that is what we have noticed.

[EddieLotter]

Did you set the "Blob Buffer(bytes)" setting in Advanced Settings of your datasource in the CF Administrator?

Cheers

Eddie

[BKBK]

SucramLegiew wrote:

I am getting Oracle error ‘ORA-01461: can bind a LONG value only for insert into a LONG column' when trying to insert into an ORACLE BLOB field. The error occurs when trying to insert a large BLOB (JPG), but does not occur when inserting a small (<1K) picture BLOB.(JPG). Any ideas?

...

<cfqueryparam value="#image_bin#" cfsqltype="CF_SQL_BLOB">

...

Weird indeed. You actually tell the database to bind to BLOB value.

You should try to_lob:

SELECT

TO_LOB(<cfqueryparam value="#image_bin#" cfsqltype="CF_SQL_BLOB">)

[BKBK]

Following up from my previous post, if you faced problems with to_lob with small BLOBs, then you could cut it like this

<cfset length_in_KBytes = len(image_bin)/1024>

<cfif length_in_KBytes GT 1>

SELECT

TO_LOB(<cfqueryparam value="#image_bin#" cfsqltype="CF_SQL_BLOB">)

<cfelse>

SELECT

<cfqueryparam value="#image_bin#" cfsqltype="CF_SQL_BLOB">

</cfif>