Steve's Blog

Reloading jspx page from a commandLink

2011-11-04T14:23:00.000+11:00

A simple example to reload a jspx page from a commandLink. [Jdev 11.1.1.4.0]

1. Run testpage.jspx.

2. Click in Reload Page link.

Using '-jarsasdbobjects -prependjarnames' parameters and calling the loaded class from a Java Stored Procedure

2011-09-12T12:08:00.001+10:00

An example using loadjava/dropjava with '-jarsasdbobjects -prependjarnames' parameters and trying to access the class in the jar from a java stored procedure.

[oracle@sracanov-au2 ~]$ cat > HelloWorld1.java
public class HelloWorld1 {
   public static void main(String[] args){
       System.out.println(new HelloWorld1());
   }
   public String toString(){
       return "Hello World 1";
   }
   public static String getHello(){
       return "Hello World 1 !!!";
   }
}
Quit
[oracle@sracanov-au2 ~]$ javac HelloWorld1.java
[oracle@sracanov-au2 ~]$ java HelloWorld1
Hello World 1
[oracle@sracanov-au2 ~]$ loadjava -u scott/tiger -jarsasdbobjects -prependjarnames HelloWorld1.java
[oracle@sracanov-au2 ~]$ cat > java_objects.sql
COL object_name format a30
COL object_type format a15
SELECT object_name, object_type, status FROM user_objects WHERE object_type IN ('JAVA SOURCE', 'JAVA CLASS', 'JAVA RESOURCE') ORDER BY object_type, object_name;
Quit
[oracle@sracanov-au2 ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 6 14:38:01 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @java_objects.sql

OBJECT_NAME                    OBJECT_TYPE     STATUS

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

HelloWorld1                    JAVA CLASS      INVALID
HelloWorld1                    JAVA SOURCE     INVALID

SQL> CREATE OR REPLACE FUNCTION HW RETURN VARCHAR2 as LANGUAGE JAVA NAME 'HelloWorld1.getHello() return String';

2 /

Function created.

SQL> select HW() from dual;

HW()

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

Hello World 1 !!!

SQL> @java_objects.sql

OBJECT_NAME                    OBJECT_TYPE     STATUS

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

HelloWorld1                    JAVA CLASS      VALID
HelloWorld1                    JAVA SOURCE     VALID

SQL> exec dbms_java.dropjava('HelloWorld1');

PL/SQL procedure successfully completed.

SQL> @java_objects.sql

no rows selected

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@sracanov-au2 ~]$ loadjava -u scott/tiger HelloWorld1.java
[oracle@sracanov-au2 ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 7 13:18:44 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @java_objects.sql

OBJECT_NAME                    OBJECT_TYPE     STATUS

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

HelloWorld1                    JAVA CLASS      INVALID
HelloWorld1                    JAVA SOURCE     INVALID

SQL> CREATE OR REPLACE FUNCTION HW RETURN VARCHAR2 as LANGUAGE JAVA NAME 'HelloWorld1.getHello() return String';

2 /

Function created.

SQL> select HW() from dual;

HW()

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

Hello World 1 !!!

SQL> @java_objects.sql

OBJECT_NAME                    OBJECT_TYPE     STATUS

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

HelloWorld1                    JAVA CLASS      VALID
HelloWorld1                    JAVA SOURCE     VALID

SQL>

[oracle@sracanov-au2 ~]$ mkdir Test
[oracle@sracanov-au2 ~]$ cp HelloWorld1.java HelloWorld2.java
[oracle@sracanov-au2 ~]$ sed -i 's/1/2/g' HelloWorld2.java
[oracle@sracanov-au2 ~]$ sed '1i\package Test;' HelloWorld2.java > Test/HelloWorld2.java
[oracle@sracanov-au2 ~]$ rm HelloWorld2.java
[oracle@sracanov-au2 ~]$ javac -cp .:./Test Test/HelloWorld2.java
[oracle@sracanov-au2 ~]$ java -cp .:./Test Test/HelloWorld2
Hello World 2
[oracle@sracanov-au2 ~]$ loadjava -u scott/tiger -jarsasdbobjects -prependjarnames Test/HelloWorld2.java
[oracle@sracanov-au2 ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 7 14:07:30 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE OR REPLACE FUNCTION HW RETURN VARCHAR2 as LANGUAGE JAVA NAME 'Test.HelloWorld2.getHello() return String';

2 /

Function created.

SQL> select HW() from dual;

HW2()

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

Hello World 2 !!!

SQL> @java_objects.sql

OBJECT_NAME                    OBJECT_TYPE     STATUS

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

HelloWorld1                    JAVA CLASS      VALID
Test/HelloWorld2               JAVA CLASS      VALID
HelloWorld1                    JAVA SOURCE     VALID
Test/HelloWorld2               JAVA SOURCE     VALID

SQL> exec dbms_java.dropjava('Test/HelloWorld2');

PL/SQL procedure successfully completed.

SQL> @java_objects.sql

OBJECT_NAME                    OBJECT_TYPE     STATUS

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

HelloWorld1                    JAVA CLASS      VALID
HelloWorld1                    JAVA SOURCE     VALID

SQL> exec dbms_java.dropjava('HelloWorld1');

PL/SQL procedure successfully completed.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@sracanov-au2 ~]$ cat > Manifest.txt
Main-Class: Test.HelloWorld2
Quit
[oracle@sracanov-au2 ~]$ jar cvfm Test.jar Manifest.txt ./Test/*class
adding: META-INF/ (in=0) (out=0) (stored 0%)
adding: META-INF/MANIFEST.MF (in=29) (out=31) (deflated -6%)
adding: Test/HelloWorld2.class (in=594) (out=355) (deflated 40%)
Total:
------
(in = 623) (out = 738) (deflated -18%)
[oracle@sracanov-au2 ~]$ java -jar Test.jar
Hello World 2
[oracle@sracanov-au2 ~]$ loadjava -u scott/tiger Test.jar
[oracle@sracanov-au2 ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 8 10:02:16 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @java_objects

OBJECT_NAME                    OBJECT_TYPE     STATUS

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

Test/HelloWorld2               JAVA CLASS      INVALID
META-INF/MANIFEST.MF           JAVA RESOURCE   VALID

SQL> CREATE OR REPLACE FUNCTION HW RETURN VARCHAR2 as LANGUAGE JAVA NAME 'Test/HelloWorld2.getHello() return String';

2 /

Function created.

SQL> select HW() from dual;

HW()

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

Hello World 2 !!!

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@sracanov-au2 ~]$ dropjava -u scott/tiger Test.jar
[oracle@sracanov-au2 ~]$ loadjava -u scott/tiger -jarsasdbobjects -prependjarnames Test.jar
[oracle@sracanov-au2 ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 12 10:33:50 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @java_objects

OBJECT_NAME                    OBJECT_TYPE     STATUS

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

Test.jar///Test/HelloWorld2    JAVA CLASS      INVALID
/233d8437_MANIFESTMF           JAVA RESOURCE   VALID

SQL> CREATE OR REPLACE FUNCTION HW RETURN VARCHAR2 as LANGUAGE JAVA NAME 'Test.jar///Test/HelloWorld2.getHello() return String';

2 /

Function created.

SQL> select HW() from dual;

HW()

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

Hello World 2 !!!

SQL>

Note: I also used the parameters to load an individual class here which is not necessary. So '-jarsasdbobjects -prependjarnames' or 'loadjava' does not do any parsing on the file type.i.e.

[oracle@sracanov-au2 ~]$ cat > somefile
blah blah
Quit
[oracle@sracanov-au2 ~]$ loadjava -u scott/tiger -jarsasdbobjects -prependjarnames somefile
[oracle@sracanov-au2 ~]$

How to find the version of ADF libraries installed on WLS 11g

2011-03-22T09:48:00.008+11:00

If I log into the WLS console I can see the ADF runtime libraries are installed. i.e. adf.oracle.domain(...) However I don't know which version of ADF is installed.

From the command line, I can run the following to find the version details:

[oracle@sracanov-au4 ~]$ cd $ORACLE_HOME
[oracle@sracanov-au4 10.3.4.0]$ find . -name adf-share-support.jar -print
./oracle_common/modules/oracle.adf.share_11.1.1/adf-share-support.jar
[oracle@sracanov-au4 10.3.4.0]$ mkdir ~/check-adf
[oracle@sracanov-au4 10.3.4.0]$ cp ./oracle_common/modules/oracle.adf.share_11.1.1/adf-share-support.jar ~/check-adf
[oracle@sracanov-au4 10.3.4.0]$ cd ~/check-adf
[oracle@sracanov-au4 check-adf]$ jar -xf adf-share-support.jar
[oracle@sracanov-au4 check-adf]$ cat META-INF/MANIFEST.MF
Manifest-Version: 1.0
Ant-Version: Apache Ant 1.7.0RC1
Created-By: 17.0-b17 (Sun Microsystems Inc.)
Oracle-Version: 11.1.1.4.37.59.23
Oracle-Label: JDEVADF_11.1.1.4.0_GENERIC_101227.1736.5923
Oracle-Builder: Official Builder
Oracle-BuildSystem: Linux - java - 1.6.0_21-b51
Oracle-BuildTimestamp: 2010-12-27 08:47:35 -0800

Inserting large XMLTYPE

2011-01-24T10:34:00.007+11:00

When inserting large XMLTYPE, I am left with the following error:


java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column
 
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
 at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:754)
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
 at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:972)
 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1192)
 at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3415)
 at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3460)
 at InsertXML.insertXMLType(InsertXML.java:47)
 at InsertXML.main(InsertXML.java:93)

I found 2 ways to get around this problem.

1. XMLType are opaque types, so I should be using oracle.xdb.XMLType. So my code should look something like:


 XMLType xml;
 byte[] byteBuffer = xmlDetails.toString().getBytes();
 InputStream is = new ByteArrayInputStream(byteBuffer);
 xml = XMLType.createXML(connection,is); 
 pstmt.setObject(1,xml);

2. In Oracle JDBC 11.2.0.2. (Not implemented in 11.2.0.1) and using JDK 1.6 (utilising JDBC 4.0) we can use SQLXML Type - java.sql.SQLXML

http://download.oracle.com/docs/cd/E11882_01/java.112/e16548/jdbcvers.htm#BABGHBCC

So the code should look something like:


 SQLXML x = conn.createSQLXML();
 x.setString(xmlDetails.toString());
 pstmt.setSQLXML(1, x);

The second option here is preferred and moving forward.

Configuring OC4J HTTP Debug Logs.

2010-09-07T10:57:00.007+10:00

When I try the following http debug parameters in opmn for my oc4j instance I get no logs after restarting instance:

http://download-west.oracle.com/docs/cd/B32110_01/web.1013/b28950/sysprops.htm#CHDFCCBF

http://download-west.oracle.com/docs/cd/B32110_01/web.1013/b28950/sysprops.htm#CHDBDBII

This is because the logging level has not been set. So I now add "oracle.oc4j.trace.finest=com.evermind.server.http" and restart the instance and then I see the logging.

Increasing Xmx on Jdev

2010-06-29T10:19:00.008+10:00

I am trying to increase the Xmx value to 1500M on JDev. However when I do so, I get the "Error occurred during initialization of VM Could not reserve enough space for object heap" error. I am on Windows XP and have 4GB of RAM. And have plenty of available RAM.


[sracanov@sracanov-au D]$ jdev -verbose
savedArg0 = D:\Oracle\jdev\111130-prod\jdeveloper\jdev\bin\jdev.exe
VM to launch D:\Oracle\jdev\111130-prod\jdk160_18\jre\bin\client\jvm.dll
Restarting the launcher with the 64bit version
Reading exe header from jvm.dll, Machine=14c
JNI Option: -Xmx1500M
JNI Option: -Xverify:none
JNI Option: -XX:MaxPermSize=256M
JNI Option: -Xbootclasspath/p:../lib/lwawt.jar
JNI Option: -Dsun.awt.keepWorkingSetOnMinimize=true
JNI Option: -Dsun.java2d.noddraw=true
JNI Option: -XX:+HeapDumpOnOutOfMemoryError
JNI Option: -Doracle.jdeveloper.webservice.showAllOwsmPolicyTypes=false
JNI Option: -Doracle.jdeveloper.webservice.hidePropertyOverride=false
JNI Option: -Dsun.awt.disablegrab=true
JNI Option: -Dide.startingArg0=D:\Oracle\jdev\111130-prod\jdeveloper\jdev\bin\jdev.exe
JNI Option: -Dide.startingArg1=-verbose
JNI Option: -Dide.launcherProcessId=2516
JNI Option: -Dide.config_pathname=D:\Oracle\jdev\111130-prod\jdeveloper\jdev\bin\jdev.conf
JNI Option: -Dide.startingcwd=D:\Oracle\jdev\111130-prod\jdeveloper\jdev\bin
JNI Option: -Dide.shell.enableFileTypeAssociation=D:\Oracle\jdev\111130-prod\jdeveloper\jdev\bin\jdev.exe
JNI Option: -Dide.pref.dir.base=C:\Documents and Settings\sracanov\Application Data
JNI Option: -Dide.work.dir.base=D:\My Contents
JNI Option: -Djava.class.path=..\..\ide\lib\ide-boot.jar
JVM path is D:\Oracle\jdev\111130-prod\jdk160_18\jre\bin\client\jvm.dll
Error occurred during initialization of VM
Could not reserve enough space for object heap

Sun JDK needs a contiguous address space to allocate memory.
On the other hand, JRockit does not need a contiguous address space.

So, if I use JRockit as JDK, and then run JDeveloper with -Xmx1500M it works.


[sracanov@sracanov-au D]$ jdev -verbose
savedArg0 = D:\Oracle\jdev\111130-prod\jdeveloper\jdev\bin\jdev.exe
VM to launch D:\Oracle\appserver\owls\10.3.1.0\jrockit_160_05_R27.6.2-20\jre\bin\jrockit\jvm.dll
Restarting the launcher with the 64bit version
Reading exe header from jvm.dll, Machine=14c
JNI Option: -Xmx1500M
JNI Option: -Xverify:none
JNI Option: -XX:MaxPermSize=256M
JNI Option: -Xbootclasspath/p:../lib/lwawt.jar
JNI Option: -Dsun.awt.keepWorkingSetOnMinimize=true
JNI Option: -Dsun.java2d.noddraw=true
JNI Option: -XX:+HeapDumpOnOutOfMemoryError
JNI Option: -Doracle.jdeveloper.webservice.showAllOwsmPolicyTypes=false
JNI Option: -Doracle.jdeveloper.webservice.hidePropertyOverride=false
JNI Option: -Dsun.awt.disablegrab=true
JNI Option: -Dide.startingArg0=D:\Oracle\jdev\111130-prod\jdeveloper\jdev\bin\jdev.exe
JNI Option: -Dide.startingArg1=-verbose
JNI Option: -Dide.launcherProcessId=2400
JNI Option: -Dide.config_pathname=D:\Oracle\jdev\111130-prod\jdeveloper\jdev\bin\jdev.conf
JNI Option: -Dide.startingcwd=D:\Oracle\jdev\111130-prod\jdeveloper\jdev\bin
JNI Option: -Dide.shell.enableFileTypeAssociation=D:\Oracle\jdev\111130-prod\jdeveloper\jdev\bin\jdev.exe
JNI Option: -Dide.pref.dir.base=C:\Documents and Settings\sracanov\Application Data
JNI Option: -Dide.work.dir.base=D:\My Contents
JNI Option: -Djava.class.path=..\..\ide\lib\ide-boot.jar
JVM path is D:\Oracle\appserver\owls\10.3.1.0\jrockit_160_05_R27.6.2-20\jre\bin\jrockit\jvm.dll
[WARN ] -XX:MaxPermSize=256M is not a valid VM option. Ignoring
[WARN ] -XX:+HeapDumpOnOutOfMemoryError is not a valid VM option. Ignoring
log4j:WARN No appenders could be found for logger (org.apache.commons.httpclient.HttpClient).
log4j:WARN Please initialize the log4j system properly.

Relevant Links:

http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=4358809
http://java.sun.com/docs/hotspot/HotSpotFAQ.html#gc_heap_32bit
http://blogs.oracle.com/jrockit/2008/09/how_to_get_almost_3_gb_heap_on_windows.html

Returning values from Nested Tables using JDBC

2010-05-28T13:25:00.021+10:00

When running a select statement in sqlplus on a nested tables we get the results returned. However the same can not be said when running the same statement through JDBC. He is an example of returning values from a nested table with JDBC.

1. Here's how the sql script looks like to create the type and tables.


drop table person;
drop type nested_addresses;
drop type address;

create or replace type address as object(
 street varchar2(50),
 city varchar2(20),
 postcode varchar2(8),
 state varchar(8))
/

create or replace type nested_addresses as table of address
/

create table person (
 firstname varchar(20),
 lastname varchar(20),
 address_list nested_addresses
 ) nested table address_list store as person_addresses
/

insert into person values ('Fred','Flinstone', nested_addresses(address('345 Cave Stone Road', 'Bedrock', 'BR345','BR'),address('1313 Cobblestone Way', 'Bedville', 'BV1313','BV')))
/

commit
/

2. If I now run the select statement we can see the results:


scott@orcl> set head on feedback on
scott@orcl> set pages    999
scott@orcl> set linesize 120
scott@orcl> col firsname format a20
scott@orcl> col lastname format a20
scott@orcl> col address_list format a50
scott@orcl> select firstname, lastname, address_list from person;

FIRSTNAME            LASTNAME             ADDRESS_LIST(STREET, CITY, POSTCODE, STATE)
-------------------- -------------------- --------------------------------------------------
Fred                 Flinstone            NESTED_ADDRESSES(ADDRESS('345 Cave Stone Road', 'B
                                          edrock', 'BR345', 'BR'), ADDRESS('1313 Cobblestone
                                           Way', 'Bedville', 'BV1313', 'BV'))


1 row selected.

3. However if I now run the same select query, through a java client using JDBC we will get null values returned.


import java.sql.*;

public class Person {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        // connection parameters
        String dbUrl = "jdbc:oracle:thin:@sracanov-au2.au.oracle.com:1522:orcl";
        String dbUser = "scott";
        String dbPass = "tiger";

        // load the driver
        Class.forName("oracle.jdbc.driver.OracleDriver");

        // create connection
        Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPass);

        // query the table
        Statement s = con.createStatement();
        String query = "select firstname, lastname, address_list from person";
        ResultSet res = s.executeQuery(query);

        // look at the result set
        res.next();
        System.out.println("First Name: " + res.getString(1));
        System.out.println("Last Name: " + res.getString(2));
        System.out.println("Address: " + res.getString(3));

        // close connection
        con.close();
    }
}


[sracanov@sracanov-au C]$ java -version
java version "1.6.0_16"
Java(TM) SE Runtime Environment (build 1.6.0_16-b01)
Java HotSpot(TM) Client VM (build 14.2-b01, mixed mode, sharing)

[sracanov@sracanov-au C]$ javac -cp .;"D:\My Contents\My Software\Oracle\JDBC\Drivers\11.2.0.1.0\ojdbc6.jar" Person.java

[sracanov@sracanov-au C]$ java -cp .;"D:\My Contents\My Software\Oracle\JDBC\Drivers\11.2.0.1.0\ojdbc6.jar" Person
First Name: Fred
Last Name: Flinstone
Address: null

This is because we need to get a collection of objects and handle them in java by returning arrays elements. The correct way to return this is:


import java.sql.*;

public class Person1 {
        public static void main(String[] args) throws SQLException,
                        ClassNotFoundException {

                // connection parameters
                String dbUrl = "jdbc:oracle:thin:@sracanov-au2.au.oracle.com:1522:orcl";
                String dbUser = "scott";
                String dbPass = "tiger";

                // load the driver
                Class.forName("oracle.jdbc.driver.OracleDriver");

                // create connection
                Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPass);

                // query the table
                Statement s = con.createStatement();
                String query = "select firstname, lastname, address_list from person";
                ResultSet res = s.executeQuery(query);

                // look at the result set
                while (res.next()) {
                        int addresscounter=1;
                        System.out.println("First Name: " + res.getString(1));
                        System.out.println("Last Name: " + res.getString(2));
                        Array array = res.getArray(3);
                        Object[] address_list = (Object[]) array.getArray();
                        for (int i = 0; i < address_list.length; i++) {
                           Struct address = (Struct) address_list[i];
                           Object[] attrib = address.getAttributes();
                           for (int j = 0; j < attrib.length; j++) {
                              System.out.println("Address " + addresscounter + ": " + attrib[j]);
                           }
                           addresscounter++;
                        }
                }

                // close connection
                con.close();
        }
}


[sracanov@sracanov-au C]$ javac -cp .;"D:\My Contents\My Software\Oracle\JDBC\Drivers\11.2.0.1.0\ojdbc6.jar" Person1.java

[sracanov@sracanov-au C]$ java -cp .;"D:\My Contents\My Software\Oracle\JDBC\Drivers\11.2.0.1.0\ojdbc6.jar" Person1
First Name: Fred
Last Name: Flinstone
Address 1: 345 Cave Stone Road
Address 1: Bedrock
Address 1: BR345
Address 1: BR
Address 2: 1313 Cobblestone Way
Address 2: Bedville
Address 2: BV1313
Address 2: BV

http://download.oracle.com/docs/cd/E11882_01/java.112/e10589/oraarr.htm#i1059642

Tracing the Bind Values

2010-05-17T11:52:00.015+10:00

Created a simple example of how you can isolate and trace the bind variables for the records inserted and understand what to look for in the trace file.

The table here consists of 3 columns. A varchar2, number and timestamp.

The jdev 11113 workspace for this example is here.

When running the application, this following results are displayed:


=============
Java Vendor is ... Sun Microsystems Inc.

Java Version is  1.6.0_18

=============
Database Product Name is ... Oracle

Database Product Version is  Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

=============
JDBC Driver Name is ........ Oracle JDBC driver

JDBC Driver Version is ..... 11.1.0.7.0-Production

=============
Dropping/Creating table...

 execSQL() returning: 0 for SQL=DROP TABLE SCOTT.BIND_TEST

 execSQL() returning: 0 for SQL=CREATE TABLE SCOTT.BIND_TEST (VARCHAR_FIELD VARCHAR2(50), NO_FIELD NUMBER(2), TIMESTAMP_FIELD TIMESTAMP)
Preparing stmt: INSERT INTO SCOTT.BIND_TEST (VARCHAR_FIELD, NO_FIELD, TIMESTAMP_FIELD) VALUES (?, ?, ?)
Inserting ...
Inserting Complete...
DISPLAY...
test_string_0.8433509446104999 0 2010-05-17 11:35:46.328
test_string_0.32801143775147246 1 2010-05-17 11:35:46.5
test_string_0.6936132181788695 2 2010-05-17 11:35:46.515
test_string_0.4458248593661084 3 2010-05-17 11:35:46.515
test_string_0.8349244238371425 4 2010-05-17 11:35:46.515
test_string_0.014215120763115707 5 2010-05-17 11:35:46.515
test_string_0.7895349187550287 6 2010-05-17 11:35:46.515
test_string_0.33693148690955965 7 2010-05-17 11:35:46.531
test_string_0.60803516417148 8 2010-05-17 11:35:46.531
test_string_0.5201490476224547 9 2010-05-17 11:35:46.531

Show Trace File...
TRACE FILE NAME : /u01/programs/oracle/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_16541.trc

Open the trace file I can see the following:


...
PARSING IN CURSOR #8 len=90 dep=0 uid=5 oct=2 lid=5 tim=1244198234987514 hv=896770016 ad='2b9e3258'
INSERT INTO SCOTT.BIND_TEST (VARCHAR_FIELD, NO_FIELD, TIMESTAMP_FIELD) VALUES (:1, :2, :3)
END OF STMT
PARSE #8:c=2000,e=2579,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1244198234987507
=====================
PARSING IN CURSOR #12 len=227 dep=1 uid=0 oct=3 lid=0 tim=1244198235054488 hv=2190775527 ad='2f56e994'
select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled  from obj$ o,user$ u,trigger$ t  where t.baseobject=:1 and t.obj#=o.obj# and o.owner#=u.user#  and bitand(property,16)=0 and bitand(property,8)=0  order by o.obj#
END OF STMT
PARSE #12:c=3000,e=2745,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1244198235054480
BINDS #12:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b7234fe8  bln=22  avl=04  flg=05
  value=150767
EXEC #12:c=5999,e=73474,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1244198235128125
FETCH #12:c=0,e=59,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1244198235128225
STAT #12 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=1 pr=0 pw=0 time=103 us)'
STAT #12 id=2 cnt=0 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=1 pr=0 pw=0 time=77 us)'
STAT #12 id=3 cnt=0 pid=2 pos=1 obj=0 op='NESTED LOOPS  (cr=1 pr=0 pw=0 time=69 us)'
STAT #12 id=4 cnt=0 pid=3 pos=1 obj=81 op='TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=1 pr=0 pw=0 time=64 us)'
STAT #12 id=5 cnt=0 pid=4 pos=1 obj=125 op='INDEX RANGE SCAN I_TRIGGER1 (cr=1 pr=0 pw=0 time=35 us)'
STAT #12 id=6 cnt=0 pid=3 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #12 id=7 cnt=0 pid=6 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #12 id=8 cnt=0 pid=2 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #12 id=9 cnt=0 pid=8 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us)'
BINDS #8:
kkscoacd
 Bind#0
  oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000010 frm=01 csi=31 siz=68 off=0
  kxsbbbfp=b722aaec  bln=32  avl=30  flg=05
  value="test_string_0.8433509446104999"
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=32
  kxsbbbfp=b722ab0c  bln=22  avl=01  flg=01
  value=0
 Bind#2
  oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=56
  kxsbbbfp=b722ab24  bln=11  avl=11  flg=01
  value=
Dump of memory from 0xB722AB24 to 0xB722AB2F
B722AB20          11056E78 132F240C 0000E28C      [xn...$/.....]
EXEC #8:c=12998,e=101172,p=0,cr=2,cu=21,mis=1,r=1,dep=0,og=1,tim=1244198235152633
XCTEND rlbk=0, rd_only=0
...

Here you can see the first insert query references "CURSOR #8" that will bind to "BINDS #8:" which shows the binding values:

value="test_string_0.8433509446104999"
value=0
value=

Timestamp is in hex format. You will to convert that yourself to find the value.

http://wiki.oracle.com/page/Structure+of+an+Extended+SQL+Trace+file

Creating a Custom ADF .JSPX Login Page

2010-04-22T09:22:00.005+10:00

Note for myself to create a simple custom ADF login page with jspx.

Create a new workspace by following the example shown here:

http://www.oracle.com/technology/products/jdev/11/cuecards111/adf_set_29/ccset29_ALL.html

Once completing this, then go through the follow link http://download.oracle.com/docs/cd/E15523_01/web.1111/b31974/adding_security.htm#BABDEICH to create the custom jspx page and backing bean to login into the application.

The finished login will look something like this.

Download completed workspace from here.

Using Oracle 11.2g Data Encryption and Integrity Connection Pool in OAS10g

2010-04-20T12:03:00.055+10:00

I haven't had the chance to write something for a while now. I was recently working on new feature with oracle advanced security data encryption and integrity using a JDBC connection pool. So I'll write my steps up of how I went about configuring this in OAS 10g.

This setup allows for data encryption and integrity without the overhead of SSL.

I used the following products to configure the steps detailed here:

* Oracle Application Server 10.1.3.2.0
* JDev 10.1.3.4.0
* JDBC 11.2.0.1.0
* Oracle Database 11.2

I am referencing these documentation for further details:

Oracle® Database Advanced Security Administrator's Guide 11g Release 2 (11.2)
Configuring Network Data Encryption and Integrity for Oracle Servers and Clients
http://download.oracle.com/docs/cd/E11882_01/network.112/e10746/asoconfg.htm#insertedID0

Oracle® Database JDBC Developer's Guide, 11g Release 2 (11.2)
Support for Data Encryption and Integrity
http://download.oracle.com/docs/cd/E11882_01/java.112/e10589/clntsec.htm#EHAFHEIG

Here are my complete steps:

1. First follow the steps to configure the database: - http://download.oracle.com/docs/cd/E11882_01/network.112/e10746/asoconfg.htm#i1006517

Depending on your selection, your 'sqlnet.ora' file should look something like this when finished:

# sqlnet.ora Network Configuration File: /home/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER= (MD5)

SQLNET.ENCRYPTION_SERVER = required

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.CRYPTO_SEED = 'example123456'

SQLNET.ENCRYPTION_TYPES_SERVER= (RC4_256)

ADR_BASE = /home/u01/app/oracle

SQLNET.CRYPTO_CHECKSUM_SERVER = required

Once the steps have been completed, restart the listener and wait for the service to come back up.

[oracle@beast admin]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 20-APR-2010 06:39:08

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1523)))
The command completed successfully
[oracle@beast admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 20-APR-2010 06:39:11

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /home/u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /home/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /home/u01/app/oracle/diag/tnslsnr/beast/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=beast.au.oracle.com)(PORT=1523)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1523)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                20-APR-2010 06:39:11
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /home/u01/app/oracle/diag/tnslsnr/beast/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=beast.au.oracle.com)(PORT=1523)))
The listener supports no services
The command completed successfully
[oracle@beast admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 20-APR-2010 06:39:17

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1523)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                20-APR-2010 06:39:11
Uptime                    0 days 0 hr. 0 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /home/u01/app/oracle/diag/tnslsnr/beast/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=beast.au.oracle.com)(PORT=1523)))
The listener supports no services
The command completed successfully
[oracle@beast admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 20-APR-2010 06:40:24

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1523)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                20-APR-2010 06:39:11
Uptime                    0 days 0 hr. 1 min. 12 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /home/u01/app/oracle/diag/tnslsnr/beast/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=beast.au.oracle.com)(PORT=1523)))
Services Summary...
Service "linux11gr2" has 1 instance(s).
 Instance "linux11gr2", status READY, has 1 handler(s) for this service...
Service "linux11gr2XDB" has 1 instance(s).
 Instance "linux11gr2", status READY, has 1 handler(s) for this service...
The command completed successfully

2. Download JDBC driver 11.2.0.1.0. You can get the driver from here. http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
Since OAS comes with JDK 1.5_06 by default, I'll used the 'ojdbc5.jar'.

3. In My Oracle Support, review document 420303.1 - How to Use The Latest Thin JDBC Driver Across All Applications For a 10.1.3.x OAS Container

Based on that document, here are the steps how I upgraded the driver and verified it updated correctly:

4. I created a new instance called JDBC112010

> createinstance -instanceName JDBC112010

5. I started the new instance and checked to make sure it was up.


> opmnctl startproc process-type=JDBC112010
opmnctl: starting opmn managed processes...

> opmnctl status

Processes in Instance: web.sracanov-au.au.oracle.com
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status
---------------------------------+--------------------+---------+---------
OC4JGroup:default_group          | OC4J:JDBC112010  |    5124 | Alive
...

6. I then deploy the following application 'drvtest.ear'

7. I accessed and ran the application from URL - 'http://<server>:<port>/drvtest/dbdetails.jsp'

I completed my database details and clicked on the 'Submit' button.

The current JDBC details are displayed:

=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 10.1.0.5.0
JDBC Driver Major Version is 10
JDBC Driver Minor Version is 1
=============

8. I then continued to followed Doc ID 420303.1 to upgrade the JDBC Driver.

In point 7 of the document, I entered

Shared Library Name : oracle.jdbc
Shared Library Version : 11.2.0.1.0

NOTE: The name must be 'oracle.jdbc'

9. In point 15 of the document, I first created a backup of 'system-applications.xml'. I then entered

NOTE: The name must be 'oracle.jdbc'. The version details must match version enter in step 8.

10. After restarting the instance, I then accessed the test application again. 'http://<server>:<port>/drvtest/dbdetails.jsp'

I completed my database details and clicked on the 'Submit' button.

This is the results I received

=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.1.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============

I can now see and confirm the JDBC Driver upgrade worked successfully.

11. Then created the datasource. You can do this following document 456270.1 - Creating a datasource in Application Server Control (ASC)/Enterprise Manager (EM) for 10.1.3.X

Here is how my connection pool looks like in 'data-sources.xml':

NOTE: You must use a managed datasource with oracle.jdbc.OracleDriver factory class. Can not use native data source. The pool properties there do not implement the encryption properties from oracle.jdbc.OracleConnection interface.

The XXX_TYPES should match the setting as configure in the database. Step 1

12. Download and deploy this application EAR file 'drvtest2.ear' which you will then connect to this connection pool.

Access the URL 'http://<server>:<port>/drvtest2/dbdetails.jsp' and enter the JNDI of the pool and click 'Submit'.

Your results should look something like:

System Information

JDK Details

=============
JDK Vendor is ... Sun Microsystems Inc.
JDK Version is ... 1.5.0_06
=============

JDBC Driver Details

=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.1.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============

Database Details

=============
Database Product Name is ... Oracle
Database Product Version is Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Secure Algorithm Details

=============
Encryption algorithm is: RC4_256
Data Integrity algorithm is: MD5

Now I can see the encryption & data integrity algorithm used in this connection. You can further download a tool like wireshark - http://www.wireshark.org/ and run it on the application server to monitor this connection pool and confirm the message are encrypted and unreadable.

You can download the JDev workspace from here

11g Database Webservice Callout Steps

2009-12-17T10:02:00.026+11:00

11g Database Webservice Callout Steps

I installed the 11g R2 database last week and though I'd go through notes 469588.1, 428775.1 and 841183.1 on My Oracle Support and jot down my steps for configuring UTL_DBWS and JPublisher to use a java webservice proxy from the database to call external webservices.

This is what I done:

1. Check to see if the database webservice utilities are installed in sys schema, check the status of the java classes and set the OJVM pool size.

a. Check and see if callout utilities are installed:

[oracle@sracanov-au2 dbhome_1]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 14 08:04:09 2009

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn sys/ as sysdba
Enter password:
Connected.

SQL> desc sys.utl_dbws
ERROR:
ORA-04043: object sys.utl_dbws does not exist

b. Check the status of the java classes:

SQL> SELECT owner, status, count(*) FROM DBA_OBJECTS WHERE OBJECT_TYPE='JAVA CLASS' GROUP BY owner, status;

OWNER                          STATUS    COUNT(*)
------------------------------ ------- ----------
MDSYS                          VALID          531
SYS                            VALID        20444
EXFSYS                         VALID           47
ORDSYS                         VALID         1898

c. Set the OJVM pool size. I'm setting the size as per note 469588.1. Although the online documentation suggest java_pool_size is set to at least 96 MB and shared_pool_size is set to at least 80 MB. http://download.oracle.com/docs/cd/E11882_01/java.112/e10587/intro.htm#BHCBACII. If this is not set, then loading and resolving java classes (jars) will occur errors:

SQL> show parameter SHARED_POOL_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 0
SQL> show parameter JAVA_POOL_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_pool_size                       big integer 0
SQL> alter system set SHARED_POOL_SIZE=132M scope=both;

System altered.

SQL> alter system set JAVA_POOL_SIZE=80M scope=both;

System altered.

SQL> show parameter SHARED_POOL_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 132M
SQL> show parameter JAVA_POOL_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_pool_size                       big integer 80M
SQL>

2. Download JPublisher & Database Web Services Callout Utilities

Webpage:
http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html
Download - JPublisher 10g Release 10.2:
http://download.oracle.com/otn/utilities_drivers/jdbc/10201/jpub_102.zip

Webpage:
http://www.oracle.com/technetwork/database/enterprise-edition/index-097123.html
Download - 10.1.3.1 Callout Utility for 10g and 11g RDBMS (ZIP, ~13MB):
http://download.oracle.com/technology/sample_code/tech/java/jsp/dbws-callout-utility-10131.zip

3. Setup the OS environment:

[oracle@sracanov-au2 ~]$ export ORACLE_HOME=/u01/programs/oracle/product/11.2.0/dbhome_1
[oracle@sracanov-au2 ~]$ export JAVA_HOME=$ORACLE_HOME/jdk
[oracle@sracanov-au2 ~]$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/sqlj/bin:$JAVA_HOME/bin:$PATH
[oracle@sracanov-au2 ~]$ export CLASSPATH=$ORACLE_HOME/sqlj/lib/translator.jar:$ORACLE_HOME/sqlj/lib/runtime12.jar:$ORACLE_HOME/sqlj/lib/dbwsa.jar:$ORACLE_HOME/javavm/lib/aurora.zip:$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/jdk/lib/dt.jar:$ORACLE_HOME/jdk/lib/tools.jar:$ORACLE_HOME/jlib/jssl-1_1.jar:$ORACLE_HOME/jlib/orai18n.jar:$ORACLE_HOME/rdbms/jlib/xdb.jar:$ORACLE_HOME/lib/xsu12.jar:$ORACLE_HOME/jlib/jndi.jar:$ORACLE_HOME/rdbms/jlib/aqapi.jar:$ORACLE_HOME/rdbms/jlib/jmscommon.jar:$ORACLE_HOME/lib/xmlparserv2.jar

I extract the downloaded files in the next setup. You can set the classpath now or later. Doing it now for simplicity.

4. Extract the downloaded files:

a. Take a backup of sqlj folder first:

[oracle@sracanov-au2 ~]$ mv $ORACLE_HOME/sqlj $ORACLE_HOME/sqlj.org

b. Extract Jpub:

[oracle@sracanov-au2 ~]$ unzip jpub_102.zip -d $ORACLE_HOME
Archive:  jpub_102.zip
   creating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/
   creating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/sqljutl.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/translator.jar
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/runtime12.jar
   creating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/bin/
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/bin/README.txt
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/bin/jpub.c
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/bin/jpub
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/bin/jpub.exe
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/README.txt
   creating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/
   creating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/Booleans.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/Indexby.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/Inherit.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/MyRationalC.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/MyRationalO.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/MyRationalO8i.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/PlsqlType.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/README.txt
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/Rational.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestBooleans.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestCallin.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestIndexby.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestInh.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestInstall.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestInstallCreateTable.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestInstallJDBC.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestQuery.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestMyRationalC.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestMyRationalO.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestMyRationalO8i.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestPlsqlType.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestRationalO.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TestRationalP.java
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/WrDbmsUtil.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/connect.properties
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/jpub.properties
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/demo/jpub/TemperatureService.wsdl
   creating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/doc/
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/doc/faq.html
[oracle@sracanov-au2 ~]$

c. Extract Callout Utilities:

[oracle@sracanov-au2 ~]$ unzip dbws-callout-utility-10131.zip sqlj\* -d $ORACLE_HOME
Archive:  dbws-callout-utility-10131.zip
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/utl_dbws_decl.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/utl_dbws_body.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/dbwsclientws.jar
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/dbwsclientdb102.jar
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/dbwsclientdb11.jar
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/dbwsa.jar
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/lib/dbwsclientdb101.jar
[oracle@sracanov-au2 ~]$ unzip dbws-callout-utility-10131.zip samples\* -d $ORACLE_HOME/sqlj
Archive:  dbws-callout-utility-10131.zip
   creating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/samples/
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/samples/javacallout.wsdl
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/samples/test-plsql-dii.sql
  inflating: /u01/programs/oracle/product/11.2.0/dbhome_1/sqlj/samples/javacallout.ear
[oracle@sracanov-au2 ~]$ unzip dbws-callout-utility-10131.zip *.htm -d sqlj/
Archive:  dbws-callout-utility-10131.zip
  inflating: sqlj/10.htm
  inflating: sqlj/11.htm
  inflating: sqlj/README.htm

d. Looking at the extracted files, there is no dbwsclient.jar.

http://download.oracle.com/docs/cd/E11882_01/java.112/e10587/intro.htm#BHCBACII

There is however dbwsclientws.jar. dbwsclientws.jar is the same as dbwsclient.jar. Jpub looks for this file when you run the jpub command. To avoid the error: 'Error loading dbwsclient.jar, please check the database trace file and make sure dbwsclient.jar exists in ORACLE_HOME/sqlj/lib.' when running the jpub command, I'll just make a copy of this file:

[oracle@sracanov-au2 lib]$ ls -al | grep dbwsc
-rw-r--r--  1 oracle oinstall  737386 Jun 18  2008 dbwsclientdb101.jar
-rw-r--r--  1 oracle oinstall  208486 Jun 18  2008 dbwsclientdb102.jar
-rw-r--r--  1 oracle oinstall  214156 Jun 18  2008 dbwsclientdb11.jar
-rw-r--r--  1 oracle oinstall 6716174 Aug  1  2008 dbwsclientws.jar
[oracle@sracanov-au2 lib]$ cp dbwsclientws.jar dbwsclient.jar
[oracle@sracanov-au2 lib]$ ls -al | grep dbwsc
-rw-r--r--  1 oracle oinstall  737386 Jun 18  2008 dbwsclientdb101.jar
-rw-r--r--  1 oracle oinstall  208486 Jun 18  2008 dbwsclientdb102.jar
-rw-r--r--  1 oracle oinstall  214156 Jun 18  2008 dbwsclientdb11.jar
-rw-r--r--  1 oracle oinstall 6716174 Dec 16 06:42 dbwsclient.jar
-rw-r--r--  1 oracle oinstall 6716174 Aug  1  2008 dbwsclientws.jar
[oracle@sracanov-au2 lib]$

I am not sure why they packaged it like this. It seems it was packaged dbwsclient.jar in the previous download version i.e. 10.1.3.0 Callout Utility for 10g RDBMS (ZIP, 6.89MB).

Note that I did not find this step crucial. I could still run jpub and each time I will get that error above 'Error loading dbwsclient.jar,..... It could still proceed without it and it worked fine in my example. However doing this I avoid seeing that error all the time when I run Jpub.

5. Set the execute permission on jpub:

[oracle@sracanov-au2 ~]$ cd $ORACLE_HOME/sqlj/bin
[oracle@sracanov-au2 bin]$ chmod +x jpub

6. I'm using the scott schema in this example. Check the schema has CONNECT, RESOURCE and CREATE PUBLIC SYNONYM grants. This is the minimum permission required. If not, allow for it.

SQL> SELECT grantee, granted_role FROM dba_role_privs where GRANTEE = 'SCOTT';

GRANTEE                        GRANTED_ROLE
------------------------------ ------------------------------
SCOTT                          RESOURCE
SCOTT                          CONNECT

SQL> select * from dba_sys_privs where GRANTEE='SCOTT';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
SCOTT                          UNLIMITED TABLESPACE                     NO

SQL> grant CREATE PUBLIC SYNONYM to scott;
SQL> select * from dba_sys_privs where GRANTEE='SCOTT';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
SCOTT                          UNLIMITED TABLESPACE                     NO
SCOTT                          CREATE PUBLIC SYNONYM                    NO

7. Load the database webservice callout utilities into the scott schema. The jar's are found in $ORACLE_HOME/sqlj/lib folder.

[oracle@sracanov-au2 bin]$ cd $ORACLE_HOME/sqlj/lib
[oracle@sracanov-au2 lib]$ loadjava -u scott/tiger -r -v -f -s -grant public -genmissing dbwsclientws.jar dbwsclientdb11.jar >& loadjava.txt

Check the log and see if it completed successfully. It should look something like:

[oracle@sracanov-au2 lib]$ tail -8 loadjava.txt
Classes Loaded: 4061
Resources Loaded: 81
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 63
Classes skipped: 0
Synonyms Created: 0
Errors: 0
[oracle@sracanov-au2 lib]$

(The values are likely to be different. This depends the on system environment and what already installed).

8. Once the database webservice call out classes have been loaded, I need to run the PL/SQL wrappers that will call theses classes. The scripts are found in $ORACLE_HOME/sqlj/lib folder. Since I installed the jar in scott, scott needs to run these scripts:

[oracle@sracanov-au2 lib]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 15 11:08:50 2009

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @utl_dbws_decl.sql

Package created.

SQL> @utl_dbws_body.sql

Package body created.

Grant succeeded.

SQL> desc utl_dbws
PROCEDURE ADD_PARAMETER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CALL_HANDLE                    NUMBER                  IN
 XML_NAME                       VARCHAR2                IN
 Q_NAME                         VARCHAR2(4096)          IN
...
...

9. Grant the following classes from the SYS schema to scott. (or whichever schema your using. Ensure the schema name is in capital letters. i.e. 'SCOTT'):

SQL> conn /as sysdba
Connected.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission', 'shutdownHooks', '' );
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.logging.LoggingPermission', 'control', '' );
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxySet','write');
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxyHost', 'write');
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxyPort', 'write');
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','getClassLoader','');
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.net.SocketPermission','*','connect,resolve');
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','*','read,write');
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','setFactory','');
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','');

EXAMPLE 1:
==========
10. Using the example code from note 841183.1, I will now use the UTL_DBWS package loaded in scott to call the webservice. The PL/SQL is created as follows:

a. Here is the PL/SQL code using UTL_DBWS to call an external webservice:

CREATE OR REPLACE FUNCTION celciusToFahrenheit(temperature NUMBER) RETURN VARCHAR2 AS

service_ scott.utl_dbws.SERVICE;
call_ scott.utl_dbws.CALL;
service_qname scott.utl_dbws.QNAME;
port_qname scott.utl_dbws.QNAME;
response sys.XMLTYPE;
request sys.XMLTYPE;

BEGIN
scott.utl_dbws.set_http_proxy('myproxy.com:80');
service_qname := scott.utl_dbws.to_qname(null, 'CelciusToFahrenheit');
service_      := scott.utl_dbws.create_service(service_qname);
call_         := scott.utl_dbws.create_call(service_);
scott.utl_dbws.set_target_endpoint_address(call_, 'http://webservices.daehosting.com/services/TemperatureConversions.wso');
scott.utl_dbws.set_property( call_, 'OPERATION_STYLE', 'document');
request       := sys.XMLTYPE(''||temperature||'');
response      := scott.utl_dbws.invoke(call_, request);
return response.extract('//CelciusToFahrenheitResult/child::text()', 'xmlns="http://webservices.daehosting.com/temperature"').getstringval();
END;
/
show errors;

b. I have changed line 11 with my proxy server details. If your environment does not use a proxy server leave this line out.
c. Since the database webservice utilities have been installed in 'scott', I have changed all the 'sys' references to webservice utilities to 'scott'
d. Ensure you can access the webservice used in this example: http://webservices.daehosting.com/services/TemperatureConversions.wso
e: Now run the example:

SQL> @celciusToFahrenheit.sql

Function created.

No errors.
SQL> SELECT celciusToFahrenheit(30) from dual;

CELCIUSTOFAHRENHEIT(30)
--------------------------------------------------------------------------------
86

SQL>

EXAMPLE 2:
==========
11. Now using the same webservice, I will use jpub to create and load the java webservice proxy classes to the database.

a. Run the jpub to create and load your java webservice client proxy. Here is the command used:

jpub -u scott/tiger -sysuser sys/welcome1 -proxywsdl=http://webservices.daehosting.com/services/TemperatureConversions.wso?WSDL -endpoint=http://webservices.daehosting.com/services/TemperatureConversions.wso -httpproxy=myproxy.com:80

Note I have specified my proxy again as the last parameter. If your environment does not use a proxy server leave this out.

[oracle@sracanov-au2 dbhome_1]$ cd sqlj
[oracle@sracanov-au2 sqlj]$ mkdir example2
[oracle@sracanov-au2 sqlj]$ cd example2/
[oracle@sracanov-au2 example2]$ jpub -u scott/tiger -sysuser sys/welcome1 -proxywsdl=http://webservices.daehosting.com/services/TemperatureConversions.wso?WSDL -endpoint=http://webservices.daehosting.com/services/TemperatureConversions.wso -httpproxy=myproxy.com:80
tmp/src/genproxy/TemperatureConversionsSoapClientJPub.java
plsql_wrapper.sql
plsql_dropper.sql
plsql_grant.sql
plsql_revoke.sql
Executing plsql_dropper.sql
Executing plsql_wrapper.sql
Executing plsql_grant.sql
Loading plsql_proxy.jar

[oracle@sracanov-au2 example2]$

Note that when using jpub I can avoid coding PL/SQL code. The PL/SQL files can be found in the current local directory where jpub is run from. They have been executed while running the jpub command.

[oracle@sracanov-au2 example2]$ ls -al
total 88
drwxr-xr-x  3 oracle oinstall  4096 Dec 16 07:27 .
drwxr-xr-x  8 oracle oinstall  4096 Dec 15 10:43 ..
-rw-r--r--  1 oracle oinstall    95 Dec 16 07:27 plsql_dropper.sql
-rw-r--r--  1 oracle oinstall   548 Dec 16 07:27 plsql_grant.sql
-rw-r--r--  1 oracle oinstall 26546 Dec 16 07:27 plsql_proxy.jar
-rw-r--r--  1 oracle oinstall   404 Dec 16 07:27 plsql_revoke.sql
-rw-r--r--  1 oracle oinstall  3261 Dec 16 07:27 plsql_wrapper.sql
drwxr-xr-x  4 oracle oinstall  4096 Dec 16 06:43 tmp

b. Now call the web Service as in sqlplus.

SQL> conn scott/tiger
Connected.
SQL> select jpub_plsql_wrapper.celciusToFahrenheit(30) as "Celcius To Fahrenheit(30)" from dual;

Celcius To Fahrenheit(30)
-------------------------
                       86

SQL>

Running jpub has created the java webservice proxy for all the methods available from the webservice:

SQL> select jpub_plsql_wrapper.fahrenheitToCelcius(86) as "Fahrenheit To Celcius(86)" from dual;

Fahrenheit To Celcius(86)
-------------------------
                       30

SQL>

For any further or clarification, see the following documentation:

Oracle® Database JPublisher User's Guide
http://download.oracle.com/docs/cd/B28359_01/java.111/b31226/toc.htm

Database Web Service Callout Utility 10.1.3.1
http://www.oracle.com/technology/sample_code/tech/java/jsp/callout_users_guide.htm

Example of using JDBC Timezone

2009-09-07T11:46:00.010+10:00

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

2009-09-01T15:10:00.016+10:00

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

2009-08-30T15:03:00.019+10:00

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

2009-08-29T18:50:00.017+10:00

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

2009-08-25T13:18:00.009+10:00

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

2009-07-07T13:43:00.014+10:00

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

2009-07-06T14:18:00.002+10:00

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

Using log4j as a shared library in Oracle Application Server 10.1.3.x.

2009-07-02T14:48:00.019+10:00

In this example I installed log4j as a shared library to my oc4j instance and then deploy my application from Jdev that utilises this library.

First Download log4j from

http://jakarta.apache.org/log4j/docs/download.html

Deploying log4j as shared library.

1. Unzip the downloaded log4j file.

2. In OAS ASC, select your oc4j instance -> Administration. Under Administration Tasks -> Properties and select Shared Libraries.

3. In Shared Libraries click Create.

4. Enter the library name/version

Shared Library Name = log4j
Shared Library Version = 1.0

5. Select the jar to load into the library.

6. Click finish and see the loaded library.

In Jdev, create a simple web application servlet that uses log4j.

7. My servlet looks like:

package project1;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;


public class Log4jDemoInOAS extends HttpServlet {
    private static final String CONTENT_TYPE = 
        "text/html; charset=windows-1252";
    Logger log = Logger.getLogger("Log4jDemoInOAS.class");

    public void init(ServletConfig config) throws ServletException {
        super.init(config);
        String prefix = getServletContext().getRealPath("/");
        String file = getInitParameter("log4j-prop-file");
        if (file != null) {
            PropertyConfigurator.configure(prefix + file);
        }

    }

    public void doGet(HttpServletRequest request, 
                      HttpServletResponse response) throws ServletException, 
                                                           IOException {
        response.setContentType(CONTENT_TYPE);
        PrintWriter out = response.getWriter();
        out.println("<html>");
        out.println("<head><title>Log4j Demo In OAS</title></head>");
        out.println("<body>");
        out.println("<h2>Using log4j shared libraries in OAS Demo</h2>");
        System.out.println("=== Log4jDemo ===");
        log.debug("== DEBUG Message ==");
        log.info("== INFO Message ==");
        log.warn("== WARN Message ==");
        log.error("== ERROR Message ==");
        log.fatal("== FATAL Message ==");        
        out.close();
    }
}

8. The orion-application.xml must import the library name define in step 4. It looks like:


   ...
   <imported-shared-libraries>
      <import-shared-library name="log4j"></import-shared-library>
   </imported-shared-libraries>
   ...

http://download-west.oracle.com/docs/cd/B32110_01/web.1013/b28957/deploysimple.htm#CHDIEFGE

9. Now, before deploying the application to oc4j, I need to uncheck the log4j library that we included in the JDev application during development. This is to ensure that when Jdev packages the application it does not for include the library in the package. From the deployment properties, uncheck this library.

10. Deploy the application and see the opmn container logs for the results.

Using admin_client.jar to deploy an application to oc4j

2009-05-13T13:29:00.015+10:00

An example of deploying an application to a single OC4J instance through the command line.

You can deploy an application using the admin_client.jar from the command line. Either locally or remotely. If you want to deploy from remotely, you will first need to download the Administration Client. Ensure you download the same client version as your OAS version.

10.1.3.0

http://download.oracle.com/otn/java/oc4j/1013/oc4j_admin_client_101300.zip

10.1.3.1

http://download.oracle.com/otn/java/oc4j/101310/oc4j_admin_client_101310.zip

10.1.3.2

http://download.oracle.com/otn/java/oc4j/101320/oc4j_admin_client_101320.zip

10.1.3.3 & 10.1.3.4

http://www.oracle.com/technology/software/products/ias/htdocs/utilsoft.html

In this example I will only deploy locally. Further details can be found here:
http://download-west.oracle.com/docs/cd/B32110_01/web.1013/b28951/adminclient.htm#BABFHIEB

1. Check the instance I want to deploy to is up and running.


[sracanov@sracanov-au D]$ opmnctl status

Processes in Instance: web.sracanov-au.au.oracle.com
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status
---------------------------------+--------------------+---------+---------
OC4JGroup:default_group          | OC4J:OC4J_Apps2    |    4296 | Alive
OC4JGroup:default_group          | OC4J:home          |    2308 | Alive
HTTP_Server                      | HTTP_Server        |    2732 | Alive
ASG                              | ASG                |     N/A | Down

2. Find the opmn port.


[sracanov@sracanov-au D]$ opmnctl status -port
sracanov-au:6007

3. Go to the $OH/j2ee/home folder where admin_client.jar sits.


[sracanov@sracanov-au D]$ cd %ORACLE_HOME%\j2ee\home

[sracanov@sracanov-au D]$ pwd
D:\Oracle\product\10.1.3.2.0\OracleAS_1\j2ee\home

[sracanov@sracanov-au D]$ dir | grep admin_
11/01/2007  06:53 PM               733 admin_client.jar

4. Deploy the application to oc4j instance. In my example OC4J_Apps2. The command line should looks something like:

java -jar admin_client.jar deployer:oc4j:opmn://:/ -deploy -file C:\drvtest.ear -deploymentName drvtest -bindAllWebApps


[sracanov@sracanov-au D]$ echo %JAVA_HOME%
D:\Oracle\product\10.1.3.2.0\OracleAS_1\jdk

[sracanov@sracanov-au D]$ 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)

[sracanov@sracanov-au D]$ java -jar admin_client.jar deployer:oc4j:opmn://localhost:6007/OC4J_Apps2
oc4jadmin welcome1 -deploy -file C:\drvtest.ear -deploymentName drvtest -bindAllWebApps
09/05/13 13:23:14 Notification ==>Application Deployer for drvtest STARTS.

09/05/13 13:23:14 Notification ==>Copy the archive to D:\Oracle\product\10.1.3.2.0
\OracleAS_1\j2ee\OC4J_Apps2\applications\drvtest.ear

09/05/13 13:23:14 Notification ==>Initialize D:\Oracle\product\10.1.3.2.0\OracleAS_1
\j2ee\OC4J_Apps2\applications\drvtest.ear begins...

09/05/13 13:23:14 Notification ==>Unpacking drvtest.ear

09/05/13 13:23:14 Notification ==>Done unpacking drvtest.ear

09/05/13 13:23:14 Notification ==>Unpacking drvtest.war

09/05/13 13:23:14 Notification ==>Done unpacking drvtest.war

09/05/13 13:23:14 Notification ==>Initialize D:\Oracle\product\10.1.3.2.0\OracleAS_1
\j2ee\OC4J_Apps2\applications\drvtest.ear ends...

09/05/13 13:23:14 Notification ==>Starting application : drvtest

09/05/13 13:23:14 Notification ==>Initializing ClassLoader(s)

09/05/13 13:23:14 Notification ==>Initializing EJB container

09/05/13 13:23:14 Notification ==>Loading connector(s)

09/05/13 13:23:14 Notification ==>Starting up resource adapters

09/05/13 13:23:14 Notification ==>Initializing EJB sessions

09/05/13 13:23:14 Notification ==>Committing ClassLoader(s)

09/05/13 13:23:14 Notification ==>Initialize drvtest begins...

09/05/13 13:23:14 Notification ==>Initialize drvtest ends...

09/05/13 13:23:14 Notification ==>Started application : drvtest

09/05/13 13:23:14 Notification ==>Binding web application(s) to site default-web-site begins...

09/05/13 13:23:14 Notification ==>Binding drvtest web-module for application drvtest 
to site default-web-site under context root drvtest

09/05/13 13:23:17 Notification ==>Initializing Servlet: 
org.apache.struts.action.ActionServlet for web application drvtest

09/05/13 13:23:17 Notification ==>Binding web application(s) to site 
default-web-site ends...

09/05/13 13:23:17 Notification ==>Application Deployer for drvtest COMPLETES. 
Operation time: 3328 msecs

5. Check the application is deployed and running:


[sracanov@sracanov-au D]$ opmnctl status -app

Applications in Instance: web.sracanov-au.au.oracle.com

application type: OC4J
-----+-----------+---------+---------+----------------+----------+--------
pid  | name      | state   | rtid    | classification | routable | parent
-----+-----------+---------+---------+----------------+----------+--------
4296 | drvtest   | started | g_rt_id | external       | true     | default
4296 | system    | started | g_rt_id | external       | true     |
4296 | default   | started | g_rt_id | external       | true     | system
4296 | jamagent  | started | g_rt_id | external       | true     | default
4296 | ascontrol | stopped | g_rt_id | external       | false    |
4296 | datatags  | started | g_rt_id | internal       | true     | default
4296 | javasso   | stopped | g_rt_id | internal       | true     |
2308 | system    | started | g_rt_id | external       | true     |
2308 | default   | started | g_rt_id | external       | true     | system
2308 | ascontrol | started | g_rt_id | external       | true     | system
2308 | datatags  | started | g_rt_id | internal       | true     | default
2308 | javasso   | stopped | g_rt_id | internal       | true     |

JDev11g Export Table To Excel

2009-04-20T09:11:00.012+10:00

JDev 11g offers a simple way to export an ADF table to excel in a couple simple steps.

1. Create af:panelCollection on the jspx page.
2. Drag a data control onto af:panelCollection and drop it as table.
3. Set af:table id to table.
4. Expand Panel Collection facets, and drop af:toolbar on toolbar facet.
5. Drop af:commandButton on af:toolbar. Set the text to "Export To Excel"
6. Drop af:exportCollectionActionListener on af:commandButton. And set the following:

ExportId: table
Type: excelHTML
Filename: Emp.xls
Title: Employees.

The exportId needs to bind to the af:table id. Which we set as 'table' in step 3

7. Run the jspx page and test the export button.

This is how the my final code looks like:


<panelCollection>
  <facet name="menus"/>
  <facet name="toolbar">
    <toolbar>
      <commandButton text="Export To Excel">
        <exportCollectionActionListener exportedId="table" type="excelHTML"
                                        filename="Emp.xls" title="Employees"/>
      </commandButton>
    </toolbar>
  </facet>
  <facet name="statusbar"/>
  <table value="#{bindings.EmpView1.collectionModel}" var="row"
         rows="#{bindings.EmpView1.rangeSize}"
         emptyText="#{bindings.EmpView1.viewable ? 'No rows yet.' : 'Access Denied.'}"
         fetchSize="#{bindings.EmpView1.rangeSize}" filterVisible="false"
         id="table">
    <column sortProperty="Empno" sortable="false"
            headerText="#{bindings.EmpView1.hints.Empno.label}"
            filterable="false">
      <outputText value="#{row.Empno}">
        <convertNumber groupingUsed="false"
                       pattern="#{bindings.EmpView1.hints.Empno.format}"/>
      </outputText>
    </column>
    ...
    ...
    ...
  </table>
</panelCollection>

Jdev 11g Web Service WSDL Parameter Name

2009-04-16T11:01:00.006+10:00


With Jdev 11g, after generating a Web Service from a java class, the WSDL contains the following 
parameter names:

...
<xsd:complexType name="sayHello">
  <xsd:sequence>
     <xsd:element name="arg0" type="xsd:string"/>
     <xsd:element name="arg1" type="xsd:string"/>
  </xsd:sequence>
</xsd:complexType>
...

My java code function looks like:
...
public String sayHello(String firstname, String lastname) {
  return "Hello " + firstname + " " + lastname;
}
...

With Jdev 10g , the same code generates the appropriate values:
...
<xsd:complexType name="sayHello">
  <xsd:sequence>
     <xsd:element name="firstname" type="xsd:string"/>
     <xsd:element name="lastname" type="xsd:string"/>
  </xsd:sequence>
</xsd:complexType>
...

To workaround this in 11g, I need to create my java class as follows:

package project1;

import javax.jws.WebMethod;
import javax.jws.WebParam;
import javax.jws.WebService;

@WebService
public class Class1 {
    @WebMethod
    public String sayHello(@WebParam(name = "fname") String firstname,
                           @WebParam(name = "lname") String lastname) {
        return "Hello " + firstname + " " + lastname;
    }
}

And now my generated WSDL looks like:
...
<xsd:complexType name="sayHello">
  <xsd:sequence>
     <xsd:element name="fname" type="xsd:string"/>
     <xsd:element name="lname" type="xsd:string"/>
  </xsd:sequence>
</xsd:complexType>
...

http://java.sun.com/javaee/5/docs/api/javax/jws/WebParam.html

ORA-29532: HTTP transport error: java.security.PrivilegedActionException: javax.xml.soap.SOAPException: Message send fail

2009-02-25T13:57:00.019+11:00

After running jpub using the 'get joke' web service and then calling the web service in sqlplus, I get the following error:


[sracanov@sracanov-au D]$ jpub -user=scott/tiger -sysuser=sys/welcome1
-proxywsdl=getjoke.asmx.xml 
-endpoint=http://interpressfact.net/webservices/getjoke.asmx
tmp\src\genproxy\GetJokeSoapClientJPub.java
plsql_wrapper.sql
plsql_dropper.sql
plsql_grant.sql
plsql_revoke.sql
Executing plsql_dropper.sql
Executing plsql_wrapper.sql
Executing plsql_grant.sql
Loading plsql_proxy.jar

[sracanov@sracanov-au D]$ sqlplus scott/tiger
Logging into sqlplus - scott @orcl

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Feb 25 14:06:32 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

scott@orcl> select jpub_plsql_wrapper.getjoke('') from dual;
select jpub_plsql_wrapper.getjoke('') from dual
                                           *
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.rmi.RemoteException: java.rmi.RemoteException:; nested exception is:
HTTP transport error: javax.xml.soap.SOAPException:
java.security.PrivilegedActionException: javax.xml.soap.SOAPException: Message
send failed: Connection refused

This error is occurring due to the proxy. So I need to execute setHttpProxy(host VARCHAR2, port NUMBER) to avoid this.

scott@orcl> call jpub_plsql_wrapper.setHttpProxy('www-proxy',8080);

Call completed.

scott@orcl> select jpub_plsql_wrapper.getjoke('') from dual;

JPUB_PLSQL_WRAPPER.GETJOKE('')
--------------------------------------------------------------------------------
Murphy's Military Laws:15.
Don't be conspicuous. In the combat zone, it draws fire. Out of the combat zone,
it draws sergeants.

To completely avoid this error, set jpub to call the WSDL directly rather than a local file and set the http proxy for jpub.


[sracanov@sracanov-au D]$ jpub -user=scott/tiger -sysuser=sys/welcome1 
-proxywsdl=http://interpressfact.net/webservices/getjoke.asmx?WSDL 
-endpoint=http://interpressfact.net/webservices/getjoke.asmx 
-httpproxy=www-proxy:8080
tmp\src\genproxy\GetJokeSoapClientJPub.java
plsql_wrapper.sql
plsql_dropper.sql
plsql_grant.sql
plsql_revoke.sql
Executing plsql_dropper.sql
Executing plsql_wrapper.sql
Executing plsql_grant.sql
Loading plsql_proxy.jar

[sracanov@sracanov-au D]$ sqlplus scott/tiger
Logging into sqlplus - scott @orcl

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Feb 25 14:02:50 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

scott@orcl> select jpub_plsql_wrapper.getjoke('') from dual;

JPUB_PLSQL_WRAPPER.GETJOKE('')
--------------------------------------------------------------------------------
Q: How many `Real Men' does it take to change a light bulb?
A: None:  `Real Men' aren't afraid of the dark.

java.sql.SQLException: Io exception: The Network Adapter

2009-01-19T11:50:00.010+11:00

When using JDBC 10.2.0.3 to connect from java standalone client to the database I get the following error:


D:\Oracle\jdev\10134-prod\jdk\bin\javaw.exe -client -classpath "D:\Oracle\jdev\10134-prod\jdev\mywork\MyApplications\JDBC-SSL2\classes;D:\My Contents\My Software\Oracle\JDBC Drivers\10.2.0.3\ojdbc14_g.jar;D:\Oracle\jdev\10134-prod\jlib\oraclepki.jar" jdbcssl2.SSLEncryptionAuthenication
Exception in thread "main" java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:190)
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:363)
 at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:401)
 at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:466)
 at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:165)
 at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
 at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:839)
 at java.sql.DriverManager.getConnection(DriverManager.java:525)
 at java.sql.DriverManager.getConnection(DriverManager.java:140)
 at jdbcssl2.SSLEncryptionAuthenication.getConnection(SSLEncryptionAuthenication.java:57)
 at jdbcssl2.SSLEncryptionAuthenication.run(SSLEncryptionAuthenication.java:39)
 at jdbcssl2.SSLEncryptionAuthenication.main(SSLEncryptionAuthenication.java:62)
Process exited with exit code 1.

The "java.sql.SQLException: Io exception: The Network Adapter could not establish the connection" is very generic and can mean a number of different issue. Also the stack error is not very detailed.

Try using JDBC 11.1.0.7 and enable "-Djavax.net.debug=ssl" java property when you run your client to allow for a detailed stack error to help you find the problem.

Using jpub to create webserivce proxy for callouts.

2008-11-11T11:58:00.007+11:00

Im my preivous blog I created a simple webservice and used plsql to call the webservice from the database. Here I will use jpub to generate my java proxy and use that to call my webservice without the need to code any plsql. Note all of these steps are done upon the previous blog beginning completed.

1. Download and unzip jpub. You can get it from http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
Im using JPublisher 10g Release 10.2


C:\Documents and Settings\stever\Desktop> set_database.bat
C:\Documents and Settings\stever\Desktop> oracle_home
D:\Oracle\product\10.2.0\db_1
C:\Documents and Settings\stever\Desktop> unzip jpub_102.zip -d %oracle_home%
Archive:  jpub_102.zip
  inflating: D:/Oracle/product/10.2.0/db_1/sqlj/lib/sqljutl.sql
  inflating: D:/Oracle/product/10.2.0/db_1/sqlj/lib/translator.jar
...

2. Set the classpath and run jpub.


C:\Documents and Settings\stever\Desktop>cdo

D:\Oracle\product\10.2.0\db_1

D:\Oracle\product\10.2.0\db_1>cd sqlj\bin

D:\Oracle\product\10.2.0\db_1\sqlj\bin>ll
 Volume in drive D is ACERDATA
 Volume Serial Number is 4059-B823

 Directory of D:\Oracle\product\10.2.0\db_1\sqlj\bin

07/21/2005  03:34 AM    <DIR>          .
07/21/2005  03:34 AM    <DIR>          ..
07/21/2005  03:34 AM             1,376 jpub
07/21/2005  03:34 AM             2,834 jpub.c
07/21/2005  03:34 AM            50,619 jpub.exe
07/21/2005  03:34 AM               267 README.txt
               4 File(s)         55,096 bytes
               2 Dir(s)   8,243,344,384 bytes free

D:\Oracle\product\10.2.0\db_1\sqlj\bin> cd ..
D:\Oracle\product\10.2.0\db_1\sqlj>java_home
D:\Oracle\product\10.2.0\db_1\jdk

D:\Oracle\product\10.2.0\db_1\sqlj>classpath
.;D:\Oracle\product\10.2.0\db_1\sqlj\lib\translator.jar;
D:\Oracle\product\10.2.0\db_1\sqlj\lib\runtime12.jar;
D:\Oracle\product\10.2.0\db_1\sqlj\lib\dbwsa.jar;D:\Oracle\product\10.2
.0\db_1\javavm\lib\aurora.zip;
D:\Oracle\product\10.2.0\db_1\jdbc\lib\ojdbc14.jar;

D:\Oracle\product\10.2.0\db_1\sqlj>jpub -u wsuser/wsuser -sysuser sys/welcome1
-proxywsdl=http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort?WSDL
-endpoint=http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort
tmp\src\genproxy\MyWebService1SoapHttpPortClientJPub.java
plsql_wrapper.sql
plsql_dropper.sql
plsql_grant.sql
plsql_revoke.sql
Executing plsql_dropper.sql
Executing plsql_wrapper.sql
Executing plsql_grant.sql
Loading plsql_proxy.jar

3. Call the webservice.


D:\Oracle\product\10.2.0\db_1\sqlj>sqlplus wsuser/wsuser

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Nov 10 19:36:58 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select jpub_plsql_wrapper.sayhelloworld from dual;

SAYHELLOWORLD
--------------------------------------------------------------------------------
Hello World

SQL> select jpub_plsql_wrapper.sayhelloname('Steve') from dual;

JPUB_PLSQL_WRAPPER.SAYHELLONAME('STEVE')
--------------------------------------------------------------------------------
Hello Steve

SQL>

NOTE: My set_database.bat file simply has my environment settings in there. 
This is where I set the classpath. It looks like:

set ORACLE_HOME=D:\Oracle\product\10.2.0\db_1
set ORACLE_SID=ORCL
set JAVA_HOME=%ORACLE_HOME%\jdk
set PATH=%ORACLE_HOME%\bin;%TT_HOME%\bin;%JAVA_HOME%\bin;%ORACLE_HOME%\sqlj\bin;%PATH%
set TNS_ADMIN=D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN
set CLASSPATH=.;%ORACLE_HOME%\sqlj\lib\translator.jar;
%ORACLE_HOME%\sqlj\lib\runtime12.jar;%ORACLE_HOME%\sqlj\lib\dbwsa.jar;
%ORACLE_HOME%\javavm\lib\aurora.zip;%ORACLE_HOME%\jdbc\lib\ojdbc14.jar;

Using UTL_DBWS Package To Call A Web Service From PL/SQL Code.

2008-10-27T15:12:00.040+11:00

I am going to show a simple example that will call my web service from my database. I will be using JDev 10.1.3.x to create and run my web service. My database and web service will all be running from my local machine.

1. My first step is to upgrade my database to 10.2.0.2 or later. UTL_DBWS will not work on 10.2.0.1. While its upgrading, I need to download the latest UTL_DBWS from
http://www.oracle.com/technology/sample_code/tech/java/jsp/dbwebservices.html

* I am going to use 10.1.3.1 Callout Utility for 10g and 11g RDBMS (ZIP, ~13MB)

Extract the downloaded file like:

unzip dbws-callout-utility-10131.zip sqlj/* -d $ORACLE_HOME
unzip dbws-callout-utility-10131.zip samples/* -d $ORACLE_HOME/sqlj
unzip dbws-callout-utility-10131.zip *.htm -d $ORACLE_HOME/sqlj

2. Ensure that I have $ORACLE_HOME/bin in my path. First, I need to run the following command to load the libraries (run this from the $ORACLE_HOME/sqlj/lib folder).

loadjava -u username/password -r -v -f -s -grant public -genmissing dbwsclientws.jar dbwsclientdb102.jar

The username and password is this example will be the sys user.

Check my database is upgraded/correct and load the jar files.

NOTE: This may take a while to run.

3. Create a new user to use web services utl_dbws package and give grant privileges.

create user wsuser identified by wsuser
default tablespace users
temporary tablespace temp
/

grant connect, resource to wsuser;

4. Once the packages had been loaded (step 2), the next step is to provide grants from the schema where the jar files have been loaded to our 'WSUSER'. These grants will have to run from where the libraries are installed. 'SYS' user.


execute dbms_java.grant_permission('<user schema>','SYS:java.lang.RuntimePermission', 

'accessClassInPackage.sun.util.calendar','');



execute dbms_java.grant_permission('<user schema>','SYS:java.lang.RuntimePermission',

'getClassLoader','');



execute dbms_java.grant_permission('<user schema>','SYS:java.net.SocketPermission','*',

'connect,resolve');



execute dbms_java.grant_permission('<user schema>','SYS:java.util.PropertyPermission','*',

'read,write');



execute dbms_java.grant_permission('<user schema>','SYS:java.lang.RuntimePermission',

'setFactory','');

5. Now using Jdev, I create my java class as follows.


package ws;

public class HelloWorld {
    public HelloWorld() {
    }
    
    public String sayHelloWorld(){
        return "Hello World";
    }
    
    public String sayHelloName(String n){
        return "Hello " + n;
    }
}

6. I then create a web service from this class.

* Select "J2EE 1.4 (JAX-RPC) Web Service"

I then click on finished at Step 1 of 8 of the 'Create Java J2EE 1.4 Web Service' wizard so it uses all the default setting.

7. Then I ran the web service in the embedded server.

And checked my WS WSDL to ensure it is running.

http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort
http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort?WSDL

This is how my deployed WSDL looks like:

This XML file does not appear to have any style information associated with it. The document tree is shown below.


-<definitions name="MyWebService1" targetNamespace="http://ws/">
 -<types>
  -<schema targetNamespace="http://ws/types/" elementFormDefault="qualified">
   -<element name="sayHelloNameElement">
    -<complexType>
      -<sequence>
         <element name="n" type="string" nillable="true"/>
      -</sequence>
     </complexType>
    </element>
   -<element name="sayHelloNameResponseElement">
    -<complexType>
     -<sequence>
       <element name="result" type="string" nillable="true"/>
      </sequence>
     </complexType>
    </element>
   -<element name="sayHelloWorldElement">
    -<complexType>
      <sequence/>
     </complexType>
    </element>
   -<element name="sayHelloWorldResponseElement">
    -<complexType>
     -<sequence>
       <element name="result" type="string" nillable="true"/>
      </sequence>
     </complexType>
    </element>    
   </schema>
  </types>
 -<message name="MyWebService1_sayHelloName">
   <part name="parameters" element="tns0:sayHelloNameElement"/>
  </message>
 -<message name="MyWebService1_sayHelloNameResponse">
   <part name="parameters" element="tns0:sayHelloNameResponseElement"/>
  </message>
 -<message name="MyWebService1_sayHelloWorld">
   <part name="parameters" element="tns0:sayHelloWorldElement"/>
  </message>
 -<message name="MyWebService1_sayHelloWorldResponse">
   <part name="parameters" element="tns0:sayHelloWorldResponseElement"/>
  </message> 
 -<portType name="MyWebService1">
  -<operation name="sayHelloName">
    <input message="tns:MyWebService1_sayHelloName"/>
    <output message="tns:MyWebService1_sayHelloNameResponse"/>    
   </operation>
   <operation name="sayHelloWorld">
    <input message="tns:MyWebService1_sayHelloWorld"/>
    <output message="tns:MyWebService1_sayHelloWorldResponse"/>
   </operation> 
  </portType>
 -<binding name="MyWebService1SoapHttp" type="tns:MyWebService1">
   <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
  -<operation name="sayHelloName">
    <soap:operation soapAction="http://ws//sayHelloName"/>
   -<input>
     <soap:body use="literal"/>
    </input>
   -<output>
     <soap:body use="literal"/>
    </output>
   </operation>
   -<operation name="sayHelloWorld">
    <soap:operation soapAction="http://ws//sayHelloWorld"/>
   -<input>
     <soap:body use="literal"/>
    </input>
   -<output>
     <soap:body use="literal"/>
    </output>
   </operation>
  </binding>
 -<service name="MyWebService1">
  -<port name="MyWebService1SoapHttpPort" binding="tns:MyWebService1SoapHttp">
    <soap:address 
      location="http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort"/>
   </port>
  </service>
</definitions>

8. Now using the UTL_DBWS package, I will create my PL/SQL code.


CREATE OR REPLACE PACKAGE HelloWorld 
AS

FUNCTION SayHelloWorld RETURN VARCHAR2;
FUNCTION sayHelloName(yourname IN VARCHAR2) RETURN VARCHAR2;

END HelloWorld;
/

CREATE OR REPLACE PACKAGE BODY HelloWorld AS

FUNCTION SayHelloWorld 
RETURN VARCHAR2 
IS

  service_           sys.utl_dbws.SERVICE;
  call_              sys.utl_dbws.CALL;
  service_qname      sys.utl_dbws.QNAME;
  port_qname         sys.utl_dbws.QNAME;
  xoperation_qname   sys.utl_dbws.QNAME;
  xstring_type_qname sys.utl_dbws.QNAME;
  response           sys.XMLTYPE;
  request            sys.XMLTYPE;

BEGIN  
  service_qname := sys.utl_dbws.to_qname(null, 'SayHelloWorld');
  service_      := sys.utl_dbws.create_service(service_qname);
  call_         := sys.utl_dbws.create_call(service_);
  sys.utl_dbws.set_target_endpoint_address(call_
    , 'http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort');

  sys.utl_dbws.set_property( call_
                           , 'SOAPACTION_USE'
                           , 'TRUE'
                           );

  sys.utl_dbws.set_property( call_
                           , 'SOAPACTION_URI'
                           , 'http://ws//sayHelloWorld'
                           );
 
  sys.utl_dbws.set_property( call_
                         , 'OPERATION_STYLE'
                         , 'document'
                          );

  request := sys.XMLTYPE('<sayHelloWorldElement xmlns="http://ws/types/" />');

  response :=sys. utl_dbws.invoke(call_, request);
  return response.extract('//child::text()'
    , 'xmlns="http://stever-5670:8988/DatabaseWSDemo-WS-context-root/
    MyWebService1SoapHttpPort"').getstringval();

END SayHelloWorld;

FUNCTION sayHelloName(yourname IN VARCHAR2) RETURN VARCHAR2 AS

  service_           sys.utl_dbws.SERVICE;
  call_              sys.utl_dbws.CALL;
  service_qname      sys.utl_dbws.QNAME;
  port_qname         sys.utl_dbws.QNAME;
  xoperation_qname   sys.utl_dbws.QNAME;
  xstring_type_qname sys.utl_dbws.QNAME;
  response           sys.XMLTYPE;
  request            sys.XMLTYPE;

BEGIN  
  service_qname := sys.utl_dbws.to_qname(null, 'SayHelloName');
  service_      := sys.utl_dbws.create_service(service_qname);
  call_         := sys.utl_dbws.create_call(service_);
  sys.utl_dbws.set_target_endpoint_address(call_
    , 'http://stever-5670:8988/DatabaseWSDemo-WS-context-root/MyWebService1SoapHttpPort');

  sys.utl_dbws.set_property( call_
                           , 'SOAPACTION_USE'
                           , 'TRUE'
                           );

  sys.utl_dbws.set_property( call_
                           , 'SOAPACTION_URI'
                           , 'http://ws//sayHelloName'
                           );
 
  sys.utl_dbws.set_property( call_
                         , 'OPERATION_STYLE'
                         , 'document'
                          );

  request := sys.XMLTYPE('<sayHelloNameElement xmlns="http://ws/types/">'
                        ||'<n>' || yourname || '</n> </sayHelloNameElement>');

  response :=sys. utl_dbws.invoke(call_, request);
  return response.extract('//child::text()'
    , 'xmlns="http://stever-5670:8988/DatabaseWSDemo-WS-context-root/
    MyWebService1SoapHttpPort"').getstringval();

END SayHelloName;

END HelloWorld;
/

9. I then ran my package:

select helloworld.sayhelloworld from dual;

select helloworld.sayhelloname('Steve') from dual;

Performance using Bind Variables in Java Application

2008-03-31T13:03:00.016+11:00

The advantage of using bind variables within your Java application, it that provides greater application performance and uses less of the shared pool from the database.

This is illustrated in the following 2 examples.

Example 1: Using Bind variables.


package bindvariables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import bindvariables.JDBCUtil;

public class Bind {

    public static void main(String[] args) throws SQLException {

        ResultSet rs = null;
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            // Get Connection
            conn = JDBCUtil.getConnection();
            // Create statement
            pstmt = conn.prepareStatement("SELECT ? FROM dual");
            System.out.println("Start: " + new Date());
            for (int i = 1; i < 100000; i++) {
                // Use bind variable for subsitution
                pstmt.setString(1, String.valueOf(i));
                // Execute the query
                rs = pstmt.executeQuery();
                //if (i % 1000 == 1)
                  //  System.out.println("Print: " + i);
            }
            System.out.println("End: " + new Date());
           
        } catch (SQLException se) {
            System.out.println("SQL Exception:");
            se.printStackTrace();
        }
        finally {
            // Close the result set, statement and the connection
            JDBCUtil.close(rs,pstmt,conn);
        }
    }
}

Running this application returns the following:

Start: Mon Mar 31 11:36:04 EST 2008
End: Mon Mar 31 11:36:51 EST 2008

It shows the application being executed within less than a minute.

Now the second example does not use bind variables and force the use of Hard Parse on the database.

Example 2: Not using bind variables.


package bindvariables;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class NonBind {

    public static void main(String[] args) throws SQLException {

        ResultSet rs = null;
        Connection conn = null;
        Statement stmt = null;

        try {
            // Get Connection            
            stmt = (JDBCUtil.getConnection()).createStatement();
            System.out.println("Start: " + new Date());
            for (int i = 0; i < 100000; i++) {
                // Execute Statement
                rs = stmt.executeQuery("SELECT " + i + " FROM dual");
                //if (i % 1000 == 1)
                  //  System.out.println("Print: " + i);
            }
            System.out.println("End: " + new Date());           
        } catch (SQLException se) {
            System.out.println("SQL Exception:");
            se.printStackTrace();
        }
        finally {
            // Close the result set, pstatement and the connection
            JDBCUtil.close(rs,stmt,conn);
        }
    }
}

Running this application returns the following:

Start: Mon Mar 31 11:31:45 EST 2008
End: Mon Mar 31 11:35:34 EST 2008

It shows the application being executed over 4 minutes.

As you can see, ensure you use bind variables for performance when coding your application.

My JDBCUtil class method look like:

...


    public static void close(ResultSet resultSet, Statement statement,
                             Connection connection) {
        try {
            if (resultSet != null)
                close(resultSet);
            if (statement != null)
                close(statement);
            if (connection != null)
                close(connection);
        }  catch (Exception e) {
            e.printStackTrace();
        }
    }
   
    public static void close(ResultSet resultSet, PreparedStatement pstatement,
                             Connection connection) {
        try {
            if (resultSet != null)
                close(resultSet);
            if (pstatement != null)
                close(pstatement);
            if (connection != null)
                close(connection);
        }  catch (Exception e) {
            e.printStackTrace();
        }
    }
   
    public static Connection getConnection() throws SQLException {
        String username = "scott";
        String password = "tiger";
        String thinConn =
            "jdbc:oracle:thin:@sracanov-au2.au.oracle.com:1522:orcl";
        DriverManager.registerDriver(new OracleDriver());
        Connection conn =
            DriverManager.getConnection(thinConn, username, password);
        conn.setAutoCommit(false);
        return conn;
    }
}

...

Database (Web Services Call-Out) class error

2008-03-19T11:28:00.007+11:00

When receiving the following error:

ERROR at line 1:
ORA-29540: class oracle/jpub/runtime/dbws/DbwsProxy does not exist
ORA-06512: at "SYS.UTL_DBWS", line 195
ORA-06512: at "INTERFACE.GET_JOKE", line 13

The java classes are not installed in the database user schema.

Logout of sqlplus and run:

loadjava -u / -r -v -f -s -grant public -genmissing  dbwsclientws.jar dbwsclientdb102.jar

Where user/password are the details of the database user using the database web service callout.

Further details can be found here:

http://www.oracle.com/technology/sample_code/tech/java/jsp/callout_users_guide.htm
http://www.oracle.com/technology/tech/java/oc4j/1003/how_to/how-to-ws-db-javacallout.html

How to configure an Oracle J2CA JMS RA and use it with a java client

2007-11-13T14:20:00.000+11:00

This article explain’s in point form to create a java client that excesses a resource adapter, from an oc4j instance that is configure with fast connection failover to RAC database. It extends from my previous JMS posts.

I am using:
OAS 10.1.3.2.0
RAC DB 10.2.0.3.0
Jdev 10.1.3.2

1. First logged in the database as “sys” user and created the queue.

I ran the following script:


------------------------------------------------------------
drop user jmsuser cascade;
------------------------------------------------------------
 create user jmsuser identified by jmsuser;

grant create database link, create sequence, create session,
       create synonym, create public synonym, drop public synonym,
       create table, create view, create indextype,
       create procedure, create trigger, create type, 
       unlimited tablespace
    to  jmsuser;

 grant aq_administrator_role      to  jmsuser;
 grant execute on sys.dbms_aqadm  to  jmsuser;
 grant execute on sys.dbms_aq     to  jmsuser;

 grant execute on sys.dbms_aqin   to  jmsuser;
 grant execute on sys.dbms_aqjms  to  jmsuser;

 grant force any transaction      to  jmsuser;

------------------------------------------------------------
connect jmsuser/jmsuser@judd;
------------------------------------------------------------
create or replace package  ROUTERAdmin
  AUTHID  DEFINER  as

  procedure  createQueueTable(qtablename  IN VARCHAR2);
  procedure  createTopicTable(ttablename  IN VARCHAR2);

  procedure  createQueue(
                qname                    IN     VARCHAR2,
                qtablename               IN     VARCHAR2);

  procedure  createTopic(
                tname                    IN     VARCHAR2,
                ttablename               IN     VARCHAR2);

end  ROUTERAdmin;
/
show errors;
------------------------------------------------------------
------------------------------------------------------------
create or replace package body ROUTERAdmin  as

---------------------------------
---------------------------------
  procedure  createQueue(
                qname                    IN     VARCHAR2,
                qtablename               IN     VARCHAR2)  is
  begin
    --
    createQueueTable(qtablename);

    --
    DBMS_AQADM.CREATE_QUEUE( 
      Queue_name          => qname, 
      Queue_table         => qtablename,
      max_retries         => '2');

    --
    DBMS_AQADM.START_QUEUE(
      queue_name         => qname);
 end;
---------------------------------
  procedure  createTopic(
                tname                    IN     VARCHAR2,
                ttablename               IN     VARCHAR2) is
  begin
    --
    createTopicTable(ttablename);
  
    --
    DBMS_AQADM.CREATE_QUEUE( 
      Queue_name          => tname, 
      Queue_table         => ttablename,
      max_retries         => '2');

    --
    DBMS_AQADM.START_QUEUE(
      queue_name         => tname);
 end;

---------------------------------
  procedure  createQueueTable(qtablename  IN VARCHAR2)   is
  begin
      DBMS_AQADM.CREATE_QUEUE_TABLE(
        Queue_table            => qtablename,
        Queue_payload_type     => 'SYS.AQ$_JMS_MESSAGE',
        sort_list => 'PRIORITY,ENQ_TIME',
 multiple_consumers  => false,
        compatible             => '9.2');
  exception
    when others then
       dbms_output.put_line('EXC: createQueueTable');
    return;
  end;
---------------------------------
  procedure  createTopicTable(ttablename  IN VARCHAR2)   is
  begin
      DBMS_AQADM.CREATE_QUEUE_TABLE(
        Queue_table            => ttablename,
        Queue_payload_type     => 'SYS.AQ$_JMS_MESSAGE',
        sort_list => 'PRIORITY,ENQ_TIME',
 multiple_consumers  => TRUE,
        compatible             => '9.2');
  exception
    when others then
       dbms_output.put_line('EXC: createTopicTable');
    return;
  end;
---------------------------------

end  ROUTERAdmin;
/
show errors;


------------------------------------------------
----- create topics and queues for jmsrouteruser
------------------------------------------------
connect jmsuser/jmsuser@judd;

begin
---- Queues ----
 ROUTERAdmin.createQueue('RPQueue','RPTABLE');
 ROUTERAdmin.createQueue('RPLogQ','RPQLOGTABLE');
end;
/

2. I am using the oc4j instance “OC4J_Apps”

Created a connection pool called “fcfConnPoolJMS”

ULR: jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(...))

Username: jmsuser
Password: jmsuser

Connection Factory Properites:
connectionCachingEnabled = true
fastConnectionFailoverEnabled =true

Initial size of Connection Cache = 10
Minimum Number of Connections = 10
Maximum Number of Connections = 20

Save

3. Create a data source

Name: fcfDSJMS
JNDI Location: jdbc/fcfDSJMS
Connection Pool: fcfConnPoolJMS

Save

4. Create a Database Persistence.

OC4J_Apps -> Administration -> Database Persistence -> Deploy

Resource Adapter Module Name = mySimpleTestRA

Add new Resource Provider
Resource Provider Name = mySimpleTestRP
Datasource JNDI Location = jdbc/fcfDSJMS

Saved and then restarted OC4J instance “OC4J_Apps”.

5. Created Connection Factory.

OC4J_Apps -> Applications -> Standalone Resource Adapters (select from combo box) -> mySimpleTestRA (Resource Adapter Module)

Connection Factories -> Create

Connection Factory Interface = javax.jms.QueueConnectionFactory
JNDI Location = mySimpleTestRA

Save.

6. Create Administration Objects

Administration Objects -> create

Object Class = oracle.j2ee.ra.jms.generic.AdminObjectQueueImpl

JNDI Location = mySimpleTestRA/AutoWrap

Configuration Properties
ResourceProviderName = mySimpleTestRP

Save.

7. Create my java test client in Jdev.


package jmsracclient;

import java.util.Hashtable;

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

import javax.jms.*;

public class JMSClient {

    public static void main(String[] args) {
        final String queueName = 
            "java:comp/resource/mySimpleTestRP/Queues/RPQueue";
        final String queueConnectionFactoryName = 
            "java:comp/resource/mySimpleTestRP/QueueConnectionFactories/QCF";
        final String oc4juser = "oc4jadmin";
        final String oc4juserpassword = "welcome1";
        final String urlProvider = "opmn:ormi://";
        final String jmsProviderHost = "sracanov-au2.au.oracle.com";
        final String colon = ":";
        final String opmnPort = "6005";
        final String oc4jinstance = "OC4J_Apps";
        final int NUMBER_OF_MESSAGES = 5;
        Context jndiContext = null;
        QueueConnectionFactory queueConnectionFactory = null;
        QueueConnection queueConnection = null;
        QueueSession queueSession = null;
        Queue queue = null;
        QueueSender queueSender = null;
        TextMessage message = null;

        /*
         * Set the environment for a connection to the OC4J instance
         */
        Hashtable env = new Hashtable();
        env.put(Context.INITIAL_CONTEXT_FACTORY, 
                "oracle.j2ee.rmi.RMIInitialContextFactory");
        env.put(Context.SECURITY_PRINCIPAL, oc4juser);
        env.put(Context.SECURITY_CREDENTIALS, oc4juserpassword);
        env.put(Context.PROVIDER_URL, 
                urlProvider + jmsProviderHost + colon + opmnPort + colon + 
                oc4jinstance + "/default");

        /*
         * Set the Context Object.
         * Lookup the Queue Connection Factory.
         * Lookup the JMS Destination.
         */
        try {
            jndiContext = new InitialContext(env);
            System.out.println("Env set");
            queueConnectionFactory = 
                    (QueueConnectionFactory)jndiContext.lookup(queueConnectionFactoryName);
            System.out.println("Got QCF");
            queue = (Queue)jndiContext.lookup(queueName);
            System.out.println("Got Q");
        } catch (NamingException e) {
            System.out.println("JNDI lookup failed: " + e.toString());
            System.exit(1);
        }
        System.out.println("End of test client");
        /*
         * Create connection.
         * Create session from connection.
         * Create sender.
         * Create text message.
         * Send messages.
         * Send non text message to end text messages.
         * Close connection.
         */
        try {
            queueConnection = queueConnectionFactory.createQueueConnection();
            queueSession = 
                    queueConnection.createQueueSession(false, Session.AUTO_ACKNOWLEDGE);
            queueSender = queueSession.createSender(queue);
            message = queueSession.createTextMessage();
            for (int i = 0; i < NUMBER_OF_MESSAGES; i++) {
                message.setText("Message " + (i + 1));
                System.out.println("Producing message: " + message.getText());
                queueSender.send(message);
            }
            queueSender.send(queueSession.createBytesMessage());
        } catch (JMSException e) {
            System.out.println("Exception occurred: " + e.toString());
        } finally {
            if (queueConnection != null) {
                try {
                    queueConnection.close();
                } catch (JMSException e) {
                    System.out.println("Closing error: " + e.toString());
                }
            }
        }
    }
}

