Главная страница | назад





Article #19504: Special SQL syntax required to INSERT into BLOBs/CLOBs when parameters are >= 4K.

Question:

Why am I having trouble inserting/updating Oracle 8 BLOB and/or CLOB datatypes where the data >=4k?

Answer:

Oracle SQL requires an different SQL syntax when parameters to BLOBS and/or CLOBS have more than 4K worth of data. This means the following SQL statement:

INSERT INTO TESTBLOB (ICOMPONENTID, GIMAGE) VALUES (:CID, :GIMAGE)

will result in the error:

EDBEngineError with message 'General SQL error.
ORA-22275: invalid LOB locator specified'

To resolve the problem for a blob do the following:

  1. Use the following SQL statement:
  2. insert into TESTBLOB (ICOMPONENTID, GIMAGE) values(:CID, empty_blob() ) returning GIMAGE into :GIMAGE

  3. Make the parameter type for GIMAGE be ftOraBlob
To resolve the problem for a clob do the following:
  1. Use the following SQL statement:
  2. insert into TESTBLOB (ICOMPONENTID, GIMAGE) values(:CID, empty_clob() ) returning GIMAGE into :GIMAGE

  3. Make the parameter type for GIMAGE be ftOraClob

Last Modified: 14-AUG-00