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:

insert into TESTBLOB (ICOMPONENTID, GIMAGE) values(:CID, empty_blob() ) returning GIMAGE into :GIMAGE

2. Make the parameter type for GIMAGE be ftOraBlob

1. Use the following SQL statement:

insert into TESTBLOB (ICOMPONENTID, GIMAGE) values(:CID, empty_clob() ) returning GIMAGE into :GIMAGE

2. Make the parameter type for GIMAGE be ftOraClob

Last Modified: 14-AUG-00