8. Ensure the project properties java options are set correctly for -Doracle.ons.oraclehome

This directory must contain opmn\conf\ons.config file. My ons.config file looks like:

localport=6100
remoteport=6202
nodes=aulnx11-vip.au.oracle.com:6202,aulnx12-vip.au.oracle.com:6202

9. The project should have the following libraries (in this order)

* JDBC Driver 10.2.0.3
* Apache Ant
* JSP Runtime
* Ons.jar
* J2EE

10. Run the java client. Received the following result:

Env set
Got QCF
Got Q
End of test client
Producing message: Message 1
Producing message: Message 2
Producing message: Message 3
Producing message: Message 4
Producing message: Message 5

Note: I didn’t explain the ons configuration on the oc4j server side for my OC4J_Apps instance.

If you have applied any patches to your Application Server, then use all the libraries from the server (step 9).

How to create a simple JMS topic, publisher and subscriber in Oracle 10g

2007-10-11T14:48:00.000+10:00

In continuing from my previous updates about jms, this time I am writing a simple file persistence jms topic.

I am still using the same version OracleAS 10.1.3.2.0 as the JMS Provider.

1. Create the JMS destination. Run the following from $OH\j2ee\home:

java -jar admin_client.jar deployer:oc4j:opmn://sracanov-au.au.oracle.com:6007/JMS oc4jadmin welcome1 -addDestination -domain topic -name jmstopic -jndiLocation jms/MyJMSTopic -persistenceFile myjmstopic.txt

