[DB-SIG] DCOracle / BLOB

[Jim Fulton]

Sebastian Schneckener wrote:
> 
> Hello everybody
> 
> I am trying to store images as BLOB in an oracle database using the DCOracle interface.
> I found out, that if I use a statement like:
> 
> cursor.execute ("INSERT INTO  images values (1,1,1,'X',:p1)",(image,))
> 
> the image has to be in a hex format.
> So far, so easy, but if the image is larger than 4000 byte, I get a problem:
> 
>   File "/software/pub_gen/lib/python1.5/site-packages/DCOracle/ociCurs.py", line 225, in execute
>     if rc!=3129: self._error()
>   File "/software/pub_gen/lib/python1.5/site-packages/DCOracle/ociCurs.py", line 81, in _error
>     raise error, (rc, oci_.OracleErrorMessage(self._c.lda, rc))
> oci.error: (1461, 'ORA-01461: can bind a LONG value only for insert into a LONG column\012')
> 
> Neither I realy understand this nor I know a workaround. The column type in images is BLOB of course.
> Anybody any ideas?

You can't really insert BLOBS using SQL in Oracle. (Well, you can insert short
Blobs with SQL, but you cant update the values with SQL.) You have to create
blob objects. :(

From the documentation for the 1.2.1 release:

    Oracle 8 Large Objects (LOBs)

      Limited support is provided for Oracle 8 large objects (LOBs).
      To get LOB support, you must link against Oracle 8 client
      libraries and make sure that the -DDCORACLE8 switch is supplied
      when the oci_ extension module is built.

      LOB Columns can be selected in SQL Select statements.  The way
      LOB data is returned depends on which fetch routine is used.

      If fetchone is used, then LOB objects are returned.  Lob objects
      support the following methods:

        length() -- Return the LOB length

        read([n,offset,csid,csfrm]) -- Read data from the lob (OCILobRead)

           Up to 'n' characters are read, starting at the given
           'offset', where the offset of the first character is **1**,
           not **0**.  If the argument 'n' is less than 1 or is
           ommitted, then all data from the offset to the end of the
           LOB is read.

	   The arguments csid and csfrm are as described in the
	   documentation for the OCILobRead function in Oracle OCI 8
	   documentation.
	   
	write(data, [offset,csid,csfrm]) -- Write data to the lob (OCILobWrite)

	   The data given by the string argument 'data' are written to
	   the LOB starting at the given 'offset', where the offset of
	   the first character is **1**, not **0**.

	   The arguments csid and csfrm are as described in the
	   documentation for the OCILobRead function in Oracle OCI 8
	   documentation.

      Note that to add or update LOB data, you can provide string
      input.  To update LOB data, you can select the lob and use the
      write method.
      
      If fetchmany or fetchall are used, then lobs are converted to
      strings and returned.

I think I've seen cheesy examples in the Oracle docs where they 
insert a blank string, then to a select, and then call write on
the selected object to actually insert data.

Jim

--
Jim Fulton           mailto:jim@digicool.com   Python Powered!        
Technical Director   (888) 344-4332            http://www.python.org  
Digital Creations    http://www.digicool.com   http://www.zope.org    

Under US Code Title 47, Sec.227(b)(1)(C), Sec.227(a)(2)(B) This email
address may not be added to any commercial mail list with out my
permission.  Violation of my privacy with advertising or SPAM will
result in a suit for a MINIMUM of $500 damages/incident, $1500 for
repeats.