Blank Modified date in Trace File Browser-Select file from DB Server

Toad for Oracle
1

The 00:00 timestamped Solaris files do not populate the Modified column when looking up the files. Not a huge issue now that I know the behavior, but it did cause a minor panic when I initially though some log rolling script was disappearing random alert logs.

-rw-r----- 1 oracle dba 162177 Jun 2 23:38 alert_msesdo_20230602.bak
-rw-r----- 1 oracle dba 480678 Jun 4 00:00 alert_msesdo_20230603.bak
-rw-r----- 1 oracle dba 157148 Jun 5 00:00 alert_msesdo_20230604.bak
-rw-r----- 1 oracle dba 222564 Jun 6 00:00 alert_msesdo_20230605.bak
-rw-r----- 1 oracle dba 232655 Jun 6 22:53 alert_msesdo_20230606.bak

3

Oh, yeah. There's a Java Source on your DB called ToadDirList that does this.

If there is an error on while processing the date, we just use NULL for the date and try again.
I suppose we could add another try...catch in there that skips the time portion .

image
image523×506 17.6 KB

4

This seems to fix it (Assuming you are on Oracle 12 or newer!). Be sure to put it in the right schema. :slight_smile:

I'm not really sure why I don't have to change the InsertStmt on the 2nd try, but I'm not complaining.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "ToadDirList"
as import java.io.*;
  import java.sql.*;
  import java.util.Date;
  import java.text.SimpleDateFormat;
  import oracle.jdbc.driver.*;

  public class ToadDirList
  {
    public static void getList(String directory) throws IOException, SQLException, SQLDataException
    {
      DriverManager.registerDriver (new OracleDriver());
      Connection conn = DriverManager.getConnection("jdbc:default:connection:");
      conn.setAutoCommit (false);

      Boolean exists = (new File(directory)).exists();
      if (exists)
      {
          File path = new File(directory);
          String[] list = path.list();
          String element;

          PreparedStatement InsertStmt = conn.prepareStatement
             ("INSERT INTO Toad_dir_listing (file_name, file_size, type, modified ) "+
              "VALUES (?,?,?,TO_TIMESTAMP(?,'YYYY-MM-DD HH24:MI:SS'))" );

          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 HH: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";
            }

            InsertStmt.setString(1,element);
            InsertStmt.setLong(2,len);
            InsertStmt.setString(3,ftype);
            InsertStmt.setString(4,sqldate);

            try
            {
              InsertStmt.executeUpdate();
            }
            catch (Exception e)
            { /* try again without the time portion of the file's modified time */
              SimpleDateFormat df2 = new SimpleDateFormat("yyyy-MM-dd");
              if (f.isFile())
              {
                date  = new Date(f.lastModified());
                sqldate = df2.format(date) ;
              }
              InsertStmt.setString(4,sqldate);
              try
              {
                InsertStmt.executeUpdate();
              }
              catch (Exception e2)
              { /* OK, proceed without the file modified time. */
                InsertStmt.setString(4,null);
                InsertStmt.executeUpdate();
              } /* try..catch */
            } /* try..catch */
          } /* for loop */
          InsertStmt.close();
          conn.close();
      } /* directory exists */
      else
      {
      throw new IOException("Folder " + directory + " does not exist on server.");
      }
    } /* getlist */
};
/
5

The kk should be hh on line 36 right? It works with that mod.

6

I'm not real familiar with Java, but I see here https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

that kk = Hour in day (1-24) and HH = Hour in day (0-23).

So you may be on to something here. Maybe I don't need that double catch after all. I think I'll leave it in though, just incase (along with kk -> HH)

Edit: You need HH in there, not hh. hh=Hour in am/pm (1-12)

I updated the code above incase some copies/pastes it in the future.

7

II can’t do much with it beyond spelling it, but it is incredibly case sensitive. I just tested the original using hh vs. HH in SimpleDateFormat and it worked. It sure doesn’t like lowercase mm though.

lchr_modified_dt = new java.text.SimpleDateFormat("MM/dd/yyyy hh:mm:ss").format(new java.util.Date (lobj_element.lastModified()));

1 Like
8

yeah MM is month and mm is minute.

with hh, for me it worked, but all of the files shown in Toad's dialog were between the times of 12:00 am and 12:59pm (nothing on or after 1:00pm)

Once I changed to HH, I saw the full range of times.