Insert binary data in to BLOB

[aspdba]

Hi All,

Is there any way in oracle where in I can directly insert data send as a byte array from dotnet into the database table using pl/sql?

its like..

create or replace procedure insertblob(input_blob blob)as
begin
INSERT INTO blobtable (id, blobcolumn) VALUES (blobtable_seq.nextVal, empty_blob());
INSTEAD....
insert into blobtable values (blobtable_seq.nextVal,input_blob);
end;

thanks
Sam

[aspdba]

Actually it happens directly...the 2nd insert statement worked nicely!!!:-)

Thanks
Sam

[RBARAER]

Actually it happens directly...the 2nd insert statement worked nicely!!!:-)

Thanks
Sam

Beware though that inserting the BLOB directly like this only works for arrays less than 4000 bytes long in SQL, as stated in the documentation and 32767 bytes long for PLSQL as stated here .

Other solutions are mainly :

- use a table of RAW buffers (SQL nested table or PLSQL Index-By table) to pass the whole byte array at once to the SP and then use the PLSQL LOB API to insert the BLOB. If the array is very big, this will require a lot of memory on the server :

CREATE OR REPLACE TYPE RAW_TABLE AS TABLE OF RAW(32500);

CREATE OR REPLACE PROCEDURE Insertblob(input_raw_array RAW_TABLE) as
   blData BLOB;
   iChunkSize BINARY_INTEGER;
BEGIN
   INSERT INTO blobtable (id, blobcolumn) 
   VALUES (blobtable_seq.nextVal, empty_blob())
   RETURNING blobcolumn
   INTO blData;

   -- It is better performance-wise to read or write
   -- an amount equal to the Lob Chunk size
   iChunkSize := DBMS_LOB.GETCHUNKSIZE(blData);

   DBMS_LOB.OPEN(blData, DBMS_LOB.lob_readwrite);

   FOR i IN 1..input_raw_array.COUNT LOOP
      DBMS_LOB.WRITEAPPEND(blData, iChunkSize, input_raw_array(i)); 
   END LOOP;

   DBMS_LOB.CLOSE(blData);

   COMMIT;

END Insertblob;

- or pass the BLOB as an OUT parameter and let .NET open the BLOB, insert data and then close the Blob and commit.

CREATE OR REPLACE PROCEDURE Insertblob(input_blob OUT BLOB) as

BEGIN
   INSERT INTO blobtable (id, blobcolumn) 
   VALUES (blobtable_seq.nextVal, empty_blob())
   RETURNING blobcolumn
   INTO input_blob;
END Insertblob;

Note that the RETURNING INTO clause implicitly does a SELECT FOR UPDATE on the row containing the BLOB, which is always needed before modifying a BLOB column. The commit releases the lock.

HTH & regards,

rbaraer