Oracle - Load local/client file to blob column

Question

is there a way to load a file from local/client file system to blob column in a sqlplus/plsql script?

I'm having something like this SQL Developer functionality but scripted. Dialog opens when you click on blob column while viewing the table data.

Answer 1

SQL statements are executed on the server and not on the client's computer and the server has no access to the local file system to be able to read files you have there.

If you particularly want to do it all from the client then use a shell script (or an equivalent) and:

1. Read 2000 bytes from the file you want to load.

2. Convert the 2000 bytes of binary data to 4000 hexadecimal characters.

3. Repeat until the file is read.

4. While doing that, dynamically create an insert statement:

   INSERT INTO table_name (
     blob_column
   ) VALUES (
     EMPTY_BLOB()
     || HEXTORAW('<first 4000 hexadecimal characters>')
     || HEXTORAW('<next 4000 hexadecimal characters>')
     -- ...
     || HEXTORAW('<last 4000 hexadecimal characters>')
   );
   

5. Execute that SQL statement in sqlplus.

Alternatively, write a small Python/Java/C#/etc. program and load the file using that.

Otherwise, if you want a pure PL/SQL solution, transfer the file to the database server and load the file from there.

---

The SQL Developer functionality that you show an image of in the question will effectively work in a similar way. It will probably:

• INSERT an empty blob into the table.
• Read back the handle to that blob.
• Read the file in chunks and stream the chunks back to the database server to append them to that blob using the handle to tell the database where the data is going.

You cannot use exactly the same method as you need to read the file on the client before the script sends the statement to the server and the statement needs to be self-contained - there cannot be any interaction from the client during the running of a single SQL or PL/SQL statement.

Answer 2

If you can move your file to the server - here is the function that reads file into BLOB returning BLOB which can be used to insert/update BLOB column in the table:

FUNCTION FILE2BLOB  (mDir       IN  VARCHAR2,
                     mFileName  IN  VARCHAR2) RETURN BLOB
IS
BEGIN
    Declare 
        mBLOB       BLOB := Empty_Blob();
        mBinFile    BFILE := BFILENAME(mDir, mFileName);
    Begin
        DBMS_LOB.OPEN(mBinFile, DBMS_LOB.LOB_READONLY);             -- Open BFILE
        DBMS_LOB.CreateTemporary(mBLOB, TRUE, DBMS_LOB.Session);    -- BLOB locator initialization
        DBMS_LOB.OPEN(mBLOB, DBMS_LOB.LOB_READWRITE);               -- Open BLOB locator for writing
        DBMS_LOB.LoadFromFile(mBLOB, mBinFile, DBMS_LOB.getLength(mBinFile));   -- Reading BFILE into BLOB
        DBMS_LOB.CLOSE(mBLOB);              -- Close BLOB locator
        DBMS_LOB.CLOSE(mBinFile);           -- Close BFILE
        RETURN mBLOB;       -- Return BLOB
    End;
END FILE2BLOB;

NOTE: you will need a Directory object (mDir parameter) that will target the server file system directory
To write BLOB into table column ...

Update your_table_name 
    Set your_blob_column = FILE2BLOB('your_directory_obj', 'your_file_name') 
Where your_pk_column = PK_VALUE;