Oracle BFILE cand Directory: Error message

Question

I am not very experienced with Oracle and BFILEs, so I apologize if the answer to my question is very obvious. I am using Oracle SQL Developer and Oracle Database 12c Enterprise Edition.

I try to save images externally via BFILE. For this I created a directory and a table and inserted the BFILEs:

DROP DIRECTORY PICTURE;
CREATE OR REPLACE DIRECTORY PICTURE AS 'C:\PICTURE';
 
DROP TABLE TEST1;
CREATE TABLE TEST1( NR INTEGER, IMAGE BFILE );
 
INSERT INTO TEST1 VALUES( 1, BFILENAME('PICTURE','IMG.png') );

The code runs without errors. Now I want to check that I have pasted the code correctly. To do this, I use the following function.

SELECT DBMS_LOB.GETLENGTH(IMAGE) FROM TEST1;  

After executing the function, I get the following error message.

ORA-22288: file or LOB Operation GETLENGTH failed
The system could not find the specified path.
ORA-06512: in "SYS.DBMS_LOB", line 850

What can be the reason? Could it be that I am not allowed to specify the path like this? The path points to a folder on my PC. Can the program access it? If that's not the problem, what could be causing the error message?

UPDATE:

When I run the Grand command to assign me the rights, I get the following error message

SQL > GRANT READ, WRITE ON DIRECTORY PICTURE TO XYZ;
ORA-01749: you may not GRANT/REVOKE privileges to/from yourself

I assumed that means I already have the rights.

Connect as XYZ works:

SQL> show user
USER is "XYZ"
SQL> select * from all_directories where directory_name = 'EXT_DIR';

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------
SYS                            PICTURE                        c:\PICTURE

SQL>

For the rest of the code, my output coincides with the output from Littlefoot's answer. Only with the .getlength () function do I get the error message described above.

It could be that the problem is that my PC is not a database server. I use a PC with Windows 10. I have downloaded the following Version:

https://www.oracle.com/de/tools/downloads/sqldev-v192-downloads.html

And I run the application every time using the following icon from the Explorer:

SQL Developer Icon

In the SQL Developer I then connected to a database instance. In the database instance a scheme is available to me with which I can set up and manage files. The PICTURE folder with the pictures is, as I said, on my PC in drive C: . I am trying to create a directory which then accesses this folder. Can I do that without having specially configured my PC?

Answer 1

Directory is an Oracle object which points to a filesystem directory which is (usually; let's pretend "always") located on a database server. If your PC isn't one, then it won't work.

As directory points to c:\picture on the database server,

• that directory must really exist there
• image must be in it
  • make sure that you didn't miss the actual file name
• you, as user, have to have (at least) read privilege to access it.

  • that's what is missing in code you posted. User (SYS, I presume), who created the directory, should have ran e.g.

    grant read, write on directory picture to sql_user;
    

    (or whichever user you really use).

---

Here's an example. I'm running Oracle 11gXE on my laptop (so it is a database server). File is located in c:\temp directory which is set to be Oracle EXT_DIR directory.

c:\Temp>dir robco.jpg
 Volume in drive C is OSDisk
 Volume Serial Number is 7635-F892

 Directory of c:\Temp

25.09.2017.  20:27             6.427 robco.jpg
               1 File(s)          6.427 bytes
               0 Dir(s)  234.166.730.752 bytes free

c:\Temp>

Let's see the Oracle side: first, grant access to user scott (who will load the file):

SQL> show user
USER is "SYS"
SQL> grant read, write on directory ext_dir to scott;

Grant succeeded.

SQL>

Connect as scott:

SQL> show user
USER is "SCOTT"
SQL> select * from all_directories where directory_name = 'EXT_DIR';

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------
SYS                            EXT_DIR                        c:\temp

SQL>

Create a table, insert a row, check the contents:

SQL> CREATE TABLE TEST1( NR INTEGER, IMAGE BFILE );

Table created.

SQL> INSERT INTO TEST1 VALUES( 1, BFILENAME('EXT_DIR','robco.jpg') );

1 row created.

SQL> SELECT * FROM test1;

        NR IMAGE
---------- --------------------------------------------------
         1 bfilename('EXT_DIR', 'robco.jpg')

SQL> SELECT DBMS_LOB.GETLENGTH(IMAGE) FROM TEST1;

DBMS_LOB.GETLENGTH(IMAGE)
-------------------------
                     6427

SQL>

So, if everything is done properly, it works.