www.oracle-developer.net/display.php has a non-PL/SQL approach but the OS dependent shell script is another sticky point.
This without the SQLJ will compile in my Solaris 11.3 64-bit Oracle 12.2.0.1. It fails with the 10 lines uncommented.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED TOAD.“ToadDirList”
as import java.io.;
import java.sql.;
import java.util.Date;
import java.text.SimpleDateFormat;
import oracle.jdbc.driver.OracleSQLException;
public class ToadDirList
{
public static void getList(String directory) throws IOException, SQLException, SQLDataException
{
boolean exists = (new File(directory)).exists();
if (exists)
{
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";
}
// try
*** // {***
*** // #sql { INSERT INTO Toad_dir_listing (file_name, file_size, type, modified)***
*** // VALUES (:element, :len, :ftype, to_date(:sqldate,‘YYYY-MM-DD HH24:MI:SS’)) };***
*** // }***
*** // catch (Exception e)***
___ // { /* Sometimes the date doesn’t get translated propertly. Proceeed without it. /___
*** // #sql { INSERT INTO Toad_dir_listing (file_name, file_size, type, modified)**
*** // VALUES (:element, :len, :ftype, null) };***
___ // } /* try…catch /___
} / for loop /
} / directory exists /
else
{
throw new IOException(“Folder " + directory + " does not exist on server.”);
}
} / getlist */
};
/
The one I got and adapted for my apps from the original TOM via Sean Dillon. It still compiles successfully in my Solaris 12.2. Some smarter guy was nice enough to update my SQLJ with JDBC syntax. I will try to evolve it to fit the Toad table…
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED myschema.“DirList”
as //
import java.io.;
import java.sql.;
import oracle.jdbc.driver.*;
public class DirList
{
public static void getList(String pchr_directory)
throws SQLException
{
DriverManager.registerDriver (new OracleDriver());
Connection conn = DriverManager.getConnection(“jdbc:default:connection:”);
conn.setAutoCommit (false);
// This filter only returns files
FileFilter lobj_filesOnly = new FileFilter()
{
public boolean accept(File lchr_file)
{
return lchr_file.isFile();
}
};
File lchr_path = new File( pchr_directory );
File[] larr_list = lchr_path.listFiles(lobj_filesOnly);
File lobj_element;
String lchr_name;
String lchr_modified_dt;
PreparedStatement InsertStmt = conn.prepareStatement
("INSERT INTO dir_list (filename_tx, file_dt) "+
"VALUES (?, TO_TIMESTAMP(?,'MM/DD/YYYY HH24:MI:SS'))" );
for(int i = 0; i < larr_list.length; i++)
{
lobj_element = larr_list[i];
lchr_name = lobj_element.getName();
lchr_modified_dt = new java.text.SimpleDateFormat("MM/dd/yyyy HH:mm:ss").format(new java.util.Date (lobj_element.lastModified()));
InsertStmt.setString(1,lchr_name);
InsertStmt.setString(2,lchr_modified_dt);
// add file name/last modified date to DIR_LIST table
InsertStmt.executeUpdate();
}
InsertStmt.close();
conn.close();
}
}
/