Skip to Main Content
  • Questions
  • reading files in a directory -- how to get a list of available files.

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, Learco.

Asked: July 17, 2000 - 4:36 am UTC

Answered by: Tom Kyte - Last updated: October 21, 2019 - 11:58 am UTC

Category: Database - Version: 8.1.5

Viewed 100K+ times! This question is

Whilst you are here, check out some content from the AskTom team: 18.3 As easy as 1...2...3

You Asked

How do I read files from a certain directory with PL/SQL, without
knowing the exact name ?

My program must interface with another system which puts files in
a directory on the server. UTL_FILE only reads a file when you
know the name of the file, but I don't know the name in advance.
Is it possible to use wildcards (eg. '*') in the name of the file?

The platform is Windows NT and we have the Jserver option.

Thanks.

and we said...

Addenda Sep 2017: The answer below is now out of date. A far easier way to list directory files is with external tables. See

http://www.oracle-developer.net/display.php?id=513

for some nice examples this

============================================
Original Answer:

We cannot do this with PLSQL directly however, using Java (or a C extproc) we can do this pretty easily.

The interface I came up with uses a global temporary table which will "lose" its rows every time you commit. You'll call a stored procedure providing a DIRECTORY to scan and I'll put a list of all of the files that are in that directory into this temp table. If you want to "filter" the files (eg: only interested in *.txt files), you'll use SQL "select * from dir_list where filename like '%.txt'" to do so.

The implementation is:

ops$tkyte@8i> GRANT JAVAUSERPRIV to ops$tkyte
2 /

Grant succeeded.

That grant must be given to the owner of the procedure.. Allows them to read directories.

ops$tkyte@8i> create global temporary table DIR_LIST
2 ( filename varchar2(255) )
3 on commit delete rows
4 /

Table created.

ops$tkyte@8i> create or replace
2 and compile java source named "DirList"
3 as
4 import java.io.*;
5 import java.sql.*;
6
7 public class DirList
8 {
9 public static void getList(String directory)
10 throws SQLException
11 {
12 File path = new File( directory );
13 String[] list = path.list();
14 String element;
15
16 for(int i = 0; i < list.length; i++)
17 {
18 element = list[i];
19 #sql { INSERT INTO DIR_LIST (FILENAME)
20 VALUES (:element) };
21 }
22 }
23
24 }
25 /

Java created.

ops$tkyte@8i>
ops$tkyte@8i> create or replace
2 procedure get_dir_list( p_directory in varchar2 )
3 as language java
4 name 'DirList.getList( java.lang.String )';
5 /

Procedure created.

ops$tkyte@8i>
ops$tkyte@8i> exec get_dir_list( '/tmp' );

PL/SQL procedure successfully completed.

ops$tkyte@8i> select * from dir_list where rownum < 5;

FILENAME
------------------------------------------------------
data.dat
.rpc_door
.pcmcia
ps_data

And thats it...

and you rated our response

  (159 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

reading files in a directory

July 09, 2001 - 7:50 am UTC

Reviewer: Marcos from Netherlands

Better option than I came up with
Thanks

Great explanation !

February 26, 2002 - 4:28 pm UTC

Reviewer: Sven Bleckwedel from Santos, SP/Brazil

Hi,

Tom, tks for your valuable explanation about this feature, using Java and the bit tricky use of "global temporary table DIR_LIST...on commit delete rows" !  Suppose that i had created these procedures in one schema (and the init.ora parameter "utl_file_dir" was configured properly).  How can the other users use it ?  I had granted "execute on get_dir_list" to another user and received the following error:

SQL> grant execute on get_dir_list to scott;

Grant succeeded.

SQL> connect scott
Enter password: *****
Connected.
SQL> exec get_dir_list( 'C:\Temp\Extract' );
BEGIN get_dir_list( 'C:\Temp\Extract' ); END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'GET_DIR_LIST' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> desc dir_list
ERROR:
ORA-04043: object dir_list does not exist

Could you explain my mistake ?  I think that the access to this table is inherited by the procedure, but...

Tks in adv,
Sven
 

Reviewing steps...

February 27, 2002 - 5:41 pm UTC

Reviewer: Sven Bleckwedel from Santos, SP/Brazil

Hi,

Excuse me, but i had found the reason of my mistake...

SQL> connect system
Enter password:
Connected.
SQL> grant select on dir_list to scott;

Grant succeeded.

SQL> connect scott
Enter password:
Connected.
SQL> desc system.dir_list;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------

 FILENAME                                           VARCHAR2(255)

SQL> exec system.get_dir_list( 'C:\Temp\Extract' );

PL/SQL procedure successfully completed.

SQL> desc system.dir_list;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------

 FILENAME                                           VARCHAR2(255)

SQL> select * from system.dir_list;

FILENAME
-----------------------------------------------------------------

LOG_ERRO.CDX
LOG_ERRO.DBF

SQL>

I made this test in one test instance only and i know the security problems that can arise when giving to another users the ability to use procedures and tables from another user...

Rgds,
Sven
 

Extremely helpful!

March 06, 2002 - 2:45 pm UTC

Reviewer: Mike Jones from Columbia, MD USA

Helpful, easy and worked first time in 8.1.6.
Great initial exposure to Java in the database.

Great! Helped me solve one of my problems

May 07, 2002 - 9:11 am UTC

Reviewer: Yogeeraj from Mauritius

Hi,

Helped me solve one of my problems where i had to query the existence of a file on the OS in one of my applications.

Helped me also build a list of files from several directories.

(only missing part on which i have to investigate is how to include the relative path to each filename.)

I can also understand that files must be residing on the same box as the database server.

The first java procedure in my database ;)
thanks a lot.

Best Regards
Yogeeraj

NullPointerException

May 09, 2002 - 11:38 pm UTC

Reviewer: Charlie

Hi Tom,

I followed the exact procedure you listed here and I know "c:\temp" folder exists on the database server.  Why do I get this NullPointerException?  Any clue?

Thanks,

SQL> exec get_dir_list('c:\temp');
BEGIN get_dir_list('c:\temp'); END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException
ORA-06512: at "TU1.GET_DIR_LIST", line 0
ORA-06512: at line 1 

Tom Kyte

Followup  

May 10, 2002 - 6:44 am UTC

version and all?

version

May 10, 2002 - 10:49 am UTC

Reviewer: Charlie

Followup:
version and all?
--------------------
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

OS: Windows2000/Pro SP2

Thanks,

Tom Kyte

Followup  

May 10, 2002 - 4:31 pm UTC

Sorry but the only way I can reproduce this is when the directory in fact DOES NOT exist:

a@ORA817DEV.US.ORACLE.COM> @connect /
a@ORA817DEV.US.ORACLE.COM> set termout off
ops$tkyte@ORA817DEV.US.ORACLE.COM> REM GET afiedt.buf NOLIST
ops$tkyte@ORA817DEV.US.ORACLE.COM> set termout on
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop user a cascade;

User dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> grant create session, create table, create procedure to a identified by a;

