DBMS Change Notification

This is about DBMS Change Notification, enhanced feature introduced from version 10g.

DBMS Change Notification can only be used for tables those are having length (including schema name) less than 30 characters. For example, Oracle Project module PA.PA_STD_BILL_RATE_SCHEDULES_ALL table length is more than 30, so this table can not be used for DBMS Change Notification.

Also DBMS Change Notification treats particular change as bulk if numbers of changed records are more than 80. In this case ROWID will be empty. So we won’t know the affected records.

How BPEL OA Adapter connect to Oracle EBS?

In this article, I listed the steps to setup the connection factory for OA Adapter to connect to EBS. There are two major portions in this setup.

1. JDBC Resource Setups
2. AppsAdapter (ie OA Adapter) Connection Factory Setups

JDBC setups need to be completed first…

1. Get Database TNS Entry and APPS schema password
2. Login as Application Server Enterprise Manager (eg: oc4jadmin) and click oc4j_soa (default OC4J Instance)
3. Go to Administrator tab and click JDBC Resources Go to Task icon
4. Create new Connection Pool by giving name and URL. Follow the URL syntax carefully
5. Enter Credential user as APPS and its password and then test the connection
6. If test connection is success then create Data Source.
7. Select Data Source Type as Managed Data Source. Enter data source name and JNDI location. JNDI Location can be start with jdbc/NAME to maintain the consistency.
8. Note down the JNDI Name, it will be used while defining Connection Factory.
9. Select the above created Connection Pool and test the connection.

Now we can to use this JDBC Resource in OA Adapter Connection Factory Setups.

1. Go to Applications tab in oc4j_soa OC4J Instance
2. Select view as Modules (Default is Applications)
3. Select AppsAdapter Module and go to Connection Factories tab
4. Create new Connection Factory. Give meaningful JNDI Location. It can start with eis/Apps/NAME. For example eis/Apps/EBS.
5. Enter xADataSourceName as JDBC JNDI Name just created.

Now OA Adapter can be used in BPEL Process. Make sure same JNDI Name is entered when Partner Link is created for OA Adapter in JDeveloper.

Java Stored Procedure in Oracle

Though we can write business logic, data validation, exception handling in pl/sql, still we need to use languages like C, Pro*C, Java for complex and system programming in Oracle.

Recently we had a requirement to convert the Images and store it into Oracle. As we know pl/sql won’t support graphics I used Java program for image conversion and used it in my plsql.

Java program should have public method with required parameters and return statement if needed. For example, let as take addition of two numbers. Two numbers should be input parameters and the method should return sum of these two.

Compile the java file and keep the class file in folder/directory registered in Oracle Database. We can create new directory if needed using following syntax.

CREATE DIRECTORY Bfile_dir AS '/usr/temp';

Store the class file into database using following syntax.

CREATE OR REPLACE JAVA CLASS USING BFILE (Bfile_dir, 'Addition.class');

loadjava executable can also be used instead of the above statement. Now java class is available to use. Create function/package to use in pl/sql.

CREATE OR REPLACE FUNCTION add_numbers(no1 NUMBER, no2 NUMBER) RETURN NUMBER AS
LANGUAGE JAVA NAME 'Addition.get(int,int) return int';

I used Oracle 10g Release 2. Refer Oracle Database Java Developer’s Guide for more details.

Single BPEL Process for Multiple Operations using Pick Activity

Usually we will create individual BPEL Processes for each Operation in either synchronous or asynchronous. This blog will help to build single BPEL Process for more than one Operation for exceptional cases like requesting system might have restricted number of adapters or ports to connect to other systems. This can be done using Pick activity.

1. Get target system WSDL file
2. Create Empty Project
3. Create Partner Link for the WSDL file
4. Select Pick Activity and drag and drop into Process
5. Don’t forget to select Create Instance check box in Pick activity
6. Add OnMessage Branch as much as the number of Operations as you have
7. Go to any one OnMessage. Select Partner Link created, select the Operation and create Local Request Variable.
8. Add activities and partner links you wanted for the selected operation
   Finally add Reply/Callback activity based on type of Operation.
9. Repeat this for all remaining Operations
10. Deploy into BPEL Server
11. Initiate the process from where ever you want. Note that this process can not be initiated from 10g BPEL Console. May be this is a bug.