Access a Data Source from OC4J remotely from a java client

I needed to access an data source in an OPMN managed OC4J instance remotely from a java client. My data source was in the 10.1.3.2 release of Oracle Application Server and my client was JDeveloper 10.1.3.2. Here is how I got this to work.

1. New Empty project in JDeveloper
2. Add the following libraries, which will ensure all the required jars needed are added to the classpath:

• Oracle JDBC
• JSP Runtime
• Apache Ant

3. Create a client as follows, the connection is made with a provider URL using opmn:ormi format as shown below:

package pas.test;

import java.sql.Connection;

import java.util.Hashtable;

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

import javax.sql.DataSource;

public class AccessDataSource10132
{

 public static void main(String[] args)
   throws Exception
 {
   Connection conn = null;
   Hashtable env = new Hashtable();
   env.put(Context.INITIAL_CONTEXT_FACTORY,
       "oracle.j2ee.rmi.RMIInitialContextFactory");
   env.put(Context.SECURITY_PRINCIPAL, "oc4jadmin");
   env.put(Context.SECURITY_CREDENTIALS, "welcome1");
   env.put(Context.PROVIDER_URL,
     "opmn:ormi://sracanov-au2.au.oracle.com:6005:OC4J_FCF/default");

   InitialContext ic = new InitialContext(env);

   DataSource ds = (DataSource) ic.lookup("jdbc/scottDS");
   conn = ds.getConnection();
   System.out.println("Auto commit is -> " + conn.getAutoCommit());

   System.out.println("all done..");
 }
}

The output is as follows:

Auto commit is -> true
all done..

ORA-01461 from JDBC program trying to insert large String

Having a database column defined as VARCHAR2(4000) in a table and trying to insert a character string which was 3983 characters failed with a runtime error as follows in a 10.2.0.2 Oracle Database with the character set AL32UTF8.

java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into
a LONG column

What is odd is that when run against the same database version 10.2.0.2 with a character set as WE8ISO5589P1, it worked fine no errors at all. The issue is that the when using character set as WE8ISO8859P1 it will be inserted without loss. But if the database is in the character set AL32UTF8 the Latin 1 characters will be expanded and the total size will increase.

Eg: http://www.unicode.org/charts/PDF/U0080.pdf

A simple Java class illustrates this as follows:

package project1;

public class Test
{
public static String test =
  "No lions. No tigers, but bears …";

static void showEncodedLength(String s, String encoding)
  throws Exception
{
  byte[] b = s.getBytes(encoding);
  System.out.println(encoding + " byte length: " + b.length);
}

public static void main(String[] args)
  throws Exception
{
  System.out.println("String length: " + Test.test.length());
  showEncodedLength(Test.test, "ASCII");
  showEncodedLength(Test.test, "ISO8859_1" );
  showEncodedLength(Test.test, "UTF8");
}
}

When run the output is as follows showing that the length increases when using UTF8 encoding even though only just this will result in a runtime error when trying to insert into large columns such as VARCHAR2(4000). The error ORA-01461 itself is not very useful, and fails to indicate the real issue here. From the output below you can see that it requires more length to insert into a UTF8 database

Output

String length: 32
ASCII byte length: 32
ISO8859_1 byte length: 32
UTF8 byte length: 34

If you have such an issue you should consider using a CLOB column if the data may exceed the maximum size of a varchar2 column as was the case here.

Connecting to the OC4J MBeanServer Doc Issue

I was attempting to connect to the OC4J MBeanServer in my opmn managed OC4J instance and was using the online documentation below.

http://download.oracle.com/docs/cd/B31017_01/web.1013/b28952/mbeans.htm#CIHFDJBJ

I found that in the example the service URL was specified incorrectly.

// Create a variable for a URL containing data needed to access 
// the connection target; in this case, an OPMN-managed OC4J instance
String url="service:jmx:rmi///opmn://opmnhost1.company.com:6003/home"

It is missing the : in the service URL. The correct way is shown below you will see the : added after rmi. A documentation bug has been filed.

Eg:

private String url="service:jmx:rmi:///opmn://papicell-au2.au.oracle.com:6003/home";

XA Transaction Errors On Websphere Talking To 10g Database Through JDBC Driver 10g

