10.1.3.1 OPMN OC4J Log Files No Longer Displayed in ASC Log Files Screen

When using ASC (Application Server Console) in 10.1.3.0 you could view the OPMN OC4J log files in the screen "Log Files". Starting with 10.1.3.1 and above those log files no longer appear in ASC. This is due to a bug which should be fixed soon via a patchset for OAS, but for now you can easily re-enable this by following these steps.

1. Open the following file in a text editor.

$ORACLE_HOME\j2ee\home\applications\ascontrol\ascontrol\WEB-INF\config\registration\OPMN.xml



2. Make the following changes.

- Go to line 105 and make the change as outlined below.

<log path="'opmn/logs/HTTP_Server~1'" componentid="'OHS'">

to

<log path="'opmn/logs/HTTP_Server~1.log' componentId='OHS'>

- Go to to line 116 and make the change as outlined below.

<log
path='opmn/logs/%COMPONENT_TYPE%~%COMPONENT_NAME%~%OPMN_PROCESS_SET%~%OPMN_PROCESS_ID%'
componentId='OPMN'>

to

<log
path='opmn/logs/%COMPONENT_TYPE%~%COMPONENT_NAME%~%OPMN_PROCESS_SET%~%OPMN_PROCESS_ID%.log'
componentId='OPMN'>

3. Save the file and restart the instance where ASC is running which is normally HOME.

Now log in ASC and when you go to the "Log Files" screen the OPMN OC4J log files will be shown under the following tree items.

- OPMN
- Process Manager
- redirected output/errors


admin_client.jar giving standard "Could not get DeploymentManager" error.

There are a few blogs on what to check if your failing to use admin_client.jar to connect to an OPMN managed OC4J instance within OAS 10.1.3.x. For example the OPMN remote port is the first thing you check as outlined in steve's blog entry below.

http://buttso.blogspot.com/2006/11/tip-on-deploying-to-oracle-application.html

The problem with this error can be a number of different things, the opmn remote port is one of them but here is a few others

• Invalid connection uri
• Invalid username or password

The most tricky to diagnose is the invalid username/password , his what happens when you provide an invalid password which you are certain is correct and it's not the password

D:\jdev\oas-admin-distribution\10132\j2ee\home>java -jar admin_client.jar deployer:oc4j:opmn://papic
ell-au2.au.oracle.com:6007/home oc4jadmin invalidpassword -validateURI

Failed at "Could not get DeploymentManager".

This is typically the result of an invalid deployer URI format being supplied, the target server not
being in a started state or incorrect authentication details being supplied.

So by turning on logging we can get more information as to what is the real issue, which we do as follows.

1. Create a file called logging.properties with a single line as follows :

oracle.oc4j.admin.jmx.client.CoreRemoteMBeanServer.level=FINEST

2. Set -Djava.util.logging.config.file=logging.properties on the admin_client.jar command line as follows, and you get more detailed information which clearly shows you that the username/password you provided is the reason for the failure as shown below:

D:\jdev\oas-admin-distribution\10132\j2ee\home>java -Djava.util.logging.config.file=logging.properti
es -jar admin_client.jar deployer:oc4j:opmn://papicell-au2.au.oracle.com:6007/home oc4jadmin invalid
password -validateURI
FINE: CoreRemoteMBeanServer.fetchMBeanServerEjbRemote Getting remote RMI context for domain: opmn-ho
me+oc4j-papicell-au2.au.oracle.com-6007-default
FINE: CoreRemoteMBeanServer.fetchMBeanServerEjbRemote CoreRemoteMBeanServer url: ormi://papicell-au2
.au.oracle.com:12402/default
SEVERE: CoreRemoteMBeanServer.fetchMBeanServerEjbRemote Error reading application-client descriptor:
Error communicating with server: Lookup error: javax.naming.AuthenticationException: Not authorized
; nested exception is:
javax.naming.AuthenticationException: Not authorized; nested exception is:
javax.naming.NamingException: Lookup error: javax.naming.AuthenticationException: Not author
ized; nested exception is:
javax.naming.AuthenticationException: Not authorized [Root exception is javax.naming.Authent
icationException: Not authorized] for URL: ormi://papicell-au2.au.oracle.com:12402/defaultjavax.nami
ng.NamingException: Error reading application-client descriptor: Error communicating with server: Lo
okup error: javax.naming.AuthenticationException: Not authorized; nested exception is:
javax.naming.AuthenticationException: Not authorized; nested exception is:
javax.naming.NamingException: Lookup error: javax.naming.AuthenticationException: Not author
ized; nested exception is:
javax.naming.AuthenticationException: Not authorized [Root exception is javax.naming.Authent
icationException: Not authorized] [Root exception is java.lang.InstantiationException: Error communi
cating with server: Lookup error: javax.naming.AuthenticationException: Not authorized; nested excep
tion is:


Sometimes turning on logging is the only way your going to know exactly why it's failing.

Stand Alone OC4J 10.1.3.3 fails to start on solaris

On Solaris after downloading stand alone OC4J 10.1.3.3 and trying to start it the following error occurs and you can't start OC4J.

Error instantiating application at file ...META-INF/application.xml

The problem can be avoided by adding the following command line option when starting OC4J stand alone.

-Doc4j.autoUnpackLockCount=-1

Connecting to OAS 10.1.3.x from JDeveloper without using oc4jadmin

Every now and then I get a request to be able to connect to OAS 10.1.3.x from JDeveloper 10.1.3.x using a custom user rather then the default oc4jadmin. A work mate of mine got this working and also found that only certain roles will allow such a connection remotely when using a custom user.

Steps

A) CONFIGURE ORACLE APPLICATION SERVER 10.1.3.X

