Skip to Main Content
  • Questions
  • Directory List including modify date and times - casting the date problem

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Tim.

Asked: January 25, 2003 - 5:07 pm UTC

Last updated: November 27, 2020 - 7:19 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

hi Tom,

I started with your dir listing code and it's working out very nice
but I am trying to figure out how to get the rest of the file info using File object. I am having a problem with the dates specifically.
Here's my attempt but as you can see theres a problem with the dates and I cant seem to figure it out..


GRANT JAVAUSERPRIV to SOLIVER;

drop table DIR_LIST;
create global temporary table DIR_LIST ( filename varchar2(255),lastmodified date) on commit delete rows;


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
{
String element;


File path = new File(directory);
File[] FileList = path.listFiles();
String TheFile;
Date ModiDate;
#sql { DELETE FROM DIR_LIST};

for(int i = 0; i < FileList.length; i++)
{
TheFile = FileList[i].getAbsolutePath();
ModiDate = new Date(FileList[i].lastModified());

#sql { INSERT INTO DIR_LIST (FILENAME,LASTMODIFIED)
VALUES (:TheFile,:ModiDate) };
}
}
}
/

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

--implementation
EXEC dbms_java.grant_permission( 'SOLIVER', 'java.io.FilePermission', 'c: emp*','read' );

exec get_dir_list( 'c: emp' );

select to_char(lastmodified,'YYYY-MM-DD HH24:MI:SS'),filename from dir_list order by lastmodified;


I would appreciate any help...

and we said...

well, i never got along with java dates myself. Here is the way I've done it in the past. I'm sure a real java programmer out there can suggest a short path. I'm assuming the problem you were having is the time was 00:00:00 for all -- if so, here is my workaround (suggest you don't use java_sys_priv after 815!):

ops$tkyte@ORA920> create global temporary table DIR_LIST ( filename varchar2(255),lastmodified date ) on commit delete rows;

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace and compile java source named "DirList"
2 as
3 import java.io.*;
4 import java.sql.*;
5
6 public class DirList
7 {
8
9 static private String dateStr( java.util.Date x )
10 {
11 if ( x != null )
12 return (x.getYear()+1900) + "/" + (x.getMonth()+1) + "/" + x.getDate() + " " +
13 x.getHours() + ":" + x.getMinutes() + ":" + x.getSeconds();
14 else return null;
15 }
16
17 public static void getList(String directory)
18 throws SQLException
19 {
20 String element;
21
22
23 File path = new File(directory);
24 File[] FileList = path.listFiles();
25 String TheFile;
26 String ModiDate;
27 #sql { DELETE FROM DIR_LIST};
28
29 for(int i = 0; i < FileList.length; i++)
30 {
31 TheFile = FileList[i].getAbsolutePath();
32 ModiDate = dateStr( new java.util.Date( FileList[i].lastModified() ) );
33
34 #sql { INSERT INTO DIR_LIST (FILENAME,LASTMODIFIED)
35 VALUES (:TheFile, to_date( :ModiDate, 'yyyy/mm/dd hh24:mi:ss') ) };
36 }
37 }
38 }
39 /

Java created.

ops$tkyte@ORA920> show err
No errors.
ops$tkyte@ORA920>
ops$tkyte@ORA920> 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@ORA920>
ops$tkyte@ORA920> exec dbms_java.grant_permission( 'OPS$TKYTE', 'SYS:java.io.FilePermission', '/tmp/*', 'read' )

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> exec dbms_java.set_output(100000);

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> @bigdate
ops$tkyte@ORA920> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

ops$tkyte@ORA920> select * from dir_list;

