-
Insert binary data in to BLOB
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..
Code:
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
-
Actually it happens directly...the 2nd insert statement worked nicely!!!:-)
Thanks
Sam
-
Originally Posted by aspdba
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 :
Code:
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.
Code:
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|