How to Access Oracle DB from Pega 7

This post explains how to access Oracle DB from Pega 7. The Oracle 11g Express Edition Database and Pega 7.1.9 are used in this example. The Pega 7 Connector and Metadata Wizard, accessible in the Designer Studio is used to automatically create the necessary PRPC rules for accessing external Oracle database tables and for mapping query input parameters and query results.

Related Posts

Summary

  1. Pega 7 Setup and Software Used in this Example
  2. Oracle 11g Express Edition DB Server Installation and Configuration
  3. Review of the Oracle Sample Application Used in this Example
  4. Verify that the Oracle 11g DB Server Accepts JDBC Connections
  5. Confirm Network Connectivity between Pega 7 and Oracle 11g DB Server Host
  6. Configuration of Pega 7 for Oracle JDBC Connection Support
  7. Create new Pega 7 DB Instance to Access Oracle DB from Pega 7
  8. Use the Pega 7 Connector and Metadata Wizard for External DB Integration
  9. Use Activity to Access Oracle DB from Pega 7 Application to Browse Records

1  Pega 7 Setup and Software used in this Example

0-0_VirtualMachine_NetworkSettings_HostOnly

2  Oracle 11g Express Edition DB Server Installation and Configuration

1-0_Download_Oracle_DB11g

  • If necessary, create an Oracle account to access the download.

1-1_Download_Oracle_DB11g_SignIn

  • Extract the downloaded file and run the setup.exe file in the folder OracleXE112_Win64\DISK1 to launch the installation wizard.
  • Click Next to continue with the installation.

2-0_Installer_Welcome

  • Accept the license agreement and click Next.

  • The next step allows to specify the destination folder for the application. In this example, the default folder for Windows installations c:\oraclexe\ was used.

  • Enter the database password and click on Next. This is the password for the SYSTEM and SYS database administrator accounts. Make sure to not lose it.

  • Review the installation summaryand click on Install to start the installation process.

  • The Setup Status step may take a while to complete the installation and will show a progress bar.

  • Once the InstallShield Wizard is complete, click on Finishto close the wizard.

  • After the installation, start the Oracle DB server using the shortcut that was created:

  • Open the Windows Services application by running services.msc from the Windows command line interface or through the Windows Start menu to confirm that the relevant services are running.
  • Confirm that two services named OracleServiceXE and OracleXETNSListener are running:

3-0_ConfirmOracleServiceXERunning

  • Open a web browser and confirm that the Oracle Database dashboard is accessible.
  • To login, use the SYSTEMuser and the password that was set during the installation.

3  Review of the Oracle Sample Application Used in this Example

  • The Oracle 11g XE DB server is shipped with a sample application. The tables in this sample application are used for showing how to connect from Pega 7.1.9 to an Oracle 11g XE DB.
  • In the Oracle DB dashboard web application, navigate to Application Express.

  • If prompted, enter the credentials for the SYSTEM account with the password that was set during the installation.

  • Create a new Application Express Workspace, including a dedicated DB user for that workspace. This user account will also be used to access the sample DB from the external Pega 7.1.9 platform.

  • The dashboard will display a confirmation message with a link to the newly created workspace.
  • Click on the click here link to open the workspace.
  • Use the Application Express credentials configured in the previous step to login: odbUser01.

  • Alternatively, use http://localhost:8080/apex/f?p=4550 to open the workspace login page.
  • Enter the Application Express username, in this case odbUser01, and the password to access the new workspace.

  • Select the Application Builder tab in the top menu and open the 100. Sample Application to review the tables in its database, which will be accessed from Pega 7.1.9 in this example.

  • The sample application provides a simple order system that maintains tables for customers, products and orders. It also provides a web interface for users to submit orders.
  • The sample application can be run, by clicking on the Run Application icon.
  • To view the database tables of this application click on SQL Workshop and select Object Browser.

  • The left-hand menu shows all of the application’s tables, open the DEMO_PRODUCT_INFO table to view its columns.
  • Going forward, this table will be used when showing how to connect to Oracle 11g DB from Pega 7.1.9.

  • Click on the Data link to view the existing records in the table.

  • The Indexes link opens a view that shows all of the tables’ indexes such as primary- and foreign keys.
  • The DEMO_PRODUCT_INFO table has one index, a primary key called PRODUCT_ID.

  • The table also has a trigger that increments the numeric primary key PRODUCT_ID by 1 for each new row. Open the Triggers view to see triggers defined for a table.

