Toad World® Forums

"ToadDirList" Reliance on 12.2 Desupported SQLJ


#1

We just upgraded to 12.2 and I cannot recreate the Toad schema objects from Trace File Browser. It results in a ORA-24344 error and the java error messages are lengthy compiler exceptions. I thought it may be the SQLJ, but I’m not a serious java programmer. Can you provide a functional ToadDirList or standalone schema generation script that is the equivalent of what TFB does? We have other custom java in these (2) databases that still work using PreparedStatement.

references:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/upgrd/desupported-features-oracle-database-12c-r2.html#GUID-EAB2EBD9-9DED-48D1-A46A-4B83AEC09B0C

SQLJ Support Inside Oracle Database

Starting with Oracle Database 12c release 2 (12.2), Oracle does not support running server-side SQLJ code.

Oracle supports using client-side SQLJ. However, Oracle does not support the use of server-side SQLJ, including running stored procedures, functions, and triggers in the database environment.


#2

The purpose of ToadDirList is to get a file listing of a particular folder on the database server, and insert that data as rows in a table called TOAD_DIR_LISTING. Toad then reads out of that table to display the list of files that you can choose from. I have a 12.2.0.1 pluggable database and it works there. My 12.2.0.1 database was a fresh install, not an upgrade. Maybe that has something to do with it.

I would do this with straight SQL or built-in Oracle packages if I could find a way to do that. Java was the only way that I was able to get this done. If you know of a non-Java way to do this, I’m interested.

The source code for it is included as a Java Source. So you should be able to find in using the Schema Browser in whatever database that ToadDirList exists.


#3

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

}
}
/


#4

This functions for me and will retrieve the files. I hardcoded the F since I don’t know or see a need for D.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED TOAD.“ToadDirList”
as
import java.io.;
import java.sql.
;
import oracle.jdbc.driver.*;

public class ToadDirList
{
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;
  long len;
  
  PreparedStatement InsertStmt = conn.prepareStatement
     ("INSERT INTO Toad_dir_listing (file_name, modified,file_size,type ) "+
      "VALUES (?, TO_TIMESTAMP(?,'MM/DD/YYYY HH24:MI:SS'),?,'F')" );

  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()));
     len   = lobj_element.length();
     InsertStmt.setString(1,lchr_name);
     InsertStmt.setString(2,lchr_modified_dt);
     InsertStmt.setLong(3,len);

     // add file name/last modified date to DIR_LIST table
     InsertStmt.executeUpdate();
  }
  InsertStmt.close();
  conn.close();

}
}
/


#5

Thanks. I just swapped PreparedStatement into my code and got it to work. I’ll put it in an upcoming beta.


#6

Just curious, has this already been implemented? I’m on Toad version 13.0.0.80 and I still have this problem.

Or would you recommend I modify the ToadDirList function myself to do it through a PreparedStatement?


#7

if Toad 13.0 created the ToadDirList function, it should be doing so with the PreparedStatement code. Maybe an older version of Toad created that function in your database?

You can either change it yourself, or delete the ToadDirList java source, and let Toad 13 recreate it.


#8

Deleting it and letting it recreate itself worked perfectly. Thank you.