This will create a presistence file “myjmstopic.txt” to store the message. You can see the file by navigating to $OH\j2ee\<instance_name>\persistence\<instance_name>_<group_name>_1

[sracanov@sracanov-au D]$ java -jar admin_client.jar deployer:oc4j:opmn://sracanov-au.au.oracle.com:6007/JMS oc4jadmin welcome1 -addDestination -domain topic -name jmstopic -jndiLocation jms/MyJMSTopic -persistenceFile myjmstopic.txt
Command was successful

2. Create the JMS connection factory. Run the following from $OH\j2ee\home:

java -jar admin_client.jar deployer:oc4j:opmn://sracanov-au.au.oracle.com:6007/JMS oc4jadmin welcome1 -addJMSConnectionFactory -domain topic -jndiLocation jms/MyJMSTCF

[sracanov@sracanov-au D]$ java -jar admin_client.jar deployer:oc4j:opmn://sracanov-au.au.oracle.com:6007/JMS oc4jadmin welcome1 -addJMSConnectionFactory -domain topic -jndiLocation jms/MyJMSTCF

3. We need to create 3 java classes. Once again, I am using Jdev to create my classes. So first I will create a new application and project.

I will need to make sure the follow libraries are including in the project:
· J2EE
· Apache Ant

4. Now create the publisher client:


