Oracle Streams and JMS: A dead combination?

Oracle Streams is supposedly a JMS implementation. After a month of waiting on Oracle support as to why some of the basic examples did not work I got:
Note:154777.1
Subject: Unable to Create Receiver on a Raw Queue
Type: PROBLEM
Status: PUBLISHED
Content Type: TEXT/X-HTML
Creation Date: 20-JUL-2001
Last Revision Date: 13-AUG-2001
* fact: Oracle Server – Enterprise Edition 8.1.7
* fact: Advanced Queuing (QUEUE)
* symptom: Runtime error using the Java Messaging Service (JMS)
* symptom: java.lang.NullPointerException
* symptom: oracle.jms.AQjmsSession.createReceiver(javax.jms.Queue)
* cause: Access to queues with a RAW payload via the JMS API is not supported.
fix:
1. Use the native Java API – oracle.AQ.* to access the RAW queue.
2. Use one of the predefined payload types for JMS. In this case SYS.AQ$
_JMS_TEXT_MESSAGE was used.
For more information on JMS and the Java AQ API, refer to :
Oracle8i Application Developer’s Guide – Advanced Queuing.
The native API is not deprecated. So all of this adds up to Oracle Streams and JMS being a dead combination on Oracle.

The World is Flat: A Book Review

I greatly enjoyed reading Thomas L Friedman’s “The World is flat”.

In the context of international trade and also competition, the term “level playing field” is often bandied around. A “level playing field” is one where each combatant has the same rules and no specific discrimination against them.

What happens if a lot of countries implement level playing fields? The whole world becomes a level playing field – the world becomes flat.

What happens after that, according to classical economic theory, is that the law of competitive advantage applies. If one country has a natural advantage over another in the production of a particular good or service, then they will be able to charge a lower price and production will move to that country.

The thesis of Friedman’s book is that the theoretical level playing fields and competitive advantage, have become reality. He argues persuasively, citing statistics and anecdotal material from interviews with key players up to the end of 2004.

The IT industry, in particular is exemplified as a flattened industry. He portrays the Americans losing jobs to Indians, in danger of losing their jobs to the 300 million Chinese learning English. Wherever smart software engineers are found, the almost zero cost of the Internet has enabled them to compete on the playing field.

The zero Internet cost alludes to some of the key enablers of the flat world. OpenSource is cited as one. I like the way he describes OpenSource as Self-Organizing Collaborative Communities. I am beginning to think that collaborating on software that is free in a GNU sense, is almost an emergent property of the Internet.

For anyone who has lived through the IT revolution of the last 20 years, Friedman appears a little naive at times. He seems to have fallen into the clutches of Craig Mundie, CTO of Micro$oft. He rightly considers the PC a flattener. He thinks Windows was way more significant than I would ascribe. My view is, that far from being a flattener, Windows stymied innovation, and held up the emergence of the world we now have by a decade.

In the end, you have to love one of the flatteners. The date 9 September 1995. Don’t remember? What started the .com boom? Still don’t remember? Read the book.

Running/Debugging JBoss within IntelliJ IDEA

I usually use Orion, and find debugging it from within IntelliJ is great for debugging. You can set breakpoints in EJBs or other server side code, start it with the Debug button and then wait for your breakpoints to be reached. You can then evaluate expressions, recompile classes and so on.

I set out to do the same thing with JBoss, which I am using for my current project and got it working. It works even better than Orion, because Orion’s classes are obfuscated, whereas JBoss’ are not.

Step 1 Create a JBoss module

Open File -> Settings -> Paths and create a new Java module as shown below:



(Click to expand)

Step 2 – Add a new Run/Debug Configuration

Add a configuration as shown below.



(Click to expand)

Note that the VM parameters do not show up fully in the screen shot. Copy and past the following:

-Xms128m -Xmx512m -Dprogram.name=run.sh -Djava.endorsed.dirs=/convergence/configunit/jboss/lib/endorsed

Using it

  1. Make sure that JBoss is not running on your machine
  2. Open Run -> Debug and select the Application tab. Select JBoss and then click on the Debug button. You should see JBoss’ start up messages appearing in the IDEA console.

Update: I had to set this up on a new project and got some errors. After playing with I discovered that it is important to exclude any other modules and classes from the JBoss module. Then it works.

Integrating with Oracle 11i: Figuring Out the APIs

Introduction