1. Go to OAS 10.1.3.x EM

http://:/em

2. Select an OC4J instance that you had created.
3. Select Administration.
4. In the Task Name section, under Security select the 'Go to Task' icon for Security Providers.
5. Press the button [Instance Level Security].
6. Select the tab link Realms.
7. In the section Security Provider Attributes: File-Based Security Provider there is a realm
named jazn.com. Select the number under the Users column.
8. Press the button [Create].
9. Add User screen:-

Name field : enter a user name
Password || Confirm Password fields : enter a valid password
In the Available Roles area, select the role like ascontrol_admin and move across to Selected Area.

Press [OK].

10. Restart OC4J container via ASC pressing the [Restart] button or use following commands:-

- opmnctl stopproc process-type=Tars
- opmnctl startproc process-type=Tars


B) JDEVELOPER >> OAS CONNECTION CUSTOM CREATED OC4J CONTAINER AND CUSTOM CREATED USER

1. In JDeveloper 10.1.3.X select Connections navigator.
2. Right-click Application Server node and select 'New Application Server Connection'.
3. On Welcome screen press [Next].
4. Step 1 of 4: Type screen :-

Connection Name field >> enter a app server connection name
Connection Type list field >> select "Oracle Application Server 10g 10.1.3".

Press [Next].

5. Step 2 of 4: Authentication screen:-

Username field : enter user name that was created in previous section at the OAS level.
Password field : enter same password was created in previous section at the OAS level.
Deploy Password checkbox : select.

Press [Next].

6. Step 3 of 4: Connection screen:-

Host Name field : enter host of OAS.
OPMN Port field : change to opmn remote port number
OC4J instance name field : enter of instance where user was created.

Press [Next].

7. Step 4 of 4: Test screen:-

Press [Test Connection]:

That should work but what we found is only 2 roles actually will work as shown by the test below which shows that only 2 roles actually work here, of course was never expecting the role "users" to work. We created 6 users each will the role shown next to them and found only ascontrol_admin and oc4j-administrators would allow such a remote connection

User1 (Role = ascontrol_admin) - SUCCESS
User2 (Role = ascontrol_appadmin) - FAILS
User3 (Role = ascontrol_monitor) - FAILS
User4 (Role = oc4j-administrators) - SUCCESS
User5 (Role = oc4j-app-administrators) - FAILS
User6 (Role = users) - FAILS

Debug PLSQL code from JDeveloper 10.1.3.x causing timed out error

You may find that when trying to debug PLSQL from JDeveloper an error as follows appears:

Connecting to the database MyHRConn.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '172.16.26.237', '4717' )
ORA-30683: failure establishing connection to debugger
ORA-12535: TNS:operation timed out
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database MyHRConn.

It's very likely from the error you have a firewall between the client (JDeveloper) and the database server in which case you can simply open up the port at the firewall level, but while it's dynamically assigned this won't work, so follow these steps to ensure you use a static port and then you can configure the firewall to enable the connection

1. If necessary, close JDeveloper.
2. In a text editor, open {jdev_install}/jdev/system/oracle.jdeveloper./ide.properties
3. Type the following:

