Example of using JDBC Timezone

Recently I was working on a timezone issue there the values returned on the daylight saving change over where incorrect. Here is the example and steps to reproduce this problem.

There sql script looks like:

connect / as sysdba

drop user timezoneuser cascade;

grant connect,resource to timezoneuser identified by timezoneuser;

connect timezoneuser/timezoneuser

create table jdbc_test (
  sequence_id number(2),
  cbt timestamp,
  cbt_utc timestamp);

alter session set NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
alter session set NLS_TIMESTAMP_TZ_FORMAT ='MM/DD/YYYY HH24:MI:SS';
alter session set NLS_TIMESTAMP_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

select * from nls_session_parameters;

insert into jdbc_test values(1,'04/05/2009 00:00:00','04/04/2009 13:00:00');
insert into jdbc_test values(2,'04/05/2009 01:00:00','04/04/2009 14:00:00');
insert into jdbc_test values(3,'04/05/2009 02:00:00','04/04/2009 15:00:00');
insert into jdbc_test values(4,'04/05/2009 02:00:00','04/04/2009 16:00:00');
insert into jdbc_test values(5,'04/05/2009 03:00:00','04/04/2009 17:00:00');
insert into jdbc_test values(6,'04/05/2009 04:00:00','04/04/2009 18:00:00');
insert into jdbc_test values(7,'04/05/2009 05:00:00','04/04/2009 19:00:00');
insert into jdbc_test values(8,'04/05/2009 06:00:00','04/04/2009 20:00:00');
insert into jdbc_test values(9,'04/05/2009 07:00:00','04/04/2009 21:00:00');
insert into jdbc_test values(10,'04/05/2009 08:00:00','04/04/2009 22:00:00');
insert into jdbc_test values(11,'04/05/2009 09:00:00','04/04/2009 23:00:00');
insert into jdbc_test values(12,'04/05/2009 10:00:00','04/05/2009 00:00:00');
insert into jdbc_test values(13,'04/05/2009 11:00:00','04/05/2009 01:00:00');
insert into jdbc_test values(14,'04/05/2009 12:00:00','04/05/2009 02:00:00');
insert into jdbc_test values(15,'04/05/2009 13:00:00','04/05/2009 03:00:00');
insert into jdbc_test values(16,'04/05/2009 14:00:00','04/05/2009 04:00:00');
insert into jdbc_test values(17,'04/05/2009 15:00:00','04/05/2009 05:00:00');
insert into jdbc_test values(18,'04/05/2009 16:00:00','04/05/2009 06:00:00');
insert into jdbc_test values(19,'04/05/2009 17:00:00','04/05/2009 07:00:00');

column seq format 999
column cbt format a20
column cbt_utc format a20
column difference format a30

select sequence_id as seq,cbt ,cbt_utc , cbt - cbt_utc as difference from jdbc_test;

and the java looks like:

package example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

import java.util.Date;
import java.util.Properties;

public class JDBCTimeStamp {

