Oracle Plan Table

Here are SQL scripts to use the Oracle plan table:

CREATE TABLE TEMP_PLAN_TABLE (
  STATEMENT_ID VARCHAR2(30),
  TIMESTAMP DATE,
  REMARKS VARCHAR2(80),
  OPERATION VARCHAR2(30),
  OPTIONS VARCHAR2(30),
  OBJECT_NODE VARCHAR2(128),
  OBJECT_OWNER VARCHAR2(30),
  OBJECT_NAME VARCHAR2(30),
  OBJECT_INSTANCE NUMBER(38),
  OBJECT_TYPE VARCHAR2(30),
  OPTIMIZER VARCHAR2(255),
  SEARCH_COLUMNS NUMBER,
  ID NUMBER(38),
  PARENT_ID NUMBER(38),
  POSITION NUMBER(38),
  COST NUMBER(38),
  CARDINALITY NUMBER(38),
  BYTES NUMBER(38),
  OTHER_TAG VARCHAR2(255),
  PARTITION_START VARCHAR2(255),
  PARTITION_STOP VARCHAR2(255),
  PARTITION_ID NUMBER(38),
  OTHER LONG,
  DISTRIBUTION VARCHAR2(30)
);

DELETE FROM TEMP_PLAN_TABLE;
EXPLAIN PLAN INTO TEMP_PLAN_TABLE FOR
SELECT * FROM DUAL
;

SELECT LPAD(' ', level-1) || operation || ' (' || options || ')' "Operation"
, object_owner||'.'||object_name||
  (SELECT ' (on '||TABLE_OWNER||'.'||TABLE_NAME||')'
   FROM ALL_INDEXES
   WHERE OWNER=TPT.OBJECT_OWNER
   AND INDEX_NAME=TPT.OBJECT_NAME
   AND TPT.OPERATION='INDEX' ) "Object"
FROM TEMP_PLAN_TABLE TPT
START WITH ID = 0
CONNECT BY PRIOR ID=PARENT_ID

DROP TABLE TEMP_PLAN_TABLE;

Hibernate

Here are some general Hibernate gotchas:

• Property names that begin or end with an underscore give Hibernate grief.  It issues error messages that it cannot find properties that exist.  In the case I was trying, the identifiers were all caps (other than the underscores).  Perhaps it works for identifiers in normal Java case?
• When trying to use Oracle LONG columns, Hibernate would issue the error message 'Stream has already been closed' when trying to materialize the containing objects.  This only occurs when the containing objects have container relationships to other objects.  This error usually occurs in JDBC when ResultSet columns are not read in left-to-right order and an attempt is made to backtrack to a LONG column.  An inspection of the Hibernate code suggests that this is a possibility.  The Hibernate forums contain references to this problem, but no-one has posted a solution.  I tried changing the mapping so that a LONG property appears:
  • as the last property for a class -- no effect.
  • in a new class mapped as a self-join to the table, with a one-to-one relationship to the original class -- this worked, but further testing is needed to verify that it works in all scenarios.  Also, this mapping does not work for updates since Hibernate will think that a new row needs to be created.
  • in a new component class -- no effect.

JScript

JScript has no direct way of creating Variant arrays (aka SafeArrays or VBArrays).  However, here is a hack to create one:

function makeVBArray(elements)
{
    var dictionary = new ActiveXObject("Scripting.Dictionary");
    for (var i = 0; i < elements.length; ++i) {
        dictionary.add(elements[i], "");
    }
    return dictionary.Keys();
}

ADSI

I came across the Variant array problem while using an ADSI collection.  The Filter property only worked when set to a variant array -- JScript array object did not work.

I wrote a script, cycle-password.js, that repeatedly changes your password until it overflows the NT password history.  This is useful to avoid an administrative policy that forces you to change your password every so often (which is less secure than a good password).