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:<\/p>\n\n\n\n
\ncreate or replace PROCEDURE UPLOAD_FILE_TO_DATABASE(p_dir_name varchar2,p_file_name varchar2,p_file_desc varchar2 default null)\nAS\n L_BLOB BLOB;\n L_BFILE BFILE := BFILENAME(p_dir_name, p_file_name);\nBEGIN\n -- Insert a reacord with empty blog\n INSERT INTO MY_FILE_TABLE(nummer,FILE_NAME, FILE_LOB,DESCRIPTION)\n VALUES (file_no_seq.nextval,p_file_name, empty_blob(),p_file_desc)\n RETURNING FILE_LOB INTO L_BLOB;-- Open the bfile locator \nDBMS_LOB.OPEN(L_BFILE, DBMS_LOB.LOB_READONLY);\n-- Open the blob locator to add the file \nDBMS_LOB.OPEN(L_BLOB, DBMS_LOB.LOB_READWRITE);\n-- Add the file to blob locator\nDBMS_LOB.LOADFROMFILE(DEST_LOB => L_BLOB,\n SRC_LOB => L_BFILE,\n AMOUNT => DBMS_LOB.GETLENGTH(L_BFILE));\n-- Close the lob locators\nDBMS_LOB.CLOSE(L_BLOB);\nDBMS_LOB.CLOSE(L_BFILE);\nCOMMIT;\nEND UPLOAD_FILE_TO_DATABASE;<\/code><\/pre>\n\n\n\nwhich call be invoked by (example) exec UPLOAD_FILE_TO_DATABASE(‘TEMP’,’2019-11-15 12.46.30.mov’,’Nora’);<\/p>\n\n\n\n
Likewise, downloading a BLOB is done via:<\/p>\n\n\n\n
create or replace procedure download_file_from_database( p_file_number number)\nis\n l_file utl_file.file_type; \n l_buffer_data raw(32767); \n l_byte_amount binary_integer := 32767; \n l_position integer := 1; \n l_blob blob; \n l_blob_length integer; \n l_file_name varchar2(250); \nbegin\n -- fetch the blob content from table into variable\n select file_lob,\n file_name\n into l_blob,\n l_file_name\n from hr.my_file_table\n where nummer = p_file_number;<\/pre>\n\n\n\n-- compute the length of blob content\n l_blob_length := dbms_lob.getlength(l_blob);\n l_position := 1;<\/pre>\n\n\n\n-- open the destination file.\n l_file := utl_file.fopen('EXTERNORA',l_file_name,'wb', l_byte_amount); <\/pre>\n\n\n\n-- 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; <\/pre>\n\n\n\n-- close the file.
utl_file.fclose(l_file); <\/pre>\n\n\n\n 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; <\/pre>\n\n\n\nAl this download is invoked via: exec DOWNLOAD_FILE_FROM_DATABASE(5);<\/p>\n\n\n\n
An alternative way is to store data directly into the database via:<\/p>\n\n\n\n
insert into my_file_table(FILE_NAME, FILE_LOB, DESCRIPTION, NUMMER) values ('prut', utl_raw.cast_to_raw('some magic here'), 'onzin',6);\n\nfollowed by\n\nexec DOWNLOAD_FILE_FROM_DATABASE(6);<\/pre>\n\n\n\nThe text “some magic here” is then shown as being inserted into file “prut” that is stored in the database.<\/p>\n","protected":false},"excerpt":{"rendered":"
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: 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-3260","post","type-post","status-publish","format-standard","hentry","category-nice-to-know"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3260","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3260"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3260\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3260"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3260"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3260"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}