  private void queryDatabase() {
      Connection conn = null;

      String DRIVER = "oracle.jdbc.driver.OracleDriver";
      String connectionName = "jdbc:comp:env:sys";
      String URL_NAME = "jdbc:oracle:thin:@celcsol1.us.oracle.com:1521:tarsr3";
      String userName = new String("timezoneuser");
      String password = new String("timezoneuser");
      Properties prop = new Properties();
      prop.put("user", userName);
      prop.put("password", password);
      prop.put("connectionName", connectionName);
      try {
          try {
              Class.forName(DRIVER);
          } catch (Exception e) {
              System.out.println("Exception in Class.forName : " + e);
          }
          try {
              conn = DriverManager.getConnection(URL_NAME, prop);
          } catch (Exception e) {
              System.out.println("Exception in creating the connection : " + e);
          }

          String sqlString = " SELECT SEQUENCE_ID,CBT,CBT_UTC" + " FROM JDBC_TEST ";

          PreparedStatement ps = conn.prepareStatement(sqlString.toString());
          ResultSet rs = null;
          try {
              ps = conn.prepareStatement(sqlString);
              rs = ps.executeQuery();
              String sequenceId = null;
              Timestamp cbtTimeStamp = null;
              Timestamp cbtUtcTimeStamp = null;
              long longValueOfUtc;
              long longValueOfCbt;
              Date cbtDateValueFromLong;
              Date utcDateValueFromLong;

              System.out.println("ID \tCBT(local time) \t\t\tCBT_UTC(GMT) \t\t\tTime Difference");
              while (rs.next()) {
                  sequenceId = rs.getString(1);
                  cbtTimeStamp = rs.getTimestamp(2);
                  cbtUtcTimeStamp = rs.getTimestamp(3);
                  longValueOfUtc = cbtUtcTimeStamp.getTime();
                  longValueOfCbt = cbtTimeStamp.getTime();
                  cbtDateValueFromLong = new Date(longValueOfCbt);
                  utcDateValueFromLong = new Date(longValueOfUtc);
                  System.out.println(sequenceId + "\t" + cbtDateValueFromLong + "\t\t" + utcDateValueFromLong + "\t\t" + ((longValueOfCbt - longValueOfUtc) / 3600000));
              }
          } finally {
              if (ps != null)
                  ps.close();
              if (rs != null)
                  rs.close();
          }
      } catch (Exception e) {
          System.out.println("Exception :::" + e);
          e.printStackTrace();
      }
  }

  public static void main(String[] args) {
      JDBCTimeStamp main = new JDBCTimeStamp();
      main.queryDatabase();
  }
}

Upon running the java code using JDK 1.6 and 11g JDBC drivers (ojdbc6.jar), the following results are show:

ID   CBT(local time)                     CBT_UTC(GMT)                        Time Difference
1    Sun Apr 05 00:00:00 EST 2009        Sat Apr 04 13:00:00 EST 2009        11
2    Sun Apr 05 01:00:00 EST 2009        Sat Apr 04 14:00:00 EST 2009        11
3    Sun Apr 05 02:00:00 EST 2009        Sat Apr 04 15:00:00 EST 2009        12
4    Sun Apr 05 02:00:00 EST 2009        Sat Apr 04 16:00:00 EST 2009        11
5    Sun Apr 05 03:00:00 EST 2009        Sat Apr 04 17:00:00 EST 2009        11
6    Sun Apr 05 04:00:00 EST 2009        Sat Apr 04 18:00:00 EST 2009        11
7    Sun Apr 05 05:00:00 EST 2009        Sat Apr 04 19:00:00 EST 2009        11
8    Sun Apr 05 06:00:00 EST 2009        Sat Apr 04 20:00:00 EST 2009        11
9    Sun Apr 05 07:00:00 EST 2009        Sat Apr 04 21:00:00 EST 2009        11
10    Sun Apr 05 08:00:00 EST 2009        Sat Apr 04 22:00:00 EST 2009        11
11    Sun Apr 05 09:00:00 EST 2009        Sat Apr 04 23:00:00 EST 2009        11
12    Sun Apr 05 10:00:00 EST 2009        Sun Apr 05 00:00:00 EST 2009        11
13    Sun Apr 05 11:00:00 EST 2009        Sun Apr 05 01:00:00 EST 2009        11
14    Sun Apr 05 12:00:00 EST 2009        Sun Apr 05 02:00:00 EST 2009        10
15    Sun Apr 05 13:00:00 EST 2009        Sun Apr 05 03:00:00 EST 2009        10
16    Sun Apr 05 14:00:00 EST 2009        Sun Apr 05 04:00:00 EST 2009        10
17    Sun Apr 05 15:00:00 EST 2009        Sun Apr 05 05:00:00 EST 2009        10
18    Sun Apr 05 16:00:00 EST 2009        Sun Apr 05 06:00:00 EST 2009        10
19    Sun Apr 05 17:00:00 EST 2009        Sun Apr 05 07:00:00 EST 2009        10

