Oracle SQL: REGEX_REPLACE Function

Recently, I participated in a forum discussion (Higher Education User Group) where someone asked if there is a way (and if so how) to strip out HTML elements from a long character field. This is mainly for scenarios where a long character field is used to store rich text enabled data (which contains HTML elements). And while querying the data from this field either using PS Query or SQL the requirement is to only retrieve the "plain text".

I found that we could use a delivered Oracle SQL function called REGEX_REPLACE to pattern match and replace contents of a string.

Here are some examples of how to use this function:

If we want to mainly get rid of HTML elements then we can use the following pattern:

select REGEXP_REPLACE(DESCRLONG, '<[^>]+>|\&(nbsp;)|\&(nbsp)|(amp;)', '',1,0, 'i'), DESCRLONG from PSMSGCATDEFN where descrlong like '%<%>%';

If we additionally want to get rid of contents inside script/style elements then we can use the following pattern:

select REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(DESCRLONG, '<script[^>]*>.*</script>', '',1,0,'in'), '<style[^>]*>.*</style>', '',1,0,'in'),'<[^>]+>|\&(nbsp;)|\&(nbsp)|(amp;)', '',1,0,'i'), DESCRLONG from spsoft.PSMSGCATDEFN where descrlong like '%<%>%';

Note:
- You will need to improvise the regex pattern for those cases which are not trapped in the above sample SQLs.
- This function REGEX_REPLACE only works on Oracle databases.
- I have to admit that I am not a RegEx expert and received help from some very talented colleagues who helped me with the second (more complex) SQL.

HCM/Campus Solutions Address Validations

If anyone has worked on a CI or a web service (e.g.: CWS - Constituent Web Service, AAWS - Admission Applications Web Service) that involves loading data related to addresses into HCM/Campus Solutions applications then you must have run into the address validation problem. The problem with the validation on the addresses component/page is that the fields displayed on the address subpage (specific to a country) would vary depending on the country.

This complicates the process of dynamically identifying the list fields that need to be populated depending on the current country that is being used for data entry. Let us take the example of the Admission Applications Web Service to put things into perspective. Typically, there would a web front-end for the admission applications and that would in turn invoke the AAWS web service to create and save applications into Campus Solutions. Now, the problem for the web front-end application is to dynamically figure out the list of fields that need to be populated on the AAWS request message based on the current country. Once we determine the list of fields that need to be populated, we would additionally run into the problem where the validation on the fields (required/translate/prompt edits) would differ per country.

Here are a list of logical steps and SQLs that would help with building some of that dynamic address validation logic. I was able to write some SQLs that look into PeopeTools metadata and pull out the information we need.

E.g.:

Address Formatting:

To identify the list of fields to display and populate (CIs/Web Service calls) data use PS_EO_ADDR_FMT_TBL.

E.g.:

select * from PS_EO_ADDR_FMT_TBL where COUNTRY = 'ESP';

The address format data could be found online as well.

Address Validations:

Required Fields:

Only the following fields are required:
-    COUNTRY
-    ADDRESS1* or ADDRESS2* or ADDDRESS3* or ADDRESS4*: At least one of these fields is required depending on how many exist in SQL¹.

* The SQL¹ provided must return the fields listed.

E.g.:

For ESP (Spain), SQL¹ only returns ADDRESS1 and ADDRESS2 fields. Therefore, at least one of ADDRESS1 or ADDRESS2 fields is required for Spain.
For BRA (Brazil), SQL¹ returns all fields ADDRESS1, ADDRESS2, ADDRESS3 and ADDRESS4. Therefore, at least one of ADDRESS1 or ADDRESS2 or ADDRESS3 or ADDRESS4 fields is required for Brazil.

Fields With Translate Edits:

The SQL¹ provided must return ‘Y’ for TRANSLATE_EDIT value for the corresponding COUNTRY, FIELDNAME.

E.g.:

ADDR_FIELD1 for Spain (ESP) is based on a translate edit.

Note: For List of Translates Values for a particular field use SQL².

Fields With Prompt Table Edits:

The SQL¹ provided must return ‘Y’ for PROMPT_EDIT value for the corresponding COUNTRY, FIELDNAME.

E.g.:

STATE field for BRA (Brazil) has a prompt table of STATE_TBL. The key fields on the prompt table are COUNTRY, STATE.
CITY field for BRA (Brazil) has a prompt table of CODE_CITY_BRA. The key fields on the prompt table are STATE, CITY.

Note: For List of Values for a particular field use the prompt table (PROMPT_TABLE) with the associated prompt table keys (PROMPT_TABLE_KEYS).

Other Scenario:

If a field exists in PS_EO_ADDR_FMT_TBL and does not fall into any of the above scenarios then there is NO field validation.

SQL¹ - Address Format Sub Page Information Based On PeopleTools MetaData:

SELECT Substr(A.pnlname, 9, 3) AS COUNTRY,
       A.fieldname,
       CASE
         WHEN Bitand(B.useedit, 512) > 0 THEN 'Y'
         ELSE 'N'
       END AS TRANSLATE_EDIT,
       CASE
         WHEN Bitand(B.useedit, 16384) > 0 THEN 'Y'
         ELSE 'N'
       END AS PROMPT_EDIT,
       edittable AS PROMPT_TABLE,
       (SELECT Listagg(Y.fieldname, ', ')
                 within GROUP (ORDER BY y.fieldnum) "PROMPT_TABLE_KEYS"
        FROM   psrecfield Y
        WHERE  Y.recname = B.edittable
               AND Bitand(Y.useedit, 1) > 0
        GROUP  BY Y.recname)   AS PROMPT_TABLE_KEYS
