Determining the RAC instance my ADF JSF application is using

I was testing ADF BC deployed applications using a RAC enabled data source setup for FCF. While I was testing I needed to know what instance in the database the ADF application module was using. To do this I simply created a dummy VO called "DetermineInstanceVO" with a simple query as follows which does not require access to v$session for example.

select
sys_context('userenv', 'instance_name') "Instance",
sys_context('userenv', 'server_host') "Host",
sys_context('userenv', 'service_name') "ServiceName"
from dual

With that View Object I then would execute a query on my pages and display the current RAC instance the Application Module was using.

Of course I couldn't see this being that useful in a production application but handy during the testing phase while taking RAC instances down to ensure you are routed to another RAC instance to service the request without any errors.

JDBC Thin connection using tnsnames.ora entry

As of the 10.2.x JDBC driver we now allow you to connect to your database using a tnsnames.ora entry from JDBC THIN. Previously this was not possible without fully qualifying the description alias. To do this in 10.2.x JDBC driver you can do it one of 2 ways.

Option 1:

Pass a system property as follows at the command line

-Doracle.net.tns_admin=D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN

Option 2:

    System.setProperty("oracle.net.tns_admin",
                        "D:\\oracle\\product\\10.2.0\\db_1\\NETWORK\\ADMIN");

His a quick code example showing how it works:

tnsnames.ora entry

LNX102 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = papicell-au2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = lnx102)
)
)

Java Code

/*
 * Option 1 : Systsem property as command line option
 *
 * -Doracle.net.tns_admin=D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN
 *
 * Option 2 : System propety within code
 *
 * System.setProperty("oracle.net.tns_admin",
 *                    "D:\\oracle\\product\\10.2.0\\db_1\\NETWORK\\ADMIN");
 */
package pas.test;

import java.sql.*;
import oracle.jdbc.OracleDriver;

public class JdbcThinTnsNamesTest
{ 
  public JdbcThinTnsNamesTest()
  {
    System.setProperty("oracle.net.tns_admin",
                        "D:\\oracle\\product\\10.2.0\\db_1\\NETWORK\\ADMIN");
  }

  public static Connection getConnection() throws SQLException
  {
    String username = "scott";
    String password = "tiger";
    String thinConn = "jdbc:oracle:thin:@lnx102";
    DriverManager.registerDriver(new OracleDriver());
    Connection conn = DriverManager.getConnection(thinConn,username,password);
    conn.setAutoCommit(false);
    return conn;
  }
 
  public void run () throws SQLException
  {
    Connection conn = getConnection();
    System.out.println("Auto Commit = " + conn.getAutoCommit());
    conn.close();
  }
 
  public static void main(String[] args)
  {
    JdbcThinTnsNamesTest test = new JdbcThinTnsNamesTest();
    try
    {
      test.run();
      System.out.println("all done..");
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
  }
 
}

Runtime Output

Auto Commit = false
all done..

SQLException: Data size bigger than max size for this type using 9i JDBC Driver

If your using the 9i JDBC Driver in this case the (9.2.0.8) driver and you try to update/insert a very large string , say 100,000 bytes into a LONG column you will get a runtime error as follows

java.sql.SQLException: Data size bigger than max size for this type: 1000000

There are 2 ways to get around this.

OPTION 1
-----------

Use CLOB to handle very large column data instead of using a LONG column. CLOB columns is the recommended way to deal with large column values, so avoid using LONG if possible.

OPTION 2
-----------

Until JDBC THIN Driver 10.1.x there is a restriction on the amount of data that can be used be with methods setBytes(), setString() for example.

When using a JDBC THIN Driver 9.x or 10.1.x you should use setBinaryStream() or setCharacterStream() instead. The 10.2.x driver will automatically switch to using streams if the data exceeds a certain value. So switching to the 10.2.0.4 JDBC driver for example will make it even easier to get around this.


Here is some code you can use to verify this with 9.2.0.8 JDBC driver then use the 10.2.0.4 driver and verify it will work in 10.2.0.4 JDBC Driver fine.

SQL Needed

drop table long_table;

create table long_table
(id number,
 long_col long)
/

insert into long_table values (1, 'Sample Data');

commit;

Java Code

import java.sql.*;

import oracle.jdbc.OracleDriver;

public class LongDemo
{
  public LongDemo()
  {
  }

