Search code examples
oracleoracle11gutl-file

Ora-29283 error on 11g

Is it possible to access a folder on my PC from oracle database? In other words database is not installed on my local machine and I have created a folder on my PC 'C:\LOB' where I want to save images retrieved from database, but it is returning an

ORA-29283 error: invalid file operation.

I am sure that directory is created because I can see it in directories folder in my database. So what could be the problem? is it because folder is created on my PC and database is somewhere else? Below is my procedure:

CREATE OR REPLACE PROCEDURE BLOB_UPDATE (file_name in varchar) IS

    file_ref UTL_FILE.file_type;
    raw_max_size constant number := 32767;

 begin

   file_ref := UTL_FILE.fopen('MY_DIR', file_name, 'WB', raw_max_size);
   -- Here it stops working ! -- 
   ....
   utl_file.fclose(file_ref);

END BLOB_UPDATE;
Solution

  • PL/SQL is run in the database system, i.e. on the server. It can only access files on the server. It cannot the files on your PC.

    Even if you have defined the directory in Oracle (CREATE DIRECTORY ...) and granted sufficient access rights to your user, it will fail because Oracle will try to access C:\LOB on the server and not on your PC.

    What you're trying to achieve cannot be solved on the server side, i.e. it cannot be solved in PL/SQL. You'll have to write code that runs on the client side (i.e. on your PC), e.g. some Java or .NET program.