Grant succeeded.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_java.grant_permission( 'A', 'java.io.FilePermission','/tmp','read' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_java.grant_permission( 'A', 'java.io.FilePermission','/tmp_NOT','read' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> @connect a/a
ops$tkyte@ORA817DEV.US.ORACLE.COM> set termout off
a@ORA817DEV.US.ORACLE.COM> REM GET afiedt.buf NOLIST
a@ORA817DEV.US.ORACLE.COM> set termout on
a@ORA817DEV.US.ORACLE.COM> 
a@ORA817DEV.US.ORACLE.COM> 
a@ORA817DEV.US.ORACLE.COM> create global temporary table DIR_LIST
  2  ( filename varchar2(255) )
  3  on commit delete rows
  4  /

Table created.

a@ORA817DEV.US.ORACLE.COM> 
a@ORA817DEV.US.ORACLE.COM> 
a@ORA817DEV.US.ORACLE.COM> create or replace
  2     and compile java source named "DirList"
  3  as
  4  import java.io.*;
  5  import java.sql.*;
  6  
  7  public class DirList
  8  {
  9  public static void getList(String directory)
 10                     throws SQLException
 11  {
 12      File path = new File( directory );
 13      String[] list = path.list();
 14      String element;
 15  
 16      for(int i = 0; i < list.length; i++)
 17      {
 18          element = list[i];
 19          #sql { INSERT INTO DIR_LIST (FILENAME)
 20                 VALUES (:element) };
 21      }
 22  }
 23  
 24  }
 25  /

Java created.

a@ORA817DEV.US.ORACLE.COM> 
a@ORA817DEV.US.ORACLE.COM> create or replace
  2  procedure get_dir_list( p_directory in varchar2 )
  3  as language java
  4  name 'DirList.getList( java.lang.String )';
  5  /

Procedure created.

a@ORA817DEV.US.ORACLE.COM> 
a@ORA817DEV.US.ORACLE.COM> 
a@ORA817DEV.US.ORACLE.COM> exec get_dir_list( '/tmp' );

PL/SQL procedure successfully completed.

a@ORA817DEV.US.ORACLE.COM> select * from dir_list where rownum < 5;

FILENAME
-----------------------------------------------------------------------------------------------------------------------------------
lost+found
.rpc_door
ps_data
.pcmcia

a@ORA817DEV.US.ORACLE.COM> 
a@ORA817DEV.US.ORACLE.COM> 
a@ORA817DEV.US.ORACLE.COM> exec get_dir_list( '/tmp_NOT' );
BEGIN get_dir_list( '/tmp_NOT' ); END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException
ORA-06512: at "A.GET_DIR_LIST", line 0
ORA-06512: at line 1


a@ORA817DEV.US.ORACLE.COM> 


 

It works fine

May 10, 2002 - 3:03 pm UTC

Reviewer: Amitabha Bhaumik from Boston,MA USA

It works GREAT but when I created the java procedure
instead of saying

"java created"

it said

"Operation 160 succeeded."

any idea.

Tom Kyte

Followup  

May 10, 2002 - 4:36 pm UTC

You are using a pre-8i sqlplus version. You should upgrade the client software to take advantage of the database new features correctly.

Solution from Heart

May 11, 2002 - 4:47 am UTC

Reviewer: Parag from India

Too good man.
I think this is one of the "THE BEST" site for Oracle related Issues/Quesries/Problems.

One Suggestion Tom , Can you incorporate following changes in your site.

1. Like Other site , as and when new qustsion is posted on your site , can you give ALERT email to whole asktom communities with the Question Link.

2. Can your site , send a ORA (Oracle) TIP OF THE DAY , to all registered asktom users. The question will be given with multi choice answere selection.

3. Can you intoduced "RATING SYSTEM". Say those who are posting maximum number of questions on your site which you are going to publish on your site , which really carry some weight.

4. Is it possibel to provide " ON LINE CHAT WITH Mr.TOM " on specified date and time regarding oracle topics.

All this are just thought's .

Butanyway Thank a lot for offering such nice services to ORA COMMUNITIES.

Regards

Yea, Yea !! I got one too!

July 03, 2002 - 1:37 pm UTC

Reviewer: John from MA

#5. How bout you stop over my place of business, fix my sloppy code (not as bad as it use to be, thanks for that!)
teach my DBA the importance of access to the V$ views so we can better do our job,ooh! actually, since your taking suggestions and have all this time on your hands, how bout rewritting the 30,000 some odd manuals that oracle has into an easier, beer on the beach kind of read.

Love the site, Tom and thanks for all the help.
(OK OK how bout just the books)

Documentation?

October 07, 2002 - 5:41 pm UTC

Reviewer: Robert from Memphis, USA

Tom,

Can you tell us where to find documentation on the specific JAVA functions (objects?)...
...Specifically the 'FILE' (function, object, 'thing'?), from your example.
I am wanting to investigate a way to return the privilege and datestamp info from the UNIX box, similar to what you would see with a 'ls -l' command.

Thanks,

Robert.


Tom Kyte

Followup  

October 07, 2002 - 6:31 pm UTC

</code> http://java.sun.com/ <code>

under documentation. Takes about a year to read it all. Java isn't "small".

Need your suggestion

October 08, 2002 - 6:50 am UTC

Reviewer: Daniel from India

Hi Tom,
I get comma-separated values (e.g. 3,4,5,6,) as input. I parse these values, with using some oracle functions. After reading this Java source example, thought of using java source to parse this data. Because I read that parsing in java program is efficient than parsing in pl/sql. So which will be the efficient way? Using oracle function or using java source? Please, I need your suggestion

Thanks in advance

daniel

Tom Kyte

Followup  

October 08, 2002 - 7:44 am UTC

I'll race your java code with my plsql code -- we'll see who wins.

Parsing a comma delimited string is TRIVIAL in plsql. Use the language you feel comfortable in.

MalFormedInputException

October 08, 2002 - 7:06 am UTC

Reviewer: A reader

Hi Tom,

A Similar Program that i had written and tried to upload into the Oracle Database Using the Create Or Replace Java Stmt. I received a MalFormedInputException.

My Database CharSet is UTF8.

Contacted support but did not get a convincing answer on this.

Regards,
Ganesh R

-------------------Code of the Java Programm---------------

Create Or Replace and compile Java Source Named FileHandling
As
import java.io.*;

class FileHandling extends Object implements FileFilter
{
public FileHandling(String dir, String newFile) throws IOException
{
openFiles(dir, newFile);
}

public void openFiles(String dir, String fileNew) throws IOException
{
File dirFile;
File sqlFile;
File newFile;
File dirListing[];

FileWriter fw;
FileReader fr;

dirFile=new File(dir);

newFile = new File(fileNew);
fw=new FileWriter(newFile.toString(),false);

int i=0;

if (dirFile.isDirectory())
{
System.out.println("Parsing Directory... " + dirFile + " \n");
dirListing = dirFile.listFiles(this);
for (i = 0;i< dirListing.length;i++)
{
if (dirListing[0].isDirectory())
{
openFiles(dirListing[i].toString(), fileNew);
}
else
{
fr=new FileReader(dirListing[i]);

System.out.print("Writing File " + dirListing[i] + " ");
if (!appendFiles(fw,fr))
{
System.out.println("Unable to Write File : " + dirListing[i] + " \t\t");
}
else
{
//System.out.println("File " + dirListing[i] + " Successfully Written");
}

}
}
System.err.println("\nRead " + i + " Files/Directory from Directory " + dirFile);
}
else
{
System.err.println("Uanble To Parse Directory... " + dirFile);
}
}

public boolean accept(File testFile)
{
if (testFile.isDirectory())
{
return true;
}
else if (testFile.isFile())
{
if (testFile.getName().endsWith(".sql"))
{
return true;
}
}
return false;
}

public boolean appendFiles(FileWriter fw, FileReader fr) throws IOException
{
if (fr.ready())
{
int i = 0;
int x=0;
do
{
i=fr.read();
x++;
if ((x == 16384))
{
x=0;
System.err.print(".");
}
if (i != -1)
fw.write(i);
}while(i != -1);
fw.write("\n".toCharArray());
fw.write("/*".toCharArray());
for(int j=0;j<118;j++)
{
fw.write('*');
}
fw.write("*/".toCharArray());
fw.write("\n".toCharArray());
fw.flush();
fr.close();
System.err.println("");
return true;
}
else
{
System.err.println("");
return false;
}
}

public static void ConcatNow(String newFile, String dirFile) throws IOException
{
new FileHandling(dirFile,newFile);
}
}

------------------------End Of Java Code-------------------

Tom Kyte

Followup  

October 08, 2002 - 7:58 am UTC

I didn't have any issues running your code in Oracle 9iR1 with UTF8. It ran verbaitim.


You didn't give me the error message, version, platform, tar# from support, etc etc etc... so thats all I can say.

Sorry About That ...

October 08, 2002 - 8:03 am UTC

Reviewer: A reader

Here is the error. It is an error when i try to run it.

Ganesh@Db9i.Gtfsgulf.Com on 9.0.1.3.1> ED
Wrote file afiedt.buf

1 cREATE OR REPLACE Procedure ConcatFile(newFile Varchar2, dirFile Varchar2)
2 As
3* Language Java Name 'FileHandling.ConcatNow(java.lang.String,java.lang.String)';
Ganesh@Db9i.Gtfsgulf.Com on 9.0.1.3.1> /

Procedure created.

Ganesh@Db9i.Gtfsgulf.Com on 9.0.1.3.1> eXEC CONCATfILE('a.TXT','D:\iFLEX');
BEGIN CONCATfILE('a.TXT','D:\iFLEX'); END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: sun.io.MalformedInputException
ORA-06512: at "GTFSDXB\GANESH.CONCATFILE", line 0
ORA-06512: at line 1


Ganesh@Db9i.Gtfsgulf.Com on 9.0.1.3.1>

Regards,
Ganesh R

Tom Kyte

Followup  

October 08, 2002 - 8:55 am UTC

See -- this is why I always always always want a straight cut and paste.  First you say:

<quote>
A Similar Program that i had written and tried to upload into the Oracle 
Database Using the Create Or Replace Java Stmt. I received a 
MalFormedInputException.
</quote>

which I reasonably took to mean "Hey, I cannot get this to compile -- I get this error "malformedinputexception"...

but lo and behold, it is not a compile time error at ALL!!! its a runtime error...


Ok, run your code like this and debug it (I get a NullPointerException myself).  There is nothing "wrong" at the Oracle end here -- there is something wrong in your code somewhere.  

Use dbms_java so you can see your system.out.printlns and add copious amounts of "debug" statements to see what is causing it to fail, eg:



ops$oracle9i@ORA9IUTF.WORLD> CREATE OR REPLACE Procedure ConcatFile(newFile Varchar2, dirFile Varchar2)
  2  As
  3  Language Java Name 'FileHandling.ConcatNow(java.lang.String,java.lang.String)';
  4  /

Procedure created.


ops$oracle9i@ORA9IUTF.WORLD> exec dbms_java.set_output( 1000000 )
PL/SQL procedure successfully completed.

ops$oracle9i@ORA9IUTF.WORLD> set serveroutput on size 1000000

ops$oracle9i@ORA9IUTF.WORLD> exec concatfile( 'a.txt', '/tmp/' );
Parsing Directory... /tmp
Parsing Directory... /tmp/lost+found
java.lang.NullPointerException
at FileHandling.openFiles(FILEHANDLING:31)
at FileHandling.openFiles(FILEHANDLING:35)
at FileHandling.<init>(FILEHANDLING:7)
at FileHandling.ConcatNow(FILEHANDLING:117)
BEGIN concatfile( 'a.txt', '/tmp/' ); END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException
ORA-06512: at "OPS$ORACLE9I.CONCATFILE", line 0
ORA-06512: at line 1



<b>So, I can see we are dying (in my case) after looping 2 times</b>

After correcting the code to deal with the NULL pointer exception via:

            dirListing = dirFile.listFiles(this);
            for (i = 0;<b>dirListing != null &&</b> i< dirListing.length;i++)
            {
                if (dirListing[0].isDirectory())



I get:

ops$oracle9i@ORA9IUTF.WORLD> exec concatfile( 'a.txt', '/tmp/' );
Parsing Directory... /tmp
Parsing Directory... /tmp/lost+found
Read 0 Files/Directory from Directory /tmp/lost+found
Parsing Directory... /tmp/.rpc_door
Read 0 Files/Directory from Directory /tmp/.rpc_door
Parsing Directory... /tmp/.pcmcia
Read 0 Files/Directory from Directory /tmp/.pcmcia
Parsing Directory... /tmp/.X11-unix
Read 0 Files/Directory from Directory /tmp/.X11-unix
Parsing Directory... /tmp/.X11-pipe
Read 0 Files/Directory from Directory /tmp/.X11-pipe
Uanble To Parse Directory... /tmp/a.sql
Uanble To Parse Directory... /tmp/flat10674.sql
Uanble To Parse Directory... /tmp/x.sql
Uanble To Parse Directory... /tmp/t.sql
Parsing Directory... /tmp/test
Writing File /tmp/test/runall.sql
Read 1 Files/Directory from Directory /tmp/test
Parsing Directory... /tmp/kk
Read 0 Files/Directory from Directory /tmp/kk
Uanble To Parse Directory... /tmp/gork.sql
Uanble To Parse Directory... /tmp/test.sql
Parsing Directory... /tmp/empty
Read 0 Files/Directory from Directory /tmp/empty
Parsing Directory... /tmp/clntst8.7259
Read 0 Files/Directory from Directory /tmp/clntst8.7259
Parsing Directory... /tmp/clntst8.15037
Read 0 Files/Directory from Directory /tmp/clntst8.15037
Parsing Directory... /tmp/clntst8.21730
Read 0 Files/Directory from Directory /tmp/clntst8.21730
Uanble To Parse Directory... /tmp/flat2465.sql
Uanble To Parse Directory... /tmp/flat20540.sql
Uanble To Parse Directory... /tmp/installhelp.sql
Uanble To Parse Directory... /tmp/alter_rbs8i.sql
Parsing Directory... /tmp/clntst8.8455
Read 0 Files/Directory from Directory /tmp/clntst8.8455
Parsing Directory... /tmp/clntst9.22241
Parsing Directory... /tmp/clntst9.22241/objs
Read 0 Files/Directory from Directory /tmp/clntst9.22241/objs
Read 1 Files/Directory from Directory /tmp/clntst9.22241
Parsing Directory... /tmp/.removable
Read 0 Files/Directory from Directory /tmp/.removable
Parsing Directory... /tmp/clntst8.15137
Read 0 Files/Directory from Directory /tmp/clntst8.15137
Uanble To Parse Directory... /tmp/obj.sql
Uanble To Parse Directory... /tmp/xw.sql
Uanble To Parse Directory... /tmp/10565.sql
Uanble To Parse Directory... /tmp/10593.sql
Uanble To Parse Directory... /tmp/10599.sql
Parsing Directory... /tmp/demo
Read 0 Files/Directory from Directory /tmp/demo
Parsing Directory... /tmp/tkyte
Read 0 Files/Directory from Directory /tmp/tkyte
Parsing Directory... /tmp/clntst9.23657
Parsing Directory... /tmp/clntst9.23657/objs
Read 0 Files/Directory from Directory /tmp/clntst9.23657/objs
Read 1 Files/Directory from Directory /tmp/clntst9.23657
Read 33 Files/Directory from Directory /tmp

PL/SQL procedure successfully completed.

so it ran thru OK for me. 

arrange by files created order

October 08, 2002 - 11:37 am UTC

Reviewer: rajesh

hi tom

while displaying is that is possible to obtain
the list in such a manner as
the file created last comes first and so on

thanks in adv

raj

Tom Kyte

Followup  

October 08, 2002 - 12:44 pm UTC

Don't know, I don't program java extensively (just as little as I need). That is more of a "java" question (eg: in java, how can I get the timestamp or can you just feed me the files in sorted order)....


Maybe devtrends.oracle.com can help

Parsing in PLSQL

October 08, 2002 - 5:03 pm UTC

Reviewer: Randy

You threw in that parsing a csv in PLSQL is trivial. I'm not feeling particularly clever today so I'd like to see your method. Even though what I have works (barely), I'm sure yours is better than what I have.

Thanks.

Tom Kyte

Followup  

October 08, 2002 - 5:33 pm UTC

search this site for

str2table




Thanks

October 10, 2002 - 1:16 am UTC

Reviewer: Gururaj Kulkarni from Bangalore,Karnataka, India

Tom,

You said: You are using a pre-8i sqlplus version. You
should upgrade the client software to take advantage of the
database new features correctly.

Could you tell us what are those features ?

Thanks
-Gururaj


Tom Kyte

Followup  

October 10, 2002 - 6:53 am UTC

well, the timing would be one.

Ability to create "new types of things" in sqlplus without sqlplus getting in the way (eg: consider

$ sqlplus scott/tiger@ora817dev

SQL*Plus: Release 8.0.6.0.0 - Production on Thu Oct 10 06:50:01 2002

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

scott@ORA817DEV.US.ORACLE.COM> set echo on
scott@ORA817DEV.US.ORACLE.COM> @rand
scott@ORA817DEV.US.ORACLE.COM> create or replace and compile java source named "RandomNumGenerator"
2 as
3 public class RandomNumGenerator
4 {
5 public static double random( java.lang.Short max )
6 {
7 double rand = Math.random();
create or replace and compile java source named "RandomNumGenerator"
*
ERROR at line 1:
ORA-29536: badly formed source: Encountered "<EOF>" at line 5, column 40.
Was expecting one of:
"instanceof" ...

hundreds of errors chopped


SQLPlus doesn't understand the new commands and chokes on them.

and so on.

Old SQL*Plus version is better for basic stuff!

October 10, 2002 - 10:31 am UTC

Reviewer: Robert from Memphis, USA

Tom, and all,

One thing that the 7.3.4 version of GUI sql*plus has over 8i... is that the old version has a 'Cancel' button so you can stop your query without killing your sql*plus session and sql*plus screen... thus losing all your buffered output.
Why even use GUI sql*plus? ... because its easier to copy/paste (windoze notwithstanding)... and it has the nifty variable selection feature that lets you copy individual columns for a set of rows instead of the entire vertical chunk.

Thanks,

Robert

Tom Kyte

Followup  

October 11, 2002 - 7:31 pm UTC

I find the DOS window trivial to cut and paste from -- I just selected text and hit enter (copy). I just hit "alt space" ep and it pasted.

The DOS window copies with "squares" as well ;)

CTL-C always works in the dos window to cancel a query.

May we following the rabbit trail a little further?

October 22, 2002 - 4:33 pm UTC

Reviewer: Robert from Memphis, USA

Tom,

I respect your opinion so I am considering looking into using the DOS sql*plus program.

I have been able to size the buffer width appropriatly (1000 chars wide so I can issue 'select *' on wide tables), so far so good...

...but it seems a pain to copy and paste.
With the GUI sql*plus you can just do a cntl-c, cntl-v... very simple... but with DOS, you have to go thru the whole rigaramarole of 'marking', copying, then pasting.....

..... Could you please give an example for some of us who may be a little slower to catch on... an actual example of how you 'mark', copy and paste in a DOS sql*plus session...including all keystrokes, mouse-clicks, etc.

Like I said... GUI sql*plus is a snap... swipe with the mouse, cntl-c, cntl-v.....DONE! .......

Your reply would be greatly appreciated!

Thanks,

Robert.

Tom Kyte

Followup  

October 22, 2002 - 7:08 pm UTC

Well, I don't actually have windows installed anywhere anymore so this is from memory.

You can adjust the properties to "smart select" or something like that -- at least on windows NT. It was the first thing I always did to dos windows (it could be made "sticky" so all dos windows would have it)


Then when you clicked and dragged with a mouse - it would select text.

So in a windows NT dos window it was:

o select text
o hit enter -->> selected text in copy buffer

o alt-space
o e
o p

and it would paste it.

RE: Old SQL*Plus version is better for basic stuff!

October 23, 2002 - 4:43 am UTC

Reviewer: Ed from Cheshire, UK

Robert,

You can still cancel queries in newer versions of Windows SQL*Plus by using the menu (File -> Cancel).

Another great thing about Windows SQL*Plus is the facility to copy and paste with a single mouse operation: Select what you want using the left mouse button and dragging - then, whilst the left button is still depressed, click the right button and what you had selected is copied straight to the SQL*Plus command line (this is internal to SQL*Plus - it doesn't copy to the Windows clipboard buffer).

I use this feature dozens of times a day when I want to quickly re-sun a SQL statement that I ran half an hour ago.


Tom Kyte

Followup  

October 23, 2002 - 7:15 am UTC

I just hit the f7 button or arrow up in character mode to get a history of commands myself (well, I used to anyway when I ran windows)



RE: Old SQL*Plus version is better for basic stuff!

October 23, 2002 - 9:56 am UTC

Reviewer: Ed from Cheshire, U.K.

I don't think that's ever worked in Windows SQL*Plus - that's one of the criticisms one of my non-Oracle colleagues has of it. If it works in the DOS version, I'd be tempted to change.

If not Windows then what ?!

October 23, 2002 - 3:28 pm UTC

Reviewer: Robert from Memphis, USA

Tom,

You said you don't have Windows installed anywhere.

Can you please tell us what OS you do use.

Also, If you don't use Windows... what do you do for spreadsheets, word processors, etc.

Thanks,

Robert.

Tom Kyte

Followup  

October 23, 2002 - 5:14 pm UTC

see

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6090133761547

and
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6066234722893 <code>
(search for crossover on that one)


I also use open office (but I need word for authoring with WROX, the comment facility is different between the two -- but 99.999% of the functionality is there)



copy & paste from dos window

October 24, 2002 - 1:40 am UTC

Reviewer: John from San Francisco, CA

Robert,

I usually do the following on XP/2000 (been a while since I've used NT, so the exact wording may have changed) for dos windows.

Start --> Run --> cmd

You should now have a dos window. Right click on the title bar, and select Properties. You want to enable QuickEdit mode (I also adjust the layout properties such as window size--so it's usable). Click OK, and save properties for future windows with same title.

Now you should be able to select text with the left mouse button, click the right mouse button to copy the selected text, another click of the right mb will paste the text into this dos window or any other (or ctrl-v into other apps).

AWESOME!!

November 06, 2002 - 10:53 pm UTC

Reviewer: Russell Hertzberg from Roanoke, VA

This was a great code snippet! I've been looking for code like this for a while...nice, simple and to the point!! Now I can take this and expand it to meet my needs. Thank You so much!

December 19, 2002 - 2:20 pm UTC

Reviewer: A reader

Excellent example!!

can we read directory from machine other then oracle server?

We have requirment to read App server directories from PL/SQL.

Thanks

Tom Kyte

Followup  

December 19, 2002 - 3:11 pm UTC

the directory needs to be mounted on the server in order for the server to read it.

you need to make it accessible to Oracle if you want a stored procedure to read, yes.

Further Java reading

December 20, 2002 - 9:05 am UTC

Reviewer: G Johnson from UK

<advert>
For people asking about further Java reading, I learnt Java from Sun Press' Just Java 2 by Peter Van der Linden. As a DBA I found this book extremely useful for getting enough Java under my belt without feeling like I was about to have to change careers to learn a little something useful!

Paperback - 1113 pages (1 December, 2001)
Prentice Hall PTR; ISBN: 0130320722
</advert>


Great approach.

February 07, 2003 - 1:01 am UTC

Reviewer: Chandra S.Reddy from India

Tom,
You are really great.

This example is highly useful and highly reliable.

Thanks


reading files in a directory -- how to get a list of available files.

April 23, 2003 - 3:45 am UTC

Reviewer: Artur from LITHUANIA

Very Very helpful even for those who do not know java and not very familiar in ORACLE :]
(works great on 9i (9.2.0.2.0))

THANX

Superb!!!

May 13, 2003 - 6:30 am UTC

Reviewer: man2002ua from Ukraine

Tom, you are BEST!

need some help

July 08, 2003 - 7:08 am UTC

Reviewer: binu from (TN)India

i am tring to unzip the zipped file from c:\test_java\ to c:\test
this program executes prefectly in java but when i execute thru PL/SQL
i face some problem (i.e.) it shows "PL/SQL procedure successfully completed."
but no files are unzipped into c:\test.

can u explain where did i go wrong??

create or replace and compile java source
named "unzip_xml"
as
import java.io.*;
import java.util.*;
import java.util.zip.*;
import java.text.*;
import java.lang.*;

class unzip_xml
{
public static void unzip(String zip_name,String Extract_to)
throws Exception
{
try
{
String str;
String xls_file;
int n=0;
ZipEntry ze;
File f_xml=new File(Extract_to);
String xmlfls[]=f_xml.list();
for(int k=0;k<xmlfls.length;k++)
{
File f=new File(Extract_to+xmlfls[k]);
if (!(f.isDirectory()))
f.delete();
}
FileInputStream fis=new FileInputStream(zip_name);
ZipInputStream zis=new ZipInputStream(fis);
while (true)
{
ze=zis.getNextEntry();
if (ze==null)
break;
str=ze.getName();
int x = fis.available();
byte [] rgb = new byte [x];
FileOutputStream fout = new FileOutputStream(Extract_to+get_zip_xml_file(str));
while((n=zis.read(rgb))>-1)
{
fout.write(rgb,0,n);
}
fout.close();
zis.closeEntry();
}
zis.close();
fis.close();
}
catch (Exception e)
{}
}//end of unzip_xml()

public static String get_zip_xml_file(String str)
{
int n=str.indexOf('.');
String file_name="";
for (int j=(n-1);j>=0;j--)
{
if (str.charAt(j)!='/')
file_name=str.charAt(j)+file_name;
else if (str.charAt(j)=='/')
{
file_name=str.substring(j+1,str.length());
break;
}
}
return file_name;
}//end of get_zip_xml_file()
}
/
create or replace procedure proc_unzip_xml(p_zip_name varchar2,p_extract_to varchar2)
as language java name 'unzip_xml.unzip(java.lang.String,java.lang.String)';
/
exec proc_unzip_xml('c:\\\\test_java\\\\xml-55-0001-08072003 9-36-36.zip','c:\\\\test\\\\');

regards
Binu



Tom Kyte

Followup  

July 08, 2003 - 7:44 am UTC

catch (Exception e)
{}


thats a beauty eh? "on error, any error, just kindly ignore it -- pretend it doesn't exist"


You might consider deleting those lines of code -- letting the error, which might be due to \\\\ or lack of privs -- to propagate up.

PLSQL is not Java -- \ is not a "special character", \\ is not \, \\ is \\ in plsql/sql.


(and remember, we catch exceptions IF and ONLY IF we are

a) expecting them
b) can do something about them

else we let them propagate up and out of our code. better to fail with a message then APPEAR to succeed)

need more help

July 08, 2003 - 7:27 am UTC

Reviewer: binu from (TN)India

i tried your first sample program

i get an error as
SP2-0734: unknown command beginning "sql { INSE..." - rest of line ignored.
and compile java source named "DirList"
*
ERROR at line 2:
ORA-29536: badly formed source: Encountered "( :" at line 16, column 8.
Was expecting one of:
";" ...
"++" ...
"--" ...
"." ...
"[" ...
"(" <INTEGER_LITERAL> ...
"(" <FLOATING_POINT_LITERAL> ...
"(" <CHARACTER_LITERAL> ...
"(" <STRING_LITERAL> ...
"(" "true" ...
"(" "false" ...
"(" "null" ...
"(" "this" ...
"(" "super" ...
"(" "(" ...
"(" "new" ...
"(" "void" ...
"(" "boolean" ...
"(" "char" ...
"(" "byte" ...
"(" "short" ...
"(" "int" ...
"(" "long" ...
"(" "float" ...
"(" "double" ...
"(" <IDENTIFIER> ...
"(" "+" ...
"(" "-" ...
"(" "++" ...
"(" "--" ...
"(" "~" ...
"(" "!" ...
"(" "#sql" ...
"(" ")" ...
":" ...
<IDENTIFIER> ...
"=" ...
"*=" ...
"/=" ...
"%=" ...
"+=" ...
"-=" ...
"<<=" ...
">>=" ...
">>>=" ...
"&=" ...
"^=" ...
"|=" ...

Thanking u


Tom Kyte

Followup  

July 08, 2003 - 8:06 am UTC

guess you need to look at your cut and paste and figure out where in your cut and paste you messed up? at least show US your cut and paste -- just like I show you (you don't need to put the entire error message -- but the code is important, you cut and pasted the code wrong)

follow up

July 08, 2003 - 7:29 am UTC

Reviewer: binu from (TN)India

my oracle version is
Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production
PL/SQL Release 8.1.6.0.0 - Production
CORE 8.1.6.0.0 Production
TNS for Linux: Version 8.1.6.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production

Array vs temp table.

July 08, 2003 - 9:51 am UTC

Reviewer: Darren from London, UK

Tom,

you inserted the values into a temporary table (no bulk binds either ;)) .. is there any (dis)advantage of returning this result set in an array rather than inserts into sql tables?



Tom Kyte

Followup  

July 08, 2003 - 11:59 am UTC

figured there would not be hundreds or thousands of directory entries.

I liked the temp table here cause I wanted to write as little java as humanly possible, as fast as I could -- hence insert was easier then messing about with IN OUT collection types.

Matter of preference.

Interacting with the OS -- what to learn?

July 08, 2003 - 11:36 am UTC

Reviewer: Brian from Phoenix

From your book, I take it that Java is the first choice in interacting with the OS. However, the Oracle documentation suggests more than one route for programming in Java.

One requires the client to install software; one does not.

I am not sure what to learn first for using Java in Oracle. I'm okay with SQL now; I'm starting to get a grasp of PL/SQL (I still need to get a better handle on packages -- reminds me a lot of C++). I would like to learn Java in August. But I'm not sure where to start.

I am using JServer 9.2.

The only two reasons why I can see using Java at all is for interacting with the OS and maybe GUI stuff.

Thanks

Tom Kyte

Followup  

July 08, 2003 - 1:12 pm UTC

do you mean java inside the database?

I don't follow you -- you talk about Oracle interacting with the OS which seems to imply java stored procedures....

then you talk about client software? I don't see the connection.




Clarification

July 08, 2003 - 1:48 pm UTC

Reviewer: Brian from Phoenix

Yep, I'm having a problem following me to.

Here's the documentation I am referring to:

</code> http://download-west.oracle.com/docs/cd/B10501_01/java.920/a96656/invokeap.htm#1007092 <code>

It's says to learn to write stored procedures first then to worry about SQLJ and JDBC.

But when I go out to www.bookpool.com and start looking at Java books. The above starts to blend together and the confusion starts.

I read your chapter on java stored procedures, but I don't understand the java. So do I go out and pick up a book on Java 2 and download the stuff from Sun? Or is there a book that discusses Java 2 with Oracle using store procedures?

I'm just confused as how to learn this stuff.

Do I go out and pick up a book on Java 2? Read through it and learn it, and then read the Oracle documentation. Or do I go out and find a Oracle specific book on Java?

I would like to be able to understand your java stored procedures for interacting with the OS and maybe write some GUI stuff later on.

Thanks.


Tom Kyte

Followup  

July 08, 2003 - 2:00 pm UTC

Java is just a programming language, like C or C++.

You can write stored procedures in java.

You can write client side applications (guis or command line programs) in java.

You can write code that runs in an application server in java.

Java is just a language.


You will not be writing j2ee stuff inside the database, you would be doing little stored procedures (only when PLSQL cannot do it, as the most efficient language in the database for manipulating data will be that -- in fact, it would be the most efficient inside OR out actually).


If you want to do little snippets of java, java stored procedures are great. All you need is sqlplus and an editor.

If you want to write full blown java apps -- then you need the client side tools.




clarification

July 10, 2003 - 7:50 am UTC

Reviewer: reader from (Delhi)India

thank u tom as usual u r most helpful for me. but one question about this
this can read the files only in the oracle server but if at all i want
to read the files from my client machine how to go about by??

Sorry if i am wrong

rajeev


Tom Kyte

Followup  

July 10, 2003 - 10:03 am UTC

(hey, your keyboard is suffering some serious failures. It is dropping exceeding important characters, namely "vowels". without vowels -- I feel like I'm reading a cell phone.)


In order to read files on the client, one writes a client side application or one uses the operating system to make the client side files visible to the server.

Otherwise, we'd have what some people might term "a virus" as it could be used to steal any file we wanted..... Oracle cannot just reach out and grab files from a client, the client has to give them to us.

Help With Next Step

September 24, 2003 - 9:20 pm UTC

Reviewer: Rob H. from Boston, MA

What technique would you then use to loop through the file names returned from dir_list... before passing the filename to UTL_FILE.FOPEN for processing?

Tom Kyte

Followup  

September 25, 2003 - 5:15 am UTC

for x in ( select * from dir_list )
loop
... process x.filename ....

Great information

December 01, 2003 - 3:08 pm UTC

Reviewer: Mike from St. Louis

Tom:

The original poster was after a way to feed an O/S directory listing into his/her PL/SQL code. Is an external language call still required given Oracle 9.2 and Oracle directory objects?

Maybe its wishful thinking but since we can CREATE DIRECTORY with full path to the disk location, maybe we can interrogate that directory object (from PL/SQL) to have it yield a file listing (or a listing limited by a file pattern) without having to code external routines. Is that possible?

Thanks,

-Mike


Tom Kyte

Followup  

December 02, 2003 - 8:03 am UTC

still needed to read the contents of a directory, yes.

Date and Time?

December 03, 2003 - 2:40 pm UTC

Reviewer: Mike from St. Louis

Thanks again Tom. Follow up question from a non-Java guy... the Java code and global temp table solution above work great. Does anyone know how to extend the code to also extract the file modification date and size for each file in the directory? -Mike


Does this routine work for Unix

December 10, 2003 - 1:50 pm UTC

Reviewer: Suhail from NY, USA

Tom,


Does this example of creating global table and then a java stored procedure and pl/sql wrapper works for UNIX environment too?

Thanks

Suhail

Tom Kyte

Followup  

December 10, 2003 - 4:17 pm UTC

well, the example was written on unix (linux) the only OS i have installed.

so, yes.

Oracle is Oracle is Oracle is Oracle.

You want the ultimate in portability? do it in the database.

Oracle is Oracle and Miracle too

December 11, 2003 - 8:11 am UTC

Reviewer: Suhail from Albany, USA

Thanks , Oracle is Oracle and Oracle is Miracle too. I love Oracle and its features. I tested this routine on NT and it works OK. On Unix platform I asked my DBA to grant my schema with DBMS_JAVA using
EXEC DBMS_JAVA.GRANT_PERMISSION('MYSCHEMA', 'java.io.FilePermission','/export/stage/data','read,write');

and he said he granted but when I dis exec get_dir_list( '/export/stage/data' ); I got all sorts of Java errors.

I did not ask for GRANT JAVAUSERPRIV to myschema.

Any idea why it snot working on UNIX box.

Thanks Tom




Tom Kyte

Followup  

December 11, 2003 - 8:26 am UTC

i cannot see your terminal from way over here, so no -- i've no idea since I've no clue what the error messages might be trying to tell us!

Sorry I did not paste the error

December 11, 2003 - 9:28 am UTC

Reviewer: Suhail from Albany, NY

Here are my error listings,
SQL> exec get_dir_list( '/export/home/staging');
java.security.AccessControlException: the Permission (java.io.FilePermission
/export/home/staging read) has not been granted to DBT. The PL/SQL to
grant this is dbms_java.grant_permission( 'DBT',
'SYS:java.io.FilePermission', '/export/home/staging', 'read' )
at
java.security.AccessControlContext.checkPermission(AccessControlContext.java:207
)
at java.security.AccessController.checkPermission(AccessController.java:403)
at java.lang.SecurityManager.checkPermission(SecurityManager.java:549)
at
oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java
:267)
at java.lang.SecurityManager.checkRead(SecurityManager.java:864)
at
oracle.aurora.rdbms.SecurityManagerImpl.checkRead(SecurityManagerImpl.java:156)
at java.io.File.list(File.java:771)
at DirList.getList(DirList:9)
BEGIN get_dir_list( '/export/home/staging'); END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
/export/home/staging read) has not been granted to DBT. The PL/SQL to
grant this is dbms_java.grant_permission( 'DBT',
'SYS:java.io.FilePermission', '/export/home/staging', 'read' )
ORA-06512: at "DBT.GET_DIR_LIST", line 0
ORA-06512: at line 1


I can read files using UTL_FILE option so I have access to this directory.

Thanks
 

Tom Kyte

Followup  

December 11, 2003 - 10:02 am UTC

the error message:

SQL> exec get_dir_list( '/export/home/staging');
java.security.AccessControlException: the Permission (java.io.FilePermission
/export/home/staging read) has not been granted to DBT. The PL/SQL to
grant this is dbms_java.grant_permission( 'DBT',
'SYS:java.io.FilePermission', '/export/home/staging', 'read' )

it pretty clear is it not??????

it gives you the exact command that need to be executed!

You may have asked the dba to execute:

EXEC DBMS_JAVA.GRANT_PERMISSION('MYSCHEMA', 
'java.io.FilePermission','/export/stage/data','read,write');

but either:

a) they did not
b) they did it to 'MYSCHEMA' and 'MYSCHEMA' != 'DBT'
c) they did it on a different server.