This because the table was not created with local timezone. The column in the database is TIMESTAMP - which holds a non timezone specified value.

When jdbc extracts values from a TIMESTAMP column into a java.sql.Timestamp via the getTimestamp method, it ensures that the value, when printed, shows the same DATE/TIME as that stored in the database.

The correct way to create the table would be:

create table jdbc_test (
  sequence_id number(2),
  cbt TIMESTAMP WITH LOCAL TIME ZONE,
  cbt_utc TIMESTAMP WITH TIME ZONE);

Now that I am using 'WITH LOCAL TIME ZONE', I need change my java code to include the timezone in the connection like '((OracleConnection)conn).setSessionTimeZone("Australia/Victoria");'

So the java code would now look like:

...
try {
 conn = DriverManager.getConnection(URL_NAME, prop);
} catch (Exception e) {
 System.out.println("Exception in creating the connection : " + e);
}

((OracleConnection)conn).setSessionTimeZone("Australia/Victoria");
String sqlString = " SELECT SEQUENCE_ID,CBT,CBT_UTC" + " FROM JDBC_TEST ";

PreparedStatement ps = conn.prepareStatement(sqlString.toString());
ResultSet rs = null;
...

Now when I run the java class, the correct results are shown:

ID   CBT(local time)                     CBT_UTC(GMT)                        Time Difference
1    Sun Apr 05 01:00:00 EST 2009        Sat Apr 04 14:00:00 EST 2009        11
2    Sun Apr 05 02:00:00 EST 2009        Sat Apr 04 15:00:00 EST 2009        11
3    Sun Apr 05 02:00:00 EST 2009        Sat Apr 04 16:00:00 EST 2009        11
4    Sun Apr 05 02:00:00 EST 2009        Sat Apr 04 17:00:00 EST 2009        10
5    Sun Apr 05 03:00:00 EST 2009        Sat Apr 04 18:00:00 EST 2009        10
6    Sun Apr 05 04:00:00 EST 2009        Sat Apr 04 19:00:00 EST 2009        10
7    Sun Apr 05 05:00:00 EST 2009        Sat Apr 04 20:00:00 EST 2009        10
8    Sun Apr 05 06:00:00 EST 2009        Sat Apr 04 21:00:00 EST 2009        10
9    Sun Apr 05 07:00:00 EST 2009        Sat Apr 04 22:00:00 EST 2009        10
10    Sun Apr 05 08:00:00 EST 2009        Sat Apr 04 23:00:00 EST 2009        10
11    Sun Apr 05 09:00:00 EST 2009        Sun Apr 05 00:00:00 EST 2009        10
12    Sun Apr 05 10:00:00 EST 2009        Sun Apr 05 01:00:00 EST 2009        10
13    Sun Apr 05 11:00:00 EST 2009        Sun Apr 05 02:00:00 EST 2009        10
14    Sun Apr 05 12:00:00 EST 2009        Sun Apr 05 02:00:00 EST 2009        10
15    Sun Apr 05 13:00:00 EST 2009        Sun Apr 05 03:00:00 EST 2009        10
16    Sun Apr 05 14:00:00 EST 2009        Sun Apr 05 04:00:00 EST 2009        10
17    Sun Apr 05 15:00:00 EST 2009        Sun Apr 05 05:00:00 EST 2009        10
18    Sun Apr 05 16:00:00 EST 2009        Sun Apr 05 06:00:00 EST 2009        10
19    Sun Apr 05 17:00:00 EST 2009        Sun Apr 05 07:00:00 EST 2009        10

Example of OracleDatabaseMetaData using OAS datasources to display table and view data

A simple example of using oracle database metadata (from JDBC) with Oracle Application Server 1013x datasources to display the data.

1. My data-sources.xml looks like:

