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

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. :D

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.

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.

Use the following to create a function or procedure:

Quote:

---

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).

:D

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');

. . . one should add that all the above refer to the server. AFAIK, there's no general way to look at a client C: drive.

Quote:

---

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.

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,

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.

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.

You will need execute privilege for DBMS_LOB and you will need the privilege for CREATE ANY DIRECTORY. But this should help you along.

Code:

---

CREATE DIRECTORY myfolder AS 'c:\myfolder\test_dat.txt';

declare
  bad_file        text_io.file_type;
  v_file_handle  BFILE;
begin
  v_file_handle := bfilename( myfolder, 'test_dat.txt');
  IF DBMS_LOB.FILEEXISTS(v_file_handle) = 1
  THEN
    bad_file := text_io.fopen('c:\myfoldr\test_bad.txt','R');
    HOST('C:\WINDOWS\notepad.exe c:\myfolder\test_dat.txt');
  ELSE
    -- let the process know that the file doesn't exist!!!
  END IF;
exception
 when others then
  null;
end;

---