UTL_FILE uses utl_file_dir

Java uses DBMS_JAVA (much much more granular) for it's permissions.  because you can utl_file it DOES NOT in any way imply java can read it. 

You are right

December 12, 2003 - 10:33 am UTC

Reviewer: Suhail from NY, USA

Actually our DBA has granted me EXECUTE on DBMS_JAVA and not the actual permission. Sorry for the confusion.

Thanks a lot.

for Mike: date and time

December 13, 2003 - 2:08 am UTC

Reviewer: Barbara Boehmer from Riverside County, CA USA

Here is some java by Cameron O'Rourke to list directories, including date and time:


</code> http://marvel.oracle.com/pls/otn/f?p=17000:8:::::F17000_P8_DISPLAYID:14855931322 <code>


pl/sql and reading remote files

December 26, 2003 - 10:21 am UTC

Reviewer: Sangeeta from Germany

Database Version: Oracle 8.1.7.4 (on HP Unix)

Hi Tom,
I want to be able to read a windows directory through a PL/SQL function (Database is on Unix..so
it is not a local directory on oracle server)..
and then do a string search on the directory..like find all the files that begins with the word
"SPECIAL_PROJECT". I know that the list of files expected won't be more than three to four at a
time..
so i want these file names to be returned in a comma delimited format..
like 'SPECIAL_PROJECT1993.pdf,SPECIAL_PROJECT1994.pdf,SPECIAL_PROJECT1995.pdf'
Have you done anything like this..

Thanks...

Tom Kyte

Followup  

December 26, 2003 - 10:58 am UTC

there will be 2 problems with this

a) how will the server get access to this remote filesystem. Solve that problem first (and it has nothing whatsoever to do with the database -- when you can "ls" this windows directory, you can read step b below to continue on). SAMBA can be used to mount the windows directory.

Unless you can read the directory at the os level, Oracle cannot either.

b) use the approach above -- all of the code to do 100% of what you ask is there.

how to get a list of available files.", version 8.1.5

December 27, 2003 - 2:13 pm UTC

Reviewer: Leonard Anukam from Finland

Hei, Tom very great again. But do we have to create the DB directory pointing to the OS directory before using this script.

Thanks


Tom Kyte

Followup  

December 27, 2003 - 2:47 pm UTC

no, this does not need a "direct object" in the database.

it does however require the appropriate dbms_java grant be issued.

How does one display the value of a directory created using "create directory ... as ..."?

February 13, 2004 - 3:29 pm UTC

Reviewer: ht from California

Tom,
select * from dba_objects where object_type='DIRECTORY' will display the directories a user has created. How does one display the actual directory value (TMPDIR=/tmp)?
Thanks,
ht

Tom Kyte

Followup  

February 13, 2004 - 3:55 pm UTC

DBA_DIRECTORIES

February 13, 2004 - 4:20 pm UTC

Reviewer: A reader


How do I read files from a certain directory with PL/SQL, without

April 02, 2004 - 10:23 am UTC

Reviewer: Adrian from Belgium

Hi,

First time that I've used your site, found this review via a google search. Solved my problem both elegently and imaginatively,

wonderfull

many thanks

How to display files and sub directories.

June 24, 2004 - 4:32 pm UTC

Reviewer: chandra from Los Angeles, CA USA

Tom,

I have successfully executed the procedure you have shown to display the list of files in a directory.

Is there a way to display the sub_directories as well along with the files.

thanks

Tom Kyte

Followup  

June 24, 2004 - 8:57 pm UTC

it does show the sub directories in the directory.

if you mean "recursively show", like ls -R, then you need to query your java resource for a bit of help...

UNIX Directory Read

July 20, 2004 - 5:51 pm UTC

Reviewer: Deanna Pegg from SF

Hi Tom

Thanks again for the great information!

Will the solution on this page work if the files are located on a server that is different from the Oracle database? If so how would the procedure change below?

ops$tkyte@8i> create global temporary table DIR_LIST
2 ( filename varchar2(255) )
3 on commit delete rows
4 /

Table created.

ops$tkyte@8i> create or replace
2 and compile java source named "DirList"
3 as
4 import java.io.*;
5 import java.sql.*;
6
7 public class DirList
8 {
9 public static void getList(String directory)
10 throws SQLException
11 {
12 File path = new File( directory );
13 String[] list = path.list();
14 String element;
15
16 for(int i = 0; i < list.length; i++)
17 {
18 element = list[i];
19 #sql { INSERT INTO DIR_LIST (FILENAME)
20 VALUES (:element) };
21 }
22 }
23
24 }
25 /

Java created.

ops$tkyte@8i>
ops$tkyte@8i> create or replace
2 procedure get_dir_list( p_directory in varchar2 )
3 as language java
4 name 'DirList.getList( java.lang.String )';
5 /

Procedure created.

ops$tkyte@8i>
ops$tkyte@8i> exec get_dir_list( '/tmp' );

PL/SQL procedure successfully completed.

ops$tkyte@8i> select * from dir_list where rownum < 5;

FILENAME
------------------------------------------------------
data.dat
.rpc_door
.pcmcia
ps_data

And thats it...

Tom Kyte

Followup  

July 20, 2004 - 8:57 pm UTC

the file system needs to be accessible to the oracle database software that is running, nfs mounts on unix would suffice

if you cannot "ls" it from the command line, we cannot magically do it either...

Just to let TOM how helpful he really is....

February 14, 2005 - 12:59 am UTC

Reviewer: Richard Seneres

You always have the suited answer for most people!!!!
Always got the answer I really needed...

Thanks very much!!

Excellent

March 09, 2005 - 8:49 pm UTC

Reviewer: stanley from Singapore

I wonder how you can response to so many queries promptly. You are not only expert in Oracle, you are also expert in time management. Salute you.

Binu, I had similar error.....Tom, got around that, but have new one

March 14, 2005 - 11:06 am UTC

Reviewer: Yuan from Newark, NJ USA

Binu, I had a similar error with Tom's original sample when I ran it from TOAD 7.5, but when I ran it in SQL Plus 8.1.7, worked just fine.

Tom, you mentioned that JAVAUSERPRIV must be granted. I got my DBA to grant me that permission, but get this error:

ESL-CHATDV>declare
2 PROCEDURE Get_Dir_Lst(pivDir VARCHAR2) AS LANGUAGE java
3 NAME 'DirLst.getLst( java.lang.String )';
4 begin
5 Get_Dir_Lst('D:\TEMP');
6 end;
7 /
PROCEDURE Get_Dir_Lst(pivDir VARCHAR2) AS LANGUAGE java
*
ERROR at line 2:
ORA-06550: line 2, column 5:
PLS-00999: implementation restriction (may be temporary) Java methods not
permitted here
ORA-06550: line 2, column 5:
PL/SQL: Item ignored