package project1;

import java.util.Hashtable;

import javax.jms.JMSException;
import javax.jms.MapMessage;
import javax.jms.Session;
import javax.jms.Topic;
import javax.jms.TopicConnection;
import javax.jms.TopicConnectionFactory;
import javax.jms.TopicPublisher;
import javax.jms.TopicSession;

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


public class MyTopicPublisher {

    public static void main(String[] args) {
        final String topicName = "jms/MyJMSTopic";
        final String topicConnectionFactoryName = "jms/MyJMSTCF";
        final String oc4juser = "oc4jadmin";
        final String oc4juserpassword = "welcome1";
        final String urlProvider = "opmn:ormi://";
        final String jmsProviderHost = "sracanov-au.au.oracle.com";
        final String colon = ":";
        final String opmnPort = "6007";
        final String oc4jinstance = "OC4J_JMS";
        final int NUMBER_OF_MESSAGES = 5;
        TopicConnection topicConnection = null;
        TopicConnectionFactory topicConnectionFactory = null;
        Context jndiContext = null;
        TopicSession topicSession = null;
        Topic topic = null;
        TopicPublisher topicPublisher = null;
        MapMessage message = null;

        /*
         * Set the environment for a connection to the OC4J instance
         */
        Hashtable env = new Hashtable();
        env.put(Context.INITIAL_CONTEXT_FACTORY, 
                "oracle.j2ee.rmi.RMIInitialContextFactory");
        env.put(Context.SECURITY_PRINCIPAL, oc4juser);
        env.put(Context.SECURITY_CREDENTIALS, oc4juserpassword);
        env.put(Context.PROVIDER_URL, 
                urlProvider + jmsProviderHost + colon + opmnPort + colon + 
                oc4jinstance + "/default");

        /*
         * Set the Context Object.
         * Lookup the Topic Connection Factory.
         * Lookup the JMS Destination.
         */
        try {
            jndiContext = new InitialContext(env);
            topicConnectionFactory = 
                    (TopicConnectionFactory)jndiContext.lookup(topicConnectionFactoryName);
            topic = (Topic)jndiContext.lookup(topicName);
        } catch (NamingException e) {
            System.out.println("Lookup failed: " + e.toString());
            System.exit(1);
        }

        /*
         * Create connection.
         * Create session from connection.
         * Create publisher.
         * Create text message.
         * Send messages.
         * Close connection.
         */
        try {
            topicConnection = topicConnectionFactory.createTopicConnection();
            topicSession = 
                    topicConnection.createTopicSession(false, Session.AUTO_ACKNOWLEDGE);
            topicPublisher = topicSession.createPublisher(topic);
            message = topicSession.createMapMessage();
            for (int i = 0; i < NUMBER_OF_MESSAGES; i++) {
                message.setStringProperty("from", "from@" + jmsProviderHost);
                message.setStringProperty("to", "to@" + jmsProviderHost);
                message.setStringProperty("subject", 
                                          "Topic Message " + (i + 1));
                message.setStringProperty("content", 
                                          "Message " + (i + 1) + " in Topic: \"" + 
                                          topicName + "\"");
                System.out.println("Publishing message: " + 
                                   message.getStringProperty("from"));
                System.out.println("Publishing message: " + 
                                   message.getStringProperty("to"));
                System.out.println("Publishing message: " + 
                                   message.getStringProperty("subject"));
                System.out.println("Publishing message: " + 
                                   message.getStringProperty("content"));
                topicPublisher.publish(message);
            }
        } catch (JMSException e) {
            System.out.println("Exception occurred: " + e.toString());
        } finally {
            if (topicConnection != null) {
                try {
                    topicConnection.close();
                } catch (JMSException e) {
                    System.out.println("Closing error: " + e.toString());
                }
            }
        }
    }
}

