Hi,

It is not recommended to implement business logic directly in DBMS (i.e., "stored procedure") because it is outside of Pega and hard to manage it for Pega developers. However, there might be times when you have to take this approach. In this post, I will share how to create stored procedure in Oracle database and call it from Pega Platform using Connect SQL. Please see attached for the detailed steps.

• What is stored procedure

Stored procedure is a group of one or more pre-compiled SQL statements that are stored in a database. It is like a subroutine in the common programming language and then can be reused over and over. In general, the main advantage of using a stored procedure is the performance:

1. By grouping SQL statements, a stored procedure allows the statements to be processed with a single call. This reduces network traffic and improves round-trip response time, in the diagram shown below.
2. Stored procedures are compiled once and stored in an executable form. As a result, procedure calls are quick and efficient. Executable code is automatically cached and shared among users. This lowers memory requirements and invocation overhead.

On the other hand, the disadvantage of using stored procedure is the limitation of portability. Stored procedure is usually written in vendor specific languages (Oracle has PL/SQL, SQL Server has Transact-SQL, PostgreSQL has PL/pgSQL, etc) and that makes it hard to transfer them from one DBMS to another.

• Stored procedure vs function

A function is similar to a stored procedure in that it contains a set of SQL statements that perform a specific task. The difference is that a function has a return type and returns a value while a stored procedure does not have a return type (it can return values using the OUT parameters). The basic idea of a function is that it should merely do computations (ex. generateId()), but not to change the database state. And the idea of a stored procedure is that it is a series of steps to change the database state.

• Stored procedure syntax (Oracle)

• When to take stored procedure approach

In my opinion, if you are building from scratch, stay away from stored procedure because this approach will reduce portability of your application and it is hard to debug or test for Pega developers. Now, what if customer already has tons of stored procedures that interact with their existing database? In that case, I would just reuse their assets (Wrap and Renew), as it is painful to rebuild the entire business logic into Pega, typically ending up with spending too much time on figuring out the current specification. The stored procedures are already tested by customer in the past and it should be stable. The responsibilities belong to customer.

• Calling stored procedures (Connect SQL)

Include the optional out keyword to capture the results of a stored procedure in a Single Value property. Use the optional in keyword to identify a property that supplies an input parameter to a stored procedure. If omitted, in is the default. For example, the SQL used to call a stored procedure can look like this:

\{call my_stored_proc({Values.In1}, {Values.Out1 out}, {Values.InOut1 in out }) \}

• Tutorial content

The tutorial attached includes how to set up below diagram:

1. Database configuration

1-1. Create user

1-2. Grant privileges to user

1-3. Create table

1-4. Create stored procedure

2. Pega Platform configuration

2-1. Create Database rule and configure JDBC settings

2-2. External Database Table Class Mapping wizard (* see update)

2-3. Create Connect SQL

• Update (7/28/2022)

Initially the attached tutorial worked fine with Pega 8.4.1, but if you try it in the later versions, you may encounter ORA-00942 error in the external database table class mapping step. Here is the overview and how to fix it.

Starting from 8.1.9, 8.2.8, 8.3.5, 8.4.3, 8.5.2, and 8.6.0, Pega has introduced a new feature "metadata query optimizations" to help boost the platform runtime performance for Oracle database. This feature is enabled by default. Previously at minimum, CREATE SESSION and UNLIMITED TABLESPACE privileges were enough to connect to external database table. However with this changes, Pega now requires two more - SELECT ON SYS.V_$PARAMETER and ALTER SESSION privileges. This change is not only for connecting to external database but for internal Pega local database as long as you are using Oracle.

GRANT SELECT ON SYS.V_$PARAMETER TO <USER>
GRANT ALTER SESSION TO <USER>

The SELECT permission on the V_$PARAMETER table gives the Pega Platform information about Oracle database’s optimizer mode, which is temporarily altered for the current Pega session for running the metadata query. If you do not give this permission and try to use external database mapping wizard, you'll fail as shown below. "ORA-00942: table or view does not exist" is thrown in the PegaRULES log.

If you can't give these permissions for any reasons, it is also possible to disable this feature by Dynamic System Settings as an alternative. This DSS instance doesn't exist by default, so you will have to create one yourself. Set the value to false. System restart is required after save.

• Owning Ruleset: Pega-Engine
• Setting Purpose: prconfig/database/oracle/allowOptimization/default

Hope this helps. 

Thanks,