Are there other privileges that I'm missing? Running on Oracle 9.2 via Toad and SQL Plus of the versions mentioned above.

Tom Kyte

Followup  

March 14, 2005 - 1:00 pm UTC

you have to create or replace that procedure get_dir_lst

and -- after 8.1.5, you don't need/want to use javauserpriv -- dbms_java can be used to grant the precise access you need in a very granular fashion.

THANKS!

March 14, 2005 - 1:20 pm UTC

Reviewer: Yuan from Newark, NJ USA

That did the trick. Saved my butt again!

Greate work Tom!

April 15, 2005 - 10:27 am UTC

Reviewer: hash

But the problem is that I have oracle 8.0.5.0.0. It would be really nice of you if you could give exactly the same solution for 805

thanks

Tom Kyte

Followup  

April 15, 2005 - 10:44 am UTC

external procedures (if you have expert one on one Oracle, it is in there -- lots of information) in C would be the only way.

Your book is not available here

April 15, 2005 - 1:58 pm UTC

Reviewer: hash

Sorry but your book is not available in Pakistan. It would cost me my one month salary. & I don't have expertise in C. do you have any handy example????
thanks

Tom Kyte

Followup  

April 15, 2005 - 2:19 pm UTC

Sure it is, I get the royalty statements. People in Pakistan have gotten it. (there is a reprint available in various countries for real cheap, I know because the statements I get from the publisher show that)...

Anyway, external procedures are documented in the plsql developers guide in that release

</code> http://docs.oracle.com/docs/cd/A64702_01/doc/server.805/a58236/10_procs.htm#433731 <code>

and many example on this site, search around for them.






April 26, 2005 - 9:11 pm UTC

Reviewer: Murtala from Nigeria

please i am student,i checked every where to get your legitimate way to ask question but i couldnt find any.
please kindly help me send me a written program in java that diagonalises a matrix of atleast 3x3(rows and columns).thanks

Tom Kyte

Followup  

April 27, 2005 - 7:34 am UTC

it's been about 20 years since I've actually had to do that.

And back then, we did it on paper typically, to learn how it was done.

April 26, 2005 - 9:13 pm UTC

Reviewer: Murtala from Nigeria

please i am student,i checked every where to get your legitimate way to ask question but i couldnt find any.
please kindly help me send me a written program in java that diagonalises a matrix of atleast 3x3(rows and columns).thanks

Thanx

June 02, 2005 - 5:25 am UTC

Reviewer: Kifayah from Jordan

Thanx alot 4 such solution . I wonder f it works on oracle9i because i have done the steps, most of them was done successfully but the last one (exec get_dir_list( 'c:\*.txt' ); faild as exception handler in java as follows:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.NullPointerException
ORA-06512: at "user1trig.GET_DIR_LIST", line 0
ORA-06512: at line 1

Regards



Tom Kyte

Followup  

June 02, 2005 - 4:37 pm UTC

ctl-f for nullpointer



June 16, 2005 - 5:18 pm UTC

Reviewer: A reader

Tom,

In 9204 can we read the contents of directory from PLSQL(utl_file or some other package) without using Java?

Thanks.

Tom Kyte

Followup  

June 16, 2005 - 10:05 pm UTC

you could also use C, but plsql doesn't have anything "native"

August 16, 2005 - 3:01 pm UTC

Reviewer: Khozema from CA, USA

Heres the cut and paste of the code
create or replace and compile java source named "DirList" as
import java.io.*;
import java.sql.*;

public class DirList
{
public static void getList(String directory)
throws SQLException
{
File path = new File( directory );
String[] list = path.list();
String element;

for(int i = 0; i < list.length; i++)
{
element = list[i];
#sql { INSERT INTO DIR_LIST (FILENAME)
VALUES (:element) };
}
}

}
/

I get the following error while compiling

create or replace and compile java source named "DirList" as
*
ERROR at line 1:
ORA-29536: badly formed source: Encountered "<EOF>" at line 1, column 17.
Was expecting:
";" ...


I dont see anything wrong though.
Please help

Tom Kyte

Followup  

August 17, 2005 - 12:07 pm UTC

ops$tkyte@ORA9IR2> create or replace and compile java source named "DirList"  as
  2      import java.io.*;
  3      import java.sql.*;
  4
  5      public class DirList
  6      {
  7      public static void getList(String directory)
  8                        throws SQLException
  9     {
 10         File path = new File( directory );
 11         String[] list = path.list();
 12         String element;
 13
 14         for(int i = 0; i < list.length; i++)
 15         {
 16             element = list[i];
 17             #sql { INSERT INTO DIR_LIST (FILENAME)
 18                    VALUES (:element) };
 19         }
 20     }
 21
 22     }
 23  /

Java created.

I just cut and pasted, you should do the same in sqlplus. 

August 17, 2005 - 2:31 pm UTC

Reviewer: Khozema Khambati from CA, USA

I did cut and paste. And I got the error.
I am on 8i - 8.1.7 database running Oracle Apps

And java is installed too. And I have granted javauserpriv from system to apps user. Do I need to anything else.

Thanks

Tom Kyte

Followup  

August 17, 2005 - 2:47 pm UTC

why don't I get to see your cut and paste from sqlplus like me??!?!???


show me what I show you. prove to me that the text you say you are putting in, is in fact what you are putting in.

August 18, 2005 - 1:08 pm UTC

Reviewer: A reader

Heres what I found.
Sqlplus 8.0 doesnt support the compilation of Java class. I downloaded 10i client from OTN.


I am testing the script now, and looks like everything is working.

Thanks a lot for lots of help and great job helping.

President likes it woohoo

August 25, 2005 - 9:02 pm UTC

Reviewer: Kevin Meade from Connecticut

Thanks Tom, once again you are the best.

Sorry I won't be at the local meeting you are comming to present here soon (CT), but many of my friends are going so I'll have one say hello for me.

FYI, the President of our company learned we recently used this code as the only JAVA routine in our system. He said: "why are you guys putting code in our system that you can't write?" (we aren't great Java guys). We said: "Tom Kyte wrote it from asktom". He said: "OH if its from Tom then its OK."

Thanks bud. Kevin CT

This is really great

October 06, 2005 - 11:01 am UTC

Reviewer: Zohar from Israel

How do I change this code to distinguish between directories and files (or just ignore the directories when inserting it into the temp-table)?

Thanks!
Zohar

Tom Kyte

Followup  

October 06, 2005 - 12:06 pm UTC

read up on the java API's to see what you can do with them.

Rename a file

December 02, 2005 - 10:35 am UTC

Reviewer: daniel

Does anybody know how to rename a file on OS using java call from a database?

Tom Kyte

Followup  

December 02, 2005 - 11:24 am UTC

hows about just using utl_file?

Not sure if it will work

December 02, 2005 - 12:12 pm UTC

Reviewer: daniel

I"m not sure if it would work...I've done some more research on your site and came up with an aproach.
Here is what I need to do. Maybe I'm crazy and there is a better way to do it:) I need to read the content of the directory to get the audit files (audit_trail_dest) and read all the files that are currently out there and load them into a database.
1. Create a external table (using file name log_to_load.aud)
2. Read a directory and load the list of files into a temporary table (found the code on this site "exec get_dir_list").
3. Using a stored procedure and a temporary table I would build the following statement (also found the code on your site):
exec rc('/usr/bin/mv audit_file.aud log_to_load.aud).
4. Then simply use the external table to load each row into a table holding the audit information.

I'm open to any suguesstions. I need to implement it on about 50 servers (AIX, SUN, HP) so I wanted to run it from a Database, instead of OS.


Tom Kyte

Followup  

December 02, 2005 - 12:21 pm UTC

why not just audit to the database in the first place??? I mean, you are trashing the audit logs in the file system?

audit_sys_operations

December 02, 2005 - 2:02 pm UTC

Reviewer: daniel

When you set audit_sys_operations=true that creates log files on the os for sysdba, sysoper activities...we're also loging all other activity to sys.aud$.

Tom Kyte

Followup  

December 03, 2005 - 10:08 am UTC

and putting them into the database where sys et.al. can truncate/delete them freely?

or are you doing this into ANOTHER database (in which case, I'd just write a program outside the database to load them)


10gr2 interestingly enough can dump the audit trail to the OS file system in XML *and* provides at the same time a v$ view that lets you query the XML as if it were in a table.

Repository

December 03, 2005 - 1:10 pm UTC

Reviewer: Daniel

I would be loading it into another database. We have a repository where we store all the sys.aud$ data from about 50 or so databases on about 50 or so servers.. Now we are required to store the audit files from OS as well. I could write a scrip to do it, I would prefer not to...The reason is that these servers range are Solarix, AIX, LINUX and HP-UX. We don't have a common transfer protocol...some use sftp, some use ftp etc...I don't even have access to some of these servers...(and all this is outside of my control and would take ages to change). So I wanted to see if doing it from inside of the database would be possible. At least I have control over that.

Tom Kyte

Followup  

December 03, 2005 - 3:13 pm UTC

why not just alter the external table to point to the file of interest, load it, erase the file, repeat

or, just use a bfile and dbms_lob to read files?

***

December 03, 2005 - 3:50 pm UTC

Reviewer: Daniel

I wasn't sure what kind of performance hit I could take if I had to alter the table...I guess I don't like doing ddl from within pl/sql. I'll look closely into bfile concept...haven't used it so far.

Tom Kyte

Followup  

December 04, 2005 - 6:01 am UTC

well, the alter will invalidate the statement (causing a hard parse for each load), you can use utl_file.frename to rename files as well (you don't need to "mv", which is just rename in disguise).

Oracle 10 XE

June 22, 2006 - 6:30 am UTC

Reviewer: Andre Meyer from GERMANY

Hello,

in reference to the original question:
Is it possible to do this in XE as it don't support Java?
Without .NET?

Regards
Andre

Tom Kyte

Followup  

June 22, 2006 - 12:31 pm UTC

external procedures could be used.

June 27, 2006 - 2:13 pm UTC

Reviewer: Alexander the ok

Hello Tom,

Can we create files in Oracle using pl/sql? Especially if say we where using windows with 9i? Thanks you.

Tom Kyte

Followup  

June 27, 2006 - 2:46 pm UTC

utl_file, yes.

June 27, 2006 - 3:01 pm UTC

Reviewer: Alexander the ok

Are you sure about that? Could you tell me which subprogram it is? I can only find reading/writing from existing files.

</code> http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10802/u_file.htm#996960 <code>

Tom Kyte

Followup  

June 27, 2006 - 3:06 pm UTC

</code> http://docs.oracle.com/docs/cd/B14117_01/appdev.101/b10802/u_file.htm#1003527 <code>

fopen does that, fopen 'w' - opens a file for writing, file does not have to exist.

June 27, 2006 - 3:41 pm UTC

Reviewer: Alexander the ok

That's really slick thanks.

reading directory

July 10, 2006 - 5:05 pm UTC

Reviewer: sam

Tom:

Does reading directory work with 8i database. WHen I ran the java stored proedure it kept going. It worked fine in 9i.

Tom Kyte

Followup  

July 11, 2006 - 7:36 pm UTC

"it kept going"

does not make sense to me, what do you mean by that.

the example you are using from above - well, it was written for 8i - 8.1.5 specifically.


a question

July 17, 2006 - 4:22 am UTC

Reviewer: Kin from Hong Kong

Dear Tom,

 I have tried your codes but I get this error:

SQL> create or replace and compile java source named "DirList"
  2  AS
  3
  4
  5     import java.io.*;
  6     import java.sql.*;
  7     import oracle.sql.*;
  8     import oracle.jdbc.*;
  9
 10     public class DirList
 11  {
 12     public static void getList(String directory) throws SQLException
 13
 14     {
 15
 16     File path = new File( directory );
 17     String[] list = path.list();
 18     String element;
 19
 20
 21             for(int i = 0;i < list.length; i++)
 22                      {
 23               element = list[i];
 24               #sql{ Insert into DIR_LIST (FILENAME) values (:element) };
 25
 26               }
 27    }
 28
 29  };
 30    /


create or replace procedure get_dir_list( p_directory in varchar2 )
as language java
 name 'DirList.getList( java.lang.String )';
 /


Java created.

SQL> SQL> SQL>   2    3    4
Procedure created.


ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.sql.SQLException: Invalid column index
ORA-06512: at "AOSDEV.GET_DIR_LIST", line 1
ORA-06512: at line 1

 I am using Oracle 10g with Unix. I am looking forward to hear from you~

best regards,
Kin 

Tom Kyte

Followup  

July 17, 2006 - 1:31 pm UTC

how's about you give FULL example?????  like, oh, the call you make?


ops$tkyte@ORA10GR2> create global temporary table DIR_LIST
  2  ( filename varchar2(255) )
  3  on commit delete rows
  4  /

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace and compile java source named "DirList"
  2  AS
  3     import java.io.*;
  4     import java.sql.*;
  5     import oracle.sql.*;
  6     import oracle.jdbc.*;
  7     public class DirList
  8  {
  9     public static void getList(String directory) throws SQLException
 10     {
 11     File path = new File( directory );
 12     String[] list = path.list();
 13     String element;
 14             for(int i = 0;i < list.length; i++)
 15             {
 16               element = list[i];
 17               #sql{ Insert into DIR_LIST (FILENAME) values (:element) };
 18             }
 19    }
 20  };
 21  /

Java created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace procedure get_dir_list( p_directory in varchar2 )
  2  as language java
  3  name 'DirList.getList( java.lang.String )';
  4  /

Procedure created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec dbms_java.grant_permission( 'OPS$TKYTE', 'SYS:java.io.FilePermission', '/tmp', 'read' )

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec get_dir_list( '/tmp' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select * from dir_list;

FILENAME
-------------------------------------------------------------------------------
jd_sockV4
.iroha_unix
.font-unix
.fam_socket
orbit-root
xtmpx.sql
ssh-RcnX4968
orbit-tkyte
ssh-aMYI5569
ssh-SmOS3421
ssh-dAdr4965
termout.sql
ssh-FONe3417
ssh-mxhH3418
ssh-uwaT3766
ssh-GcEO3490
ssh-crNF3698
ssh-bOtB3574
ssh-nIJZ3737
ssh-mhNT3596
ssh-Elqo3812
ssh-ksQC3466
notermout.sql
ssh-Uowj4393
ssh-OZc30228
jpsock.142_04.32534
OSL_PIPE_500_SingleOfficeIPC_88abacfc386e83b4df4a27cdd02ff
jpsock.142_04.6968

28 rows selected.

 

a Question

July 17, 2006 - 4:24 am UTC

Reviewer: Kin from Hong Kong

Those errors appear all the time after I execute thte procedure.

July 17, 2006 - 8:30 pm UTC

Reviewer: Kin from Hong kong

My call is just like:

exec get_dir_list('/mnt/home1/topro/asint/' );

select * from DIR_LIST where rownum < 5;



I don't know why...

July 17, 2006 - 8:42 pm UTC

Reviewer: Kin from Hong kong

When I gramt the permission it shows like:
SQL> exec dbms_java.grant_permission( 'aosdev', 'SYS:java.io.FilePermission', '/mnt/home1/toprod/asint/', 'read' )
BEGIN dbms_java.grant_permission( 'aosdev', 'SYS:java.io.FilePermission', '/mnt/home1/toprod/asint/', 'read' ); END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
oracle.aurora.vm.IdNotFoundException: -1 is not the number of a user or role
ORA-06512: at "SYS.DBMS_JAVA", line 313
ORA-06512: at line 1

SQL> exec get_dir_list( '/mnt/home1/toprod/asint/' );
BEGIN get_dir_list( '/mnt/home1/toprod/asint/' ); END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.sql.SQLException: Invalid column index
ORA-06512: at "AOSDEV.GET_DIR_LIST", line 1
ORA-06512: at line 1


Is that because the grant right of me? 

Tom Kyte

Followup  

July 18, 2006 - 8:06 am UTC

after seeing the dbms_java call - I'm inclined to believe the java installation is botched there - that call should NOT be failing like that.

Please utilize support for this one.

Invalid File ID

August 09, 2006 - 12:05 pm UTC

Reviewer: Paulo from Portugal

yes it helps, but i have another problem

when i'm running my package, appears the follow errors:
ERRO na linha 1:
ORA-29282: ID do ficheiro inválida
ORA-06512: na "SYS.UTL_FILE", linha 714
ORA-06512: na "EV01D_DATA.PRODUTO_OPORTUNIDADE", linha 275
ORA-29283: operação do ficheiro inválida
ORA-06512: na "SYS.UTL_FILE", linha 449
ORA-29283: operação do ficheiro inválida
ORA-06512: na linha 1

I have done:
create directory rw_dir as '/temp';
grant read on directory rw_dir to public;
HOST MD C:\temp;

All of this was ok.
I don't know what's missing.

This code works on my machine, but when I run on another (server), it fails. This machine SO is a unix.

It's my problems with permissions?
I'm not a dba on the server

should I ask the dba to grant me the privelegies?

Bye


Tom Kyte

Followup  

August 09, 2006 - 1:09 pm UTC

you need to show the MIMIMAL code needed to produce this.

this sounds like something happening AFTER you call fopen

Invalid File ID

August 09, 2006 - 1:48 pm UTC

Reviewer: Paulo from Portugal

ok were it goes

-- Name of the file
str := 'Seguros_'||to_char(LOCALTIMESTAMP), 'dd.mm.yyyy_HH24.MM')||'.txt';
-- Open the file
fseg := utl_file.fopen('RW_DIR',str,'W');
-- Put a line in a string
str := 'Produto|Nome|Sobrenome|Telefone|Telemovel|E_Mail|NIF|Código do Ponto de Entrega|Rua|Número da Porta|Número do Complemento|Zona|Código Postal|Localidade';
-- Put the string in the file
utl_file.put_line(fseg,str);

The problem resides in 'RW_DIR'. I believe that this object it's not point to the correct directory, because i'm not a dba of th DB. But when I execute:

SELECT DIRECTORY_PATH
FROM ALL_DIRECTORIES
WHERE DIRECTORY_NAME = 'RW_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/temp

he gives the correct path.

I'm out of ideas
The machine is a unix

Tom Kyte

Followup  

August 09, 2006 - 4:49 pm UTC

what line of code is failing - can you write a small snippet that actually runs from START to FINISH in sqplus and capture the entire error stack and code.

Invalid File Id

August 09, 2006 - 1:51 pm UTC

Reviewer: Paulo from Portugal

sorry, run this code instead

CREATE OR REPLACE PACKAGE BODY PRODUTO_OPORTUNIDADE AS
PROCEDURE PRODUTOS
IS


fseg utl_file.file_type;




str varchar2(500);

v_err_sqlcode varchar2(10);
v_err_sqlerrmsg varchar2(200);


BEGIN

str := 'Seguros_'||to_char(LOCALTIMESTAMP(), 'dd.mm.yyyy_HH24.MM')||'.txt';
fseg := utl_file.fopen('RW_DIR',str,'W');
str := 'Produto|Nome|Sobrenome|Telefone|Telemovel|E_Mail|NIF|Código do Ponto de Entrega|Rua|Número da Porta|Número do Complemento|Zona|Código Postal|Localidade';
utl_file.put_line(fseg,str);


v_err_sqlcode := null;
v_err_sqlerrmsg := null;


EXCEPTION
WHEN OTHERS THEN

v_err_sqlcode := SQLCODE;
v_err_sqlerrmsg := substr(SQLERRM, 1, 200);
str := '*** ' ||v_err_sqlcode||' - '||v_err_sqlerrmsg;
dbms_output.put_line(str);

END PRODUTOS;
END PRODUTO_OPORTUNIDADE;
/

Tom Kyte

Followup  

August 09, 2006 - 4:51 pm UTC

you run it :) and cut and paste it.

I want the CREATE in the cut and paste so we have LINE NUMBERS to look at.

lose the useless and actually "information reducing and quite in the way" exception block you have. get rid of the entire exception block - it will make your ability to figure out what the issue is impossibly hard.

Never code exception blocks like that - just a waste of keystrokes and only makes the errors "seem like they are not happening"

Thanks for the help??

October 06, 2006 - 8:26 am UTC

Reviewer: omer from jeddah, saudia arabia

This was very helpful ,i was struggling to get to this, well lots of good stuff on your site.

Read from an Oracle defined directory

October 10, 2006 - 4:27 am UTC

Reviewer: Mette from Denmark

Hi Tom

Thanks for the java program for reading the directory, it works perfectly apart from the fact that we use Oracle dirs when accessing the filesystem. (create dir_udump as d:\......udump' fashion). So I cant use it in prod unless I access through Oracle dirs

How do I modify the java program to use an Oracle dir for input instead of the actual path&dir name?

regards
Mette

Tom Kyte

Followup  

October 10, 2006 - 7:55 am UTC

you would not, you see - java works on "real directories". You could pass this the directory name and have it query dba_directories to get the real name but you will HAVE to grant permission on the REAL directory name - not the oracle indirect directory name.

Think I solved it my self

October 10, 2006 - 6:19 am UTC

Reviewer: Mette from Denmark

Hi again

Maybe not the most elegant solution - so no big laughs, please :-)

regards
Mette

-------------

CREATE OR REPLACE procedure get_dir_list_p( p_dir in varchar2)
is
w_dir varchar2(100);
begin

select directory_path into w_dir
from all_directories
where directory_name = p_dir;

get_dir_list(w_dir);

exception
when no_data_found then
get_dir_list(p_dir);

end get_dir_list_p;
/

Tom Kyte

Followup  

October 10, 2006 - 8:19 am UTC

that'll work - yes, but java still is working with "real directories" and needs be granted on real directories so on production, they'll still be seeing "real directories"

How about date & length into the dirlist?

October 10, 2006 - 10:28 am UTC

Reviewer: Mette from Denmark

Hi again.

I have added 2 new columns filelength, filetype and filemodified, but I dont have that much knowledge of java, so the date is annoying me: (sorry for the weird tabs here - dont know how to remove them ...)

translated to english the message is like this: cannot analyze the sql sentence. Cant decide elementtype for host element #4 java.util.date

Can you guide me nearer to a solution?

regards
Mette (again)

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "DirList" AS
import java.io.*;
import java.sql.*;
import java.util.Date;
import java.text.SimpleDateFormat;


public class DirList
{
public static void getList(String directory)
throws SQLException
{
File path = new File( directory );
String[] list = path.list();
String element;

for(int i = 0; i < list.length; i++)
{
element = list[i];


String fpath=directory+"/"+list[i];

File f = new File(fpath);

long len;
Date date;
String ftype;

if (f.isFile()) {

len = f.length();
date = new Date(f.lastModified());
ftype = "F";

} else {

len = 0;
date = null;
ftype = "D";
}

#sql { INSERT INTO DIR_LIST (FILENAME, filelength, filetype, filemodified)
VALUES (:element, :len, :ftype, :date) };
}
}

}
/



Tom Kyte

Followup  

October 10, 2006 - 12:19 pm UTC

you'll need to find a java programmer to help you out :)

sorry... I don't have that code at hand, the concept is here, a little digging around should turn up how to do that.

Java not installed

October 10, 2006 - 11:46 am UTC

Reviewer: Vinayak from Zurich

Hi Tom,
I am using Oracle 10g Express Edition. When I tried to run your script on the very first followup of this thread, it said "Java not installed".
Is there any way to run this on Oracle 10g XE.


Tom Kyte

Followup  

October 10, 2006 - 8:07 pm UTC

nope, no java in XE at all.

A little digging did the trick

October 10, 2006 - 1:23 pm UTC

Reviewer: Mette from Denmark

... as you said:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "DirList" AS
import java.io.*;
import java.sql.*;
import java.util.Date;
import java.text.SimpleDateFormat;


public class DirList
{
public static void getList(String directory)
throws SQLException
{
File path = new File( directory );
String[] list = path.list();
String element;

for(int i = 0; i < list.length; i++)
{
element = list[i];


String fpath=directory+"/"+list[i];

File f = new File(fpath);

long len;
Date date;

String ftype;
String sqldate;

SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss");

if (f.isFile()) {

len = f.length();
date = new Date(f.lastModified());
sqldate = df.format(date) ;
ftype = "F";

} else {

len = 0;
sqldate = null;
ftype = "D";
}

#sql { INSERT INTO DIR_LIST (FILENAME, filelength, filetype, filemodified)
VALUES (:element, :len, :ftype, to_date(:sqldate,'YYYY-MM-DD HH24:MI:SS')) };
}
}

}
/
------------------

Now I get date/time modified, length, type and filename.

Thank you very much for the great idea

regards
Mette

Tom Kyte

Followup  

October 10, 2006 - 8:10 pm UTC

and thank YOU very much for contributing back!

to Mette

October 12, 2006 - 9:58 am UTC

Reviewer: Michel Cadot from France

Thank you very much for this update, Mette.
Just a remark: compared to the original DirList procedure you now need read privilege on all the files in the directory and not only on the directory itself.

Regards
Michel


How to read a network directory

November 14, 2006 - 11:40 am UTC

Reviewer: Edu from Spain

Hi Tom

Is it possible to read a network directory (f.e: \\server1\dir1) following your examples?

Thanks.

Tom Kyte

Followup  

November 15, 2006 - 6:39 am UTC

depends on whether the oracle process can see it.

remember, windows is not easy like unix/linux. Just because YOUR user session can see \\server1\dir1 does not mean the user session running the oracle process can - in fact, you have to bend over backwards to make that happen.

January 09, 2007 - 5:36 am UTC

Reviewer: A reader

Hello Tom,

I tried to create java source "DirList" usin the code:

CREATE OR REPLACE
AND COMPILE JAVA SOURCE NAMED "DirList"
AS
import java.io.*;
import java.sql.*;

public class dirlist
{
public static void getlist(string directory)
throws sqlexception
{
file path = new file( directory );
string[] list = path.list();
string element;

for(int i = 0; i < list.length; i++)
{
element = list[i];
#sql { insert into dir_list (filename)
values (:element) };
}
}

}
/