FROM   pspnlfield A,
       psrecfield B
WHERE  A.pnlname IN (SELECT X.pnlname
                     FROM   ps_eo_addr_subpage X)
       AND A.recname <> ' '
       AND A.fieldname <> ' '
       AND MOD(A.fielduse, 2) = 0
       AND A.recname = B.recname
       AND A.fieldname = B.fieldname
--and substr(A.PNLNAME, 9,3) = 'BRA'
ORDER  BY country,
          A.fieldnum; 

SQL² – List Translate Values For A Field:

SELECT A.fieldname,
       A.fieldvalue,
       A.xlatlongname  AS DESCR,
       A.xlatshortname AS DESCR_SHORT
FROM   psxlatitem A
WHERE  A.fieldname = 'ADDR_FIELD1'
       AND A.effdt = (SELECT Max(A_ED.effdt)
                      FROM   psxlatitem A_ED
                      WHERE  A.fieldname = A_ED.fieldname
                             AND A.fieldvalue = A_ED.fieldvalue
                             AND A_ED.effdt <= SYSDATE)
       AND A.eff_status = 'A';

APPMSGARCH Process - Performance Tuning

I recently worked on a requirement to tune the performance of the delivered APPMSGARCH process (batch approach used to archive service operation data). The process was taking longer to run everyday and got to a point where it would run for over 12 hours.

While investigating the problem in production, it was found that the following SQL was the main cause for our performance issue.

SqlExec is located in APPMSGARCH.MAIN.GBL.default.1900-01-01.ARCHASYN.OnExecute:

DELETE
FROM PSAPMSGPUBDATA
WHERE EXISTS
  (SELECT '*'
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE (PSAPMSGPUBDATA.IBTRANSACTIONID = C.CANONICALTRSFRMID
  OR PSAPMSGPUBDATA.IBTRANSACTIONID     = C.IBTRANSACTIONID)
  AND B.IBTRANSACTIONID                 = C.IBPUBTRANSACTID
  );

Note: This is not to imply that all performance problems with this process is directly related to this SQL. There could be other issues depending on each individual environment. But I do find that the structure of all the SQLs particularly the DELETEs follow a similar theme (with the usage of EXISTS clause). So it could be a common problem for which the following solution could be applied.

Once it was identified that this SQL was the main issue in our environment, I tried to look in My Oracle Support for potential solutions (the first place I would look to research a problem with anything delivered). I found this document E-IB: APPMSGARCH Performance Issue (Doc ID 754437.1).

Amongst other things in the document, it was recommended to replace the SQL (mentioned above) with two different SQL statements to separate the OR clause.

SQL 1:
DELETE
FROM PSAPMSGPUBDATA
WHERE EXISTS
  (SELECT '*'
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE PSAPMSGPUBDATA.IBTRANSACTIONID = C.CANONICALTRSFRMID
  AND B.IBTRANSACTIONID                = C.IBPUBTRANSACTID
  );

SQL 2:
DELETE
FROM PSAPMSGPUBDATA
WHERE EXISTS
  (SELECT '*'
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE PSAPMSGPUBDATA.IBTRANSACTIONID = C.IBTRANSACTIONID
  AND B.IBTRANSACTIONID                = C.IBPUBTRANSACTID
  );

Since it was a recommendation of potential value (and one that was pertinent to our problem), I went ahead and applied this change and tested again. Unfortunately, it did not help with the performance at all.

At this point, I started looking into the data in the tables as well as the SQL statements to identify any tuning opportunities. I found that PSAPMSGPUBDATA had around 800,000 rows of data in it. The way the SQL statements are written, it appears that the process would go over each and every row in the table (PSAPMSGPUBDATA) and check for the EXISTS clause before deleting.

Here is how I re-wrote the SQL statements which helped considerably.

SQL1 (Re-write):
  DELETE
FROM PSAPMSGPUBDATA
WHERE IBTRANSACTIONID IN
  (SELECT C.CANONICALTRSFRMID
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE B.IBTRANSACTIONID = C.IBPUBTRANSACTID
  );

SQL2 (Re-write):
DELETE
FROM PSAPMSGPUBDATA
WHERE IBTRANSACTIONID IN
  (SELECT C.IBTRANSACTIONID
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE B.IBTRANSACTIONID = C.IBPUBTRANSACTID
  );

If you notice the new SQL statements, you will find that the main difference was the replacement of the EXISTS clause with a IN clause.

Now, instead of looping through each row in PSAPMSGPUBDATA and checking the EXISTS clause, the SQL would just look for the rows that are in the results of the IN clause.

Here is a good article that details the usage and difference between the EXISTS and IN clause:
Usage of EXISTS and IN clause

If you are having issues with other DELETE sql statements (using EXISTS) in the APPMSGARCH app engine then you could try a similar approach. As always test the changes in your environment to see if you achieve the desired performance gains.

Note: Alternate solutions that I know have helped others in improving performance of APPMSGARCH :

1. Indexing affected tables appropriately.
2. Staging transaction ids that need to be deleted in a custom table (temporarily) to avoid complex where clauses in the DELETE statements.