I am working on JMS integration with Oracle 11i. It looks like multiple approaches are possible. I went with implementing an adapter in JBoss using a Message Driven Bean receiving messages from a topic and then using Oracle 11i’s public stored procedure or other APIs to update data.

Sound simple? It should be. A lack of Oracle developer documentation turns this into a nightmare. The intent here is to remedy, in a small way, that lack of documentation.

The techniques are illustrated with an exam ple. It is using getOrganizationRec in Oracle’s HZ_PARTY_V2PUB package.

The APIs

The HZ_PARTY_V2PUB Package

This is the PL/SQL API used for integrating with Oracle Financials. Documentation about these API’s and record structures can be found in Chapter 3 of the Oracle Trading Community Architecture Technical Implementation Guide located here :
http://download-west.oracle.com/docs/cd/B16981_02/current/acrobat/115hztig.pdf.

get_organization_rec, shown below, uses a ORGANIZATION_REC_TYPE.

PROCEDURE get_organization_rec (
p_init_msg_list                    IN      VARCHAR2 := FND_API.G_FALSE,
p_party_id                         IN      NUMBER,
p_content_source_type              IN      VARCHAR2 := G_MISS_CONTENT_SOURCE_TYPE,
x_organization_rec                 OUT     NOCOPY ORGANIZATION_REC_TYPE,
x_return_status                    OUT NOCOPY     VARCHAR2,
x_msg_count                        OUT NOCOPY     NUMBER,
x_msg_data                         OUT NOCOPY     VARCHAR2
);

An ORGANIZATION_REC_TYPE is a RECORD. You cannot use pl/sql RECORD types in jdbc calls: PL/SQL TABLE, BOOLEAN, and RECORD Types. You’ll have to create a sql type for the record, then you can use it using a jdbc struct. Note that this limitation is present in both the thin and oci8 versions of Oracle’s drivers.

See:

  1. http://download-west.oracle.com/docs/cd/B10501_01/java.920/a96654/ref.htm#1005988

  2. http://download-west.oracle.com/docs/cd/B10501_01/java.920/a96654/oraoot.htm#1039477