<managed-data-source connection-pool-name="scottPool2" jndi-name="jdbc/ScottDS2"
name="jdbc/ScottDS2"/>
<connection-pool name="scottPool2">
   <connection-factory factory-class="oracle.jdbc.pool.OracleDataSource"
user="scott" password="tiger" url="jdbc:oracle:thin:@//sracanov-au2.au.oracle.com:1522/orcl"/>
</connection-pool>

2. My java code in jdev looks like:

package examples;

import java.sql.Connection;

import java.sql.ResultSet;

import java.util.Properties;

import javax.naming.Context;
import javax.naming.InitialContext;

import javax.sql.DataSource;

import oracle.jdbc.OracleDatabaseMetaData;

public class TestOracleDatabaseMetaDataDataSource {
public static void main(String[] args) throws Exception {
   Properties props = new Properties();
   props.put(Context.PROVIDER_URL,
             "opmn:ormi://sracanov-au2.au.oracle.com:6005:OC4J_Apps/default");
   props.put(Context.INITIAL_CONTEXT_FACTORY,
             "oracle.j2ee.rmi.RMIInitialContextFactory");
   props.put(Context.SECURITY_PRINCIPAL, "oc4jadmin");
   props.put(Context.SECURITY_CREDENTIALS, "welcome1");
   Context ctx = new InitialContext(props);
   DataSource ds = (DataSource)ctx.lookup("jdbc/ScottDS2");
   Connection conn = ds.getConnection();
   OracleDatabaseMetaData dbmd =
       (OracleDatabaseMetaData)conn.getMetaData();
   ResultSet rs =
       dbmd.getTables(null, "%", "%", new String[] { "TABLE", "VIEW" });
   while (rs.next()) {
       System.out.println("   " + rs.getString(3) + " : " +
                          rs.getString(4));
   }
}

}

3. Ensure the following libraries are in the project:

* JDBC driver
* Apache Ant
* JSP Runtime

4. Run the class and the results are displayed:

      DR$NUMBER_SEQUENCE : TABLE
  DR$OBJECT_ATTRIBUTE : TABLE
  DR$POLICY_TAB : TABLE
  RLM$PARSEDCOND : TABLE
  OGIS_GEOMETRY_COLUMNS : TABLE
  OGIS_SPATIAL_REFERENCE_SYSTEMS : TABLE
  ....
  ....
  ....
  WM$REPLICATION_INFO : VIEW
  WM$TABLE_NEXTVERS_VIEW : VIEW
  WM$TABLE_PARVERS_VIEW : VIEW
  WM$TABLE_VERSIONS_IN_LIVE_VIEW : VIEW
  WM$TABLE_WS_PARVERS_VIEW : VIEW
  WM$VERSIONS_IN_LIVE_VIEW : VIEW
  WM$VER_BEF_INST_NEXTVERS_VIEW : VIEW
  WM$VER_BEF_INST_PARVERS_VIEW : VIEW
  WM_EVENTS_INFO : VIEW
  PATH_VIEW : VIEW
  RESOURCE_VIEW : VIEW      

Results of the output have been truncated.

RMI-IIOP Example with oc4j 10.1.3.x

I have been trying to follow the rmi-iiop how to:

http://www.oracle.com/technology/tech/java/oc4j/htdocs/how-to-rmi-iiop.html

with oc4j 1013x. I experienced some issues and noticed some changes in the documentation compared to the previous versions.

http://download.oracle.com/docs/cd/E14101_01/doc.1013/e13975/rmi.htm#CHDBDEII

So I recreated the example to run with the latest oc4j and noted the following additional requirements:

1. The client stub generation command has changed.

http://download.oracle.com/docs/cd/E14101_01/doc.1013/e13975/rmi.htm#i1084457

You need to specified the '-iiopClientJar stub_jar_filename' parameter during deployment.

First start oc4j with command:

java -DGenerateIIOP=true -jar oc4j.jar