4  Verify that the Oracle 11g DB Server accepts JDBC Connections

  • The OracleXETNSListener service facilitates JDBC connections from clients to the Oracle DB server. Make sure that the service is running (…see section 2 for details).
  • A simple Java JDBC client program can be used to confirm JDBC connectivity.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class OracleApexDAO {
      
    public Connection getConnection(
            String host, 
            String port, 
            String user, 
            String password) throws SQLException {
        
        return DriverManager.getConnection(
            "jdbc:oracle:thin:@" + host + ":" + port + "/XE", user, password);
    }
   
    public static void main(String[] args) throws SQLException {
        // Establish a JDBC connection
        //
        Connection c = new OracleApexDAO().getConnection(
                "localhost",
                "1521",
                "odbUser01",
                "******");

        // Execute a query
        //
        Statement stmt =  c.createStatement();
        ResultSet rs = stmt.executeQuery(
            "select PRODUCT_NAME, LIST_PRICE from DEMO_PRODUCT_INFO where LIST_PRICE > 100");
        
        // Process and print the query results
        //
        while (rs.next()) {
            String productName = rs.getString("PRODUCT_NAME");
            Float productPrice = rs.getFloat("LIST_PRICE");
            
            System.out.printf("%-20s $%.2f\r\n", productName, productPrice);
        }
    }
}
  • Copy and paste the above Java code to a file, in this case the Java source file is named OracleApexDAO.java
  • From a command line window, compile the Java program with the javac compiler. The syntax is:
"<JDK_BIN_PATH>\javac" -cp <JDBC_DRIVER_FILE_PATH>\ojdbc6.jar OracleApexDAO.java
  • Note that the Java classpath is configured to include the Oracle JDBC driver JAR file that is shipped with the Oracle 11g DB server application. When using the default installation folder, the file location is:
C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc6.jar
  • The Oracle Database 11g Release 2 JDBC Driver can also be downloaded from the Oracle web site
  • The below screen shot shows the source file, the compilation command for a Java JDK 8 installed in the default location on Windows 10 and the generated Java class file.

  • The compiled Java program can be run with the java command, where the syntax is:
"<JRE_BIN_PATH>\java" -cp .;<JDBC_DRIVER_FILE_PATH>\ojdbc6.jar OracleApexDAO
  • Note the .; in the classpath. It refers to the current folder which contains the OracleApexDAO class file.

The Pega 7.1.9 platform is built on Java and also uses the java.sql package and Java Database Connectivity (JDBC) to access external databases.

5  Confirm Network Connectivity between Pega 7 and Oracle 11g DB Server Host

It is necessary to make sure that the host (here Windows 10) and guest (here virtual machine running Pega 7.1.9) operating systems can communicate with each other over the network. The Windows 10 host’s IP address, running the Oracle 11g DB server, can be determined by running ipconfig from the command line. In this case the IP address is 192.168.92.1. Look for an ethernet adapter called VMnet1.

The VMWare command prompt of the virtual machine on which Pega 7 runs can be used to obtain the VM’s IP address, in this case 192.168.92.128 and to confirm connectivity to the host system by executing the ping command with the host’s IP address: ping 192.168.92.1.

Similarly, the virtual machine should be reachable by the Windows 10 host system (running the Oracle 11g DB server). This can be confirmed by running the ping command (…with the virtual machine’s IP address) on the host system as shown below: ping 192.168.92.128.

Note: By default, the Windows firewall may block incoming ICMPv4 (echo) packets required for ping to succeed. Please refer to http://www.sysprobs.com/enable-ping-reply-and-ftp-traffic-in-windows-10-and-server for instructions on how to configure the firewall so that Windows responds to incoming ping requests (…and if needed to open other ports).

6  Configuration of Pega 7 for Oracle JDBC Connection Support

Before running the Pega 7.1.9 Connector and Metadata Wizard, it is necessary to copy the Oracle 11g DB JDBC driver to the Pega 7.1.9 web server and to configure the prconfig.xml file so that it can load the Oracle 11g DB JDBC driver class:

  • Use a FTP client (…such as FileZilla) to copy the ojdbc6.jar JDBC driver file to the Pega 7 web application’s lib folder. In this case, i.e. Pega 7.1.9 on Tomcat 7, the folder is:
/opt/tomcat/webapps/prweb/WEB-INF/lib
  • For Pega 7.1.6 on Tomcat 7, the folder is:
/usr/share/tomcat7/webapps/prweb/WEB-INF/lib
  • When using the default Oracle 11g DB installation settings, the ojdbct6.jar file is located in:
C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc6.jar
  • By default, the Pega 7 SFTP username is root and the password is install.

  • On the Pega 7.1.9 virtual machine, navigate to the folder that contains the prconfig.xml file for configuring a Pega instance. In this case, the file is located at:
/opt/tomcat/webapps/prweb/WEB-INF/classes/prconfig.xml
  • On Pega 7.1.6, the configuration file is located at:
/usr/share/tomcat7/webapps/prweb/WEB-INF/classes/prconfig.xml

  • Download the configuration file, open it in a text editor and add the line:
