Insert binary data in to BLOB
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Insert binary data in to BLOB

  1. #1
    Join Date
    Aug 2002
    Posts
    115

    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

  2. #2
    Join Date
    Aug 2002
    Posts
    115
    Actually it happens directly...the 2nd insert statement worked nicely!!!:-)

    Thanks
    Sam

  3. #3
    Join Date
    May 2005
    Location
    France
    Posts
    34
    Quote 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
  •  


Click Here to Expand Forum to Full Width