Then deploy the application to generate the client stub as follows:

java -jar $J2EE_HOME/admin.jar
ormi://localhost admin welcome
-deploy -file filename
-deployment_name application_name
-iiopClientJar stub_jar_filename

2. The client must have the ejb_sec.properties defined.

http://download.oracle.com/docs/cd/E14101_01/doc.1013/e13977/csiv2.htm#i1014488

In the example, the ejb_sec.properties contains the following properties:

oc4j.iiop.trustedServers=*
client.sendpassword=true
nameservice.useSSL=false

3. The client requires the following 2 system properties:

-Djavax.rmi.CORBA.PortableRemoteObjectClass=com.sun.corba.ee.impl.javax.rmi.PortableRemoteObject
-Dcom.oracle.CORBA.OrbManager=com.oracle.corba.ee.impl.orb.ORBManagerIm

4. The client requires jazn.jar to be included in the classpath.

You can download the example from here. Follow the steps in the readme.txt to run it.

java.rmi.AccessException: CORBA NO_PERMISSION error

I have been trying to run my RMI/IIOP client in Jdev 10135, and I kept on getting the following stack error:

java.rmi.AccessException: CORBA NO_PERMISSION 0 No; nested exception is: 
       org.omg.CORBA.NO_PERMISSION: ----------BEGIN server-side stack trace----------
org.omg.CORBA.NO_PERMISSION:   vmcid: 0x0  minor code: 0  completed: No
       at oracle.oc4j.corba.iiop.security.SecServerRequestInterceptor.receive_request(SecServerRequestInterceptor.java:355)
       at com.sun.corba.ee.impl.interceptors.InterceptorInvoker.invokeServerInterceptorIntermediatePoint(InterceptorInvoker.java:509)
       at com.sun.corba.ee.impl.interceptors.PIHandlerImpl.invokeServerPIIntermediatePoint(PIHandlerImpl.java:505)
       at com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.getServantWithPI(CorbaServerRequestDispatcherImpl.java:429)
       at com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.dispatch(CorbaServerRequestDispatcherImpl.java:191)
       at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequestRequest(CorbaMessageMediatorImpl.java:1653)
       at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:1513)
       at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleInput(CorbaMessageMediatorImpl.java:895)
       at com.sun.corba.ee.impl.protocol.giopmsgheaders.RequestMessage_1_2.callback(RequestMessage_1_2.java:172)
       at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:668)
       at com.sun.corba.ee.impl.transport.SocketOrChannelConnectionImpl.dispatch(SocketOrChannelConnectionImpl.java:375)
       at com.sun.corba.ee.impl.transport.SocketOrChannelConnectionImpl.read(SocketOrChannelConnectionImpl.java:284)
       at com.sun.corba.ee.impl.transport.ReaderThreadImpl.doWork(ReaderThreadImpl.java:73)
       at com.sun.corba.ee.impl.orbutil.threadpool.ThreadPoolImpl$WorkerThread.run(ThreadPoolImpl.java:382)

----------END server-side stack trace----------  vmcid: 0x0  minor code: 0  completed: No
       at com.sun.corba.ee.impl.javax.rmi.CORBA.Util.mapSystemException(Util.java:204)
       at javax.rmi.CORBA.Util.mapSystemException(Util.java:67)
       at howto.rmiiiop.oc4j.ejb._RemoteHome_Stub.create(Unknown Source)
       at howto.rmiiiop.oc4j.client.RemoteClient.run(RemoteClient.java:55)
       at howto.rmiiiop.oc4j.client.RemoteClient.main(RemoteClient.java:91)
