Creating a JDBC Connection Pool for Oracle (Using the BPEL Service Engine in a Project)

Using the BPEL Service Engine in a Project

Creating a JDBC Connection Pool for Oracle

The following procedure provides the steps for creating an Oracle JDBC connection pool. Before you create your JDBC connection for Oracle, you must create an Oracle user (with required permissions) and tablespace for BPEL Service Engine persistence.

To create an Oracle user

Log into Oracle as sysdba (from SQLPlus, connect using connect sys/manager@machine-name as sysdba).

Execute the following script with default values:

Click here to download the annotated scripts.

CREATE TABLESPACE bpelsedb

DATAFILE 'bpelsedb.dat' SIZE 512M REUSE

AUTOEXTEND ON NEXT 2048M MAXSIZE UNLIMITED;

CREATE USER bpelse_user

IDENTIFIED BY bpelse_user

DEFAULT TABLESPACE bpelsedb

QUOTA UNLIMITED ON bpelsedb

TEMPORARY TABLESPACE temp

QUOTA 0M ON system;

GRANT CREATE session to bpelse_user;

GRANT CREATE table to bpelse_user;

GRANT CREATE procedure to bpelse_user;

GRANT select on sys.dba_pending_transactions to bpelse_user;

GRANT select on sys.pending_trans$ to bpelse_user;

GRANT select on sys.dba_2pc_pending to bpelse_user;

GRANT execute on sys.dbms_system to bpelse_user;

GRANT select on SYS.dba_2pc_neighbors to bpelse_user;

GRANT force any transaction to bpelse_user;

Note –

You can also connect using NetBeans IDE or a number of SQL clients by entering the username sys as sysdba. The password should be the same as that of the system user. You can also change the user, tablespace, datafile name, and size/quota according to your requirements.

To create a JDBC Connection Pool

In your web browser, log into the Sun Java System Application Server Administrator Console. For example:

http://localhost:4848, username:admin, password: adminadmin

Add Oracle JDBC driver classes to the application server classpath.

See the Sun Java System Application Server documentation for details on how to add the classes to the application server's classpath. One method is to navigate to Application Server > JVM Settings > Path Settings and specify the path to the jar file (including the jar file name) in the Classpath Suffix box. You must restart the application server before you continue.

In the navigation tree, expand the Resources -> JDBC nodes, and select Connection Pools.

In the right panel, click the New button.

Under General Settings, specify a name (such as bpelseDB).

Set the Resource Type to javax.sql.XADatasource.

Set the database vendor to Oracle and click Next.

Under Connection Validation, enable Allow Non Component Callers by selecting the Enabled check box.

Under Additional Properties, specify the URL, User, and Password, as follows.

You can use the following values, or choose your own for user, password and URL.
- URL: jdbc:oracle:thin:@machine-name:port:sid
- User: bpelse_user
- Password: bpelse_use

Click Finish, click the connection pool name and click the Ping button.

This verifies your database connection.

Click Finish to prepare for creating a new JDBC resource.