How to declare Windows path in Oracle PLSQL

Question

I need to burn a blob column from an image that is saved in windows. How do I declare the image path in Oracle PLSQL?

Ex:

DECLARE
  dest_lob BLOB;

  -- this line report ORA22285 "non-existent directory or file for %s operation"
  src_lob  BFILE := BFILENAME('MY_DIR', 'C:\Users\gus\Desktop\image.jpg');

BEGIN
  INSERT INTO teste_gustavo_blob VALUES(2, EMPTY_BLOB())
    RETURNING imagem INTO dest_lob;

  DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
                     SRC_LOB  => src_lob,
                     AMOUNT   => DBMS_LOB.GETLENGTH(src_lob) );
  DBMS_LOB.CLOSE(src_lob);

  COMMIT;
END;

Note: I'm trying to insert a record into a table through a Windows machine using SQLDeveloper. The database is on a remote server.

Answer 1

The database can only see directories which are local to it. It cannot see files on your PC (unless the database is running on that PC). So you cannot load your file through PL/SQL.

However, you say you are using SQL Developer. You can load a BLOB by editing the table's data in the Table Navigator. Click on the Data tab then edit the cell (the BLOB column of the row you want to load the file into). Use the Local Data > Load option to upload your file. That Jeff Smith has publish a detailed step-by-step guide on his blog.