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.
Published
Categorized as Java

By Greg Luck

As Terracotta’s CTO, Greg (@gregrluck) is entrusted with understanding market and technology forces and the business drivers that impact Terracotta’s product innovation and customer success. He helps shape company and technology strategy and designs many of the features in Terracotta’s products. Greg came to Terracotta on the acquisition of the popular caching project Ehcache which he founded in 2003. Prior to joining Terracotta, Greg served as Chief Architect at Australian online travel giant Wotif.com. He also served as a lead consultant for ThoughtWorks on accounts in the United States and Australia, was CIO at Virgin Blue, Tempo Services, Stamford Hotels and Resorts and Australian Resorts and spent seven years as a Chartered Accountant in KPMG’s small business and insolvency divisions. He is a regular speaker at conferences and contributor of articles to the technical press.

1 comment

  1. I am working with the beloved TCA as well. Thanks for taking the extra step to post that. I went through the same process to figure that out a couple of months back. It makes me wonder – do you think it is supported by Oracle in any way to use the java api outside of the financials implementation?
    The next disappointment that you find is that the api only works with a OracleConnection instead of the generic Connection. It just makes things ugly.

Comments are closed.