Forums | Aggregator | Books | Industry News | Firefox Plugins | Social | Links


File Handling From PL/SQL - Perform basic file manipulation from PL/SQL using this simple API.

Chris F said...

Talk about complete! Thanks a million for saving me hours of work this afternoon.

prajakta said...

this is very eassy to understand

John T said...

That is so cool, just what I was looking for.
Thank you for sharing.

Motasem said...

That is very impressive,
but the question is how can i write to file?

thank you very much

Tim... said...


Oracle reads and writes files using th UTL_FILE package.

If you have any questions about UTL_FILE, please ask them on the forum.



Motasem said...

thanks for replay,

i know how to use utl_file package,
but i need to use java classes to write to xml file.

all classes are worked with me but for the .txt files only

Hindy said...

Is there any way to use these functions in the file_api package within an sql query?

Tim... said...


Questions in the forum, not in the comments!

Open a forum thread and tell me the type of thing you want to do and we'll discuss it there.



Sander said...

Thanks a million !!
I've been looking like crazy for a functionality in PL/SQL which simply lists the files in a directory. This is the only easy option I found !!
Thanks again !

Santosh said...

This is really useful..!! Thanks a million Tim....

daih said...

Hi, Tim:

I tried your code, but it didn't work on my side. My email is Would you please email me to help me out? Thanks.

Slavo said...

I've been also looking for a functionality to list the files in a directory. And finnaly found !! :)
Thanks !!!!

dharmaraj said...

it is very helpful for me. But the following query not working some time.It throw ora-24345 error. Mostly the number of files high.

select file_api.list('Directory_path') from dual

For this, i used StringBuffer method instead of String. But still it throw error. i found the reason why it throw error. because of file_api.list function return varchar2 value. If i change that value varchar2 to clob then it will throw inconsistent data type error.
Please help for this.
please mail

Claudio said...

Hi All,

I have just one problem with this impressive solution.

In the list method, the variable returned is a string and it is fixed to 32767 bytes. My list of files is too big, do you know how can I solve this? Is there any way or workaround?

Tim... said...


You would have to rewrite the list function to append the data into a CLOB type, rather than a string.



Emito said...

excelent!!! tks!!!

Dan Ismael said...

Hi Tim, i did the procedure without an error, but when i execute any sentense always appear 0 "canRead : 0
copy : 0
isFile : 0"

Tim... said...


The files must be present and Oracle must have the correct OS permissions to read/write them. In addition, you must grant the JVM permission to access the file system.

If you have any more trouble, post it on my forum and I'll take a proper look at it.



Sreekanth said...

Hi Tim,
Always output is coming 0.

How can i get output

Can u plz tel me

Tim... said...


Open a thread on the forum and we'll discuss it. Chances are it is a permissions issue.



Andreas said...

Really great work! Much appreciated.

Lauri said...

A very good work. Compliments!!

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

Add your comments here.
(max 400 chars - plain text)