This is the error I get:

DirList:11: Class string not found.

I am on 10g 10.1.0.3.0 / Windows2003.

Thanks in advance,

Denes Kubicek
Tom Kyte

Followup  

January 11, 2007 - 9:19 am UTC

cut and paste straight from sqlplus the entire thing - just like I do.

January 14, 2007 - 12:10 pm UTC

Reviewer: A reader from Bensheim, Germany

O.K.

This is what I did:

As SYS:

SQL> GRANT JAVAUSERPRIV to ORACLE_BOM;

As ORACLE_BOM user:

SQL> CREATE GLOBAL TEMPORARY TABLE dir_list
2 ( filename VARCHAR2(255) )
3 ON COMMIT DELETE ROWS
4 /

Tabelle wurde erstellt.

SQL> CREATE OR REPLACE
2 AND COMPILE JAVA SOURCE NAMED "DirList"
3 AS
4 import java.io.*;
5 import java.sql.*;
6
7 public class dirlist
8 {
9 public static void getlist(string directory)
10 throws sqlexception
11 {
12 file path = new file( directory );
13 string[] list = path.list();
14 string element;
15
16 for(int i = 0; i < list.length; i++)
17 {
18 element = list[i];
19 #sql { insert into dir_list (filename)
20 values (:element) };
21 }
22 }
23
24 }
25 /

Warnung: Java mit Kompilierungsfehlern erstellt.

SQL>
SQL> CREATE OR REPLACE PROCEDURE get_dir_list (p_directory IN VARCHAR2)
2 AS
3 LANGUAGE JAVA
4 NAME 'DirList.getList( java.lang.String )';
5 /

Prozedur wurde erstellt.

(if this is what you meant)

Denes Kubicek

January 14, 2007 - 12:25 pm UTC

Reviewer: A reader

O.K.

I found a mistake. Toad formating changed some capital letters
into lower.

Sorry for not seeing this.

Thanks again.

Denes Kubicek

Why i am getting this?

February 08, 2007 - 10:39 am UTC

Reviewer: Sumanth from India

Hi Tom,

Thanks for this excellent source.

when i try to execute this source in my system

  1   create or replace and compile java source named DirList
  2   as
  3   import java.io.*;
  4   import java.sql.*;
  5   public class DirList
  6   {
  7   public static void getList(String directory) throws SQLException
  8   {
  9         File path = new File( directory );
 10         String[] list = path.list();
 11         String element;
 12         for(int i = 0; i < list.length; i++)
 13         {
 14             element = list[i];
 15             #sql { INSERT INTO DIR_LIST (FILENAME)
 16                    VALUES (:element) };
 17         }
 18     }
 19*    }
 20  /
 create or replace and compile java source named DirList
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


SQL/> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production



what went wrong here?
Tom Kyte

Followup  

February 08, 2007 - 11:21 am UTC

please utilize support.

on another server

February 26, 2007 - 12:19 pm UTC

Reviewer: A reader

exec system.get_dir_list( 'C:\Temp\Extract' );

if the directory is on another server

like I have to use '//tomkyte/datasource/scripts/'

instaed of 'C:\Temp\Extract' its not working like that.


Tom Kyte

Followup  

February 26, 2007 - 3:28 pm UTC

the file system must be available to the Oracle database software itself (which is running as a low privileged user as a service and cannot see your network shares)

waht about created_date

February 26, 2007 - 5:09 pm UTC

Reviewer: A reader

How can I get cretaed date attribute of a file in that particular directory in Oracle and inserteing it into a table.
Tom Kyte

Followup  

February 26, 2007 - 5:19 pm UTC

same way you would read a list of files - a tiny bit of java.

doesn't know java

February 26, 2007 - 5:21 pm UTC

Reviewer: A reader

nobody know java here.
Tom Kyte

Followup  

February 27, 2007 - 9:18 am UTC

I didn't either really, however, by searching around a bit - I was able to glue together the simple example. I'm sure if you looked into a little on your own, you would be able to figure out how to get the needed information from the Java APIs.

March 07, 2007 - 2:20 pm UTC

Reviewer: A reader

Tom,

I asked my DBA to grant this permission:
dbms_java.grant_permission( 'WCLAIM', 'SYS:java.io.FilePermission','WCLAIM_FORD', 'read,write' )

and used your examples, but getting following error while executing the procedure:

SQL> exec get_dir_list('/WCLAIM_FORD')
BEGIN get_dir_list('/WCLAIM_FORD'); END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
\WCLAIM_FORD read) has not been granted to WCLAIM. The PL/SQL to grant this is
dbms_java.grant_permission( 'WCLAIM', 'SYS:java.io.FilePermission',
'\WCLAIM_FORD', 'read' )
ORA-06512: at "WCLAIM.GET_DIR_LIST", line 1
ORA-06512: at line 1

Could you please let me know what is wrong?
Tom Kyte

Followup  

March 07, 2007 - 3:15 pm UTC

either

a) you did not log in again after getting granted
b) your dba did not do the grant.

March 07, 2007 - 2:34 pm UTC

Reviewer: A reader

Tom,

This is the output I am getting when I create the package:

SQL> create or replace
2 and compile java source named "DirList"
3 as
4 import java.io.*;
5 import java.sql.*;
6
7 public class DirList
8 {
9 public static void getList(String directory)
10 throws SQLException
11 {
12 File path = new File( directory );
13 String[] list = path.list();
14 String element;
15
16 for(int i = 0; i < list.length; i++)
17 {
18 element = list[i];
19 #sql { INSERT INTO DIR_LIST (FILENAME)
20 VALUES (:element) };
21 }
22 }
23 /
and compile java source named "DirList"
*
ERROR at line 2:
ORA-29536: badly formed source: Encountered "<EOF>" at line 19, column 4.

Was expecting one of:

"#sql" ...

"static" ...

"abstract" ...

"final" ...

"private" ...

"protected" ...

"public" ...

"strictfp" ...

"boolean" ...

"byte" ...

"char" ...

"class" ...

"double" ...

"float" ...

"int" ...

"interface" ...

"long" ...

"native" ...

"short" ...

"synchronized" ...

"transient" ...

"void" ...

"volatile" ...

<IDENTIFIER> ...

"{" ...

"}" ...

";" ...


Tom Kyte

Followup  

March 07, 2007 - 3:23 pm UTC

I cut and paste your code - failed
I cut and paste my original code - success

therefore, you have a cut and paste error somewhere, find it.


ops$tkyte%ORA9IR2> create or replace
  2     and compile java source named "DirList"
  3   as
  4   import java.io.*;
  5   import java.sql.*;
  6   public class DirList
  7   {
  8   public static void getList(String directory)
  9                throws SQLException
 10  {
 11      File path = new File( directory );
 12      String[] list = path.list();
 13      String element;
 14      for(int i = 0; i < list.length; i++)
 15      {
 16        element = list[i];
 17        #sql { INSERT INTO DIR_LIST (FILENAME)
 18            VALUES (:element) };
 19      }
 20  }
 21  /
   and compile java source named "DirList"
                                 *
ERROR at line 2:
ORA-29536: badly formed source: Encountered "<EOF>" at line 17, column 1.
Was expecting one of:
"#sql" ...
"static" ...
"abstract" ...
"final" ...
"private" ...
"protected" ...
"public" ...
"strictfp" ...
"boolean" ...
"byte" ...
"char" ...
"class" ...
"double" ...
"float" ...
"int" ...
"interface" ...
"long" ...
"native" ...
"short" ...
"synchronized" ...
"transient" ...
"void" ...
"volatile" ...
<IDENTIFIER> ...
"{" ...
"}" ...
";" ...


ops$tkyte%ORA9IR2> @mine
ops$tkyte%ORA9IR2> create or replace
  2     and compile java source named "DirList"
  3  as
  4  import java.io.*;
  5  import java.sql.*;
  6  public class DirList
  7  {
  8  public static void getList(String directory)
  9                     throws SQLException
 10  {
 11      File path = new File( directory );
 12      String[] list = path.list();
 13      String element;
 14      for(int i = 0; i < list.length; i++)
 15      {
 16          element = list[i];
 17          #sql { INSERT INTO DIR_LIST (FILENAME)
 18                 VALUES (:element) };
 19      }
 20  }
 21  }
 22  /