5. Now we need to create the message listener that will listen to meesages on map types:


package project1;

import javax.jms.JMSException;
import javax.jms.MapMessage;
import javax.jms.Message;
import javax.jms.MessageListener;

public class JMSTopicMapListener implements MessageListener {

    public void onMessage(Message message) {
        MapMessage msg = null;

        try {
            if (message instanceof MapMessage) {
                msg = (MapMessage)message;
                String from = msg.getStringProperty("from");
                String to = msg.getStringProperty("to");
                String subject = msg.getStringProperty("subject");
                String content = msg.getStringProperty("content");
                System.out.println("READING MESSAGE \n=============== \nFrom: " + 
                                   from + "\nTo: " + to + "\nSubject: " + 
                                   subject + "\nContent: " + content);
            } else {
                System.out.println("Wrong type");
            }
        } catch (JMSException e) {
            System.out.println("JMSException in onMessage(): " + e.toString());
        } catch (Throwable t) {
            System.out.println("Exception in onMessage():" + t.getMessage());
        }
    }
}

6. Create the subscriber client:


package project1;

import javax.jms.*;

import javax.naming.*;

import java.util.Hashtable;

public class MyTopicSubscriber {

    public static void main(String[] args) {
        final String topicName = "jms/MyJMSTopic";
        final String topicConnectionFactoryName = "jms/MyJMSTCF";
        final String oc4juser = "oc4jadmin";
        final String oc4juserpassword = "welcome1";
        final String urlProvider = "opmn:ormi://";
        final String jmsProviderHost = "sracanov-au.au.oracle.com";
        final String colon = ":";
        final String opmnPort = "6007";
        final String oc4jinstance = "OC4J_JMS";
        Context jndiContext = null;
        TopicConnectionFactory topicConnectionFactory = null;
        TopicConnection topicConnection = null;
        TopicSession topicSession = null;
        Topic topic = null;
        TopicSubscriber topicSubscriber = null;
        JMSTopicMapListener topicListener = null;

        /*
         * Set the environment for a connection to the OC4J instance
         */
        Hashtable env = new Hashtable();
        env.put(Context.INITIAL_CONTEXT_FACTORY, 
                "oracle.j2ee.rmi.RMIInitialContextFactory");
        env.put(Context.SECURITY_PRINCIPAL, oc4juser);
        env.put(Context.SECURITY_CREDENTIALS, oc4juserpassword);
        env.put(Context.PROVIDER_URL, 
                urlProvider + jmsProviderHost + colon + opmnPort + colon + 
                oc4jinstance + "/default");

        /*
         * Set the Context Object.
         * Lookup the Topic Connection Factory.
         * Lookup the JMS Destination.
         */
        try {
            jndiContext = new InitialContext(env);
            topicConnectionFactory = 
                    (TopicConnectionFactory)jndiContext.lookup(topicConnectionFactoryName);
            topic = (Topic)jndiContext.lookup(topicName);
        } catch (NamingException e) {
            System.out.println("Lookup failed: " + e.toString());
            System.exit(1);
        }

        /*
         * Create connection.
         * Create session from connection; false means session is
         * not transacted.
         * Create subscriber.
         * Register message listener (TextListener).
         * Receive text messages from topic.
         * Close connection.
         */
        try {
            topicConnection = topicConnectionFactory.createTopicConnection();
            topicSession = 
                    topicConnection.createTopicSession(false, Session.AUTO_ACKNOWLEDGE);
            topicSubscriber = topicSession.createSubscriber(topic);
            topicListener = new JMSTopicMapListener();
            topicSubscriber.setMessageListener(topicListener);
            topicConnection.start();
            System.out.println("Subscripted to topic: \"" + topicName + "\"");
            while (true) {
            }
        } catch (JMSException e) {
            System.out.println("Exception occurred: " + e.toString());
        } finally {
            if (topicConnection != null) {
                try {
                    topicConnection.close();
                } catch (JMSException e) {
                    System.out.println("Closing error: " + e.toString());
                }
            }
        }
    }
}