DatabaseDebuggerPortOverride={port_number}

where is the {port_number} is the port number you want the debugger to use.

4. Save ide.properties.

When you restart JDeveloper, the port you specified will be used and hopefully you can connect without any issue.

Debug PLSQL code from JDeveloper 10.1.3.x causing timed out error.

You may find that when trying to debug PLSQL from JDeveloper an error as follows appears:

Connecting to the database MyHRConn.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '172.16.26.237', '4717' )
ORA-30683: failure establishing connection to debugger
ORA-12535: TNS:operation timed out
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database MyHRConn.

It's very likely from the error you have a firewall between the client (JDeveloper) and the database server in which case you can simply open up the port at the firewall level, but while it's dynamically assigned this won't work, so follow these steps to ensure you use a static port and then you can configure the firewall to enable the connection

1. If necessary, close JDeveloper. 2. In a text editor, open /ide.properties . 3. Type the following: DatabaseDebuggerPortOverride= where is the port number you want the debugger to use. 4. Save ide.properties.

When you restart JDeveloper, the port you specified will be used and hopefully you can connect without any issue.

Application Server Connection 10.1.3.x from Jdeveloper 10.1.3.x

Creating an application server connection from JDeveloper 10.1.3.x requires you to know a bit about the application server here is all you need to find out exactly what to put in the fields. If you don't know it's worth running some of these commands to avoid having to determine why the connection is failing to establish when testing it.

OPMN Remote Port

Issue the following command on the OAS server to get the OPMN port number:

> opmnctl status -port

Output:

papicell-au2.au.oracle.com:6007

Hostname

Ensure you can ping the host by name or IP address from your machine where Jdeveloper is running. You could even try connecting using telnet should the host allow it, but normally ping is enough to verify you have a reachable host.

> telnet papicell-au2.au.oracle.com 6007

That command will tell you whether or not you can reach the host through the OPMN port remotely.

OC4J Instance Name

To determine this name you simply use a command as follows on the OAS server, which has a column known as process-type which identifies the OC4J instances you can connect to.

> opmnctl status

Connect to

Here we decide if we are connecting to a group or a single instance. A group is a collection of OC4J instances which allows you to deploy applications to a group which in turns deploys your application to every container within the group. Basically when you select the check box "group" you then need to provide a group name.

The easiest way to determine the group name is to log into ASC (Application server console) which shows you exactly what type of topology setup you have for your OAS.

Also you can use the command we used previously to get the group name as follows

> opmnctl status

This time your looking at the column ias-component which will give you the group name you want to use which identified by OC4JGroup:{name} all you need is {name} portion to use as the group name

Eg: OC4JGroup:pas_group

Hope this helps those remote connection problems you may have from JDeveloper 10.1.3.x to OAS 10.1.3.x.

Accessing a JMS queue from stand alone client in OAS 10.1.2

I am normally accessing JMS queues from OAS 10.1.3 but today I had to do it in 10.1.2 and found that unless I created a new user I was unable to get access to the queue once authenticated, his exactly what I did.

1. Log into ias console and click on your oc4j container, I used home
2. Click on administration link
3. Click on security link
4. Click add user button and created a user as follows

user: test
password: test123

Note: Check all check boxes to add all group names to ensure no permission issues will exist using this user

5. Edit $ORACLE_HOME/j2ee/home/config/jms.xml and ensure the following two entries exist

<queue name="Demo Queue" location="jms/demoQueue">
A dummy queue
</queue>

<queue-connection-factory
name="jms/QueueConnectionFactory"
location="jms/QueueConnectionFactory"/>

6. Now the code will be as follows, I am using an ormi:// connection where I obtained the container RMI port using "opmnctl status -l"

package mypackage1;
import java.util.Hashtable;
import javax.jms.Queue;
import javax.jms.QueueConnectionFactory;
import javax.naming.Context;
import javax.naming.InitialContext;

public class TestJMSConnection
{

  /**
   *
   * @param args
   */
  public static void main(String[] args) throws Exception
  {
    TestJMSConnection testJMSConnection = new TestJMSConnection();
    Hashtable env = new Hashtable();
    env.put(Context.INITIAL_CONTEXT_FACTORY, "com.evermind.server.rmi.RMIInitialContextFactory");
    env.put(Context.PROVIDER_URL,"ormi://10.187.80.135:12401");
    env.put(Context.SECURITY_PRINCIPAL,"test");
    env.put(Context.SECURITY_CREDENTIALS,"test123");
    InitialContext ctx = new InitialContext(env);
   
    System.out.println("Initial Context Set");

    QueueConnectionFactory qcf = (QueueConnectionFactory)
       ctx.lookup("jms/QueueConnectionFactory");
   
    System.out.println("Found factory jms/QueueConnectionFactory");
   
    Queue q = (Queue) ctx.lookup("jms/demoQueue");
       
    System.out.println("Found queue jms/demoQueue");
   
    System.out.println("all done..");
  }
}

7. In JDeveloper 10.1.2 you need to add these two libraries so the code compiles

• Oracle 9ias
• J2EE

8. When run the output is as follows:

Initial Context Set
Found factory jms/QueueConnectionFactory
Found queue jms/demoQueue
all done..

Using Oracle Ant Tasks within JDeveloper 10g

With OAS 10.1.3.x they released a set of Ant tasks that enable operations such as application deployment, resource configuration and server lifecycle tasks to be performed. I normally run these from the command line but recently needed to set this up in JDeveloper 10.1.3.2, his how I did that.

1. Firstly I decided to use this 10.1.3.x how to on the OTN web site

http://www.oracle.com/technology/tech/java/oc4j/1013/how_to/index.html
How-To Swap XML Parsers ( zip ) Aug-2006

I downloaded the ZIP file onto my file system so I could set this up in Jdeveloper. The 3 files I need are as follows.

• ant-oracle.properties
• ant-oracle.xml
• build.xml

2. In JDeveloper create a new empty project within a workspace
3. From the Object gallery add a new empty build.xml file
4. Copy the 3 files above into the same directory the build.xml was added to in JDeveloper. Basically you are replacing the empty build.xml and adding the 2 other files needed for oracle ant tasks.
5. Use the Refresh icon on the application navigator to ensure all 3 files appear in the project as shown below.




6. Now we need to configure the ant-oracle.properties file to specify our deployment server settings. I am deploying to a OAS 10.1.3.2 instance named pas_oc4j. That said his the settings I am using , thats pretty much all you need to set in that file.

oracleas.host=papicell-linux.au.oracle.com
oracleas.http.port=7779
oracleas.admin.port=6007
oracleas.admin.user=oc4jadmin
oracleas.admin.password=welcome1
oracleas.binding.module=default-web-site
oracleas.deployer.uri=deployer:oc4j:opmn://${oracleas.host}:${oracleas.admin.port}/${oracleas.oc4j.instance}
oracleas.oc4j.instance=pas_oc4j

7. Now we have an empty project , but in order to use this otn how to we need to add a JSP to the project, a library and a few deployment descriptor XML files.
8. Add the library "Oracle XML Parser v2" to the project using the Project Properties dialog
9. Add a new JSP file called "index.jsp" using the new object gallery, I placed it in a folder named "web".

What you will find is a web.xml is created automatically for you once the JSP was created in the public_html\WEB-INF directory. All we need to do now is copy the files below from the downloaded how to ZIP into that directory on the file system and press Refresh to show the files in the JDeveloper project

• orion-application.xml
• application.xml

10. Now we are ready to deploy using ant BUT before we do that we need to alter the build.xml as the file downloaded from OTN is setup based on the ZIP file, but in JDeveloper we have a slightly different directory structure so in the build.xml we need to alter the "init" target so it looks as follows which matches our directory structure in the project


<target name="init" depends="common">

<echo message="-----> Initializing project properties"/>

<property name="app.name" value="how-to-swapxmlparser"/>

<property name="ear.name" value="${app.name}"/>

<property name="web.name" value="${ear.name}-web"/>

<property name="src.dir" value="./public_html"/>

<property name="bld.dir" value="./build"/>

<property name="lib.dir" value="D:\jdev\jdevprod\10132\jdev\mywork\OracleAntDemo\OC4JAntDelpoy/lib"/>

<property name="etc.dir" value="./public_html/WEB-INF"/>

<property name="log.dir" value="./log"/>

<property name="src.web.dir" value="${src.dir}/web"/>

<property name="bld.web.dir" value="${bld.dir}/${web.name}"/>

<property name="bld.ear.dir" value="${bld.dir}/${ear.name}"/>

<property name="bld.compiler" value="classic"/>

<property name="class.files" value="**/*.class"/>

<property name="bak.files" value="**/*~"/>

</target>


11. You will see I hard coded the full path to the final EAR file we will deploy due to an issue with JDeveloper which once I sort it out I will update this blog with a more elegant way of referring to that file. For now simple replace the full path with your directory structure ensuring "lib" remains as ant script is using the "lib" directory.

D:\jdev\jdevprod\10132\jdev\mywork\OracleAntDemo\OC4JAntDelpoy/lib

12. Right click on build.xml and select -> Run Ant and select the target all

Output as follows

Buildfile: D:\jdev\jdevprod\10132\jdev\mywork\OracleAntDemo\OC4JAntDelpoy\build.xml

common:
[echo] BuildName: how-to-swapxmlparser
[echo] BuildHome: D:\jdev\jdevprod\10132\jdev\mywork\OracleAntDemo\OC4JAntDelpoy
[echo] BuildFile: D:\jdev\jdevprod\10132\jdev\mywork\OracleAntDemo\OC4JAntDelpoy\build.xml
[echo] BuildJVM: 1.5

init:
[echo] -----> Initializing project properties

setup:
[echo] -----> Creating the required sub-directories

package-web:

package-app:

checkoc4javailable:
[echo] ------> Checking to see if OC4J is available to deploy to
[echo] Pinging papicell-au2.au.oracle.com:6007
[echo] If OC4J is not started, please start it now.

skipdeploy:

deploy-ear:
[echo] -----> Deploying the application module deployment (ear) file
[echo] Target: deployer:oc4j:opmn://papicell-au2.au.oracle.com:6007/pas_oc4j
[echo] lib.dir: D:\jdev\jdevprod\10132\jdev\mywork\OracleAntDemo\OC4JAntDelpoy/lib/how-to-swapxmlparser.ear
[oracle:deploy] Deploying application how-to-swapxmlparser.
[oracle:deploy] Application deployer for how-to-swapxmlparser COMPLETES.

deploy:
[echo] -----> Deployed the application components required by OC4J for the execution of this application

all:
[echo] -----> Created all deployable units for the application

BUILD SUCCESSFUL
Total time: 4 seconds


So after a little tweaking we can easily get the OC4J OTN 10.1.3.x how-to's incorporated into JDeveloper using Oracle Ant Tasks to do our compilation/deployment for us.

Using the DAO (Data Access Object) J2EE Design pattern with ADF

I have always been a big fan of ADF BC , after all it's a lot less coding but I still do write DAO (Data Access Objects) applications and using ADF it doesn't take look to get those DAO method which return database data results onto my page. I find the ADF JSF experience just a little too rich so I always opt for for using pure JSP pages and using the Struts Page Flow to quickly assemble my web UI. His an example of how easy it is to get data from your DAO queries onto your JSP pages using ADF in JDeveloper 10.1.3.3.

TalentDAO Interface

package oracle.support.au.dao;

import java.util.List;
import oracle.support.au.dao.beans.Manager;


public interface TalentDAO
{
public List<Manager> retrieveManagerList() throws TalentException;

.....

TalentDAOImpl Class

package oracle.support.au.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.List;

import java.util.logging.Level;

import java.util.logging.Logger;

import oracle.support.au.dao.beans.Manager;

public class TalentDAOImpl implements TalentDAO
{
// Use JDK Logging
private static Logger logger = Logger.getLogger("talentlogging");

public List<Manager> retrieveManagerList() throws TalentException
{
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
List<Manager> mgrs = null;

conn = TalentUtil.getDataSourceConnection();

try
{
  stmt = conn.createStatement();
  rset = stmt.executeQuery(Constants.ALL_MANAGERS);

  mgrs = TalentUtil.makeManagerListFromResultSet(rset);
}
catch (SQLException se)
{
  logger.log
   (Level.SEVERE,
    "** TALENT (TalentDAOImpl.retrieveManagerList() : " +
    se.getMessage());
  throw new TalentException(se);
}
finally
{
  // close all resources
  JDBCUtil.close(rset, stmt, conn);
}

return mgrs;
}

.....

1. Right click on my DAO Implementation class and select "Create Data Control"
2. Open up my Struts Page Flow diagram
3. Drag and Drop a "Data Page" onto the diagram
4. Double click on the Data Page icon and create your JSP page.
5. Open up the "Data Control Palette"
6. Drag the the "Result" of the method retrieveManagerList() onto the Visual Editor and select "Tables -> Read Only Table".

That's all you need to do here and the Manager bean which is simply a java class with getter/setters for each property is what is in the actual return list which we clearly specify by the method signature. ADF data binding will then do it's magic to expose the return List of Managers to the UI at runtime without having to write any code to setup the bindings.

public List<Manager> retrieveManagerList() throws TalentException

Be sure to run the Data Page itself and not the JSP page to see the return results in a table.

Avoiding having to enter password when invoking SQL*Plus

Back in the early days with JDeveloper you could invoke SQL*Plus without having to provide a password but now in 10g you are always promoted for a password. To get around this I simply created an "External Tools" entry which allowed me to invoke SQL*Plus without a password, his how I did this.

1. Tools -> External Tools
2. Select the "New" button
3. Leave the default option as "External Program" and press Next
4. For the "Program Executable" refer to SQL*PLus on your file system.

D:\oracle\product\10.2.0\db_1\BIN\sqlplusw.exe

5. Enter in the full description which includes the username/password in the arguments text field.

talent/talent@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=
(PROTOCOL=TCP)(HOST=papicell-au2.au.oracle.com)(PORT=1521)))
(CONNECT_DATA=(SID=lnx102))) @${file.path}

Notice how I use @ to invoke the current selected file which will automatically call my SQL for me.

6. Enter your display properties and click Next
7. I normally add my integration from the navigator context menu, so select that and press Next
8. Finally associate this with SQL files and press Finish

Now when we right click on a SQL file in navigator and select the new context menu option we just created it will run that SQL file in SQL*Plus without prompting me for a password. Saves a lot of time.

External Tables with 11g JDBC driver

External tables arrived in the Oracle 10g database today I had to give it a test drive on a 11g database with the 11g JDBC driver. In the old days I would use sql*loader to load a flat file into the database and then access the data but with external tables you can avoid that as shown in this very simple demo.

External Data file: mydata.txt

1, 'Pas Apicella'
2, 'Adrian Campanaro'
3, 'Fred Blog'
4, 'Dave Hughes'
5, 'Joe Blow'

1. Create a directory on the server where the file exists

create directory scott_dir as '/home/oracle/pas';

2. Create the external table as shown below

drop table my_emps;

create table my_emps
(empno number,
ename varchar2(20))
organization external
(type oracle_loader
default directory scott_dir
access parameters
(
  fields terminated by ','
  optionally enclosed by "'"
  missing field values are null
)
location ('mydata.txt')
);

3. Here is the simple JDBC code which sees it as an ordinary table

 public void run() throws SQLException
 {
   Connection conn = getConnection();
   conn.setAutoCommit(false);
   Statement stmt = null;
   ResultSet rset = null;
   String sql = "select empno, ename from my_emps";
   SQLWarning x = null;
 
   try
   {
     stmt = conn.createStatement();
     rset = stmt.executeQuery(sql);
     
     while (rset.next())
     {
       System.out.println
         (rset.getInt(1) + " : " +
          rset.getString(2));
     }
 

     System.out.println("Successfully read external table");
     conn.commit();
   }
   catch (Exception e)
   {
     e.printStackTrace();
   }
   finally
   {
     if (rset != null)
     {
       rset.close();
     }

     if (stmt != null)
     {
       stmt.close();
     }

     if (conn != null)
     {
       conn.close();
     }
   }
 }

PreparedStatement method setNClob(int, Reader) causes HANG with 11g JDBC Driver

When using JDK 1.6 with the new 11G JDBC driver the following code causes a HANG to occur.

   // Get connection
   Connection conn = getConnection();

   // start test
   String lobContent = "WLS JDBC4 test for NClob.";

   String insertSql = "INSERT INTO nclob_table VALUES (?, ?)";
   PreparedStatement pstmt = conn.prepareStatement(insertSql);

   int loopCount = 10;
   StringReader sreader = null;

   try
   {
       for (int i = 0; i < loopCount; i++)
       {
           pstmt.setInt(1, i);
           sreader = new StringReader(lobContent + "-" + i);
           // Hangs here
           pstmt.setNClob(2, sreader);
           pstmt.execute();
           sreader.close();
           System.out.println("inserted a row..." );
       }
   }
   finally
   {
       if (pstmt != null)
       {
           pstmt.close();
       }
       if (conn != null)
       {
           conn.close();
       }
   }
 }

The hang occurs at this line of code:

pstmt.setNClob(2, sreader);

In order to avoid the hang you can use the following method:

• setNClob(int, Reader, long)

This then works fine when the following is used.

pstmt.setNClob(2, sreader, sreader.toString().length());

Inserting/Reading BLOB's with 11g / JDK 1.6

Here is some code I used to insert/read BLOB images in an 11g database using 11g JDBC driver with JDK 1.6. In these tests I uploaded 30K images which was lighting fast as you would expect.

Table:

drop sequence blob_seq;
drop table blob_fun;

create sequence blob_seq
start with 1
increment by 1
/

create table blob_fun (id number, image_col blob, image_size number)
/

Insert Code:

  public void run() throws SQLException, IOException
 {
   Connection conn = getConnection();
   conn.setAutoCommit(false);
   PreparedStatement pstmt = null;
   String sql = "insert into blob_fun values (blob_seq.nextval, ?, ?)";
 
   byte [] b = new byte[1024];
    ByteArrayOutputStream b1 = new ByteArrayOutputStream();

   FileInputStream f1 = new FileInputStream("D:\\temp\\blob\\ip-settings.jpg");
 
   int i;
   do
   {
     i = f1.read(b);
     b1.write(b);
   } while( i!= -1);

   ByteArrayInputStream bais = new ByteArrayInputStream(b1.toByteArray());
 
   try
   {
     pstmt = conn.prepareStatement(sql);
     pstmt.setBlob(1, bais);
     pstmt.setInt(2, bais.available());
     pstmt.execute();
   
     System.out.println("Successfully uploaded BLOB");
     conn.commit();
     bais.close();
     f1.close();
   }
   catch (Exception e)
   {
     e.printStackTrace();
   }
   finally
   {
     if (pstmt != null)
     {
       pstmt.close();
     }
   
     if (conn != null)
     {
       conn.close();
     }
   }

   conn.close();
 }

Read Code:

public void run() throws SQLException, IOException
{
  Connection conn = getConnection();
  conn.setAutoCommit(false);
  PreparedStatement pstmt = null;
  BLOB b = null;
  ResultSet rset = null;
  String sql = "select image_col from blob_fun where id = 1";

  try
  {
    pstmt = conn.prepareStatement(sql);
    rset = pstmt.executeQuery();
    rset.next();
    b = (BLOB) rset.getBlob(1);
    int chunk = b.getChunkSize();
    byte[] buffer = new byte[chunk];
    int length;
        
    FileOutputStream outFile = null;
    outFile = new FileOutputStream
      ("D:\\temp\\blob\\readfromdbfile.jpg");
    InputStream instream = b.getBinaryStream();
    
    // Fetch data  
    while ((length = instream.read(buffer)) != -1)
    {
      outFile.write(buffer, 0, length);
    }

    // Close input and output streams
    instream.close();
    outFile.close();  
  
    System.out.println("Successfully read/saved BLOB to file system from DB");
    conn.commit();
  }
  catch (Exception e)
  {
     e.printStackTrace();
  }
  finally
  {
    if (rset != null)
    {
      rset.close();
    }
  
    if (pstmt != null)
    {
      pstmt.close();
    }

    if (conn != null)
    {
      conn.close();
    }
  }

  conn.close();
}

UpdateableResultSet with BLOB column

When trying to use an UpdateableResultSet with a BLOB column the following error occurs:

Exception in thread "main" java.sql.SQLException: Invalid operation for read only resultset: updateBlob
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)

The code being used looks something like this:

   String querySql2 = "SELECT id, col2 FROM blob_test";
 
   stmt = null;
   stmt = conn.createStatement
     (ResultSet.TYPE_SCROLL_INSENSITIVE,
      ResultSet.CONCUR_UPDATABLE);
   ResultSet rs = null;
   rs = stmt.executeQuery(querySql2);
   rs.next();
   // Fails at this line with exception
   rs.updateBlob(2, inputStreamUpdate); 

This occurs because updateBlob(int, java.io.InputStream) realizes that the user is binding a stream with unknown length and throws an exception. To get around this you need to use the following two methods which work fine.

• updateBlob(int, Blob)
• updateBlob(int,java.io.InputStream, long)

See the following documentation for more information on ResultSet's:

http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/resltset.htm#i1022319