How to set a values for a connection to an external Oracle database connection
My application is working with an external Oracle database as it's SOR. We have defined the connection to this database in a Database rule, using a JDBC Connection Pool. The connection string defines a user created specifically for our Pega application for the connection. So, any connections that are made made from the pool appear to be from this user.
We now have an auditing requirement to know the identity of the logged in user that executed the CRUD operations that we do to this database. There is a way to do this by calling a procedure when the connection is initiated according to this article: https://oracle-base.com/articles/misc/dbms_session
We have very few Connect-SQL rules defined in our app, and are instead using the Data classes that map to the external database. Does Pega provide any hooks or options into the connection process on call this stored procedure to set the client information for the connection? This seems like this would be needed by people who deal with connection pools and auditing for an external database.
3. If Declare Triggers are viable, can they be setup in a parent class so it will run for any of it's children? - Yes, assuming your data class structure is similar to the following:
Create your trigger rule (one for committed save, and one for committed delete) in Org-Div-FW-YourAppFW-Data-ExtDB class, and it would get triggered for all sub data classes of it.
Keep up to date on this post and subscribe to comments
- Update Syntax for update external database(oracle) in Connect SQL
- How to obtain back Primary KEY in Pega for new Object saved in Oracle External Database generated by sequence/trigger on database side.
- Maximum number of records through RDB-List method from external database (Oracle)
- Not connecting to external database when using external data base class mapping tool
- How to change setting database from PegaData to external Database