7. Ensure the follow Strings in the publisher and subcriber java class are set according to your environment:

final String topicName = "jms/MyJMSTopic";
final String topicConnectionFactoryName = "jms/MyJMSTCF";
final String oc4juser = "oc4jadmin";
final String oc4juserpassword = "welcome1";
final String urlProvider = "opmn:ormi://";
final String jmsProviderHost = "sracanov-au.au.oracle.com";
final String colon = ":";
final String opmnPort = "6007";
final String oc4jinstance = "JMS";

8. Run the subcriber class. The subscriber is waiting for message to be sent by the publisher. You will see the following message in the console.

Subscripted to topic: "jms/MyJMSTopic"

9. Run the publisher class. The following results in displayed in the publisher console:

Publishing message: from@sracanov-au.au.oracle.com
Publishing message: to@sracanov-au.au.oracle.com
Publishing message: Topic Message 1
Publishing message: Message 1 in Topic: "jms/MyJMSTopic"
Publishing message: from@sracanov-au.au.oracle.com
Publishing message: to@sracanov-au.au.oracle.com
Publishing message: Topic Message 2
Publishing message: Message 2 in Topic: "jms/MyJMSTopic"
Publishing message: from@sracanov-au.au.oracle.com
Publishing message: to@sracanov-au.au.oracle.com
Publishing message: Topic Message 3
Publishing message: Message 3 in Topic: "jms/MyJMSTopic"
Publishing message: from@sracanov-au.au.oracle.com
Publishing message: to@sracanov-au.au.oracle.com
Publishing message: Topic Message 4
Publishing message: Message 4 in Topic: "jms/MyJMSTopic"
Publishing message: from@sracanov-au.au.oracle.com
Publishing message: to@sracanov-au.au.oracle.com
Publishing message: Topic Message 5
Publishing message: Message 5 in Topic: "jms/MyJMSTopic"
Process exited with exit code 0.