Java created.


March 07, 2007 - 4:04 pm UTC

Reviewer: A reader

Thanks Tom, that worked. But getting following error when executing the stored procedure:

SQL> exec get_dir_list('WCLAIM_FORD')
BEGIN get_dir_list('WCLAIM_FORD'); END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.NullPointerException
ORA-06512: at "WCLAIM.GET_DIR_LIST", line 1
ORA-06512: at line 1

Directory has files. I have tried with both the inputs:
1.'/wclaim_ford'
2.'wclaim_ford'

What is the mistake?

Thanks.
Tom Kyte

Followup  

March 07, 2007 - 4:19 pm UTC

i doubt wclaim_ford is a directory

it would have to be an immediate subdirectory of the current working directory (which you don't control)

ctl-f for nullpointer on this page.

use a real directory name.

March 08, 2007 - 9:42 am UTC

Reviewer: A reader

Tom,

You are right, I am refering to a subdirectory.
Is there a way to refer to a subdirectory of a remote server?

Thanks for your help.

Tom Kyte

Followup  

March 08, 2007 - 11:10 am UTC

by fully qualifying the path

/path/to/where/the/stuff/actually/is

would let you access the subdirectory "is" under that path.

March 08, 2007 - 11:51 am UTC

Reviewer: A reader

Thanks Tom, I did that but it gives following error:
SQL> exec get_dir_list('c:\1\jan_loads')
BEGIN get_dir_list('c:\1\jan_loads'); END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.NullPointerException
ORA-06512: at "WCLAIM.GET_DIR_LIST", line 1
ORA-06512: at line 1

on my c drive I have folder called 1 and a subfolder 'jan_loads' under it. And the subfolder has 10 files. What is the mistake?

Thanks
Tom Kyte

Followup  

March 08, 2007 - 12:45 pm UTC

on the DATABASE SERVER is there a c:\1\jan_loads

the server can only read the directory of..... the server.

So, I've a feeling YOU have a directory by that name, but the server does not.

on xe

March 30, 2007 - 4:24 pm UTC

Reviewer: Hegyvari Krisztian from Hungary

Hi,

I came here when I had the problem of reading a directory list under 8.1.7.4 and been successfully using your solution up until now. (For that, thanks....) Now I have to write the same thing against an XE database and this is killing me. Is there an easy way to do it? I do not know anything about external procedures, but if that is the only way I have to read up on them I fear. For this thing do I have to write an own external procedure in C or whatever or can I just execute a "dir" command and capture the output?

<sigh> all the other limitations on XE can be understood, but why is the JVM gone? </sigh>

Cheers.

Krisztian

never mind

April 01, 2007 - 4:05 pm UTC

Reviewer: Hegyvari Krisztian from Hungary

I found one of your old posts from 1997 on groups.google.com, where a "client" program is running on the server and listens and answers requests using dbms_pipe. I think I will write something like that.

K

July 18, 2007 - 8:38 am UTC

Reviewer: Shehab

Hello
I followed Exactly the same steps and codes
but when i executed the procedure as
@ exec get_dir_list('/usr/src/bfile_blob');
i had the following errors
BEGIN get_dir_list('/usr/src/bfile_blob'); END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: oracle.jdbc.driver.OracleSQLException: O
ORA-06512: at "TEST.GET_DIR_LIST", line 1
ORA-06512: at line 1

Please its urgent to get over this problem
Tom Kyte

Followup  

July 18, 2007 - 10:54 am UTC

ok, put some debug into the code and see where it is going wrong.


put some system.out.println's in there and using dbms_java to set the output on, test it.

October 05, 2007 - 9:44 am UTC

Reviewer: A reader

Hi,
This is what I was looking for.
I have files in the server and I need to get the file names into the Application express applciation.
As of now I am trying with the directory available on my local system.
The error I am faceing is that when I execute
exec get_dir_list( 'C:\temp' ); I get an error
"ORA-00900: invalid SQL statement".
Can you please address me where I am going wrong?

Thanks in Advance.




October 08, 2007 - 6:53 am UTC

Reviewer: A reader

Execuse me for my previous posting.
I understand that the files should be available on same box/instance.


Installation

February 13, 2008 - 7:17 am UTC

Reviewer: Ramesh from India

Hi Tom,

Can i install the Oracle8i (8.1.6.2.0) on A.S 4 it is possible r not r either i had to install on A.S.3,plse
tell me as it is very urgent to me.

Thanks,

Tom Kyte

Followup  

February 13, 2008 - 1:10 pm UTC

no clue what "r not r" is

no clue what "A.S 4" could be

clueless as to what "A.S.3" is

and really at a loss to understand how installing an obsolete, unsupported piece of software on something has to do with reading files in a directory?

Do without Java for the directory listing problem

June 05, 2008 - 6:39 am UTC

Reviewer: Mungo Henning from Scotland

Hoping that this has not been covered before, I came to this article because I wanted to perform a directory listing on an XE database.
And then I found that XE does not support Java... sigh.
The PC hosting this XE database is otherwise idle, hence I stumbled upon a workaround.
Data files arrive into the aforementioned PC via FTP from some other (remote) process.
But here's the trick: these data files have a uniform name that includes a date stamp. Something like "DataFile080605_12:23:59" or so: the name depending on the time of the data file. (080605 meaning 2008 June 5th for those that don't twig this)
So my trick is to record the name of the last processed data file in a database table and then (brute force) cycle through all future names using PLSQL until the utl_file.fopen succeeds.
And that's how I can process the next file, whatever its name.
It's not pretty, but as I said the machine is otherwise idle so why not get it to "probe" the target directory and look for files in a dumb fashion.
Hope this idea is deemed useful; sincere thanks to Tom for all his dedication.

Regards

Mungo Henning

January 29, 2009 - 7:35 pm UTC

Reviewer: kyle from Left Coast, Canada


When I gramt the permission it shows like:
SQL> exec dbms_java.grant_permission( 'aosdev', 'SYS:java.io.FilePermission',
'/mnt/home1/toprod/asint/', 'read' )

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
oracle.aurora.vm.IdNotFoundException: -1 is not the number of a user or role
ORA-06512: at "SYS.DBMS_JAVA", line 313
ORA-06512: at line 1

Followup July 18, 2006 - 8am US/Eastern:

after seeing the dbms_java call - I'm inclined to believe the java installation is botched there -
that call should NOT be failing like that.

Please utilize support for this one.


I resolved the SYS.DBMS_JAVA error by changing the username to uppercase.

Like this:

exec dbms_java.grant_permission( 'AOSDEV', 'SYS:java.io.FilePermission',
'/mnt/home1/toprod/asint/', 'read' )

Let's use a table function...

March 14, 2009 - 3:39 am UTC

Reviewer: Anthony Wilson from Perth, Western Australia

My standard solution to this:
create or replace type dir_entry as object (
   file_type varchar2(1),
   readable varchar2(1),
   writeable varchar2(1),
   hidden varchar2(1),
   file_size number,
   modified date,
   name varchar2(4000)
);
/

create or replace type dir_array as table of dir_entry;
/

create or replace and compile java source named "Util" as

import java.io.File;
import java.io.FilenameFilter;
import java.io.IOException;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Timestamp;

import oracle.sql.ARRAY;
import oracle.sql.STRUCT;
import oracle.sql.ArrayDescriptor;
import oracle.sql.StructDescriptor;

import oracle.jdbc.driver.OracleDriver;

public class Util {

    private static Connection conn;

    static {
        try {
            conn = (new OracleDriver()).defaultConnection();
        } catch (SQLException e) {
            System.out.println(e);
        }
    }


    /**
     * List the files in the directory represented by the given Oracle DIRECTORY
     * object.
     *
     * @param dirname The name of the DIRECTORY object for which we want to list
     * the files (case sensitive).
     * @throws IOException
     * @throws SQLException
     */
    public static ARRAY listFiles(String dirname)
        throws IOException, SQLException {

        String dirpath = getDirectoryPath(dirname);
        File directory = getDirectory(dirpath);

        STRUCT[] ret = fileList(directory);

        // Create an array descriptor and return it.
        ArrayDescriptor desc = ArrayDescriptor.createDescriptor (
            "DIR_ARRAY", conn);

        return new ARRAY(desc, conn, ret);
    }

    /**
     * Create a File object with the abstract pathname given by the parameter.
     *
     * @param dirpath The filesystem path of the directory
     * @throws IOException If the directory represented by this pathname does
     * not exist, or if it is a file.
     */
    private static File getDirectory(String dirpath) throws IOException {

        File directory = new File(dirpath);

        if(!directory.exists()) {
            throw new IOException("Directory: "+dirpath+" does not exist.");
        }
        if(!directory.isDirectory()) {
            throw new IOException("Path: "+dirpath+" is not a directory.");
        }

        return directory;
    }

    /**
     * Get the filesystem path for the Oracle DIRECTORY object given by the
     * input parameter.
     *
     * @param dir The name of the DIRECTORY object for which we want the path.
     * @throws IOException If there is no DIRECTORY object with the given name.
     */
    private static String getDirectoryPath(String dir)
        throws SQLException, IOException {
        String sql = "select directory_path from all_directories where " +
            "directory_name = ?";

        PreparedStatement s = conn.prepareStatement(sql);
        s.setString(1, dir);
        ResultSet rs = s.executeQuery();

        // There should be one row and one only.
        if(rs.next()) {
            return rs.getString(1);
        } else {
            throw new IOException("Directory object "+dir+" does not exist.");
        }

    }

    /**
     * Create an array of STRUCT objects representing the files in the given
     * directory.
     *
     * @param directory The File object representing the directory.
     * @throws SQLException
     */
    private static STRUCT[] fileList(File directory) throws SQLException {

        // Create the array of files to add.
        File[] files = directory.listFiles (
            new FilenameFilter() {
                // Accept all files
                public boolean accept(File dirpath, String name) {
                    return true;
                }
            }
        );

        // No files in directory
        if(files == null) {
            return null;
        }

        STRUCT[] ret = new STRUCT[files.length];

        // Create the struct entry for each file.
        for(int i=0; i<files.length; i++) {
            File f = files[i];
            StructDescriptor sd = StructDescriptor.createDescriptor (
                "DIR_ENTRY", conn);
            Object[] attributes = {
                f.isDirectory() ? "D" : (f.isFile() ? "F" : "U"),
                f.canRead() ? "Y" : "N",
                f.canWrite() ? "Y" : "N",
                f.isHidden() ? "Y" : "N",
                new Long(f.length()),
                new Timestamp(f.lastModified()),
                f.getName()
            };
            STRUCT s = new STRUCT(sd, conn, attributes);
            ret[i] = s;
        }

        return ret;
    }

}
/

create or replace package util as
   function ls(p_dirname in varchar2) return dir_array;
end;
/

create or replace package body util as
   function ls(p_dirname in varchar2) return dir_array is
   language java
   name 'Util.listFiles(java.lang.String) return oracle.sql.ARRAY';
end;
/

Then you can run queries like the following:
  1  select *
  2  from table(util.ls('YOUR_DIR_NAME'))
  3  where name like '%.zip'
  4  and modified >= sysdate - 3
  5* and file_size > 1000000
SQL> /

F R W H  FILE_SIZE MODIFIED  NAME
- - - - ---------- --------- ----------------------------------------
F Y Y N    1321135 09-MAR-09 w00076.20090309101312.zip
F Y Y N    1321337 09-MAR-09 w00076.20090309102948.zip
F Y Y N    1321576 09-MAR-09 w00076.20090309112136.zip
F Y Y N    1320715 09-MAR-09 w00076.20090309110427.zip
F Y Y N    1321437 09-MAR-09 w00076.20090309123505.zip

Hope that helps...

cheers,
Anthony

Packaged pipelined function to list directory

August 24, 2009 - 4:05 am UTC

Reviewer: Adrien Sales from France

Hi,

i've developped a set of packages (11g), and one of them does the "list directory contents"
and provides the output in a pipelined table with all properties that
are available from Java. I've alos added checksum and Mime-Type features
so you can easily list files, check corruption, file types
straight from SQL.
Here is a call example :

<--
select *
from table(LIST_FILES(iDirectory => 'H:\Incoming', -- The dir
iMatchStrIngRegExp => NULL,-- All files
iJackSumAlgorithm => 'crc32',
iJacksumEncodIng => 8,
iJacksumSeparator => NULL,
iJacksumFormat => '#CHECKSUM',
iJacksumGroupingIndex => -1,
iJacksumGroupingSymbol => NULL,
igetMimeType => 1
));
-->

It returns such pipelined table :

FILE_ABSOLUTE_PATH NVARCHAR2(255),
FILE_CANONICAL_PATH NVARCHAR2(255),
FILE_NAME NVARCHAR2(255),
FILE_PARENT NVARCHAR2(255),
FILE_PATH NVARCHAR2(255),
PATH_SEPARATOR NVARCHAR2(1),
PATH_SEPARATOR_CHAR NVARCHAR2(1),
SEPARATOR NVARCHAR2(1),
SEPARATOR_CHAR NVARCHAR2(255),
IS_FILE_READABLE INTEGER,
IS_FILE_WRITABLE INTEGER,
IS_FILE_EXISTING INTEGER,
FILE_HASH_CODE NVARCHAR2(255),
IS_FILE_ABSOLUTE INTEGER ,
IS_FILE_DIRECTORY INTEGER,
IS_FILE INTEGER,
IS_FILE_HIDDEN INTEGER,
LAST_MODIFIED INTEGER,
LAST_MODIFIED_DATE TIMESTAMP,
LENGTH_KO INTEGER,
LENGTH_MO NUMBER,
JACKSUM_ALGORITHM VARCHAR2(10),
JACKSUM_BRUTE_HASH VARCHAR2(500),
JACKSUM_FORMATTED_HASH NVARCHAR2(2000),
MIME_DESCRIPTION VARCHAR2(100),
MIME_EXTENSION VARCHAR2(10),
MIME_TYPE VARCHAR2(100),
MIME_COMPARATOR VARCHAR2(100)

Have a look at screenshot for more details :

https://sourceforge.net/project/screenshots.php?group_id=255704

Hope this helps.

Regards,

Adrien

Re: Michel Cadot's on Mette's modified DirList

September 10, 2009 - 1:39 pm UTC

Reviewer: Doug Hersh from Charlestown MA US

Michel Cadot correctly noted that Mette's modified DirList (which retrieves filename, length, type and date modified) requires "read privilege on all the files in the directory and not only on the directory itself."

For the /tmp directory in Tom's example, this is accomplished by executing:

exec dbms_java.grant_permission ('OPS$TKYTE', 'SYS:java.io.FilePermission', '/tmp/*', 'read' )

The asterisk provides access to all the files in the directory. Without this privilege (i.e. without the asterisk), for example:

exec dbms_java.grant_permission ( 'OPS$TKYTE', 'SYS:java.io.FilePermission',
'/tmp', 'read' )

a call to the modified DirList would produces an error message like this one:

-----
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException:

the Permission (java.io.FilePermission '/tmp/somefilename' read) has not been granted to EQLOAD. The PL/SQL to grant this is dbms_java.grant_permission( 'OPS$TKYTE',
'SYS:java.io.FilePermission', '/tmp/somefilename', 'read' )
-----

Also, on our Windows server (with RDBMS 9.2.0.8.0), I find that the drive letter is case-sensitive. If a permission is created for 'C:\tmp\*' then input to DirList must be an upper-case "C". The filename itself is case-insensitive.

Everything compiled correctly but no file shown

January 15, 2010 - 2:23 pm UTC

Reviewer: Ligon from Canberra, ACT, Australia

Hi Tom,

I tried all the step you have explained correctly. All the objects created/compiled correctly and the permissions are set as requested.

I then did exec get_dir_list( 'C:\Temp' );

and then select * from dir_list

but no results.

Since there is no error message I really can't figure out why the table not getting populated, I have even tried to change the GTT to "preserve" but won't help.

I have tried this both on 10g in Unix box and 11g in windows 7.

Hope you could help

Kind Regards
Ligon
Tom Kyte

Followup  

January 18, 2010 - 5:27 pm UTC

perhaps c:\temp on the server is empty, you do know that c:\temp is on the DATABASE server right? Not your pc.

put some debug in there. You could insert a row or two of your own into the table to give yourself a message about what is happening.

Pipelined ??

January 19, 2010 - 12:27 pm UTC

Reviewer: bc from macomb twp, mi

Hey Tom,

Just wondering if it would be possible to have the java code return the directory entries as piped rows instead of populating a global temporary table ?

If yes, how would you do it ?

Thanks

Tom Kyte

Followup  

January 19, 2010 - 5:02 pm UTC

the java code would not pipeline, plsql code can. the java code could return an object type (a collection) and that could be selected from though.

so you would change the code to return an object type (collection) and then you could select * from table(java_stored_procedure)

Pipelined ??

January 26, 2010 - 12:26 pm UTC