Caused by: org.omg.CORBA.NO_PERMISSION: ----------BEGIN server-side stack trace----------
org.omg.CORBA.NO_PERMISSION:   vmcid: 0x0  minor code: 0  completed: No
       at oracle.oc4j.corba.iiop.security.SecServerRequestInterceptor.receive_request(SecServerRequestInterceptor.java:355)
       at com.sun.corba.ee.impl.interceptors.InterceptorInvoker.invokeServerInterceptorIntermediatePoint(InterceptorInvoker.java:509)
       at com.sun.corba.ee.impl.interceptors.PIHandlerImpl.invokeServerPIIntermediatePoint(PIHandlerImpl.java:505)
       at com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.getServantWithPI(CorbaServerRequestDispatcherImpl.java:429)
       at com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.dispatch(CorbaServerRequestDispatcherImpl.java:191)
       at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequestRequest(CorbaMessageMediatorImpl.java:1653)
       at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:1513)
       at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleInput(CorbaMessageMediatorImpl.java:895)
       at com.sun.corba.ee.impl.protocol.giopmsgheaders.RequestMessage_1_2.callback(RequestMessage_1_2.java:172)
       at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:668)
       at com.sun.corba.ee.impl.transport.SocketOrChannelConnectionImpl.dispatch(SocketOrChannelConnectionImpl.java:375)
       at com.sun.corba.ee.impl.transport.SocketOrChannelConnectionImpl.read(SocketOrChannelConnectionImpl.java:284)
       at com.sun.corba.ee.impl.transport.ReaderThreadImpl.doWork(ReaderThreadImpl.java:73)
       at com.sun.corba.ee.impl.orbutil.threadpool.ThreadPoolImpl$WorkerThread.run(ThreadPoolImpl.java:382)

----------END server-side stack trace----------  vmcid: 0x0  minor code: 0  completed: No
       at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
       at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
       at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
       at java.lang.reflect.Constructor.newInstance(Constructor.java:494)
       at com.sun.corba.ee.impl.protocol.giopmsgheaders.MessageBase.getSystemException(MessageBase.java:791)
       at com.sun.corba.ee.impl.protocol.giopmsgheaders.ReplyMessage_1_2.getSystemException(ReplyMessage_1_2.java:97)
       at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.getSystemExceptionReply(CorbaMessageMediatorImpl.java:546)
       at com.sun.corba.ee.impl.protocol.CorbaClientRequestDispatcherImpl.processResponse(CorbaClientRequestDispatcherImpl.java:430)
       at com.sun.corba.ee.impl.protocol.CorbaClientRequestDispatcherImpl.marshalingComplete(CorbaClientRequestDispatcherImpl.java:326)
       at com.sun.corba.ee.impl.protocol.CorbaClientDelegateImpl.invoke(CorbaClientDelegateImpl.java:132)
       at org.omg.CORBA.portable.ObjectImpl._invoke(ObjectImpl.java:457)
       ... 3 more

After reviewing the documentation http://download.oracle.com/docs/cd/E14101_01/doc.1013/e13975/rmi.htm#CHDBDEII I had no inside why this error was occurring or if there was any misconfiguration on my client. Evenually I found the the following documentation http://download.oracle.com/docs/cd/E14101_01/doc.1013/e13977/csiv2.htm#i1014488 which states:

Any client, whether running inside a server or not, has EJB security properties. Table 19-2 following lists the EJB client security properties controlled by the ejb_sec.properties file. By default, OC4J searches for this file in the current directory when running as a client, or in ORACLE_HOME/j2ee/home/config when running in the server. You can specify the location of this file explicitly with the system property setting -Dejb_sec_properties_location=pathname.

I then created the ejb_sec.properties file with the following settings:

oc4j.iiop.trustedServers=*
client.sendpassword=true
nameservice.useSSL=false

Then I ran the client (as described by passing the file as system property) and it worked!

Using useFetchSizeWithLongColumn and verifying prefetch returns the expected number of rows

The following is a simple example that uses 'useFetchSizeWithLongColumn' on a long column where a the row prefetch is set to 15.

I confirm it returns 15 rows on each trip to the database by tracing the session and checking the trc file on the database.

My code looks at follows:

--------------------------------------------------------------------------------------------------------