FILENAME LASTMODIFIED
------------------------- --------------------
/tmp/mcop-tkyte 25-jan-2003 09:18:32
/tmp/.font-unix 14-jan-2003 19:09:26
/tmp/.gdm_socket 14-jan-2003 19:09:31
/tmp/ssh-XX2vjpbQ 27-oct-2002 09:48:22
/tmp/ksocket-tkyte 25-jan-2003 19:55:02
/tmp/ac3 28-dec-2002 10:54:18
/tmp/kde-tkyte 25-jan-2003 09:18:27
/tmp/gaim_tkyte.19403 27-oct-2002 09:48:52
/tmp/.X11-unix 14-jan-2003 19:09:31
/tmp/.X0-lock 14-jan-2003 19:09:31
/tmp/.ICE-unix 25-jan-2003 09:18:29
/tmp/ssh-XXIRgZYj 04-dec-2002 07:12:39
/tmp/gaim_tkyte.31340 04-dec-2002 12:13:25
/tmp/ssh-XXolOTKE 25-jan-2003 09:18:19
/tmp/gaim_tkyte.2033 17-nov-2002 19:09:10
/tmp/orbit-tkyte 25-jan-2003 09:18:44
/tmp/kde-lori 15-jan-2003 15:52:12
/tmp/ksocket-lori 15-jan-2003 16:06:00
/tmp/gaim_tkyte.22754 22-dec-2002 18:49:16
/tmp/hostname.txt 24-jan-2003 17:26:39
/tmp/.wine-tkyte 18-jan-2003 10:27:05
/tmp/test.dat 18-jan-2003 14:28:56
/tmp/mcop-lori 15-jan-2003 16:06:00
/tmp/gaim_tkyte.31779 05-nov-2002 07:51:15
/tmp/nsmail-1.tmp 21-jan-2003 09:10:45
/tmp/nsmail.tmp 21-jan-2003 09:10:25
/tmp/nsmail-2.tmp 21-jan-2003 09:10:45
/tmp/nsmail-3.tmp 21-jan-2003 09:10:45
/tmp/nsmail-4.tmp 21-jan-2003 09:10:45
/tmp/nsmail-5.tmp 21-jan-2003 09:10:45
/tmp/nsmail-6.tmp 21-jan-2003 09:10:45
/tmp/nsmail.html 21-jan-2003 09:10:45
/tmp/nsmail-7.tmp 21-jan-2003 09:10:45
/tmp/nsmail.eml 21-jan-2003 09:10:45
/tmp/audio 28-dec-2002 10:54:18
/tmp/nscopy.tmp 21-jan-2003 09:10:49
/tmp/orbit-root 24-jan-2003 17:32:17
/tmp/80000.trc 25-jan-2003 16:55:01
/tmp/2000.trc 25-jan-2003 16:54:51
/tmp/100.trc 25-jan-2003 16:54:41
/tmp/DbConnection.java.sw 25-jan-2003 19:50:16
p

/tmp/AdobeFnt06.lst.500 30-dec-2002 20:27:58

42 rows selected.

ops$tkyte@ORA920>

Rating

  (22 ratings)

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

Comments

Trying to run /usr/bin/env from PL/sql using Java Stored Procedure"

Jamil Alshaibani, January 26, 2003 - 3:08 am UTC

Hi Tom
Would you please tell me how to run this example because I do not know how to run Java program?
Under this version of SQL PLUS
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production

Can you give an example how to run Java program with ORACLE because I am just started learning Java?
Would you recommend me a god site for learning Java program with oracle?
Thank you very much in advance

Jamil



Tom Kyte
January 26, 2003 - 9:17 am UTC

Hey, i just cut and pasted a SQLPlus session -- type in exactly what you see on this page and wah-lah, you have your java program.

you might be interested in:

</code> http://www.amazon.com/exec/obidos/ASIN/1861006020/ <code>

which is a book geared towards plsql programmers who want to do a little java in the database.

Thanks..

Tim, January 26, 2003 - 9:28 am UTC

That worked great thanks for the help.

One question what is @bigdate ?

Did what I wanted without it but just curious.


Tom Kyte
January 26, 2003 - 9:54 am UTC

ops$tkyte@ORA920> @bigdate
ops$tkyte@ORA920> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.


it just does that alter session for me 

Why

A reader, January 27, 2003 - 12:04 am UTC