Reviewer: bc from macomb twp, mi

Thanks Tom

Directory entries as piped rows

March 02, 2010 - 7:36 am UTC

Reviewer: Adrien Sales from France

Hi,

such a function has already been fully implemented in
open source oracle-jutils project. Once the function called you get a pipelined table. I did develop it for 11g (JDK 1.5)

https://sourceforge.net/projects/oracle-jutils/

Screenshots :
http://oracle-jutils.sourceforge.net/img/JDBMS_FILESYSTEM_1.png

http://oracle-jutils.sourceforge.net/img/JDBMS_FILESYSTEM_2.png

In fact you even do get more informations than basic metadatas.

it is very easy to use :

select *
from table(LIST_FILES(iDirectory => 'H:\Incoming', -- The dir
iMatchStrIngRegExp => '.*zip',-- All zip files
iJackSumAlgorithm => NULL,
iJacksumEncodIng => NULL,
iJacksumSeparator => NULL,
iJacksumFormat => NULL,
iJacksumGroupingIndex => -1,
iJacksumGroupingSymbol => NULL,
igetMimeType => 0
));

You have plenty of examples in the source distribution.
Hope this helps.

Best regards,

Adrien



Tom Kyte

Followup  

March 02, 2010 - 8:47 am UTC

thanks!

Code didnt complied

March 25, 2010 - 3:48 am UTC

Reviewer: Arc

create or replace procedure get_dir_list( p_directory in varchar2 )
as language java
name 'DirList.getList( java.lang.String )';


Error(4): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; The symbol ";" was substituted for "end-of-file" to continue.
Tom Kyte

Followup  

March 26, 2010 - 11:25 am UTC

ops$tkyte%ORA10GR2> create or replace procedure get_dir_list( p_directory in varchar2 )
  2      as language java
  3      name 'DirList.getList( java.lang.String )';
  4  /

Procedure created.



so, cut and paste or at least tell us how you compiled it.

I literally copied your code above and pasted into sqlplus.

Unix command with PLSQL

April 15, 2010 - 3:54 pm UTC

Reviewer: Kenya from TX USA

I tried the above codes to satisfy my need but it just doesnt work:
my task:create a pl/sql logic to call a unix command(e.g /ls *.dat > test.txt) that copy file names(*.dat) from a directory into test.txt file.
I executed all the above objects successly but it fails to writes names into test.txt....any idea??
Tom Kyte

Followup  

April 16, 2010 - 9:14 am UTC

think about it.

Is

"ls *.dat > test.txt"

a COMMAND, a program, an executable....


or, is it

a script that a shell command processor interprets and processes.


It is the latter, it is NOT a command, it is a "script", input, to a shell.


You'd need to put that into a script and run the shell script.

Or, just run the ls command - as you get the output, and you write that to test.txt if you wanted it in a file.


reading filesysystem

April 17, 2010 - 2:14 pm UTC

Reviewer: A reader


Compiling your Java code in a specific schema

July 21, 2010 - 5:04 pm UTC

Reviewer: Nevin from Cedar Rapids, IA USA

Tom,

I'm trying to incorporate what you have written above into a specific schema so its not created under the user I'm logged in as. I thought it would be as simple as added "<schema>." in front of the temporary table (myschema.dir_list), the Java source (myschema."DirList"), the insert statement within the Java of course, and the procedure used to call the Java (myschema.get_dir_list).

But when I do that, I get the following error:

Error starting at line 121 in command:
EXEC eapp.get_dir_list( 'D:\test' )
Error report:
ORA-29540: class myschema/DirList does not exist
ORA-06512: at "MYSCHEMA.GET_DIR_LIST", line 1
ORA-06512: at line 1
29540. 00000 - "class %s does not exist"
*Cause: Java method execution failed to find a class with the indicated name.
*Action: Correct the name or add the missing Java class.
Tom Kyte

Followup  

July 23, 2010 - 9:03 am UTC

full test case so we can see what you did

Excellant material...gr8

September 23, 2010 - 3:08 pm UTC

Reviewer: pavan from USA


how to get directory from share path or ip-address?

December 20, 2010 - 12:19 am UTC

Reviewer: John from SG

Hi Tom,

how to read the file by using path?
can you provide an example?

-------------------------------------
exec get_dir_list( 'C:\TESTING' );

** this is ok!
-------------------------------------

how about i wan to take from share drive or by ip-address?
exec get_dir_list( '\128.53.1.51\it share\SHARE\Drivers\System\John\testing' );


Same using your sample
this >>>>> /path/to/where/the/stuff/actually/is
but how it works?

Thanks Tom.
John
Tom Kyte

Followup  

December 20, 2010 - 7:46 am UTC

well, you would have to use a UNC name - which you didn't. that would not work form the command line. Use something that works from the command line

and they you'd have to make sure the account the oracle software is running as (the account the oracle SERVICE on windows is running as) can 'see' that UNC named object as well.

remember - just because YOU can see it does not mean the account the Oracle software is running as can see it.

how to read a txt files and insert into DB table?

January 02, 2011 - 7:43 pm UTC

Reviewer: John from SG

Hi TOM,

I would like to ask about how to read a txt file and insert into database table by using PL/SQL?

Thanks.
John
Tom Kyte

Followup  

January 03, 2011 - 8:57 am UTC

easiest way - use an external table (search for that term on this site, tons of example)

harder way - use sqlldr (do not use plsql)

hardest way - use utl_file to open the file, read a line of text, parse it, insert it, read next line, and so on....


I have this error using external table way

January 03, 2011 - 6:44 pm UTC

Reviewer: John from SG

Hi Tom,

I still encounter of this error. I don't understand of this error. What is this problem and how to solve?
thanks.

SQL> SELECT * FROM EXTERNAL_TABLE;
SELECT * FROM EXTERNAL_TABLE
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file TEST.txt in DATA_DIR not found
ORA-06512: at "SYS.ORACLE_LOADER", line 19

I have this error using external table way

January 03, 2011 - 8:36 pm UTC

Reviewer: John from SG

Tom,

I found the problem. It seem like does not get the latest txt file inside my C:\testing folder. Some older txt files, i can read the content inside but not the latest content in the txt file.
I have been tried update my txt file content to this:
1001,traditional,english

but cannot get it, it only can get the previous data inside the txt file even I've modified before.
How can solve this issues?

Thanks....

SUCCESSFUL!!!

January 04, 2011 - 12:20 am UTC

Reviewer: John from SG

Tom,

Very happy and exciting. Finally i know what is the error and solved it. Yeah...
Now proceed to research on read files from another share server. How to create the directory for the share path?
Like \\128.53.1.51\testing ?

Thanks Tom.
John

What if I'm tryng to use Oracle XE ?

April 08, 2011 - 4:12 am UTC

Reviewer: Alessandro Malaguti from Bologna, Italy

Sadly, Oracle express edition (even last 11gR2 XE on beta) still don't support java.


http://download.oracle.com/docs/cd/E17781_01/license.112/e18068/toc.htm#BABHACFJ


Is there any other way than java to grab file information (such as timestamp and dir list) from the oracle local filesystem?


Tom Kyte

Followup  

May 23, 2011 - 11:49 am UTC


I haven't tried it yet on XE, but...

what if you had a script:

...
#/bin/bash
cd somewhere
/bin/ls
....

and a table:

CREATE TABLE ls
(
  line varchar2(255)
)
ORGANIZATION external
( TYPE oracle_loader
  DEFAULT DIRECTORY load_dir
  ACCESS PARAMETERS
  ( RECORDS DELIMITED BY NEWLINE
    preprocessor  exec_dir:'run_ls.sh'
    FIELDS TERMINATED BY "|" LDRTRIM
  )
  location ( 'run_ls.sh')
)
/



where exec_dir pointed to the directory where the script was located....

selecting from that gives you a list of files in that directory. Would work on windows too...

get_dir_list with UNC path at 11gR2 rac at windows hosts

April 13, 2011 - 9:54 am UTC

Reviewer: Juergen from Stuttgart, Germany

Hi Tom, I am using this get_dir_list function for years with 10g and 11g single instance db's at windows hosts, everythings working excellent. I had to run the database service and the listener service with a domain user when I am using UNC path's at other servers. It works perfect.

But how to use UNC path's at 11gR2 (11.2.02) RAC running on 2 windowows 2008 servers. I Tried to run the database service and the local Lister on one node with a domain user. It also doesn't work if I run only the database service with a domain user. In both cases I can't start the database service on that node. Which Services I need to run with domain users? Or any other config change necessary? What about the scan listeners?

Thanks in advance.
Regards
Juergen
Tom Kyte

Followup  

April 13, 2011 - 10:08 am UTC

Sorry, I don't do "windows" at all - I know very little about administering it. I'll have to refer you to support or to otn.oracle.com discussion forums.

problems with äöüÄÖÜ in filenames

May 16, 2011 - 4:30 pm UTC

Reviewer: Thomas from Germany

Hello Tom,

I have a little problem with foreign characters in the file names.

I am testing on a Oracle 11 on a windows XP OS.

Here is my test case:

I created 2 files in directory "C:\Test\" - testü.txt and testu.txt

This is the code I use:

create or replace and compile java source named dirlist as
import java.io.*;
import java.sql.*;

public class dirlist
{
  public static void getList(String directory) throws SQLException
  {
    File f = new File(directory);
    
    System.out.println(f.isDirectory());

    File[] filenames = f.listFiles();
    for (File s : filenames)
    {
      System.out.println("Is Directory: "+s+" "+s.isDirectory());
      System.out.println("Is File: "+s+" "+s.isFile());
    }
  }
}

CREATE OR REPLACE PROCEDURE GET_DIR_LIST(p_directory IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'dirlist.getList(java.lang.String)';


running:
begin
  dbms_java.SET_OUTPUT(100000);
  get_dir_list('C:\Test');
end;


the output is:

true
Is Directory: C:\Test\testu.txt false
Is File: C:\Test\testu.txt true
Is Directory: C:\Test\testü.txt false
Is File: C:\Test\testü.txt false
                           =====


The file testü.txt is not recognized as a file. What do I have to do in order to read and write the "äöü" files correct ? I hope there some "settings" I can change.

My Database is a 11.2.0.1.0
NLS_CHARACTERSET = AL32UTF8 / NLS_NCHAR_CHARACTERSET = AL16UTF16

Windows XP Home Edition - German - SP3

Thanks a lot
Thomas
Tom Kyte

Followup  

May 18, 2011 - 3:53 am UTC

this'll be tricky for me, I don't know how to create a file with that name :)

why don't we start by implementing get dir list from above - and iterating over the names you receive and see what they look like - and see what happens if you turn around and "stat" them after you retrieve them?

I tried this.

May 22, 2011 - 3:54 am UTC

Reviewer: Thomas from Germany

Hi,

the DUMP shows the character as 195,188 (CHR(195*256+188)) - it is also available in the "English ASCII list" - as CHR(129).

I implemented the dirlist and get the same result - files come back just perfect (as I see them on the OS filesystem) - but when I get try to open them with java I hit the "file does not exists" wall.


When I create the file with java (testü.txt) - I find on the file system a file called "testļ.txt" (Upper A with something above and the 1/4 sign) - This file now works when I access it as "testü.txt" from Java - and also the above procedure will show it as "testü.txt" (IS file TRUE) in the dir listing - but when I run a dir on the windows file system I see the "testA¼.txt" (uppercase A with the 1/4 char).

I wonder if some character set conversion is done wrong somewhere. I did some re-search on google but found nothing yet.

Any help is appreciated :-)

Thanks
Thomas
Tom Kyte

Followup  

May 23, 2011 - 11:52 am UTC

sounds like a unicode-ism of java at work.

What I would suggest is putting together the minimal test case that demonstrates this issue (as small as humanly possible) and filing a support ticket - make sure to like out all of the character sets like that of the database, the clients setting, etc.

read the docs

May 23, 2011 - 2:57 pm UTC

Reviewer: Sokrates


f.isFile() does not return true if and only if f is a file but not a directory
it does return true if and only if f is a normal file (not a directory and "satisfies other system-dependent criteria")

see http://download.oracle.com/javase/6/docs/api/java/io/File.html#isFile( )


so probably you are on a system where äöüÄÖÜ in the filename is a criterium for not being normal ?

Thomas, did you use java to create this file ?

Why does the db see a different list of files?

May 28, 2011 - 12:39 pm UTC

Reviewer: Joe from Leesburg, VA, USA

Tom,

I used your original code and the variation to the Java code that Mette from Denmark provided back in Oct 2006 and I get an output but it's not what I'm expecting.

After executing the procedure the dir_list table indicates that there are 4 directories at the location that I am interested in. But if I navigate to that same location I see a totally different list of directories.

Here is a copy / paste of the session:

SQL*Plus: Release 10.1.0.5.0 - Production on Sat May 28 13:05:14 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exec get_dir_list('/sparc/dev1/inbound');

PL/SQL procedure successfully completed.

SQL> select * from dir_list;

FILENAME                       FILETYPE
------------------------------ ----------
03-16-2011                     DIRECTORY
03-15-2011                     DIRECTORY
03-18-2011                     DIRECTORY
03-17-2011                     DIRECTORY

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oradev3@vapd-erp-frm2 inbound]$ cd /sparc/dev1/inbound
[oradev3@vapd-erp-frm2 inbound]$ ls -al
total 20
drwxr-xr-x 5 oradev1 dba 4096 May 27 19:37 .
drwxr-xr-x 5 oradev1 dba 4096 Mar 29 15:22 ..
drwxr-xr-x 2 oradev1 dba 4096 Apr  6 11:55 03-15-2011
drwxrwxr-x 2 oradev1 dba 4096 May 28 11:34 bankstatements
drwxr-xr-x 2 oradev1 dba 4096 Mar 29 15:32 boa
[oradev3@vapd-erp-frm2 inbound]$


Any ideas as to why the database is seeing a different set of files?

Here is the code that I'm executing:
CREATE GLOBAL TEMPORARY TABLE DIR_LIST
(
   filename       VARCHAR2 (50),
   filetype       VARCHAR2 (10)
)
ON COMMIT DELETE ROWS;

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "DirList"
   AS import java.io.*;
import java.sql.*;
import java.util.Date;
import java.text.SimpleDateFormat;

    
    public class DirList
    {
    public static void getList(String directory)
                      throws SQLException
   {
       File path = new File( directory );
       String[] list = path.list();
       String element;
          
       for(int i = 0; i < list.length; i++)
       {
           element = list[i];
           
           
           String fpath=directory+"/"+list[i];  
                                
           File f = new File(fpath);
         
           String ftype;
           
           if (f.isFile()) {
           
             ftype = "FILE";
             
           } else {
               if(f.isDirectory()) {
               
               ftype = "DIRECTORY";
               
             } else {
               
               ftype = "OTHER";
               
             }
           }
        
           #sql { INSERT INTO DIR_LIST (FILENAME, filetype)
                  VALUES (:element, :ftype) };
       }
   }
}
/

CREATE OR REPLACE PROCEDURE get_dir_list (p_directory IN VARCHAR2)
AS
   LANGUAGE JAVA
   NAME 'DirList.getList(java.lang.String)';
/


Tom Kyte

Followup  

May 31, 2011 - 9:47 am UTC

when you peek at the filesystem - are you on the database server itself? That is not clear to me here.

Why does the db see a different list of files?

May 31, 2011 - 1:49 pm UTC

Reviewer: Joe from Leesburg, VA

Tom,

Thanks for your response. That was it. I was on the Apps tier when running my script via SQL*Plus and navigating to that location on the filesystem. The /sparc/dev1/inbound path on the database tier was pointing to a different location when compared to the Apps tier. Had my dba correct that and it works now.

Thanks for the tip.

August 02, 2011 - 9:06 am UTC

Reviewer: A reader

Thank dear Tom,

I have a question.I have too much files that extend the string length and returns an error
ORA-24345: A Truncation or null fetch error occurred
Do you have any idea what should I do?

thank you in advance
Sara

Tom Kyte

Followup  

August 02, 2011 - 11:33 am UTC

do a bit of debugging, unless you can tell me how to reproduce this - I don't have any idea what line of code it is happening on.

Since I don't fetch any data - not sure how this could be happening in the java code.

why don't you cut and paste just like I did - in its entirety - the creation of everything and how you get the error.

Look at my initial answer on this page, I'm looking for something like that - 100% complete.

Use oracle directory object

August 29, 2011 - 5:38 pm UTC

Reviewer: Jerry from Norwich, vt

Can you create a java procedure that would take an Oracle Directory Object name and produce the list of files? If so, can this solve the problem of running on something other than the system you are running SQL from like the database server?
Tom Kyte

Followup  

August 31, 2011 - 1:59 pm UTC

sure, you would take the directory name passed to you and and select from dba_directories to translate it into an OS directory name.

alternatively, using a new external table feature, you could:


