Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2003
    Location
    Chicago/Illinois
    Posts
    5

    Unanswered: ORA-22288 FILEOPEN Failed

    I am trying to open a file on a local directory that is shared on a network. When I do so using the regular file name "C:\...", the file opens without any trouble. When I do so using the UNC path, I get an error message, which doesn't say that it doesn't exist, yet something else. I'm logged on to the machine as the administrator of the network, so I definitely have permissions to the shared path, and I have Oracle logged on to Windows 2000 as the administrator as well.

    Here is the script I'm using:

    PIndexerFileName:='IXtest.txt';
    PIndexerFileDir:='\\srvr0x2k\images';
    EXECUTE IMMEDIATE ('CREATE OR REPLACE DIRECTORY INDEXER_DIR AS '''||PIndexerFileDir||'''');
    SrcFile:=BFILENAME('INDEXER_DIR', PIndexerFileName);
    DBMS_LOB.FileOpen(SrcFile,DBMS_LOB.FILE_READONLY);
    Every time I executed, I get the following error message:

    SQL> EXEC RRIXtest('\\srvr0x2k\IMAGES\IXTEST.txt');
    BEGIN RRIXtest('\\srvr0x2k\IMAGES\IXTEST.txt'); END;

    *
    ERROR at line 1:
    ORA-22288: file or LOB operation FILEOPEN failed
    ORA-06512: at "SYS.DBMS_LOB", line 504
    ORA-06512: at "VW.RRIXTEST", line 18
    ORA-06512: at line 1
    I scoured the internet for hours yesterday looking for a similar case, but to no avail.

    Could anyone at least an idea of what is going on? I tried many ways to troubleshoot or figure out the problem, but couldn't. I would appreciate any input.

    Thanks.

  2. #2
    Join Date
    Mar 2003
    Location
    Chicago/Illinois
    Posts
    5
    By the way, I opened the SYS.DBMS_LOB file, and found out that it only has 477 lines and not 504 or more. So I wonder where the error on line 504 came from!!!

  3. #3
    Join Date
    Mar 2003
    Location
    Chicago/Illinois
    Posts
    5
    I would appreciate any input, or at least reference to other sites or forums that are helpful.

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    The oracle docs aren't very helpfull with the error message you're getting. If as you say everything is fine when using local paths (C:\..) but only failing with UNC paths then it would point to one of two problems....

    1. The BFILENAME procedure getting confused by a UNC path, I can't find any way of confirming what the resultant path is though.

    2. Oracle is unable to resolve the UNC path, in which case it may be a SQLNet.ora configuration problem?

    Sorry i can't be more definate.

    HTH
    Bill
    Last edited by billm; 04-02-03 at 12:40.

  5. #5
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118
    Originally posted by andyct
    By the way, I opened the SYS.DBMS_LOB file, and found out that it only has 477 lines and not 504 or more. So I wonder where the error on line 504 came from!!!

    That's probaly because the source is wrapped, looking into this...


    Remi




    http://askremi.ora-0000.com
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  6. #6
    Join Date
    Mar 2003
    Location
    Chicago/Illinois
    Posts
    5
    Originally posted by billm
    Hi,

    The oracle docs aren't very helpfull with the error message you're getting. If as you say everything is fine when using local paths (C:\..) but only failing with UNC paths then it would point to one of two problems....

    1. The BFILENAME procedure getting confused by a UNC path, I can't find any way of confirming what the resultant path is though.

    2. Oracle is unable to resolve the UNC path, in which case it may be a SQLNet.ora configuration problem?

    Sorry i can't be more definate.

    HTH
    Bill
    Hey Bill,
    Can you be more specific on the SQLNet.ora paramater that relates to this issue. My Oracle server does login to a domain and the name resolution to the oracle machine is fine.

    Thanks in advance.
    Andy

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Andy,

    Don't know if these specifics will help but ....

    About a year ago I installed an 8.1.7 at a client site, as far as installation goes everything was standard (or so I thought).

    1 year later they're having problems - couldnt get any extproc routines working at all. I remember from installation that the db was say 'x' and the instance was 'y' - but still major extproc connection problems,

    After much umming and ahhing (I'm a developer not a DBA) we worked out the problem. The Oracle installer (very clever beast) has sussed that db name 'x' really meant x.com. The machine was part of a domain named 'x.com' so the installer figured when I said 'x' I really meant 'x.com'.

    It's the only time I've seen it, but it has caused no end of manual listener.ora, tnsnames.ora and sqlnet.ora tuning :-)

    If I remember correctly all was fixed when we commented a sqlnet.ora param called... DEFAULT_DOMAIN or something similar, maybe DEFAULT_DOMAIN_RESOLUTION_ORDER, I can't remember exactly but it should give you the idea.

    Once again I apologise for being so vague, I just felt that from your post that maybe you were hitting similar problems?

    HTH
    Bill

    Incidentally - remi is right, the reason why the error line numbers don't mean anything is because the source is wrapped (read 'encrypted'). That's one of the reasons for wrapping your code, no bugger knows where the error is :-)
    Last edited by billm; 04-10-03 at 21:11.

  8. #8
    Join Date
    Apr 2003
    Posts
    7
    this is what i got from a utility that translates the ora errors

    "ORA-22288: file operation name failed

    Cause:
    The operation attempted on the file failed.

    Action:
    Verify that the file exists and that the necessary privileges are set for
    the specified operation.If the error still persists, report the error to the DBA."
    xexexe

  9. #9
    Join Date
    Mar 2003
    Location
    Chicago/Illinois
    Posts
    5
    Yeah, that's what's puzzling me. The file is available and accessible to me through the network.

    Thanks for the reply anyways.

  10. #10
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Thumbs up

    Oracle doesn't translates the relative paths. You have to map this share on the db server and then user the absolute path:

    PIndexerFileDir:='DRIVE_LETTER\images';

    in your code. The same as LOG_ARCHIVE_DEST, etc.


    Hope that helps,

    clio_usa - OCP - DBA


  11. #11
    Join Date
    Jun 2004
    Location
    Germany
    Posts
    1

    Unhappy

    Hi,

    I found this thread about dbms_lob.fileopen and I find it's usefull to
    discuss this problem again - because I have the same problem and I
    still need a solution.
    During my experiments with Oracle interMedia I tried to solve the problem
    to load documents from another computer into the oracle database.
    I think that it is not unusable to have different machines for different jobs -
    for example file server, db server, web server and so on .
    Ok, in my eyes it has to work to load files from another machine into the
    oracle instance.
    But the only way to load files by using dbms_lob.fileopen and
    dbms_lob.loadfromfile was to store the documents on the same machine.

    The first idea I had was to give the oracle instance the chance to
    authenticate properly against other machines by changing the user of the
    running service. Now the oracle instance and the listener service are
    running under the account 'oracle' and not 'system'.
    On the fileserver now also exists an account 'oracle' with appropriate rights.
    Oracle now should have the required rights to use the fileserver share to
    load documents from there.

    You can test it by using the following code:
    -- execute as sys
    create or replace directory DOCS as '\\FILES\DOCS\';
    grant read on directory DOCS to public;

    -- execute as user
    declare inp_bfile BFILE;
    begin
    inp_bfile := bfilename( 'DOCS', 'Test\test.txt');
    if dbms_lob.fileexists( inp_bfile ) = 1 then
    begin
    dbms_output.put_line( 'Exists!');
    dbms_output.put_line( 'Filesize: '||dbms_lob.getlength( inp_bfile ));
    end;
    else
    dbms_output.put_line( 'doesn''t exist!');
    end if;
    end;
    /

    The result:
    SQL> declare inp_bfile BFILE;
    2 begin
    3 inp_bfile := bfilename( 'DOCS', 'Test\test.txt');
    4 if dbms_lob.fileexists( inp_bfile ) = 1 then
    5 begin
    6 dbms_output.put_line( 'Exists!');
    7 dbms_output.put_line( 'Filesize: '||dbms_lob.getlength( inp_bfile ));
    8 end;
    9 else
    10 dbms_output.put_line( 'doesn''t exist!');
    11 end if;
    12 end;
    13 /
    Exists!
    Filesize: 6

    Checking the existence and length of the file works. This means: oracle
    instance has the required rights to look into this fileserver share.
    Now I want to extend the code with 'fileopen':

    declare inp_bfile BFILE;
    begin
    inp_bfile := bfilename( 'DOCS', 'Test\test.txt');
    if dbms_lob.fileexists( inp_bfile ) = 1 then
    begin
    dbms_output.put_line( 'Exists!');
    dbms_output.put_line( 'Filesize: '||dbms_lob.getlength( inp_bfile ));
    dbms_lob.fileopen( inp_bfile, dbms_lob.file_readonly );
    dbms_lob.fileclose( inp_bfile );
    end;
    else
    dbms_output.put_line( 'doesn''t exist!');
    end if;
    end;
    /

    The result:
    SQL> declare inp_bfile BFILE;
    2 begin
    3 inp_bfile := bfilename( 'DOCS', 'Test\test.txt');
    4 if dbms_lob.fileexists( inp_bfile ) = 1 then
    5 begin
    6 dbms_output.put_line( 'Exists!');
    7 dbms_output.put_line( 'Filesize: '||dbms_lob.getlength( inp_bfile ));
    8 dbms_lob.fileopen( inp_bfile, dbms_lob.file_readonly );
    9 dbms_lob.fileclose( inp_bfile );
    10 end;
    11 else
    12 dbms_output.put_line( 'doesn''t exist!');
    13 end if;
    14 end;
    15 /
    Exists!
    Filesize: 6
    declare inp_bfile BFILE;
    *
    FEHLER in Zeile 1:
    ORA-22288: Datei- oder LOB-Vorgang FILEOPEN nicht erfolgreich
    ORA-06512: in "SYS.DBMS_LOB", Zeile 504
    ORA-06512: in Zeile 8

    This behaviour is documented in Oracle Metalink as Bug 2740478. This (in my
    eyes bug) bug is classified as "closed, not bug". This behaviour is absolutely
    reproducible and occurs since version 9.2.
    I quote:
    "DIAGNOSTIC ANALYSIS:
    --------------------
    it seems, we have problems with dbms_lob.fileopen to interpret the
    UNC notation. We can write into the same directory with utl_file.
    dbms_lob.fileexists also shows that the file is there and dbms_lob.getlength
    can also access the file in the directory. there is no problem in general.
    when using NT file notation instead (eg: c:\tmp instead
    of \\bgulden1\testshare) this works as well. .
    WORKAROUND:
    -----------
    do not use UNC notation "

    OK, this means: the car don't work - don't use the car!

    Using this hint I mapped this fileserver share to a drive letter on the
    db server, replaced the directory object and granted the rights.

    The result:
    SQL> declare inp_bfile BFILE;
    2 begin
    3 inp_bfile := bfilename( 'DOCS', 'Test\test.txt');
    4 if dbms_lob.fileexists( inp_bfile ) = 1 then
    5 begin
    6 dbms_output.put_line( 'Exists!');
    7 dbms_output.put_line( 'Filesize: '||dbms_lob.getlength( inp_bfile ));
    8 end;
    9 else
    10 dbms_output.put_line( 'doesn''t exist!');
    11 end if;
    12 end;
    13 /
    doesn't exist!

    With or without backslash in the path makes no difference. I absolutely don't
    know why this simple case will not work.
    Is there anybody who loaded a file by using dbms_lob.loadfromfile from
    another machine with success ???
    I don't want to copy all files explicitly to the db server to load it into the
    tablespace.

    hope that someone has still ideas ...

    askanier

  12. #12
    Join Date
    Apr 2009
    Posts
    1
    Hi,

    This is happening because you don't have a file called "Test\test.txt" in the directory "DIR_TEMP". You actucally have a file called "test.txt".

    The directory object points to the directory, the next parameter is simply the file name. you should switch to using: inp_bfile := bfilename( 'DOCS', 'test.txt');

    Let me know if this has helped you.

    Regards,
    Suntrupth

Posting Permissions

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