10. Back in the subscriber console, the following message is displayed:

READING MESSAGE
===============
From: from@sracanov-au.au.oracle.com
To: to@sracanov-au.au.oracle.com
Subject: Topic Message 1
Content: Message 1 in Topic: "jms/MyJMSTopic"
READING MESSAGE
===============
From: from@sracanov-au.au.oracle.com
To: to@sracanov-au.au.oracle.com
Subject: Topic Message 2
Content: Message 2 in Topic: "jms/MyJMSTopic"
READING MESSAGE
===============
From: from@sracanov-au.au.oracle.com
To: to@sracanov-au.au.oracle.com
Subject: Topic Message 3
Content: Message 3 in Topic: "jms/MyJMSTopic"
READING MESSAGE
===============
From: from@sracanov-au.au.oracle.com
To: to@sracanov-au.au.oracle.com
Subject: Topic Message 4
Content: Message 4 in Topic: "jms/MyJMSTopic"
READING MESSAGE
===============
From: from@sracanov-au.au.oracle.com
To: to@sracanov-au.au.oracle.com
Subject: Topic Message 5
Content: Message 5 in Topic: "jms/MyJMSTopic"

Ensure you stop the subscriber client when done.

How to Send JMS Messages to one Oracle Application Server while reading it from another.

2007-10-02T14:01:00.000+10:00