<env name="database/drivers" value="oracle.jdbc.OracleDriver" />
  • Important: After modifying the prconfig.xml file, restart the Pega 7.1.9 PRPC application. This can be done with the Tomcat manager application shown on the Pega 7.1.9 start page:

  • By default, the manaper app’s username is admin and the password is admin.

  • Locate the prweb application and click on the Reload button.

7  Create new Pega 7 DB Instance to Access Oracle DB from Pega 7

Open the Pega 7.1.9 Designer Studio to create a new Database instance that will contain the JDBC connection parameters to the external Oracle 11g database.

  • In the Designer Studio, navigate to Records > SysAdmin > Database.

  • Click on the Create button and enter a name for the new database instance on the next screen.

  • On the Edit Database form select use JDBC URL listed below in the first drop-down.
  • In the JDBC definition text field, enter the JDBC URL. In this case, the URL is:
jdbc:oracle:thin:@192.168.92.1:1521/XE
  • To connect to the Oracle sample DB, use the username odbUser01 and password pwd123, as configured earlier.
  • Click on Test Connection to confirm JDBC connectivity from Pega 7.1.9 to the Oracle 11g DB server.

  • Connection test successful: A confirmation message will be shown in a new window. This means the configuration to access Oracle DB from Pega 7 was done correctly.

Pega 7 Designer Studio - Edit Database - Test Connection - Access Oracle DB from Pega 7 - Connection Success

Note: If the connection fails, it could be due to the below listed reasons. See host OS firewall settings as well.
  • a) No suitable driver found for jdbc:oracle: Refer to section 6 and make sure that the JDBC driver is in the correct location and that the prconfig.xml file has been correctly configured.

Pega 7 Designer Studio - Edit Database - Test Connection - Access Oracle DB from Pega 7 - Error: No suitable driver found for jdbc:oracle

  • b) The network adapter could not establish the connection: Make sure that the Oracle 11g DB server is running, that you are using the correct IP address and that the Pega 7.1.9 system can reach the DB server with the given IP address.

Pega 7 Designer Studio - Edit Database - Test Connection - Access Oracle DB from Pega 7 - Error: The network adapter could not establish the connection

  • c) Invalid username/password; login denied:  Make sure that the username and password are correct and that the user account has the correct privileges to access the Oracle database.

Pega 7 Designer Studio - Edit Database - Test Connection - Access Oracle DB from Pega 7 - Error: Invalid username/password; login denied

  • If establishing a connection continues to fail, check the host operating system’s firewall settings, the default Oracle JDBC port 1521 could be blocked.
  • If needed, try to temporarily disable the host OS firewall (…here Windows 10, i.e. firewall for private and public networks).

6-0_TurnOffWindowsFirewall

  • The new database instance will now show up in the list of Instances of Database.

6-6_Pega7.1.9_InstancesOfDatabase

  • The new database instance will now show up in the list of Instances of Database.

8  Use the Pega 7 Connector and Metadata Wizard for External DB Integration

  • In Designer Studio, select Integration > Connectors > Connector and Metadata Wizard.

7-0_Pega719_DesignerStudio_Integration_Connectors_ConnectorAndMetadataWizard

Step 1: Choose Purpose
  • Purpose – Generate Connector Rules
  • Metadata Type – SQL
  • Choose a rule set name and version
  • Select the base class (…will contain data model rules) and connector activity class (…will contain activities for browse, insert, update and delete)

7-1_Pega719_ConnectorAndMetadataWizard_ChoosePurpose

Step 2(a): Process Metadata – “Select Database Table
  • Select the database instance that was setup in section 7, in this case it is Oracle11gDB.
  • In this example, the table DEMO_PRODUCT_INFO, shown in section 3, will be accessed.

7-2_Pega719_ConnectorAndMetadataWizard_ProcessMetadata_SelectDatabaseTable

Step 2(b): Process Metadata – “Select Table Columns
  • The view will automatically display all columns that exist in the selected table.
  • Click on the Select All button to map all table columns for access.

7-3_Pega719_ConnectorAndMetadataWizard_ProcessMetadata_SelectColumns

Step 2(c): Process Metadata – “Select SQL Operations
  • In this example, all SQL operations are selected: Browse, Open a single record, update and delete.

7-4_Pega719_ConnectorAndMetadataWizard_ProcessMetadata_SelectSQLOperations

Step 2(d): Process Metadata – “Select Browse Criteria
  • If Browse the database was selected on the previous step, the next view allows to select the table columns that will be used for browsing.
  • In this example, 3 columns are selected: PRODUCT_NAME, CATEGORY and LIST_PRICE

7-5_Pega719_ConnectorAndMetadataWizard_ProcessMetadata_SelectBrowseCriteria

