DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Check file on c:\drive

  1. #1
    Join Date
    Jul 2001
    Posts
    334

    Check file on c:\drive

    Hi,
    Question, How I can check in pl/sql if the file test.txt exist on my
    C:\myfolder\test.txt

    Thanks in advance

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    There are a couple ways to do this.

    One try to open the file and have an error handler handle the error when the file doesn't exist. Not quite what you were looking for.

    the second option is a little off the beaten path DBMS_LOB has a function called FILEEXISTS. It only works in 9i. You need to create a directory in the database:

    Code:
    CREATE DIRECTORY fred AS '/home/fred/log';
    The file would need to be in '/home/fred/log' and you would use the function to see if it exists. You can at the same time load it into a clob if you like, since you have done most of the work.

    You can also write a 'C' function that will do this and integrate the function into your database.

    I'm not aware of an easier solution.

  3. #3
    Join Date
    Jul 2001
    Posts
    334
    One try to open the file and have an error handler handle the error when the file doesn't exist. Not quite what you were looking for.
    ---------

    Hi,
    can you give me a code example how it works?

    Thanks.

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492
    Use the following to create a function or procedure:

    Declare
    Lr_MyFil UTL_FILE.File_Type;
    Begin
    Lr_MyFil := utl_file.fopen('C:\myfolder\','test.txt','r');
    Dbms_Output.Put_Line('File Exists.');
    --<< File exists >>--
    Exception
    When Utl_File.Invalid_Path Then
    Dbms_Output.Put_Line('File Does Not Exist.');
    --Raise_Application_Error(-20100,'Invalid Path');
    When Others Then
    Utl_File.Fclose(Lr_Myfil);
    Dbms_Output.Put_Line('File Error:');
    Dbms_Output.Put_Line(Sqlerrm);
    End;
    /
    NOTE: I do not know if the las '\' in the path is required or not in windows (I work w/unix).

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492
    Ooops, apparently the back slash '\\' is the escape character on the forum editor, therefore the note should read:

    NOTE: I do not know if the last '\\'(backslash) in the path is required or not in windows

    And the open statement would be:

    Lr_MyFil := utl_file.fopen('C:\myfolder\','test.txt','r');
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    . . . one should add that all the above refer to the server. AFAIK, there's no general way to look at a client C: drive.

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by DaPi
    . . . one should add that all the above refer to the server. AFAIK, there's no general way to look at a client C: drive.
    That's a good point. But if you post the question to a dba group, you get a bunch of responses on how to do it in the database.

    Perhaps Aph should specify exactly what he meant with his question.

  8. #8
    Join Date
    Jul 2001
    Posts
    334
    Hi everybody,
    What exactly I am trying is I am invoking sqlldr through d2k form using HOST(); in addition I have to invoke *.bad or *dsc file in case data file is incorrect. In this case I want to check first which file has been created/not created on c:\.... depends, so before I invoke notepad to see the *dsc or *bad file I simply want to verify if the file exist then execute notepad else not.


    1.server database 7.3
    2.data file, control file on clint. c:\....\..
    3.login to database in the form.
    4.invoke sqlldr using HOST command
    5.load the server table
    6.now in case some thing wrong in the data file
    7.need to see the *dsc or *bad file by invoking notepad
    (but before I invoke *bad file in notepad, I want to check if really there is a bad file).

    every this is ok and perfect till step 6. I am still working on step 7.


    Hope that clear
    Thanks,

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Are you using Oracle forms? So you are loading from the client? I'm not a Forms developer, but maybe there is a way to see if a file exists on a client using a function in Oracle Forms.

    If you can't come up with anything else You can either write a VB/C program or take a look at WinBatch.

  10. #10
    Join Date
    Jul 2001
    Posts
    334
    Here is the code to check the file exist or not which is generated by sqlldr.



    declare
    bad_file text_io.file_type;
    begin
    bad_file := text_io.fopen('c:\myfoldr\test_bad.txt','R');
    HOST('C:\WINDOWS\notepad.exe c:\myfolder\test_dat.txt');
    exception
    when others then
    null;
    end;


    Thanks for your help and time.

Posting Permissions

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


Click Here to Expand Forum to Full Width