How to get file name from a directory

[kllick123]

hi,

I had a problem to find all the filenames which are in
a directory on the server where the oracle is installed.

If I would get the filenames, I can open a cussor, fetch
the filename 1 by 1 and process the file using UTL_FILE.

Hope for an positive response....


Arun Tayal

 

[rcurva]

I don't quite understand your requirement. Could you give an example? Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[kllick123]

Hi,

The full problem is as follows:

We are getting files from different organisation in our
one of the machine through VSAT in a particular machine.

These files are Plain text files containing data of
our application where feilds separated by a delimeter.

These files are loaded into oracle database / tables
using UTL_FILE package.

Loading process today is a manual process because we
don't know the file name which we had received from that
organisation.

Now we want to automate this process by reading the
filename from that directory and then load the data
into the database/table using UTL_FILE package.

If anyone can help us to know the filenames of all the
files which are present in a particular directory in
PL/SQL, we would be able to automate this process.

Hope for an positive response....


Arun Tayal

 

[rcurva]

My assumption here is you are already using UTL_FILE in your loading, perhaps to a staging table right? I am just curious why you didn't use sqlloader for this purpose.

Anyway, if you are using Unix environment, you can create a script to do what you want. Correct my logic if wrong;

cd <directory>
if [ -s <filename> ]; then
sqlplus usr/pwd <<!
-- your loading script here.
!
fi

Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War

 

[kllick123]

Hi,

Thanks for your Help.

But I was searching for a way where I can directly
getting the filename in PL/SQL Block rather than a
UNIX/DOS script.


Using UTL_FILE insted of Sqlloder is because of
the conditions I have to impose on the text file loading
procedure which cann't be handeled by Sqlloder.


I have heard that there if a package UTL_SMTP / UTL_TCP
in oracle through which we can get this done.

But Neither I had these packages loaded in my database
nor I have any more information about it.

Can anyone help me on this.....


Thanks once again


Arun Tayal