Step 2(e): Process Metadata – “Map Parameter Values
  • This view shows the activities that will be created for each selected SQL operation.
  • Each activity shows the column name(s) that will be used and the mapping to activity parameters.
  • For example, an activity called BrowseDEMO_PRODUCT_INFO will be created and it will have 3 parameter pairs for each of the 3 selected table columns.

7-6_Pega719_ConnectorAndMetadataWizard_ProcessMetadata_MapParameterValues

Step 3: Review and Save
  • The last step of the wizard shows a summary of the rules that will be generated.
  • Click on the Finish button to let the wizard generate the rules.

7-7_Pega719_ConnectorAndMetadataWizard_ReviewAndSave

Wizard Summary
  • The wizard will show a summary of the rules that were generated. In this case, 16 rules were generated.
  • See the 4 activity rules for the selected SQL operations and the property rules reflecting the table’s columns.

7-8_Pega719_ConnectorAndMetadataWizard_GenerationSummary

  • Click on the Close button to close the Connector and Metadata Wizard.
  • The generated rules can be viewed in the Application Explorer.
  • The base class (here PGX-CustomerOrderSystem-Int) entered in step 1 contains the generated properties based on the table columns.

7-9_Pega719_ApplicationExplorer_GeneratedRules_INT

  • The connector activity class (here PGX-CustomerOrderSystem-Work) entered in step 1 contains the wizard-generated activities.

7-10_Pega719_ApplicationExplorer_GeneratedRules_WORK


9  Use Activity to Access Oracle DB from Pega 7 Application to Browse Records

  • Open the wizard-generated activity BrowseDEMO_PRODUCT_INFO and click on the Parameters tab.
  • The wizard may generate some unnecessary parameters, e.g. that only apply to numeric data types, where a range can be provided (…here to search for products with prices between $50 and $200).
  • Range parameters for text types don’t make much sense, so remove unnecessary parameters as needed.

8-1_Pega719_BrowseActivity_Parameters

  • For this example, the wizard-generated parameters have been modified as shown below:

8-2_Pega719_BrowseActivity_ParametersModified

  • Switch to the Steps tab of the activity and expand the first step that calls the Obj-Browse method.
  • The default values can be left unchanged, but it is a best practice to limit the number of records that the DB can return. In this case, the MaxRecords parameter has been set to 100. By default, the method returns 10,000 records and may result in performance issues.
  • Checking the UseLightWeightList parameter is a best practice too as it may reduce memory requirements during processing. See the PDN article About lightweight lists for more information.
  • In this case, the activity will query the DEMO_PRODUCT_INFO table by category name (see where Condition Is Equal). The other columns, where the Condition is Value only, are included in the result set.
  • Step 2 calls the Show-Page method and displays the XML of the pyTempList page which holds the query results.

8-3_Pega719_BrowseActivity_Steps

  • Run the BrowseDEMO_PRODUCT_INFO activity by clicking on Actions > Run.
  • A new window will open, allowing to set the activity’s input parameters.
  • Here, all products in the “Accessories” category will be fetched.

8-4_Pega719_BrowseActivity_Run_ParameterPage

  • After the execution of the activity, a new window will open and show the page XML.
  • In the below excerpt, the SQL query string that was generated as per the Obj-Browse method configuration is shown in the pxSQLStatementPost node.

8-5_Pega719_BrowseActivity_Run_ShowPageXML_SQLStmt

  • Furthermore, expand the pxResults node to view a list of the result records.
  • In this example, 3 records were returned: Belt, Bag and Wallet.

8-6_Pega719_BrowseActivity_Run_ShowPageXML_pxResult

The generated activity rule can now be used in a Pega 7.1.9 application to e.g. populate a data page using its Load Activity. The other activities for inserting, updating and deleting data can be used in a similar fashion.

8 thoughts on “How to Access Oracle DB from Pega 7

  1. Hi Bruno,
    I have read your blogs with great interest. Your publications are very comprehensible and very well structured and give an excellent guide to solve the described problems.
    Thank you, very good work!

  2. Hello Bruno how r u? Can u give a explanation for JMS and MQ Integration also and if u have time can u give explanation for agents which you have not covered this scenarios

  3. Hi Bruno,

    I’m unable to Configure Pega 7.1.9 for Oracle JDBC Connection Support. I’m unable to see below directory lisitng:
    /opt/tomcat/webapps/prweb/WEB-INF/lib

    Could you please help?

    THanks

  4. Hi Bruno,

    I’m unable to Configure Pega 7.1.9 for Oracle JDBC Connection Support. I’m unable to see below directory listing:
    /opt/tomcat/webapps/prweb/WEB-INF/lib

    Could you please help?

    Thanks!

  5. Hi Bruno,

    The connector & Metadata Wizard is deprecated in 7.3 version and is a legacy rule now.
    So please can you help me us with some blog of external classes to connect databases.

Leave a Reply