The example demonstrates a queue on one server (Server A), while having an MDB on another server (Server B) that listens to the queue. It also includes a client that connects to Server B and reads the messages.

The example involves having 2 Oracle Application Server. I'm using Oracle AS 10.1.3.2.0.

The following outline the tasks to be complete:
a) Server A is used to send the message to.
Need to create a JMS destination and queue connection factory
The queue on Server A will be called jms/MyJMSQueue.
The queue connection factory Server A will be called jms/MyJMSQCF.
The client will connect to server A and send a message to the queue.

b) Server B is used to read the messages.
Need to create a queue connection factory destination that point to Server A and the JMS destination as in Server A.
The queue on Server B is the same as Server A.
The queue connection factory Server B will be called jms/MyRemoteQCF.
The MDB will be deployed to Server B. The MDB will listen to messages on Server A.
The client will connect to server B and read messages from the queue.

1. On Server A, create a JMS Destination.

[sracanov@sracanov-au2 JMS_SR]$ cd $ORACLE_HOME\j2ee\JMS

[sracanov@sracanov-au2]$ java -jar admin_client.jar deployer:oc4j:opmn://sracanov-au2.au.oracle.com:6005/JMS_SR oc4jadmin welcome1 -addDestination -domain queue -name jmsqueue -jndiLocation jms/MyJMSQueue

2. On Server B, create a JMS Destination. Same as in step 1.

[sracanov@sracanov-au2]$ java -jar admin_client.jar deployer:oc4j:opmn://sracanov-au.au.oracle.com:6007/JMS oc4jadmin welcome1 -addDestination -domain queue -name jmsqueue -jndiLocation jms/MyJMSQueue

3. On Server A, create a Connection Factory.

[sracanov@sracanov-au2 JMS_SR]$ java -jar admin_client.jar deployer:oc4j:opmn:// sracanov-au2.au.oracle.com:6005/JMS_SR oc4jadmin welcome1 -addJMSConnectionFactory -domain queue -jndiLocation jms/MyJMSQCF

4. On Server B, create a Connection Factory.

First determine the jms port for the OC4J instance you are using on Server A.

[oracle@sracanov-au2 JMS_SR]$ opmnctl status -l

…
OC4JGroup:default_group | OC4J:JMS_SR | 11196 | Alive | 857355163 | 253252 | 23:27:49 | jms:12604,ajp:12504,rmis:12704,rmi:12404
…

The instance in the example is using port 12604.

On Server B, run the following:

[sracanov@sracanov-au D]$ java -jar admin_client.jar deployer:oc4j:opmn:// sracanov-au.au.oracle.com:6007/JMS oc4jadmin welcome1 -addJMSConnectionFactory -jndiLocation jms/MyRemoteQCF -domain queue -host sracanov-au2.au.oracle.com -username oc4jadmin -password welcome1 -port 12604

5. Create the QueueProducer.

Use the example code from the previous blog to create the QueueProducer class. The QueueProducer will connection to Server A and send messages to the queue. You will need to make the following changes to the class.

…
String queueName = "jms/MyJMSQueue";
String queueConnectionFactoryName = "jms/MyJMSQCF";
…
And
…
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:JMS_SR/default");
…

Ensure you have set this to your Server A details, including opmn port, and oc4j instance.Use ‘opmnctl status –port’ to find your opmn port.

6) Run the QueueProducer class to send the message to the queue.

The is result:
Producing message: Message 1
Producing message: Message 2
Producing message: Message 3
Producing message: Message 4
Producing message: Message 5

7) From the Oracle EM, select your instance (in this example, ‘JMS_SR’) -> Administration -> JMS Destinations -> Memory Performance (on ‘jmsqueue’ in this example). Here you will see you messages in the queue.

8) Create the QueueConsumer.

Use the example code from the previous blog to create the QueueConsumer class. The QueueConsumer will connection to Server B and read messages from the queue on Server A. You will need to make the following changes to the class.

…
String queueName = "jms/MyJMSQueue";
String queueConnectionFactoryName = " jms/MyRemoteQCF";
…
And
…
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-au.au.oracle.com:6007:JMS/default");
…

Ensure you have set this to your Server B details, including opmn port, and oc4j instance.

9) Now run the QueueConsumer class that will connect to Server B and receive the messages from the queue on Server A.

This is the result:

Consuming message: Message 1
Consuming message: Message 2
Consuming message: Message 3
Consuming message: Message 4
Consuming message: Message 5

10) Create an MBD and deploy to Server B that will listen and print the messages in the queue on Server A.

a.Create the MessageDrivenEJBBean as follows:


package project3;

import javax.ejb.EJBException;
import javax.ejb.MessageDrivenBean;
import javax.ejb.MessageDrivenContext;

import javax.jms.JMSException;
import javax.jms.Message;
import javax.jms.MessageListener;
import javax.jms.TextMessage;

public class MessageDrivenEJBBean implements MessageDrivenBean, 
                                             MessageListener {
    private MessageDrivenContext _context;

    public void ejbCreate() {
    }

    public void setMessageDrivenContext(MessageDrivenContext context) throws EJBException {
        _context = context;
    }

    public void ejbRemove() throws EJBException {
    }

    public void onMessage(Message message) {
        TextMessage msg = null;

        try {
            if (message instanceof TextMessage) {
                msg = (TextMessage)message;
                System.out.println("Reading message: " + msg.getText());
            } else {
                System.out.println("Wrong Message type");
            }
        } catch (JMSException e) {
            System.out.println("JMSException in onMessage(): " + e.toString());
        } catch (Throwable t) {
            System.out.println("Exception in onMessage():" + t.getMessage());
        }
    }
}

b.Edit the ejb-jar.xml as follows:


<?xml version = '1.0' encoding = 'windows-1252'?>
<ejb-jar xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee 
 http://java.sun.com/xml/ns/j2ee/ejb-jar_2_1.xsd" version="2.1" 
 xmlns="http://java.sun.com/xml/ns/j2ee">
  <enterprise-beans>
    <message-driven>
      <description>Message Driven Bean</description>
      <display-name>MessageDrivenEJB</display-name>
      <ejb-name>MessageDrivenEJB</ejb-name>
      <ejb-class>project3.MessageDrivenEJBBean</ejb-class>
      <messaging-type>javax.jms.MessageListener</messaging-type>
      <transaction-type>Container
      <message-destination-type>javax.jms.Queue</message-destination-type>
      <activation-config>
           <activation-config-property>
             <activation-config-property-name>destinationType</activation-config-property-name>
             <activation-config-property-value>javax.jms.Queue</activation-config-property-value>
           </activation-config-property>
           <activation-config-property>
             <activation-config-property-name>acknowledgeMode</activation-config-property-name>
             <activation-config-property-value>Auto-acknowledge</activation-config-property-value>
           </activation-config-property>
           <activation-config-property>
             <activation-config-property-name>subscriptionDurability</activation-config-property-name>
             <activation-config-property-value>NonDurable</activation-config-property-value>
           </activation-config-property>
         </activation-config>
    </message-driven>
  </enterprise-beans>
</ejb-jar>

c.Edit the orion-ejb-jar.xml as follows:


<?xml version = '1.0' encoding = 'windows-1252'?>
<orion-ejb-jar xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:noNamespaceSchemaLocation="http://xmlns.oracle.com/oracleas/schema/orion-ejb-jar-10_0.xsd" 
   schema-major-version="10" schema-minor-version="0">
    <enterprise-beans>
        <message-driven-deployment name="MessageDrivenEJB"
                                   connection-factory-location="jms/MyRemoteQCF"
                                   destination-location="jms/MyJMSQueue"/>
    </enterprise-beans>
    <assembly-descriptor>
        <default-method-access>
            <security-role-mapping/>
        </default-method-access>
    </assembly-descriptor>
</orion-ejb-jar>

d.Deploy MDB to Server B.

e.Run the QueueProduce to send some messages to the queue.

f.Open the file $ORACLE_HOME\opmn\log\default_group~~default_group~1.log

To view the result:

07/10/02 08:52:24 Reading message: Message 1
07/10/02 08:52:24 Reading message: Message 2
07/10/02 08:52:24 Reading message: Message 3
07/10/02 08:52:24 Reading message: Message 4
07/10/02 08:52:24 Reading message: Message 5
07/10/02 08:52:24 Wrong Message type

How to create a simple JMS queue, producer and consumer in Oracle 10g

2007-09-28T15:19:00.001+10:00

This demonstration shows how to create a simple jms producer and consumser using Oracle 10g as the JMS Provider.

I am using Oracle AS 10.1.3.2.0 and JDev 10.1.3.2.0 in this example. This article assumes you have installed and configure the environment for both applications.

1. Once you have your application server running, first create a new oc4j instance for the application and start the instance.

[sracanov@sracanov-au D]$ createinstance -instanceName JMS
Creating OC4J instance "JMS"...
Set OC4J administrator password for "JMS" (password text will not be displayed as it is entered):
Enter password:
Confirm password:
A new OC4J instance "JMS" is created in the group "default_group".

[sracanov@sracanov-au D]$ opmnctl startproc process-type=JMS
opmnctl: starting opmn managed processes...

2. Create a JMS Destination.

Our new JMS instance will already have some default JMS destination configured that we could use. See http://download-west.oracle.com/docs/cd/B25221_04/web.1013/b14427/jms.htm#i1084454

However in this example we will create our own in-memory JMS destination.

First let's determine the opmn port:

[sracanov@sracanov-au D]$ opmnctl status -port
sracanov-au:6007

Now create a new JMS destination:

[sracanov@sracanov-au D]$ cd /D %ORACLE_HOME\j2ee\JMS

[sracanov@sracanov-au D]$ java -jar admin_client.jar deployer:oc4j:opmn://localhost:6007/JMS oc4jadmin welcome1 -addDestination -domain queue -name myfirstqueue -jndiLocation jms/MyFirstQueue

3. Create a Connection Factory.

Our new JMS instance will already have some default connection factory configured that we could use. See http://download-west.oracle.com/docs/cd/B25221_04/web.1013/b14427/jms.htm#i1084454

However in this example we will create our connection factory
[sracanov@sracanov-au D]$ java -jar admin_client.jar deployer:oc4j:opmn://localhost:6007/JMS oc4jadmin welcome1 -addJMSConnectionFactory -domain queue -jndiLocation jms/MyFirstQCF

4. In Jdev, now create a simple producer and consumer.

a) Create a new Application and Project.

b) Create a simple producer class that will send 5 messages to the queue.


package project1;

import javax.jms.*;
import javax.naming.*;
import java.util.Hashtable;

public class QueueProducer {

    public static void main(String[] args) {
        String                  queueName = "jms/MyFirstQueue";
        String                  queueConnectionFactoryName = "jms/MyFirstQCF";
        Context                 jndiContext = null;
        QueueConnectionFactory  queueConnectionFactory = null;
        QueueConnection         queueConnection = null;
        QueueSession            queueSession = null;
        Queue                   queue = null;
        QueueSender             queueSender = null;
        TextMessage             message = null;
        int                     noMessages = 5;        
       
        /*
         * Set the environment for a connection to the OC4J instance 
         */
        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://localhost:6007:JMS/default");
        
        /*
         * Set the Context Object.
         * Lookup the Queue Connection Factory.
         * Lookup the JMS Destination.
         */        
        try {
            jndiContext = new InitialContext(env);
            queueConnectionFactory = (QueueConnectionFactory)
                jndiContext.lookup(queueConnectionFactoryName);
            queue = (Queue) jndiContext.lookup(queueName);
        } catch (NamingException e) {
            System.out.println("JNDI lookup failed: " +
                e.toString());
            System.exit(1);
        }

        /*
         * Create connection.
         * Create session from connection.
         * Create sender.
         * Create text message.
         * Send messages.
         * Send non text message to end text messages.
         * Close connection.
         */
        try {
            queueConnection = 
                queueConnectionFactory.createQueueConnection();
            queueSession = 
                queueConnection.createQueueSession(false, 
                    Session.AUTO_ACKNOWLEDGE);
            queueSender = queueSession.createSender(queue);
            message = queueSession.createTextMessage();
            for (int i = 0; i < noMessages; i++) {
                message.setText("Message " + (i + 1));
                System.out.println("Producing message: " + 
                    message.getText());
                queueSender.send(message);
            }
          queueSender.send(queueSession.createBytesMessage());;
        } catch (JMSException e) {
            System.out.println("Exception occurred: " + 
                e.toString());
        } finally {
            if (queueConnection != null) {
                try {
                    queueConnection.close();
                } catch (JMSException e) {
                    System.out.println("Closing error: " + e.toString());
                }
            }
        }
    }
}

c) Create a simple consumer class that will read all text messages from the queue and end once it reads a non text message:


package project1;

import java.util.Hashtable;

import javax.jms.*;

import javax.naming.*;

public class QueueConsumer {

    public static void main(String[] args) {
        String queueName = "jms/MyFirstQueue";
        String queueConnectionFactoryName = "jms/MyFirstQCF";
        Context jndiContext = null;
        QueueConnectionFactory queueConnectionFactory = null;
        QueueConnection queueConnection = null;
        QueueSession queueSession = null;
        Queue queue = null;
        QueueReceiver queueReceiver = null;
        TextMessage message = null;

        /*
             * Set the environment for a connection to the OC4J instance
             */
        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://localhost:6007:JMS/default");

        /*
              * Set the Context Object.
              * Lookup the Queue Connection Factory.
              * Lookup the JMS Destination.
              */
        try {
            jndiContext = new InitialContext(env);
            queueConnectionFactory = 
                    (QueueConnectionFactory)jndiContext.lookup(queueConnectionFactoryName);
            queue = (Queue)jndiContext.lookup(queueName);
        } catch (NamingException e) {
            System.out.println("JNDI lookup failed: " + e.toString());
            System.exit(1);
        }

        /*
             * Create connection.
             * Create session from connection.
             * Create receiver.
             * Receive all text messages from queue until non text message is receivied
             * Close connection.
             */
        try {
            queueConnection = queueConnectionFactory.createQueueConnection();
            queueSession = 
                    queueConnection.createQueueSession(false, Session.AUTO_ACKNOWLEDGE);
            queueReceiver = queueSession.createReceiver(queue);
            queueConnection.start();
            while (true) {
                Message m = queueReceiver.receive(1);
                if (m != null) {
                    if (m instanceof TextMessage) {
                        message = (TextMessage)m;
                        System.out.println("Consuming message: " + 
                                           message.getText());
                    } else {
                        break;
                    }
                }
            }
        } catch (JMSException e) {
            System.out.println("Exception occurred: " + e.toString());
        } finally {
            if (queueConnection != null) {
                try {
                    queueConnection.close();
                } catch (JMSException e) {
                    System.out.println("Closing error: " + e.toString());
                }
            }
        }
    }
}

d) Add the following libraries to the project:
* J2EE
* Apache Ant

5) Run the QueueProducer class to send the message to the queue.

The is result:
Producing message: Message 1
Producing message: Message 2
Producing message: Message 3
Producing message: Message 4
Producing message: Message 5

6) Run the QueueConsumer class to receive the message from the queue.

This is the result:

Consuming message: Message 1
Consuming message: Message 2
Consuming message: Message 3
Consuming message: Message 4
Consuming message: Message 5