package project1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import oracle.jdbc.OracleDriver;

public class TestFetchSizeWithLongColumn {

    private Connection conn = null;
    private Statement stmt = null;
    private Statement altstmt = null;
    private ResultSet rset = null;

    final String traceSQL = 
        "select c.value || '/' || d.instance_name || '_ora_' || " + 
        "to_char(a.spid, 'fm99999') || '.trc' " + 
        "from v$process a, v$session b, v$parameter c, v$instance d " + 
        "where a.addr = b.paddr " + "and b.audsid = userenv('sessionid') " + 
        "and c.name = 'user_dump_dest'";

    public static void main(String[] args) throws SQLException {
        new TestFetchSizeWithLongColumn().run();
    }

    public void run() {
        try {

            Connection conn = getConnection();

            altstmt = conn.createStatement();
            stmt = conn.createStatement();

            altstmt.execute("alter session set events '10046 trace name " + 
                            "context  forever, level 12'");
            ResultSet rset = 
                stmt.executeQuery("select search_condition,rownum" + 
                                  "from dba_constraints");
            altstmt.execute("alter session set events '10046 trace " + 
                            "name context off'");
            displayTraceFileName(conn);
            //            while (rset.next())
            //                System.out.println(rset.getInt(2));            

        } catch (SQLException sqle) {
            //sqle.printStackTrace();
            System.out.println("sqlexception");
        } finally {
            try {
                if (rset != null)
                    rset.close();
                if (stmt != null)
                    stmt.close();
                if (altstmt != null)
                    altstmt.close();
                if (conn != null)
                    conn.close();
            } catch (Exception ex) {
                ex.printStackTrace();
                System.out.println("fatal error");
            }
        }
    }

    public static Connection getConnection() throws SQLException {
        String url = 
            "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)" + 
            "(HOST=sracanov-au2.au.oracle.com)(PORT=1522))" + 
            "(CONNECT_DATA=(SERVICE_NAME=orcl)))";
        java.util.Properties props = new java.util.Properties();

        props.setProperty("user", "system");
        props.setProperty("password", "welcome1");
        props.setProperty("defaultRowPrefetch", "15");
        props.setProperty("useFetchSizeWithLongColumn", "true");

        DriverManager.registerDriver(new OracleDriver());
        Connection conn = DriverManager.getConnection(url, props);
        return conn;
    }

    public void displayTraceFileName(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery(traceSQL);
        rset.next();
        String fileName = rset.getString(1);
        System.out.println("TRACE FILE NAME : " + fileName);
    }
}

--------------------------------------------------------------------------------------------------------

Now run the class as follows:

[stever@STEVER-8500 C]$ java -version
java version "1.5.0_06"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_06-b05)
Java HotSpot(TM) Client VM (build 1.5.0_06-b05, mixed mode)

[stever@STEVER-8500 C]$ javac -cp .;"D:\My Contents\My Software\Oracle\JDBC Drivers\10.2.0.4\ojdbc14.jar" TestFetchSizeWithLongColumn.java

[stever@STEVER-8500 C]$ java -cp .;"D:\My Contents\My Software\Oracle\JDBC Drivers\10.2.0.4\ojdbc14.jar" TestFetchSizeWithLongColumn
TRACE FILE NAME : /u01/programs/oracle/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_7002.trc

[stever@STEVER-8500 C]$

The result returned is the location of my trace file on the database.

Upon checking the trace file, the following is shown:

=====================
PARSING IN CURSOR #1 len=51 dep=0 uid=5 oct=3 lid=5 tim=1221750605502677 hv=1856978345 ad='2bdf40f8'
select search_condition,rownum from dba_constraints
END OF STMT
PARSE #1:c=0,e=108,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1221750605502672
BINDS #1:
EXEC #1:c=0,e=98,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1221750605502850
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1221750605502898
FETCH #1:c=232965,e=227064,p=0,cr=7679,cu=0,mis=0,r=15,dep=0,og=1,tim=1221750605730008
WAIT #1: nam='SQL*Net message from client' ela= 77131 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1221750605807896
=====================

