Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Question Unanswered: SQL Error ORA-22285 with DBMS_LOB

    Hi,

    I want to know if a file exists or not.
    Before I use UTLFILE.FOPEN with UTL_FILE_DIR parameter, it was OK.

    Now i want to try the same with DIRECTORY and BFILE command.
    I work with Oracle 9.2.0.1.

    1) First of all I create my directory :
    create or replace directory mydir as 'D:\TEST\';

    2) I have a table 'mytable' with a BFILE columns 'myfile'.
    SO I update this table :
    update mytable set myfile = bfilename('mydir','test.jpg') where myId=1;
    of course, the file test.jpg is in directory d:\test

    3)After I want to try if the file exists :
    declare
    theFile BFILE;
    begin
    select myFile into theFile from mytable where myId=1;
    if (DBMS_LOB.FILEEXISTS(theFile) !=0) then
    dbms_output.put_line('OK');
    else
    dbms_output.put_line('KO');
    end if;
    end;

    Always I have this SQL error :
    ORA-22285 non-existent directory or file for string operation

    I don't understand why ???
    Maybe i forget a rights ?

    thanks for your help

    Philippe
    Last edited by philwv; 04-28-04 at 04:49.

  2. #2
    Join Date
    Apr 2004
    Posts
    2
    The error cause is

    Error: ORA-22285 Text: non-existent directory or file for %s operation --------------------------------------------------------------------------- Cause: Attempted to access a directory that does not exist, or attempted to access a file in a directory that does not exist.
    Action: Ensure that a system object corresponding to the specified directory exists in the database dictionary, or make sure the name is correct.

    Check the file is exist in the path u methined.

    For example, the following statement associates an alias, AMY_ALIAS,
    with the path e:\jonathan\oracle_bible\amy_files:

    CREATE DIRECTORY AMY_ALIAS
    AS ‘E:\JONATHAN\ORACLE_BIBLE\AMY_FILES’;

    You must have the CREATE ANY DIRECTORY system privilege to create a directory.Also, creating a directory in Oracle doesn’t do anything at the operating-systemlevel. You must create the corresponding operating system directory yourself.

    For users to be able to use the directory, you have to grant them read access.
    The following grant gives the user AMY access to the AMY_ALIAS directory:
    GRANT READ ON DIRECTORY AMY_ALIAS TO AMY;

    R. Ganesh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •