BFILE error on oracle

Question

I would like to make sure that every BFILE in my database really exists on the file system it is related to. I have the following error :

 ERROR at line 1:
  ORA-06502: PL/SQL: numeric or value error: invalid
  LOB locator specified: ORA-22275 ORA-06512: at line 59

with the following PL/SQL :

  1  declare
  2  sSQL varchar2(32767);
  3  TYPE refcursor is REF CURSOR;
  4  outCursor refcursor;
  5  v_1 bfile;
  6  dir_alias varchar2(500);
  7  name      varchar2(500);
  8  bfile_exists   BOOLEAN := FALSE;
  9  --
 10  -- DECLARATION DES CURSEURS
 11  --
 12  cursor find_bfile is
 13  SELECT owner, table_name, column_name from dba_tab_columns where data_type='BFILE' order by 1,2,3;
 14  begin
 15  -- curseur principal sur dba_tab_columns
 16  for boucle_find_bfile in find_bfile loop
 17       dbms_output.put_line('Owner : ' || boucle_find_bfile.owner || 'Table : ' || boucle_find_bfile.table_name|| boucle_find_bfile.column_name );
 18       sSQL := 'select ' || boucle_find_bfile.column_name || ' from ' || boucle_find_bfile.owner || '.' || boucle_find_bfile.table_name;
 19       dbms_output.put_line('sSQL : ' || sSQL);
 20        OPEN outCursor FOR sSQL;
 21        LOOP
 22             -- lecture initiale
 23          FETCH outCursor INTO v_1;
 24                 dbms_output.put_line('Avant bfile_exists');
 25                 begin
 26                 bfile_exists := DBMS_LOB.FILEEXISTS( v_1 ) = 1;
 27                             EXCEPTION
 28                     WHEN DBMS_LOB.NOEXIST_DIRECTORY THEN
 29                                      dbms_output.put_line('>>>>> Directory does not exist !');
 30                     WHEN DBMS_LOB.NOPRIV_DIRECTORY THEN
 31                                      dbms_output.put_line('>>>>> Directory with privs pb  !');
 32                     WHEN DBMS_LOB.INVALID_DIRECTORY THEN
 33                                      dbms_output.put_line('>>>>> Invalid Directory !');
 34                             WHEN OTHERS THEN
 35                                     dbms_output.put_line('Milieu : Erreur inattendue : ' || SQLERRM);
 36                                     RAISE;
 37                 end;
 38                 dbms_output.put_line('Apres bfile_exists');
 39                 IF bfile_exists
 40                 THEN
 41                             dbms_lob.filegetname(v_1, dir_alias, name);
 42                             dbms_output.put_line('Table : ' || boucle_find_bfile.table_name || ' / dir_alias : ' || dir_alias || ' / name : ' || name );
 43                 END IF;
 44             EXIT WHEN outCursor%NOTFOUND;
 45             -- lecture suivante
 46             EXIT WHEN outCursor%NOTFOUND;
 47        END LOOP;
 48        CLOSE outCursor;
 49  end loop; -- fin curseur find_bfile
 50        EXCEPTION
 51     WHEN DBMS_LOB.NOEXIST_DIRECTORY THEN
 52                                      dbms_output.put_line('>>>>> Directory does not exist !');
 53     WHEN DBMS_LOB.NOPRIV_DIRECTORY THEN
 54                                      dbms_output.put_line('>>>>> Directory with privs pb  !');
 55     WHEN DBMS_LOB.INVALID_DIRECTORY THEN
 56                                      dbms_output.put_line('>>>>> Invalid Directory !');
 57             WHEN OTHERS THEN
 58                                     dbms_output.put_line('Fin ; Erreur inattendue : ' || SQLERRM);
 59                                     RAISE;
 60* end;

A hint : I have many "bfilename(NULL)" in my LOB type column. I know I do not use the DBMS_LOB.FILEEXISTS function correctly.

How can I do ?

Thanks in advance

Jean-michel A., Nemours, FRANCE

RDBMS : Oracle 11G on Linux with ASM

Answer 1

First you must fix command

EXIT WHEN outCursor%NOTFOUND;

because it should be located right below

FETCH outCursor INTO v_1;

to finish loop correctly and avoid one invalid iteration:

...
OPEN outCursor FOR sSQL;
LOOP
  -- lecture initiale
  FETCH outCursor INTO v_1;
  EXIT WHEN outCursor%NOTFOUND;   -- RIGHT HERE
  dbms_output.put_line('Avant bfile_exists');
  begin
    bfile_exists := DBMS_LOB.FILEEXISTS(v_1) = 1;
  EXCEPTION
    WHEN DBMS_LOB.NOEXIST_DIRECTORY THEN
      dbms_output.put_line('>>>>> Directory does not exist !');
    WHEN DBMS_LOB.NOPRIV_DIRECTORY THEN
      dbms_output.put_line('>>>>> Directory with privs pb  !');
    WHEN DBMS_LOB.INVALID_DIRECTORY THEN
      dbms_output.put_line('>>>>> Invalid Directory !');
    WHEN OTHERS THEN
      dbms_output.put_line('Milieu : Erreur inattendue : ' || SQLERRM);
      RAISE;
  end;
  dbms_output.put_line('Apres bfile_exists');
  IF bfile_exists THEN
    dbms_lob.filegetname(v_1, dir_alias, name);
    dbms_output.put_line('Table : ' || boucle_find_bfile.table_name ||
                         ' / dir_alias : ' || dir_alias || ' / name : ' || name);
  END IF;
  -- EXIT WHEN outCursor%NOTFOUND; not here :/
  -- lecture suivante 
  -- EXIT WHEN outCursor%NOTFOUND; here neither :/
END LOOP;
CLOSE outCursor;
...

About invalid LOB locator: When BFILE columns value are null then DBMS_LOB.FILEEXISTS will throw exception ORA-22275: invalid LOB locator specified. A way to solve the problem is catching this into an exception/when block by ora-code. You can init/declare an exception via pragma as below and catch it later:

declare
  invalid_lob_loc exception; -- naming my custom exc as 'invalid_lob_loc'
  pragma exception_init(invalid_lob_loc, -22275); -- ORA: 22275
begin
  ...
  begin
    ...
    -- this moment exception will be thrown if BFILE column value is null
    bfile_exists = DBMS_LOB.FILEEXISTS(v_1) = 1;  
    ...
  exception
    when invalid_lob_loc then -- catching it
      -- set that file does not exists
      bfile_exists = false;
  end;
  ...
end;

Althought, if BFILE column is not null but file does not exists anymore in filesystem then DBMS_LOB.FILEEXISTS will return false and everything will flow as planned :)