Hi, Tom,
I have the following problem:
SQL> exec dbms_java.grant_permission( 'username','SYS:java.io.FilePermission', 'c:\', 'read' );
BEGIN dbms_java.grant_permission( 'username','SYS:java.io.FilePermission', 'c:\', 'read' ); END
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.SecurityException: policy table update SYS:java.io.FilePermission,
c:\
ORA-06512: at "SYS.DBMS_JAVA", line 0
ORA-06512: at line 1

Do I need any more previleges under my account

Thanks
 

Tom Kyte
January 27, 2003 - 7:35 am UTC

Looks that way:

ops$tkyte@ORA817DEV> drop user a cascade;
User dropped.

ops$tkyte@ORA817DEV> grant create session to a identified by a;
Grant succeeded.

ops$tkyte@ORA817DEV> @connect a/a

a@ORA817DEV> exec dbms_java.grant_permission( 'SCOTT','SYS:java.io.FilePermission', '/tmp', 'read' )
BEGIN dbms_java.grant_permission( 'SCOTT','SYS:java.io.FilePermission', '/tmp', 'read' ); END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.SecurityException: policy table update
SYS:java.io.FilePermission, /tmp
ORA-06512: at "SYS.DBMS_JAVA", line 0
ORA-06512: at line 1


a@ORA817DEV> @connect /

ops$tkyte@ORA817DEV> grant dba to a;
Grant succeeded.

ops$tkyte@ORA817DEV> @connect a/a

a@ORA817DEV> exec dbms_java.grant_permission( 'SCOTT','SYS:java.io.FilePermission', '/tmp', 'read' )

PL/SQL procedure successfully completed.

a@ORA817DEV>

see
http://docs.oracle.com/docs/cd/B10501_01/java.920/a96656/security.htm#1006699
for all of the details on getting the ability to do these grants.  DBA works but is not necessary 

Use java.sql.Timestamp

Dan Kefford, January 27, 2003 - 11:17 am UTC

If the time component (hours, minutes, seconds) is critical, use java.sql.Timestamp. Consider the following:

SQL> CREATE OR REPLACE PACKAGE dk234.fun_with_dates_pkg AS
  2     FUNCTION pass_in_date
  3       (p_date_from_java IN DATE)
  4        RETURN DATE
  5     IS
  6        l_date_from_oracle DATE ;
  7     BEGIN
  8        l_date_from_oracle := p_date_from_java ;
  9        RETURN l_date_from_oracle ;
 10     END pass_in_date ;
 11  END fun_with_dates_pkg ;
 12  /

Package created.

No errors.

Package body created.

No errors.


Source for FunWithDates.java:

package com.dankefford.test ;

import java.sql.CallableStatement ;
import java.sql.Connection ;
import java.sql.Date ;
import java.sql.DriverManager ;
import java.sql.SQLException ;
import java.sql.Timestamp ;

import java.util.Calendar ;

import oracle.jdbc.driver.OracleTypes ;

public class FunWithDates {
    private static final String    DATABASE_DRIVER   = "oracle.jdbc.driver.OracleDriver" ;
    private static final String    DATABASE_URL      = "jdbc:oracle:thin:@lacey:1521:BAN3" ;
    private static final String    DATABASE_USERID   = "dk234" ;
    private static final String    DATABASE_PASSWORD = "dk234" ;

    private static final String packageCall = "{ ? = call fun_with_dates_pkg.pass_in_date(?) }" ;

    public FunWithDates() {
        Connection conn = null ;
        CallableStatement cs = null ;
        Date javaDate = new java.sql.Date(new java.util.Date().getTime()) ;
        Timestamp javaTimestamp = new java.sql.Timestamp(new java.util.Date().getTime()) ;

        // Getters in both Date classes for hours 
        // and minutes are deprecated
        // so we have to use Calendar.
        Calendar cal = Calendar.getInstance() ;

        try {
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

            conn = DriverManager.getConnection(DATABASE_URL,
                                               DATABASE_USERID,
                                               DATABASE_PASSWORD ) ;

            cs = conn.prepareCall(packageCall) ;
            cs.registerOutParameter(1, OracleTypes.DATE) ;
            cs.setDate(2, javaDate) ;
            cs.execute() ;
            cal.setTime(cs.getDate(1));
            System.out.println("Passing in java.sql.Date, retrieving java.sql.Date, the current time is " +
                               cal.get(Calendar.HOUR_OF_DAY) + ":" +
                               cal.get(Calendar.MINUTE)) ;
            cal.setTime(cs.getTimestamp(1));
            System.out.println("Passing in java.sql.Date, retrieving java.sql.Timestamp, the current time is " +
                               cal.get(Calendar.HOUR_OF_DAY) + ":" +
                               cal.get(Calendar.MINUTE)) ;
            cs.registerOutParameter(1, OracleTypes.TIMESTAMP) ;
            cs.setTimestamp(2, javaTimestamp) ;
            cs.execute() ;
            cal.setTime(cs.getTimestamp(1));
            System.out.println("Passing in java.sql.Timestamp, retrieving java.sql.Timestamp, the current time is " +
                               cal.get(Calendar.HOUR_OF_DAY) + ":" +
                               cal.get(Calendar.MINUTE)) ;
            cal.setTime(cs.getDate(1));
            System.out.println("Passing in java.sql.Timestamp, retrieving java.sql.Date, the current time is " +
                               cal.get(Calendar.HOUR_OF_DAY) + ":" +
                               cal.get(Calendar.MINUTE)) ;
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage()) ;
            System.exit(1); ;
        } catch (Exception e) {
            System.out.println(e.getMessage()) ;
            System.exit(1); ;
        }
    }
    public static void main(String[] args) {
        FunWithDates fwd = new FunWithDates();
    }
}

... and the output:

Passing in java.sql.Date, retrieving java.sql.Date, the current time is 0:0

Passing in java.sql.Date, retrieving java.sql.Timestamp, the current time is 0:0

Passing in java.sql.Timestamp, retrieving java.sql.Timestamp, the current time is 11:11

Passing in java.sql.Timestamp, retrieving java.sql.Date, the current time is 0:0

When using java.sql.Timestamp all around (the third combination in the code and the third result in the output), the time is preserved.

HTH,

dan 

Tom Kyte
January 27, 2003 - 11:42 am UTC

thank you -- knew it was something simple ;)

