Sunday, December 9, 2012

ORA-24344 in Toad trace browser

Quest Toad, probably the best Oracle DBA tool today, has released 11.6 version, which offer, by the first time, use of x64 Oracle client. This mean that choosing Toad version (x86 or x64) you are determine Oracle client as well. Beside that the whole new compiler version (x64) is released, there are really huge number of new useful features, this withdraw fact that many new bugs has appeared.
One of really cool feature is ability to trace (and record) Oracle trace file from any client, releasing the whole task from DBAs to developers, where this, at least on DEV instance, task belong.
After initial calling Database|Diagnose|Trace File Browser menu option, you get dialog, which should be run as privileged Oracle user, because additional TOAD user's objects has to be created.
This is possible only if you have previously created Toad server side objects, which in this release is somehow hidden under Database|Administer|Toad security...
Directory option is not enabled, because some TOAD schema objects were not previously created. So, when you choose "Create objects in TOAD schema" button, next dialog is shown:

The problem

Choosing "Yes" start predefined TOAD objects creating script. This script on 10g version finish with ORA-24344 and only partial set of objects were created. Problem is that java code (which is interface for simulation of file browse from Oracle):
Boolean exists = (new File(directory)).exists();
which should be:
boolean exists = (new File(directory)).exists();
Small "b" should be defined.
Interesting, as mentioned, on Oracle 11gR2 (that I tested) this code pass. Even thought you fix this Java source, still some other objects were missing and Trace browser was still not possible to run.

The Solution

Because there is no way to guess what is still missing in TOAD schema, I have tried to trace session on other (11gR2 instance), where this script was running without problems. Here is the whole Toad trace file.
Editing this gave me a proper solution. Here is the "cleaned" Toad trace file code:
connect toad/<pwd>@db;

drop table TOAD.Toad_dir_listing
;

create global temporary table TOAD.Toad_dir_listing
  ( file_name varchar2(255),
    file_size number,
    type      varchar2(1),
    modified  date  )
  on commit delete rows
;

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
    {
      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 (OracleSQLException 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 */
};

create or replace procedure TOAD.Populate_Toad_Dir_Listing( p_directory in varchar2 )
  as language java
  name 'ToadDirList.getList( java.lang.String )'
;

grant select on TOAD.TOAD_DIR_LISTING to PUBLIC
;

grant execute on TOAD.POPULATE_TOAD_DIR_LISTING to PUBLIC
;

create or replace directory TOAD_BDUMP_DIR as 'C:\ORACLE\diag\rdbms\xe\xe\trace\'
;
with replacing 'C:\ORACLE\diag\rdbms\xe\xe\trace\' value to your actual database trace directory. When I create objects with shown script and return to "Trace file browser" dialog again I was still unable to proceed but problematic part was far away from me and I was able to continue:

The end

Every new product carry new features and unfortunately new bugs. Some of them are solvable (with workaround like this) but some of them need to be fix by Quest.
A small advice for Quest for Oracle 11.6 version. If you do not really need x64 bit version, rather install x86 one. Seems that many bugs are raised because new Delphi 64 bit compiler and partial incompatibility with some components that are part of Toad.

Hope this helps someone

Cheers!

Zagreb u srcu!

Copyright © 2009-2018 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign