If you work with an application where you have to save BLOBs, such as an ID card app, you’ll run into this situation. You’ve just made a procedure that needs testing, but you need a BLOB to test it. In a few simple steps, you can have that BLOB.

The following will load a file into a variable and then insert it into a table.

SQL> create table vic.tst (tst_id integer, pic blob);

Table created.

SQL> declare
    v_blob BLOB := null;
    v_file BFILE := BFILENAME('MY_DIR','testphoto.jpg');
    offset number := 1;
begin
    DBMS_LOB.CREATETEMPORARY(v_blob, true);
    DBMS_LOB.OPEN(v_blob, DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.FILEOPEN(v_file,DBMS_LOB.FILE_READONLY);
    DBMS_LOB.LOADBLOBFROMFILE (v_blob, v_file, DBMS_LOB.GETLENGTH(v_file),OFFSET, OFFSET);
    DBMS_LOB.FILECLOSE(v_file);

    insert into vic.tst values (1, v_blob);
end;
/

1. Create a directory. Maybe you have already done this because getting a file into a database requires you to have a directory that the database knows about. They are easy enough to create, just make sure that you’re making the directory on the server where you’re executing the code. It won’t work on your machine unless your machine is where the database is.

SQL> create directory [directory name] as '[the location]';

For example, you might do:

SQL> create directory MY_DIR as 'c:UsersDBADocuments';

Or…

SQL> create directory MY_DIR as '/home/oracle';

Step 1 complete! Now you have somewhere to drop files and pick them up in the database using various commands.

2. Add variables to handle the BLOB. First, you make a BLOB variable. Next, you make a BFILE variable.

  • A BLOB stores unstructured binary data up to 128 terabytes in size! What the…?!
  •  A BFILE datatype stores unstructured binary data in an external file in a file system.  BFILEs are read only but the file they refer to are not. For the purposes of this exercise, we don’t need it to be readable and won’t be editing the file itself. But it’s something to keep in mind. A BFILE column or attribute stores a file locator that points to an external file in a file system. The size is only limited by the OS.
    We are going to use our BFILE variable to store the file locator.
  • BFILENAME returns a file locator that will point to the LOB on your system. Notice how below you first give the directory name (created in step 1) and then the file name. Make sure you have that file there, or it will fail.
  • Offset will be used by the LoadBLOBFromFile below.
declare
    v_blob BLOB := null;
    v_file BFILE := BFILENAME('MY_DIR','testphoto.jpg');
    offset number := 1;

3. Add the body. First, create a temporary LOB. Then, open the temporary LOB.

Temporary LOBs are created and stored in the temporary tablespace and are deleted from the tablespace when the session ends or by using a FREETEMPORARY command.

  • CREATETEMPORARY creates a temporary LOB and its index in the temporary tablespace. By default, it lasts for the session. The first parameter refers to the blob variable we are using. The second indicates whether or not it should be read into the buffer.
  • OPEN will open the BLOB in the mode indicated. We open it in Read-Write mode because we are going to write the contents of that file into the v_blob variable later on.
begin
    DBMS_LOB.CREATETEMPORARY(v_blob, true);
    DBMS_LOB.OPEN(v_blob, DBMS_LOB.LOB_READWRITE);

4. Now, we open the file, read it into our variable and then close the file.

  • FILEOPEN opens a BFILE for read-only access. You may be wondering why there’s an option. I know I am… 🙂
  • LOADBLOBFROMFILE loads a BFILE into a BLOB, as the name may suggest to our more astute readers. The offset variables are used because they are in/out parameters used to both determine and report on the offset for the source and destination BLOBs. Using a number here, like “1”, will give you an error because it isn’t writable.
  • FILECLOSE closes a BFILE.
    DBMS_LOB.FILEOPEN(v_file,DBMS_LOB.FILE_READONLY);
    DBMS_LOB.LOADBLOBFROMFILE (v_blob, v_file, DBMS_LOB.GETLENGTH(v_file),OFFSET, OFFSET);
    DBMS_LOB.FILECLOSE(v_file);

5. Now for a little test, we will insert this file into a table.

First, make the table.

    create table vic.tst (tst_id integer, pic blob);

And add this line at the end of the procedure:

    insert into vic.tst values (1, v_blob);

6. End it!!!

end;

Source: I got a good understanding of this topic from here, which deserves a good read, as well. I wanted to re-explain it in my own words and filter it down to what I wanted most from it.

Tagged: ,