Here is the modified, smaller 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);
File[] FileList = path.listFiles();
String TheFile;
Timestamp ModiDate;
#sql { DELETE FROM DIR_LIST};

for(int i = 0; i < FileList.length; i++)
{
TheFile = FileList[i].getAbsolutePath();
ModiDate = new Timestamp( FileList[i].lastModified() );

#sql { INSERT INTO DIR_LIST (FILENAME,LASTMODIFIED)
VALUES (:TheFile, :ModiDate ) };
}
}
}
/


Lacks

A reader, January 28, 2003 - 12:12 am UTC

Hi, Tom,
THere is a problem here:
1. If the file is a folder, then the time stamp is not the same as my OS modified time stamp;
2. How could i read alll the other files under the nested folder, things like recursive call, could give us details please?

Thanks


Tom Kyte
January 28, 2003 - 7:17 am UTC

1) sounds like a question for javaguy@somewhere.else.com i guess
2) see #1

sorry - java is not my native language - you can lookup this sort of stuff on the sun site.

Java and Oracle Book

Andrew Gilfrin, January 28, 2003 - 7:36 am UTC

Tom

You recommend

</code> http://www.amazon.com/exec/obidos/ASIN/1861006020/ <code>

Which looks good but will it be any use for me as I am using 8i only and cannot get access to 9i.

Does anybody have this book and if so is it really only usefull for 9i database programming.



Tom Kyte
January 28, 2003 - 8:05 am UTC

almost the entire book applies to 8i as well.

Thanks

Andrew Gilfrin, January 28, 2003 - 9:08 am UTC

Tom

Thanks for taking the time to answer my small question, I actually did think it would and ordered it anyway. We have a very restrictive I.T. structure here and I am unable to load 9i on my machine nor am I allowed to connect to the single 9i test database we have installed. Its a real pain as I would love to try out some of the new features such as ANSI Join syntax.

Thanks Again

File modified date.

Scott, August 06, 2003 - 1:45 am UTC

G'day

This is what we did to determine the date the file was last modified, and we added 8 hours to get in the correct timezone.
lastModified returns milliseconds since midnight 1970.

long last_mod_time;
java.sql.Timestamp mod_date;

last_mod_time = element.lastModified() + 28800000;
mod_date = new java.sql.Timestamp(last_mod_time);

