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.

Is the database running on your local machine? Or on a server? When you say "burn a blob" do you mean "load a blob"? — Justin Cave
– Justin Cave, Commented Nov 21, 2019 at 19:44

OldProgrammer · Accepted Answer · 2019-11-21 19:45:28Z

2

the path needs to be part of the directory object definition, and the file name is just that - just the file name. Example;

CREATE DIRECTORY MY_DIR AS 'C:\Users\gus\Desktop';
..
   BFILENAME('MY_DIR', 'image.jpg');

Note that the directory is created on the server not your local machine. So if you are trying to create a file on a local machine, this will not work in pl/sql. PL/sql runs on the server, not the client. In that case, you need to code a client.

answered Nov 21, 2019 at 19:45

OldProgrammer

12.2k4 gold badges27 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gustavo de Freitas Over a year ago

The error persists. Error Reporting - ORA-22288: File or LOBFILEOPEN operation failed No such file or directory ORA-06512: at "SYS.DBMS_LOB", line 1014 ORA-06512: in line 8 22288. 00000 - "file or LOB operation% s failed \ n% s" * Cause: The operation attempted on the file or LOB failed. * Action: See the next error message in the error stack for more detailed information. Also, verify that the file or LOB exists and that the necessary privileges are set for the specified operation. If the error still persists, report the error to the DBA.

OldProgrammer Over a year ago

So you are running the database server on your windows machine then?

Gustavo de Freitas Over a year ago

No, I'm trying to insert a record into a table through a Windows machine using SQLDeveloper

OldProgrammer Over a year ago

You can't do that. The file has to exist on the server. From oracle docs - "A directory object specifies an alias for a directory on the server file system where external binary file LOBs (BFILEs) and external table data are located. "

APC · Accepted Answer · 2019-11-21 20:35:32Z

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.

answered Nov 21, 2019 at 20:35

APC

147k20 gold badges180 silver badges289 bronze badges

Comments

Georgi Metodiev · Accepted Answer · 2022-11-01 10:37:43Z

0

I had a similar problem recently. I needed to dev test a feature and I needed a PDF file in my DB. I read few questions here and formulated an answer. I inserted a row manually with SQL and changed the file manually after that. The SQL:

insert into ATTACHMENT_TABLE (id, file_content) values 
(1, utl_raw.cast_to_raw('some dummy text'));

Using DBeaver, I edited the row and loaded the file from my windows PC. The end result is a row which contains the PDF file I wanted.

answered Nov 1, 2022 at 10:37

Georgi Metodiev

1

Collectives™ on Stack Overflow

How to declare Windows path in Oracle PLSQL

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related