Categories
BLOB Load File Into BLOB Oracle PL/SQL

Loading a File Into a BLOB Object in Oracle


Warning: Undefined array key "ssba_bar_buttons" in /home/techdribble/public_html/wp-content/plugins/simple-share-buttons-adder/php/class-buttons.php on line 598

Warning: Undefined array key "ssba_bar_buttons" in /home/techdribble/public_html/wp-content/plugins/simple-share-buttons-adder/php/class-buttons.php on line 598

Warning: Undefined array key "ssba_bar_buttons" in /home/techdribble/public_html/wp-content/plugins/simple-share-buttons-adder/php/class-buttons.php on line 598

Warning: Undefined array key "ssba_bar_buttons" in /home/techdribble/public_html/wp-content/plugins/simple-share-buttons-adder/php/class-buttons.php on line 598

Warning: Undefined array key "ssba_bar_buttons" in /home/techdribble/public_html/wp-content/plugins/simple-share-buttons-adder/php/class-buttons.php on line 598

Loading a file into a BLOB object in Oracle is easy. All we need to do is use the DBMS_LOB package to load the file into a temporary LOB object. This is demonstrated in the following example.

This example assumes that you have a directory object set up where the files are stored. In this example the following object is used:

CREATE DIRECTORY FILEUPLOADS AS '/home_new/dmt/public_html/fileuploads';

Use the following function to load a file based on the filename parameter and return the BLOB reference for processing:


CREATE OR REPLACE FUNCTION loadBlobFromFile(p_file_name VARCHAR2) RETURN BLOB AS
  dest_loc  BLOB := empty_blob();
  src_loc   BFILE := BFILENAME('FILEUPLOADS', p_file_name);
BEGIN
  -- Open source binary file from OS
  DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);
  
  -- Create temporary LOB object
  DBMS_LOB.CREATETEMPORARY(
        lob_loc => dest_loc
      , cache   => true
      , dur     => dbms_lob.session
  );
    
  -- Open temporary lob
  DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
  
  -- Load binary file into temporary LOB
  DBMS_LOB.LOADFROMFILE(
        dest_lob => dest_loc
      , src_lob  => src_loc
      , amount   => DBMS_LOB.getLength(src_loc));
  
  -- Close lob objects
  DBMS_LOB.CLOSE(dest_loc);
  DBMS_LOB.CLOSE(src_loc);
  
  -- Return temporary LOB object
  RETURN dest_loc;
END loadBlobFromFile;
/

Easy as that.

8 replies on “Loading a File Into a BLOB Object in Oracle”

Lorrie,
It really depends on what your trying to do. If you can provide me with more details of your requirements then I may be able to help you out.

Cheers,
Ariel

hello all,

Can anyone help me with my requirement

1) I need to upload the file from apex , file browse button.
2) File need to store in the folder of our system not table or database.
3) I need to provide table of uploaded files with download button.
4) When i click download on respective file , that file only should be download.

Ex: i upload abc.png image, that is stored in upload folder. Now in apex front end i provide table with filename & download button beside it. When click on download button. abc.png only should down.

Note i gave example for one image. it can be any file extension

Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *