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.