The line:

FETCH #1:c=232965,e=227064,p=0,cr=7679,cu=0,mis=0,r=15,dep=0,og=1,tim=1221750605730008

Contains 'r=15'. 15 rows are prefetched.

Closing socket as no data read from it during the configured idle timeout of 5 secs

In Jdeveloper 11.1.1.1.0, when I am running my web application I am frequently left with the following error from the integrated server:

<Warning> <Socket> <BEA-000449> <Closing socket as no data read from it during the configured idle timeout of 5 secs>

This is because I sometimes have multiple browsers/windows open of same application connections due to stopping and start the server each time opens a new window. Closing down all browsers/windows related to the application connection can avoid the problem.

WARNING: DeployerRunnable.run NoClassDefFoundErrororacle.oc4j.admin.internal.DeployerException: NoClassDefFoundError

When deploying a simple ADF application to standalone oc4j for the first time I get this error:

09/07/06 14:11:10 WARNING: DeployerRunnable.run NoClassDefFoundErrororacle.oc4j.admin.internal.DeployerException: NoClassDefFoundError
at oracle.oc4j.admin.internal.DeployerBase.execute(DeployerBase.java:134)
at oracle.oc4j.admin.jmx.server.mbeans.deploy.OC4JDeployerRunnable.doRun(OC4JDeployerRunnable.java:52)
at oracle.oc4j.admin.jmx.server.mbeans.deploy.DeployerRunnable.run(DeployerRunnable.java:81)
at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:298)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.lang.NoClassDefFoundError: oracle/jbo/JboException
at java.lang.Class.getDeclaredConstructors0(Native Method)
at java.lang.Class.privateGetDeclaredConstructors(Class.java:2357)
at java.lang.Class.getConstructor0(Class.java:2671)
at java.lang.Class.newInstance0(Class.java:321)
at java.lang.Class.newInstance(Class.java:303)
at com.sun.faces.config.ConfigureListener.configure(ConfigureListener.java:731)
at com.sun.faces.config.ConfigureListener.configure(ConfigureListener.java:418)
at com.sun.faces.config.ConfigureListener.contextInitialized(ConfigureListener.java:348)
at com.evermind.server.http.HttpApplication.initDynamic(HttpApplication.java:1141)
at com.evermind.server.http.HttpApplication.(HttpApplication.java:741)
at com.evermind.server.ApplicationStateRunning.getHttpApplication(ApplicationStateRunning.java:414)
at com.evermind.server.Application.getHttpApplication(Application.java:570)
at com.evermind.server.http.HttpSite$HttpApplicationRunTimeReference.createHttpApplicationFromReference(HttpSite.java:1987)
at com.evermind.server.http.HttpSite$HttpApplicationRunTimeReference.(HttpSite.java:1906)
at com.evermind.server.http.HttpSite.addHttpApplication(HttpSite.java:1603)
at oracle.oc4j.admin.internal.WebApplicationBinder.bindWebApp(WebApplicationBinder.java:238)
at oracle.oc4j.admin.internal.WebApplicationBinder.bindWebApp(WebApplicationBinder.java:99)
at oracle.oc4j.admin.internal.ApplicationDeployer.bindWebApp(ApplicationDeployer.java:547)
at oracle.oc4j.admin.internal.ApplicationDeployer.doDeploy(ApplicationDeployer.java:202)
at oracle.oc4j.admin.internal.DeployerBase.execute(DeployerBase.java:93)
... 4 more

This is because the ADF libraries are not installed on oc4j standalone.

1. Shut down oc4j.
2. Using the same Jdev version as oc4j, install the libraries from the menu: Tools -> ADF Runtime Installer - Standalone OC4J.
3. Select the oc4j home and install i.e D:\Oracle\oc4j\oc4j_extended_101330