TYPE organization_rec_type IS RECORD(
organization_name               VARCHAR2(360),
duns_number_c                   VARCHAR2(30),
enquiry_duns                    VARCHAR2(15),
ceo_name                        VARCHAR2(240),
ceo_title                       VARCHAR2(240),
principal_name                  VARCHAR2(240),
principal_title                 VARCHAR2(240),
legal_status                    VARCHAR2(30),
control_yr                      NUMBER,
employees_total                 NUMBER,
hq_branch_ind                   VARCHAR2(30),
branch_flag                     VARCHAR2(1),
oob_ind                         VARCHAR2(30),
line_of_business                VARCHAR2(240),
cong_dist_code                  VARCHAR2(2),
sic_code                        VARCHAR2(30),
import_ind                      VARCHAR2(30),
export_ind                      VARCHAR2(30),
labor_surplus_ind               VARCHAR2(30),
debarment_ind                   VARCHAR2(30),
minority_owned_ind              VARCHAR2(30),
minority_owned_type             VARCHAR2(30),
woman_owned_ind                 VARCHAR2(30),
disadv_8a_ind                   VARCHAR2(30),
small_bus_ind                   VARCHAR2(30),
rent_own_ind                    VARCHAR2(30),
debarments_count                NUMBER,
debarments_date                 DATE,
failure_score                   VARCHAR2(30),
failure_score_natnl_percentile  NUMBER,
failure_score_override_code     VARCHAR2(30),
failure_score_commentary        VARCHAR2(30),
global_failure_score            VARCHAR2(5),
db_rating                       VARCHAR2(5),
credit_score                    VARCHAR2(30),
credit_score_commentary         VARCHAR2(30),
paydex_score                    VARCHAR2(3),
paydex_three_months_ago         VARCHAR2(3),
paydex_norm                     VARCHAR2(3),
best_time_contact_begin         DATE,
best_time_contact_end           DATE,
organization_name_phonetic      VARCHAR2(320),
tax_reference                   VARCHAR2(50),
gsa_indicator_flag              VARCHAR2(1),
jgzz_fiscal_code                VARCHAR2(20),
analysis_fy                     VARCHAR2(5),
fiscal_yearend_month            VARCHAR2(30),
curr_fy_potential_revenue       NUMBER,
next_fy_potential_revenue       NUMBER,
year_established                NUMBER,
mission_statement               VARCHAR2(2000),
organization_type               VARCHAR2(30),
business_scope                  VARCHAR2(20),
corporation_class               VARCHAR2(60),
known_as                        VARCHAR2(240),
known_as2                       VARCHAR2(240),
known_as3                       VARCHAR2(240),
known_as4                       VARCHAR2(240),
known_as5                       VARCHAR2(240),
local_bus_iden_type             VARCHAR2(30),
local_bus_identifier            VARCHAR2(60),
pref_functional_currency        VARCHAR2(30),
registration_type               VARCHAR2(30),
total_employees_text            VARCHAR2(60),
total_employees_ind             VARCHAR2(30),
total_emp_est_ind               VARCHAR2(30),
total_emp_min_ind               VARCHAR2(30),
parent_sub_ind                  VARCHAR2(30),
incorp_year                     NUMBER,
sic_code_type                   VARCHAR2(30),
public_private_ownership_flag   VARCHAR2(1),
internal_flag                   VARCHAR2(30),
local_activity_code_type        VARCHAR2(30),
local_activity_code             VARCHAR2(30),
emp_at_primary_adr              VARCHAR2(10),
emp_at_primary_adr_text         VARCHAR2(12),
emp_at_primary_adr_est_ind      VARCHAR2(30),
emp_at_primary_adr_min_ind      VARCHAR2(30),
high_credit                     NUMBER,
avg_high_credit                 NUMBER,
total_payments                  NUMBER,
credit_score_class              NUMBER,
credit_score_natl_percentile    NUMBER,
credit_score_incd_default       NUMBER,
credit_score_age                NUMBER,
credit_score_date               DATE,
credit_score_commentary2        VARCHAR2(30),
credit_score_commentary3        VARCHAR2(30),
credit_score_commentary4        VARCHAR2(30),
credit_score_commentary5        VARCHAR2(30),
credit_score_commentary6        VARCHAR2(30),
credit_score_commentary7        VARCHAR2(30),
credit_score_commentary8        VARCHAR2(30),
credit_score_commentary9        VARCHAR2(30),
credit_score_commentary10       VARCHAR2(30),
failure_score_class             NUMBER,
failure_score_incd_default      NUMBER,
failure_score_age               NUMBER,
failure_score_date              DATE,
failure_score_commentary2       VARCHAR2(30),
failure_score_commentary3       VARCHAR2(30),
failure_score_commentary4       VARCHAR2(30),
failure_score_commentary5       VARCHAR2(30),
failure_score_commentary6       VARCHAR2(30),
failure_score_commentary7       VARCHAR2(30),
failure_score_commentary8       VARCHAR2(30),
failure_score_commentary9       VARCHAR2(30),
failure_score_commentary10      VARCHAR2(30),
maximum_credit_recommendation   NUMBER,
maximum_credit_currency_code    VARCHAR2(240),
displayed_duns_party_id         NUMBER,
content_source_type             VARCHAR2(30) := G_MISS_CONTENT_SOURCE_TYPE,
content_source_number           VARCHAR2(30),
attribute_category              VARCHAR2(30),
attribute1                      VARCHAR2(150),
attribute2                      VARCHAR2(150),
attribute3                      VARCHAR2(150),
attribute4                      VARCHAR2(150),
attribute5                      VARCHAR2(150),
attribute6                      VARCHAR2(150),
attribute7                      VARCHAR2(150),
attribute8                      VARCHAR2(150),
attribute9                      VARCHAR2(150),
attribute10                     VARCHAR2(150),
attribute11                     VARCHAR2(150),
attribute12                     VARCHAR2(150),
attribute13                     VARCHAR2(150),
attribute14                     VARCHAR2(150),
attribute15                     VARCHAR2(150),
attribute16                     VARCHAR2(150),
attribute17                     VARCHAR2(150),
attribute18                     VARCHAR2(150),
attribute19                     VARCHAR2(150),
attribute20                     VARCHAR2(150),
created_by_module               VARCHAR2(150),
application_id                  NUMBER,
do_not_confuse_with             VARCHAR2(255),
actual_content_source           VARCHAR2(30) := G_SST_SOURCE_TYPE,
party_rec                       PARTY_REC_TYPE:= G_MISS_PARTY_REC
);

The HZ_PARTY_V2PUB_JW Package

This package is the same as the HZ_PARTY_V2PUB package except that the procedure signatures are changed to work with JDBC. The “_JW” suffix stands for Java Wrapper.

The problem with this API is that it is extremely verbose, for our purposes. The get_organization_rec has about 130 parameters.

