Inserting a BLOB

Inserting a BLOB in Oracle is slightly different from inserting from inserting a value in an Oracle database. We first have to create a location with n empty BLOB. After that the BLOB can be inserted. In a PL/SQL programme:


create or replace PROCEDURE UPLOAD_FILE_TO_DATABASE(p_dir_name varchar2,p_file_name varchar2,p_file_desc varchar2 default null)
AS
    L_BLOB  BLOB;
    L_BFILE  BFILE := BFILENAME(p_dir_name, p_file_name);
BEGIN
    -- Insert a reacord with empty blog
    INSERT INTO MY_FILE_TABLE(nummer,FILE_NAME, FILE_LOB,DESCRIPTION)
        VALUES (file_no_seq.nextval,p_file_name, empty_blob(),p_file_desc)
        RETURNING FILE_LOB INTO L_BLOB;-- Open the bfile locator 
DBMS_LOB.OPEN(L_BFILE, DBMS_LOB.LOB_READONLY);
-- Open the blob locator to add the file 
DBMS_LOB.OPEN(L_BLOB, DBMS_LOB.LOB_READWRITE);
-- Add the file to blob locator
DBMS_LOB.LOADFROMFILE(DEST_LOB => L_BLOB,
                      SRC_LOB  => L_BFILE,
                      AMOUNT   => DBMS_LOB.GETLENGTH(L_BFILE));
-- Close the lob locators
DBMS_LOB.CLOSE(L_BLOB);
DBMS_LOB.CLOSE(L_BFILE);
COMMIT;
END UPLOAD_FILE_TO_DATABASE;

which call be invoked by (example) exec UPLOAD_FILE_TO_DATABASE(‘TEMP’,’2019-11-15 12.46.30.mov’,’Nora’);

Likewise, downloading a BLOB is done via:

create or replace procedure download_file_from_database( p_file_number number)
is
    l_file           utl_file.file_type;    
    l_buffer_data    raw(32767);            
    l_byte_amount    binary_integer := 32767;  
    l_position       integer        := 1;   
    l_blob           blob;                 
    l_blob_length    integer;              
    l_file_name      varchar2(250);       
begin
  -- fetch the blob content from table into variable
  select file_lob,
         file_name
  into l_blob,
       l_file_name
  from hr.my_file_table
  where nummer  = p_file_number;
-- compute the length of blob content
  l_blob_length   := dbms_lob.getlength(l_blob);
  l_position      := 1;
-- open the destination file.
  l_file := utl_file.fopen('EXTERNORA',l_file_name,'wb', l_byte_amount); 
-- read chunks of the blob and write them to the file
-- until complete.
while l_position < l_blob_length
loop
dbms_lob.read(l_blob, l_byte_amount, l_position, l_buffer_data);
utl_file.put_raw(l_file, l_buffer_data, true);
l_position := l_position + l_byte_amount;
end loop;
-- close the file.
utl_file.fclose(l_file);
 exception
when others then
-- if anything goes wrong, then close the file.
if utl_file.is_open(l_file) then
utl_file.fclose(l_file);
end if;
raise;
end download_file_from_database;

Al this download is invoked via: exec DOWNLOAD_FILE_FROM_DATABASE(5);

An alternative way is to store data directly into the database via:

insert into my_file_table(FILE_NAME, FILE_LOB, DESCRIPTION, NUMMER) values ('prut', utl_raw.cast_to_raw('some magic here'), 'onzin',6);

followed by

exec DOWNLOAD_FILE_FROM_DATABASE(6);

The text “some magic here” is then shown as being inserted into file “prut” that is stored in the database.