  public void run () throws SQLException
  {
    Connection conn = getConnection();

     // print driver details
     // Create Oracle DatabaseMetaData object
     DatabaseMetaData meta = conn.getMetaData ();

     // gets driver info:

     System.out.println("\n=============\nDatabase Product Name is ... " +
     meta.getDatabaseProductName());
     System.out.println("Database Product Version is  " +
     meta.getDatabaseProductVersion());
     System.out.println("\n=============\nJDBC Driver Name is ........ " +
     meta.getDriverName());
     System.out.println("JDBC Driver Version is ..... " +
     meta.getDriverVersion());
     System.out.println("JDBC Driver Major Version is " +
     meta.getDriverMajorVersion());
     System.out.println("JDBC Driver Minor Version is " +
     meta.getDriverMinorVersion());
     System.out.println("=============");
    
    String updateQuery = "update long_table set long_col = ? where id = 1";
    PreparedStatement updateStatement = conn.prepareStatement(updateQuery);
    String outValue = createLargeString(1000000);
    System.out.println("Update String size: " + outValue.length());
    updateStatement.setString(1, outValue);
    updateStatement.executeUpdate();
    updateStatement.close();
   
    conn.commit();
    conn.close();
  }

  /*
   * Creates a large string the size we require
   */
  private String createLargeString(int size)
  {
    StringBuffer b = new StringBuffer(size);
    for (int i=0; i<size; i++)
    {
      b.append("X");
    }
    return b.toString();
  }
 
  public static Connection getConnection() throws SQLException
  {
    String username = "tars";
    String password = "tars";
    String thinConn = "jdbc:oracle:thin:@papicell-au2:1521:lnx102";
    DriverManager.registerDriver(new OracleDriver());
    Connection conn = DriverManager.getConnection(thinConn,username,password);
    conn.setAutoCommit(false);
    return conn;
  }
 
  public static void main(String[] args)
  {
    LongDemo longDemo = new LongDemo();
    try
    {
      longDemo.run();
      System.out.println("update worked fine");
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
  }
}

Obtaining statistics on a Message Driven Bean from a JMX Client connecting to OC4J 10.1.3.x

A while ago steve showed me a few code demos on how to access OC4J Mbeans within OC4J 10.1.3.x. Since then I have created a few command line clients to perform tasks which admin_client.jar doesn't expose. A few for example include these.

• View application statuses
• Stop/Start a Message Driven Bean

While doing this I noticed that if you ever need to access a MDB statistics you would do it with code as follows.

 private void showStats(ObjectName cp) throws Exception
 {

   EJBStats stats =
     (EJBStats) mbs.getAttribute(cp, "stats");

When you run this with say with a 10.1.3.3 OAS client side distribution home you will get a runtime exception as follows.

[java] 14/04/2008 12:05:27 oracle.j2ee.rmi.RMIMessages
EXCEPTION_ORIGINATES_FROM_THE_REMOTE_SERVER
[java] WARNING: Exception returned by remote server: {0}
[java] java.lang.NoClassDefFoundError:
Loracle/oc4j/admin/management/callbackinterfaces/MessageDrivenBeanCallBackIf;

In order to get around this add the following JAR to your classpath, which can be obtained from the OAS server home or a stand alone OC4J home.

$ORACLE_HOME\j2ee\home\lib\oc4j-internal.jar

Changes made in bc4j.xcfg from JDeveloper Application Module Configuration editor

The file bc4j.xcfg has various configuration parameter's which if not displayed will take on their defaults. After a recent customer visit I was asked if a default configuration parameter was added to the file and then reverted back to it's original default value is the parameter removed from the bc4j.xcfg file to ensure it can be supplied at the container level if required?

I would suspect it would but to prove that here is what I did.

1. Created a simple ADF BC project based on 1 entity and 1 view object, in this case EMP
2. Right click on the application module and select -> Configurations
3. Click the "Edit" button on your configuration
4. Click on the tab "Pooling and scalability"
5. Set the initial pool size of the "Application Pool" to 5 and press OK

The following is added to the bc4j.xcfg file.

<jbo.ampool.initpoolsize>5</jbo.ampool.initpoolsize>

Now if we redit the config and set the same value to 0 (which is it's default) does it remove the parameter?

The answer is yes.

Of course you can click on "Reset" button BUT that will remove all configuration parameters you set which may not be what you require.