ops$tkyte%ORA11GR2> CREATE TABLE ls
  2  (
  3    line varchar2(4000)
  4  )
  5  ORGANIZATION external
  6  ( TYPE oracle_loader
  7    DEFAULT DIRECTORY load_dir
  8    ACCESS PARAMETERS
  9    ( RECORDS DELIMITED BY NEWLINE<b>
 10          preprocessor  exec_dir:'run_ls.sh'</b>
 11      FIELDS TERMINATED BY "|" LDRTRIM
 12    )
 13    location ( 'run_ls.sh')
 14  )
 15  /

Table created.




and then just:



ops$tkyte%ORA11GR2> select * from ls;

LINE
-------------------------------------------------------------------------------
allobjects.dat
ALL_OBJECTS_UNLOAD_5189.log
...
termout.sql
VMwareDnD
xtmpx2011082327706.sql

52 rows selected.



The run_ls script is just:

ops$tkyte%ORA11GR2> !cat run_ls.sh
#!/bin/bash
cd /tmp
/bin/ls



Using this technique you could actually select * from just about any unix command you wanted to - like ps, whatever...


Shebang incomplete ?

August 31, 2011 - 9:03 am UTC

Reviewer: Sokrates

I suppose your script run_ls.sh is supposed to begin with
#!/bin/bash
and not with
#/bin/bash
?
Tom Kyte

Followup  

August 31, 2011 - 1:58 pm UTC

doh, good catch.

The # just made it a comment :)

My default shell is /bin/bash so it worked by accident.

I corrected it above

Superb

September 13, 2011 - 5:54 am UTC

Reviewer: Saurabh Aneja from India.

I have only one question, how #sql {insert into } works here, what is this exactly, please give the clarification on it.
Tom Kyte

Followup  

September 13, 2011 - 6:25 am UTC

it is sqlj, a standard

http://en.wikipedia.org/wiki/SQLJ

OK

October 03, 2011 - 7:52 am UTC

Reviewer: Kumar from Pune,India

Hi Tom,
suppose if we want to login into a server and read its folders using this program, what modifications would it need?
I feel we need to have a connection to the server.
Any java class exists to do so?

Thank you!
Tom Kyte

Followup  

October 03, 2011 - 10:32 am UTC

you don't "login to a server and read its folders" with this.

You can use this to read a directory you have been granted access to on the database server alone.

So, you log into a database, you run this procedure, and you see a directory you were granted access to.

This is not for reading a remote directory.

Please Help me

November 25, 2011 - 5:07 pm UTC

Reviewer: Lanfra from Milano

Hi, I find your "fantastic" code.
I have implemented it with an other parameter, it inserted in the record in the table.

This is the code

##### Code
--ABILITAZIONI

GRANT JAVAUSERPRIV to SEANAG;



--TABELLA TEMPORANEA

DROP TABLE U_DIR_LIST_ANAG;

create table U_DIR_LIST_ANAG

( filename varchar2(255),

ticket number);

GRANT ALTER, DELETE, INSERT, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON SEANAG.U_DIR_LIST_ANAG TO SEANAG_USE;



--APPS JAVA

create or replace

and compile java source named "DirListAnag"

as

import java.io.*;

import java.sql.*;



public class DirListAnag

{

public static void getList(String directory, int ticket)

throws SQLException

{

File path = new File( directory );

String[] list = path.list();

String element;



for(int i = 0; i < list.length; i++)

{

element = list[i];

#sql { INSERT INTO U_DIR_LIST_ANAG (FILENAME, TICKET)

VALUES (:element, :ticket) };

}

}



}



--STORED PROCEDURE

create or replace

procedure u_get_dir_list( pc_directory in varchar2, pn_ticket IN number )

as language java

name 'DirListAnag.getList(java.lang.String, java.lang.int)';



--EXE

exec u_get_dir_list( 'C:\Unione_report\Temp\CSV\', 12345 );


#####

When it work the result is an error.
Ora29531 nessun metodo getList nella classe DirListAnag
ora06512 ....

Is it a problem about privileges????


11g

February 13, 2012 - 3:30 pm UTC

Reviewer: A reader

having 11g -- is there any "new" way of finding/displaying trace files with pl/sql or maybe by enterprise manager or sql developer?
regards,
Tom Kyte

Followup  

February 13, 2012 - 4:27 pm UTC

sql developer can - but that didn't require 11g.

tkprof and sql developer are two tools we have to interact with trace files

what about ...

February 14, 2012 - 6:45 am UTC

Reviewer: Sokrates

...tkprof and sql developer are two tools we have to interact with trace files...

what about adrci ?

the
SHOW TRACEFILE
command at least allows you to search for tracefiles
http://docs.oracle.com/cd/E11882_01/server.112/e22490/adrci.htm#BGBHHBGB
Tom Kyte

Followup  

February 14, 2012 - 8:57 am UTC



better link:

http://docs.oracle.com/cd/E11882_01/server.112/e22490/adrci.htm#SUTIL1475

I keep forgetting about that one for 11g, yes - thanks!

Need some help

August 03, 2012 - 3:26 am UTC

Reviewer: Dima from Ukraine

I've just tried to execute your code using Oracle Database 10g and PL/SQL Developer v.7.0.0.1050. It executed perfectly in both ways either using SQL*plus or PL/SQL Developer (folder and procedure created), but there is a problem - I see results in SQL*plus and don't see in PL/SQL Developer. What should I do?
Tom Kyte

Followup  

August 15, 2012 - 5:11 pm UTC

I don't use plsql developer - if you just execute:

begin dbms_output.put_line( 'hi' ); end;

in plsql developer do you see any output? if not, figure out from their documentation how to turn it on in their tool.

Dima

August 16, 2012 - 2:05 am UTC

Reviewer: Sokrates

... I see results in SQL*plus and don't see in PL/SQL Developer. What should I do? ...


- Tools -> Preferences -> Oracle -> Output -> Enabled
- File -> New -> Test Window -> begin dbms_output.put_line('hi');end; -> F8 -> Tab "DBMS Output" shows hi

They also have a good forum on
http://allroundautomations.com/
-> Support -> Online Forum -> PL/SQL Developper

Windows files from Unix

July 08, 2013 - 1:46 am UTC

Reviewer: A reader

Hi Tom,

How do I get windows files date and time from unix with PL/SQL.

Thanks

Tom Kyte

Followup  

July 16, 2013 - 2:28 pm UTC

ummm, are the windows file systems mounted to the unix box? If not, it would be pretty scary to think that plsql could reach out to a client machine - any client machine - and read its file system.

and if the windows file system is available on unix, then the answer is "the same way you get a unix file date and time"

Read File creation date

October 25, 2013 - 7:53 pm UTC

Reviewer: Chintan Gandhi from India

Hi,

I am able to implement above code and read directory files. Many thanks for this post.

In addition to this, I would also like to read file creation date. Can you please advice how can I achieve it?

Thank you in advance.
Tom Kyte

Followup  

November 01, 2013 - 8:59 pm UTC

just read up on the necessary java code I suppose?

you have the technique, as a programmer - you should be able to fill in the blanks right?


but here is another interesting approach, available in 10.2.0.5 and up:



ops$tkyte%ORA11GR2> !cat /tmp/run_ls.sh
#/bin/bash
cd /tmp
/bin/ls -l




ops$tkyte%ORA11GR2> CREATE or replace DIRECTORY exec_dir AS '/tmp'
  2  /

Directory created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> CREATE TABLE ls
  2  (
  3    line varchar2(255)
  4  )
  5  ORGANIZATION external
  6  ( TYPE oracle_loader
  7    DEFAULT DIRECTORY exec_dir
  8    ACCESS PARAMETERS
  9    ( RECORDS DELIMITED BY NEWLINE
 10          preprocessor  exec_dir:'run_ls.sh'
 11      FIELDS TERMINATED BY "|" LDRTRIM
 12    )
 13    location ( 'run_ls.sh')
 14  )
 15  /

Table created.

ops$tkyte%ORA11GR2> select * from ls;

LINE
-------------------------------------------------------------------------------
total 1048
-rw-rw----. 1 ora11gr2 ora11gr2 5251072 Nov  1 14:15 foo.dbf
drwx------. 2 tkyte    tkyte       4096 Jun 30 09:33 keyring-M8U9Ag
-rw-r--r--. 1 ora11gr2 ora11gr2    1153 Nov  1 16:52 LS_31821.log
drwx------. 2 gdm      gdm         4096 Oct 28 17:36 orbit-gdm
drwx------. 2 gdm      gdm         4096 Oct 28 17:36 pulse-QFmfTa6orL3r
drwx------. 2 tkyte    tkyte       4096 Jun 30 09:33 pulse-SLM7wKM200Dt
-rwxrwxr-x. 1 tkyte    tkyte         30 Nov  1 16:52 run_ls.sh

8 rows selected.

File with Chinese Character

February 12, 2015 - 6:23 am UTC

Reviewer: Erwin Hermanto from Indonesia

Hi Tom

I've been using this code for quite some time
But recently I bumped into trouble with getting a filename with Chinese character (example: 121150039 石药欧意.pdf)

after I executed get_dir_list(file_path)
and query SELECT * FROM DIR_LIST on SQL Developer
it shows as 121150039 ????.pdf
I need to process the file in Oracle procedure using DBMS_LOB.fileopen, but it returns me ORA-22288

The NLS_CHARACTER set is already set into ALU32UTF8
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE parameter = 'NLS_CHARACTERSET'

Could you help me with this?

Thank You in advance

February 26, 2015 - 6:29 pm UTC

Reviewer: zayed saad from Yemen

thanks tom,
how can i exec get_dir_list( 'C:\Temp\Extract' ); by using forms 6i
also the table dir_list sid not show any rows in plsdl devloper,but in oracle sql does,
why

February 26, 2015 - 6:30 pm UTC

Reviewer: zayed saad from Yemen

thanks tom,
how can i exec get_dir_list( 'C:\Temp\Extract' ); by using forms 6i
also the table dir_list did not show any rows in plsdl devloper,but in oracle sql does,
why

Dated, better ways with less coding

February 28, 2017 - 10:06 pm UTC

Reviewer: TR Rudkin from Tucson AZ

The code of this solution is correct and useful. The issue lies with the fact there is code in Java. If you are a Java programmer that is well and good. But if not and something goes wrong then there code be issues and if you started relying on this code and something goes wrong it could be hard to resolve.

A better solution is to use external files and the preprocessor feature of external files. A simple shell script that gets the listing and adds commas and then presents as an external table.

http://plsql.me/taking_stock shows using a shell script as a preprocessor to get information.
Connor McDonald

Followup  

March 01, 2017 - 2:02 am UTC

Agreed. This is why you can see on AskTom we have a

"Is this answer out of date? If it is, please let us know via a Review"

facility. Because, as you can see, this question was first asked 17 years ago !

April 10, 2017 - 2:39 pm UTC

Reviewer: Sandra Guedelha from Brazil

Hi,
I used this java source for a long time, but I get error on 12c, compilation error: (sorry for my english)
An exception has occurred in the compiler (1.8.0_121-ea). Please file a bug against the Java compiler via the Java bug reporting page ( http://bugreport.java.com ) after checking the Bug Database ( http://bugs.java.com ) for duplicates. Include your program and the following diagnostic in your report. Thank you.
java.lang.IllegalArgumentException

Please, help me


Connor McDonald

Followup  

April 11, 2017 - 12:39 am UTC

I would ditch the Java. There are easier ways now in later releases

Take a look here

http://www.oracle-developer.net/display.php?id=513

http://www.oracle.com/technetwork/issue-archive/2011/11-mar/o21nanda-312277.html

Solution does not work in 12.2

August 30, 2017 - 10:30 am UTC

Reviewer: Paul from Netherlands

Connor McDonald

Followup  

August 31, 2017 - 7:44 am UTC

Agreed. External tables are the way to go now.

http://www.oracle-developer.net/display.php?id=513

Still doesn't work...

February 08, 2018 - 12:05 pm UTC

Reviewer: Klaus Klöser from Düsseldorf, Germany

Hi,
I tested with a 12.0.1 EE database on Windows.
I followed the instructions exactly on http://www.oracle-developer.net/display.php?id=513 and still get a KUP-04001 Error opening File c:\mydir\sticky.txt
OS message: Access is denied.

The database runs under LOCAL SYSTEM and LOCAL SYSTEM has all OS rights on folder and particularly on sticky.txt (checked).

Directories are set up sufficiently, all rights have been granted.
I can read other text files from the exact same directory as external tables.

What do I miss here, any ideas ?
Thanks in advance
Klaus
Connor McDonald

Followup  

February 09, 2018 - 2:21 am UTC

Show us your *entire* code, including all the DDL

DirList correction

April 17, 2018 - 5:31 am UTC

Reviewer: Scott Wesley from Perth

For anyone using Mette's extended DirList code, there is a correction on mismatching date formats. Replace kk with HH

SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");


Otherwise you get ora-01850 when the file is modified after midnight.
Connor McDonald

Followup  

April 17, 2018 - 6:56 am UTC

Thanks for the input.

Use Oracle FILE_UTIL package to list files in Directory

January 12, 2019 - 5:08 pm UTC

Reviewer: hjiang from TX USA

For example, to find the file information in DATA_PUMP_DIR directory,

select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by mtime;

Problems with java on Oracle 19c

October 17, 2019 - 1:30 pm UTC

Reviewer: Dusan Valasek from Czech

Tom's famous and easy to use way of getting file names from directory stopped to work on Version 19.3.0.0.0.
I have successfully installed java on both CDB and PDB databases using @/u01/app/oracle/product/19/javavm/install/initjvm.sql
But I cannot successfully compile the following. I am not Java programmer at all.


1 create or replace
2 and compile java source named "DirList"
3 as
4 import java.io.*;
5 import java.sql.*;
6 public class DirList
7 {
8 public static void getList(String directory)
9 throws SQLException
10 {
11 File path = new File( directory );
12 String[] list = path.list();
13 String element;
14 for(int i = 0; i < list.length; i++)
15 {
16 element = list[i];
17 #sql { INSERT INTO DIR_LIST (FILENAME)
18 VALUES (:element) };
19 }
20 }
21* }
22 /

Warning: Java created with compilation errors.

dmt_etl_owner@PDB182> show err
Errors for JAVA SOURCE "DirList":

LINE/COL ERROR
-------- --------------------------------------------------------
0/0 DirList:14: error: illegal character: '#'
0/0 10 errors
0/0 ^
0/0 DirList:14: error: not a statement
0/0 #sql { INSERT INTO DIR_LIST (FILENAME)
0/0 ^
0/0 DirList:14: error: ';' expected
0/0 #sql { INSERT INTO DIR_LIST (FILENAME)
0/0 ^
0/0 DirList:14: error: ';' expected
0/0 #sql { INSERT INTO DIR_LIST (FILENAME)
0/0 ^
0/0 DirList:14: error: ';' expected
0/0 #sql { INSERT INTO DIR_LIST (FILENAME)
0/0 ^
0/0 DirList:15: error: illegal start of expression
0/0 VALUES (:element) };
0/0 ^
0/0 DirList:15: error: ';' expected
0/0 VALUES (:element) };
0/0 ^
0/0 DirList:15: error: illegal start of expression
0/0 VALUES (:element) };
0/0 ^
0/0 DirList:15: error: ';' expected
0/0 VALUES (:element) };
0/0 ^
0/0 DirList:18: error: reached end of file while parsing
0/0 }
0/0 ^
0/0 #sql { INSERT INTO DIR_LIST (FILENAME)
dmt_etl_owner@PDB182>

Connor McDonald

Followup  

October 21, 2019 - 6:16 am UTC

sqlj is going away which is why you're getting those errors.

A better way is to use an external table with the preprocessor.

Take a look here

https://asktom.oracle.com/pls/asktom/asktom.search?tag=reading-files-in-a-directory-how-to-get-a-list-of-available-files

another approach for SQLJ (Depreciated in 12.2)

October 21, 2019 - 11:56 am UTC

Reviewer: Rajeshwaran Jeyabal

...
Tom's famous and easy to use way of getting file names from directory stopped to work on Version 19.3.0.0.0.
...


It is not with 19.3, it is due to SQLJ got depreciated in 12.2 database, very recently we migrated an application database from 10g to 12.2 and found the above method/implementation stopped working (and the DBA's team don't even give us the privilege to build a directory object to access the External table pre-processor utility)

so here is an alternate approach to the above method provided by Tom - https://tinyurl.com/y64trp64

Hope this helps.
Connor McDonald

Followup  

October 21, 2019 - 11:58 am UTC

and the DBA's team don't even give us the privilege to build a directory object to access the External table pre-processor utility


That's pretty sad.

Cool solution!

October 22, 2019 - 11:51 am UTC

Reviewer: Dusan Valasek from Czech

Cool solution, described in https://tinyurl.com/y64trp64
Successfully tested!
Just not to forget that java should be installed on both Container and Pluggable databases:

https://itkbs.wordpress.com/2014/02/15/how-to-install-java-in-oracle-database-ora-29538/

And, be careful with:

String sql = "INSERT INTO dir_list (x) values (?)";

x - to be possibly different column name, in my case

String sql = "INSERT INTO dir_list (filename) values (?)";

Thank you, Rajesh!

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.