When attempting to run XA JDBC program on Websphere the following runtime error can occur:

13545 The XA Error is : -7
13546 The XA Error message is : Resource manager is unavailable.
13547 The Oracle Error code is : 17002
13548 The Oracle Error message is: Internal XA Error
13549 The cause is : null.
13550 [5/18/07 10:29:24:538 MST] 00000084 WSRdbXaResour E DSRA0302E: XAException occurred.
Error code is: XAER_RMFAIL (-7). Exception is:
13551 [5/18/07 10:29:24:538 MST] 00000084 WSRdbXaResour 3 XAER_RMFAIL connection error occurred
13552 [5/18/07 10:29:24:538 MST] 00000084 WSRdbXaResour 3 process connection error occurred
event for XAResource com.ibm.ws.rsadapter.spi.WSRdbXaResourceImpl@43e4a31d
13553 [5/18/07 10:29:24:538 MST] 00000084 WSJdbcConnect > close Entry
13554 com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@5f13637b
13555 [5/18/07 10:29:24:538 MST] 00000084 WSJdbcConnect 1 state --> CLOSED

It turns out that from the stack trace/error a 17002 error means that the connection pool is handing out stale connections which have been closed. This is documented on IBM web site with various different ways of trouble shooting stale connection errors from WebSphere. See the link below.

http://www-1.ibm.com/support/docview.wss?uid=swg21247168#SCETroubleshooting
Troubleshooting stale connection problems

Getting to the Java SSO configuration screen in ASC differs in stand alone OC4J versus OPMN managed OC4J instances

A while ago I setup my stand alone OC4J instance to use Java Sigle Sign On (Java SSO) as part of OC4J 10.1.3.1. Today I needed to do the same in an OPMN managed OC4J instance as part of OAS 10.1.3.1 and found that to configure it the steps are slightly different between the two in ASC (Application Server Console).

Here is how to get to the configure screen for Java SSO:

Stand Alone

1. Log into ASC
2. Click on the "Administration" link on the top of the page.
3. On this page you will find the following -> "Task Name -> Administration Taks -> Properties -> SSO Configuration" click on the icon for "Go to Task".

OPMN Managed OAS

1. Log in ASC
2. Scroll down to the bottom of the topology page and click the link "Java SSO Configuration"

Note: Of course in order to configure Java SSO you need to start "javasso" application as part of the MiddleWare Services application area, before you can configure it.

Dependency injection is not possible from a Struts Action class

Thanks to Debu for pointing out that when trying to use dependency injection JAVA EE 5 support limits this only to managed classes such as EJBs, Interceptors, Servlets, etc. I was attempting to do this from a Struts Action class and was not able do so, now I know why.

See Debu's blog entry for more details:
http://debupanda.blogspot.com/2007/06/injecting-pojos-and-using-resource.html

In the end I was forced into using regular JNDI lookup as follows from JDeveloper 10.1.3.1 / OC4J 10.1.3.1 from a Struts Action class:

 HttpSession session = request.getSession();
 ResultPager bean = (ResultPager) session.getAttribute("pagerbean");

 if (bean == null)
 {
   try
   {
     Context ctx = new InitialContext();
     bean = (ResultPager) ctx.lookup("ResultPager");
     bean.init(_pageSize, "AllObjectsRo.countAll", "AllObjectsRo.findAll");
     session.setAttribute("pagerbean", bean);    
   }
   catch (NamingException ex)
   {
     throw new ServletException(ex);
   }

 }

Getting a query count of records in EJB3

I was working on a small pagination demo with EJB3 and JDeveloper 10.1.3.1. I thought creating a query to count the number of records in my result set would be as simple as "select count(*)" but in EJB3 and JPQL world you can't use "select count(*)" unless you create a native query. Here is my named queries I used to get a count of my result set. In JPQL it's done with a query as follows, so no need to use a native query after all.

select COUNT(o) from AllObjectsRo o

@NamedQueries({@NamedQuery(name="AllObjectsRo.countAll",
query="select COUNT(o) from AllObjectsRo o"),
@NamedQuery(name="AllObjectsRo.findAll",
query="select o from AllObjectsRo o")
})