By the way, if you had the following read,delete permission on a directory
/server/dir/*_flag
Would you infer that it meant you could delete any file ending with "_flag"
Because it doesn't seem to work that way. I need:
/server/dir/*
But I don't want to be that loose.

Thanks.

Tom Kyte
August 06, 2003 - 7:46 am UTC

you'd have to read the j2ee spec to see what level of granularity you can use. I have a feeling it is going to be

a) file name
b) directory with a *

but I'm not sure. Maybe a j2ee expert knows and will see this.

A reader, September 24, 2003 - 12:02 pm UTC


How to extend DirList to use Object types?

Richard, June 21, 2004 - 12:33 pm UTC

Tom, could you give some tips on extending the DirList procedure to instead use some combination of object types and pipelined function? Basically we'd like to do something like:
select filename from table(dir_list('DB_DIR_OBJ'));
where dir_list takes a database directory object as the parameter and returns an object table consisting of filename, filesize, timestamp.
I have the java code already created (from devtrends site) to get these file attributes into a global temp table, but I'm stuck on how to return an object table instead of inserting into the gtt. I also have a filetype object and dirtype object (table of filetype) created.
Tips/pointers to my next step would be great!
Thanks again for this great resource for developers & dba's.

Tom Kyte
June 21, 2004 - 8:09 pm UTC

do you have access to "Expert one on one Oracle"?

EO4 & JPub Docs

Richard, June 21, 2004 - 8:43 pm UTC

Yes, I have a copy of "Expert"... the object wrapper for utl_file is what sent me down this track for DirList. I also just printed a copy of the JPub 9.2 docs.
Sounds majority of the work is in creating the SQL Object Type and the Nested Table Object Type and then exporting a java implementation of those types in JPub. Then I can use the .java from JPub and incorporate it with my existing java class to implement a return type of the Nested Table Object Type. Once I have the class returning the Nested Table Object it should be a mechanical exercise to create the plsql stored function that returns the SQL Object Table type which can then be select'ed from.
Or have I missed that left turn at Albuquerque again?

Tom Kyte
June 22, 2004 - 7:36 am UTC

Great -- in the chapter on Java External Routines, I demonstrate how to pass strings, number, dates, integers, raws, lobs, arrays of strings/numbers/dates.

to paraphrase, you'll:

tkyte@TKYTE816> create or replace type strArray as table of varchar2(255);
Type created.


to create an array of strings in SQL and the wrapper will look like:

34 procedure pass( p_in in strArray, p_out out strArray )
35 as language java
36 name 'demo_passing_pkg.pass_str_array( oracle.sql.ARRAY,
37 oracle.sql.ARRAY[] )';


and the java stored procedure will resemble:


141 public static void
142 pass_str_array( oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out )
143 throws java.sql.SQLException,IOException
144 {
145 show_array_info( p_in );
146 String[] values = (String[])p_in.getArray();
147
148 for( int i = 0; i < p_in.length(); i++ )
149 System.out.println( "p_in["+i+"] = " + values[i] );
150
151 Connection conn = new OracleDriver().defaultConnection();
152 ArrayDescriptor descriptor =
153 ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn );
154
155 p_out[0] = new ARRAY( descriptor, conn, values );
156
157 }

which shows an array of strings being sent in and an array of strings being sent back out.

Need to return multiple columns/attributes in the array

Richard, June 22, 2004 - 11:19 am UTC

Here's the types I have created in SQL (9.2.0.4 HPUX):
create type filetype as object (
pathname varchar2(255),
filename varchar2(255),
filesize integer,
filedate date,
issubdir varchar2(1) );
create type dirtype as table of filetype;
create global temporary table dir_list of filetype
on commit preserve rows;
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "DirList" AS
import java.io.*;
import java.sql.*;
public class DirList
{
public static void popList(String directory) throws SQLException
{
File path = new File( directory );
File[] FileList = path.listFiles();
java.util.Arrays.sort(FileList);
String TheFile;
String ThePath;
Timestamp ModiDate;
long TheSize;
String IsAsubdir;
for(int i = 0; i < FileList.length; i++)
{
TheFile = FileList[i].getName();
ThePath = FileList[i].getParent();
ModiDate = new Timestamp( FileList[i].lastModified() );
TheSize = FileList[i].length();
if ( FileList[i].isDirectory() )
{
IsAsubdir = "Y";
} else {
IsAsubdir = "N";
}
#sql { INSERT INTO DIR_LIST (FILENAME,FILESIZE,FILEDATE,ISSUBDIR,PATHNAME)
VALUES (:TheFile, :TheSize, :ModiDate, :IsAsubdir, :ThePath) };
}
}
}
/
CREATE OR REPLACE procedure pop_dir_list(p_directory in varchar2) is
language java name 'DirList.popList( java.lang.String )';
So, I have a GTT where contents are available for the session and it's populated by calls to pop_dir_list. Instead of executing pop_dir_list and then selecting from dir_list, I want to do:
select pathname,filename,filesize,filedate
from table(cast(get_dir_list('DATABASE_DIRECTORY_OBJECT') as dirtype)
where filesize > 100;
Such that get_dir_list takes a database directory object as the parameter, looks up the OS path from ALL_DIRECTORIES and passes that path to the java SP which would return a nested table of DIRTYPE.
The get_dir_list function is a non-issue for looking up the directory object (in fact, if the parameter is not a valid directory object, I simply pass the value entered to the java SP to try if it is a valid os directory (ie '/tmp').

Tom Kyte
June 22, 2004 - 1:55 pm UTC

i find objects too much of a bother here. I'd just create a wrapper layer on top of the dir_list table -- that is, plsql to call the java and then read the table and pipe the rows back.

that is a solution (easily understandable) you could have running in 2 minutes from now...

ORA-29532: ORA-14551 Using JavaSP to Populate GTT inside Pipelined Function

Richard, June 23, 2004 - 9:35 am UTC

Tom, I created the following function to use to be able to populate and select a directory listing but get ORA-29532: ORA-14551 cannot perform a DML operation inside a query.
create or replace
function get_dir_list(p_directory in varchar2)
return dirtype
pipelined as
l_file_info dir_list%rowtype;
cursor d is
select filename, filesize, filemodified, issubdir, pathname
from dir_list;
begin
pop_dir_list(get_path(p_directory));
for l_file_info in (select filename, filesize, filemodified, issubdir, pathname
from dir_list)
loop
pipe row (filetype(l_file_info.filename,
l_file_info.filesize,
l_file_info.filemodified,
l_file_info.issubdir,
l_file_info.pathname));
end loop;
return;
end;
/
The GET_PATH function simply tried to get the DIRECTORY_PATH from ALL_DIRECTORIES for the p_directory specified, or just returns the same if it is not a valid directory object in the database.

Tom Kyte
June 23, 2004 - 9:42 am UTC

a safe "use" of the autonomous_transaction directive:


as
pragma autonomous_transaction;
....
begin
.....

commit;
return;
end;




Always the best!

Richard, June 23, 2004 - 12:48 pm UTC

Thanks for the quick replies. Autonomous_transaction was the solution I needed. That's a major hurdle to a big chunk of one of my projects solved.... Thanks again.

Very good, but why does the date return 0?

Phil, May 26, 2005 - 9:34 am UTC

In trying to make use of your original procedure I got a date of 0 being returned if using a long or 1st Jan 1970 with timestamp. Can you just clarify why this occurs, you mention a workaround in the first response, what exactly is happening differently there?



Tom Kyte
May 26, 2005 - 10:19 am UTC

the workaround was for TIME of 0, not a date of 0

what does a "date" of 0 look like? not sure what you mean

long value

Phil, May 31, 2005 - 11:33 am UTC

The value of the long was 0 when returned rather than having a date value (time from epoch?) in it. I was just wondering why this would occur?

Tom Kyte
May 31, 2005 - 6:34 pm UTC

can you give me an entire example some way to see what you saw?

Sean Dwyer, June 09, 2005 - 11:39 am UTC

hi,
i've been using this to get results back from the server however i'm not having any luck getting the results of a mapped network drive on the server, 
is there an obvious reason why i cannot do this or another way of doing it?

here is my output:
SQL> exec dbms_java.grant_permission( 'PWATCH', 'SYS:java.io.FilePermission', 'v:\*', 'read' );  

PL/SQL procedure successfully completed.

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

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

Tom Kyte
June 09, 2005 - 6:09 pm UTC

because of the ways windows is put together.

that v: drive, what happens to it when you log off? It is part of "your session", it is not like an NFS mount at all. The server is running in its own session, with its own privs and set of resources.

you might be able to use unc naming, or mount the disk under an environment the database server actually can see.

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

Any way with PL/SQL (not Java)

J, October 27, 2005 - 2:21 pm UTC

Is there a way to do this without using Java (DBMS_DIR)?
version10.2

Tom Kyte
October 27, 2005 - 2:52 pm UTC

No, this is one of the last things we need java for (or C)

How do you like this

charlie, May 04, 2008 - 3:26 pm UTC

create or replace and compile java source named "DirList" as
import java.io.*;
import java.sql.*;
import java.sql.Timestamp;
import java.util.*;
public class DirList
{
public static Timestamp lastModified (String path) {
File myFile = new File (path);
return new Timestamp(myFile.lastModified());
}
public static String list (String path) {
String list = path + "/";
String file = "";
Timestamp file1;
File myFile = new File (path);
String[] arrayList = myFile.list();
Arrays.sort(arrayList, String.CASE_INSENSITIVE_ORDER);
for (int i=0; i < arrayList.length; i++) {
// Prevent directory listing expanding if we will blow VARCHAR2 limit.
if ((list.length() + arrayList[i].length() + 1) > 32767)
break;
file = path + "/" + arrayList[i];
file1 = DirList.lastModified(file);
if (!list.equals(""))
list += path + "/" + arrayList[i] + "\t" + file1 + "\n";
else
list += arrayList[i];
}
return list;
}
}

create or replace function list (p_path IN VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'DirList.list (java.lang.String) return java.lang.String';

select list('/tmp') from dual;

This does not require to update the temporary table if there is any changes in the directory

Tom Kyte
May 05, 2008 - 2:43 am UTC

well, given that the max string size in sql will be 4,000 bytes - I'd be worried about blowing that limit rapidly. I don't know what you mean by the last paragraph.

Directory List including modify date

Adrien Sales, August 21, 2009 - 11:25 am UTC

Dear Tom,

i had to face many times the "list directory contents" and i
wanted to sort this out one time for all, and be able
to get the list in a relational way (straight from sql), with many options,
hash compute, mime-type, privileges, wilcards, etc...

So i did develop it for myself and then shared it on sourceforge
inside the oracle-jutils project.

The result perfectly suits my needs.

You can get screenshot here https://sourceforge.net/projects/oracle-jutils/
at the screenshots section.

Or look at the pdf : http://oracle-jutils.sourceforge.net/oracle-jutils.xml.pdf

The JDBMS_FILESYSTEM package is ready to use.

Maybe will somme other people find it useful.

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

Best Regards,

Adrien

Error when I am trying to get the last modified date of file

Bhumi, November 25, 2020 - 11:01 am UTC

Hi Tom,

I tried to use the same program as suggested (mentioned below) but getting the error:
Error: Note: Recompile with -Xlint:deprecation for details.
Error: Note: sqlj translation of DirList uses or overrides a deprecated API.

I am using Oracle 12c. The program works fine when I try to get the filename only but as soon as I am trying to get the last modified date, it starts throwing error. Please suggest.

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

public class DirList
{

static private String dateStr( java.util.Date x )
{
if ( x != null )
return (x.getYear()+1900) + "/" + (x.getMonth()+1) + "/" + x.getDate() + " " +
x.getHours() + ":" + x.getMinutes() + ":" + x.getSeconds();
else return null;
}

public static void getList(String directory)
throws SQLException
{
String element;


File path = new File(directory);
File[] FileList = path.listFiles();
String TheFile;
String ModiDate;
#sql { DELETE FROM DIR_LIST};

for(int i = 0; i < FileList.length; i++)
{
TheFile = FileList[i].getAbsolutePath();
ModiDate = dateStr( new java.util.Date( FileList[i].lastModified() ) );

#sql { INSERT INTO DIR_LIST (FILENAME,LASTMODIFIED)
VALUES (:TheFile, to_date( :ModiDate, 'yyyy/mm/dd hh24:mi:ss') ) };
}
}
}
Chris Saxon
November 25, 2020 - 5:45 pm UTC

Exactly what error are you getting?

Error when I am trying to get the last modified date of file

Bhumi, November 26, 2020 - 6:24 am UTC

The error is:

Error: Note: Recompile with -Xlint:deprecation for details.
Error: Note: sqlj translation of DirList uses or overrides a deprecated API.
Connor McDonald
November 27, 2020 - 7:19 am UTC

Personally I would ditch the dependency on Java and just go with an external table

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


More to Explore

DBMS_JAVA

More on PL/SQL routine DBMS_JAVA here