oracleplsqloracle-sqldevelopersqlplusoracle19c

Oracle - Load local/client file to blob column

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.

enter image description here

Solution

  • 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:

    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.