The oracle.apps.ar.hz.v2api.HzPartyV2Pub Package

This is a Java package. We have created a jar called hz.jar. Oracle distributes the Java classes as classes in an exploded directory structure under the $OA_JAVA directory of an Oracle Financials installation. We use the smike dev environment for obtaining the classes.

To recreate the jar:

  1. login to a server with Oracle 11i installed on it
  2. cd to $OA_JAVA
  3. jar -cvf hz.jar oracle/apps/ar/hz

Inspecting the classes shows that the use the HZ_PARTY_V2PUB_JW. They provide a convenient wrapper.

Summary

The steps to figuring out how to use a PL/SQL package in Oracle 11i are:

  1. Find the PL/SQL package and procedure using Oracle Enterprise Manager or similar
  2. Check the parameters to see if any are unsupported by the Oracle JDBC driver e.g. PL/SQL TABLE, BOOLEAN, and RECORD Types. If there are none go ahead and use it.
  3. If there are unsupported parameters Oracle should have a PL/SQL package of the same name with a “_JW” appended to the end. This is the Java Wrapper package. It will contain the same PL/SQL functions and procedures but with exploded parameters supported by JDBC.
  4. You can use the PL/SQL directly or look for a convenience Java API. To do so do a recursive grep under $OA_JAVA. When you find a hit, create a jar of that package and add it your classpath. You should find a Java class which calls the PL/SQL.

The Horror of Oracle’s OCI driver

It has been about two years since I last used Oracle’s OCI driver. I remember it was painful to get going. I wish I had maintained a blog then, because I would have written the steps down and avoided a few hours of pain today. Which is why I am going to document what needs to be done. Hopefully some other poor soul can avoid the experience.

Step by step

  1. Download the instance client and unzip it into a directory. Let’s assume that directory is /u01/instantclient.
  2. Copy the exact ojdbc14.jar in /u01/instantclient to your project’s lib directory. The exact jar, and not a similar one needs to be in your classpath when you execute your java code.
  3. In your profile, say .bash_profile, add
    export LD_LIBRARY_PATH=/u01/instantclient

    . This lets Oracle’s shared objects know where to find its other shared objects.

  4. Add
    -Djava.library.path=/u01/instantclient

    to your Java launch command. This tells java to add the path to its native library search path.

  5. Gotchas

    Oracle is silent on two of the three requirements. They mention the LD_LIBRARY_PATH but not the other two.

    The error messages when something goes wrong are not very informative.

HOWTO: Java 5 on Mac OS X 10.4 Tiger

Mac OS X 10.4 aka Tiger does not come with Java 5. However Java 5 was released as a download the day OS X 10.4 was released: 29 April 2005. This post is about getting and using Java 5 on Mac OS X 10.4.

Getting It

You can download Java 5 for Mac OS X 10.4 here.

Configuration

After installation Java 1.4.2 remains the default. You can change the default for Applications and Applets to Java 5 through the new utility:

/Applications/Utilities/Java/J2SE 5.0/Java Preferences

With these changes command line invocation remains at Java 1.4.2. I changed this by making some changes to my shell profile. I use bash, so I changed my .bash_profile in my home directory as follows:

export JAVA_HOME=/System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home
export ANT_HOME=~/apache-ant-1.6.2
export PATH=PATH=$JAVA_HOME/bin:$ANT_HOME/bin:$PATH

To get the new profile to take immediate effect, I typed

. .bash_profile

.

IntelliJ Idea on Java 5

IntelliJ Idea 4.5 requires Java 1.4.2. Changing the application default to Java 5 (using the new Java 5 preferences utility discussed above) breaks Idea.

Fortunately, the latest Idea 5.0 beta now supports Java 1.4.2 and Java 5. Get it from http://intellij.net/eap (free registration required).

Performance

To test performance, I used JPam, an open source Java-PAM bridge I have been working on lately. The default Ant target uses javac, javah, jar, gcc, javadoc and runs many jUnit tests, some of which are concurrency tests using 50 threads.

I ran three tests using each JDK and got similar minimum, maximum and mean run times. In this test at least, there was no significant differences to report.

More Information

Here is a list of useful Apple resources for Java 5:

  1. Mailing List Archive – java-dev
  2. Java Development Guide
  3. Java Release Notes

Conclusion

I am happy to have Java 5 on Mac OS X. It seems to be solid and ready for use. Now that it is here it will simplify the lives of tool makers who were holding of supporting Java 5.