Roberto Franco Roberto Franco - 14 days ago 6
SQL Question

How do I get the size of a file(BFILE) in oracle g11 directly from a directory in my pc?

I was trying to get the size of a file from my desktop in Oracle SQL Developer, I'm working with a "BFILE" field.

enter image description here

And this is the query that I'm using

SET SERVEROUTPUT ON
DECLARE
v_bfile BFILE;
begin
select valor into v_bfile from TABLA_BFILE where id = 4;
DBMS_OUTPUT.PUT_LINE('El archivo ocupa: '||DBMS_LOB.GETLENGTH(v_bfile)||' bytes.');
end;


And I'm getting the next error

ORA-06512: en "SYS.DBMS_LOB", línea 787
ORA-06512: en línea 5
22285. 00000 - "non-existent directory or file for %s operation"


What I'm missing here?

Answer

Your code works.
The issue was with the population of the table.
The directory should not be a path but a DIRECTORY object.

create directory DESKTOP as 'C:\Users\Roberto\Desktop';
Insert into TABLA_BFILE (ID,valor) values (1,BFILENAME('DESKTOP','oracle.jpg'));