Oracle – Find Hard-coded Dates In PL/SQL Code Using Regular Expressions

Recently a situation came up in PL/SQL code where the format specifier for date was misplaced. Oracle did not complain because it can implicitly convert strings to date. It may not even fail at run-time under most circumstances (always recommend using an explicit format specification though).

This is the SQL

SELECT
   NVL(TO_DATE([date_variable], 'DD-MON-YYYY'), '31-DEC-9999')
FROM dual

..but the Developer meant this:

NVL([variable], TO_DATE('31-DEC-9999', 'DD-MON-YYYY'))

In any case, this broke the application functionality under certain circumstances (when NLS_DATE_FORMAT was changed) and we had to find all the places in code that had a similar problem.

Normally, it will take a lot of conditions in the WHERE clause to find that, but with regular expressions, it becomes quite easy.

Knowing the fact that the developers used one of these formats

  • DD-MON-YYYY
  • DD/MON/YYYY
  • DD-MON-YY
  • DD-MON-YY
  • MM-DD-YYYY
  • MM/DD/YYYY
  • MM-DD-YY
  • MM/DD/YY

This is the SQL to find all the places in code that have hard-coded dates in the above formats

SELECT *
FROM dba_source
WHERE
    (
        --single quote followed by dd followed by
        -- or / followed by 3 letters followed by
        -- or / followed by 4 digits followed by single quote
        regexp_like(text,'''\d{2}(-|\/)...(-|\/)\d{4}''')
        --same as above except when the year has only two digits
        OR regexp_like(text,'''\d{2}(-|\/)...(-|\/)\d{2}''')
        --Similar to above but mm/dd/yy or mm/dd/yyyy format
        OR regexp_like(text,'''\d{2}(-|\/)\d{2}(-|\/)\d{4}''')
        --same as above except when the year has only two digits
        OR regexp_like(text,'''\d{2}(-|\/)\d{2}(-|\/)\d{2}''')
    )
    AND owner NOT LIKE 'SYS%' --Ignore sys/system code
    AND text NOT LIKE '--%'   --Ignore comments

Here is a sample of the results:

OWNER NAME TYPE LINE TEXT
ODS PKGINF_IN_COMPARE PACKAGE BODY 723         SELECT NVL(MAX(as_of_date), TO_DATE(’12/31/1801′,’MM/DD/YYYY’))
ODS PKGINF_IN_COMPARE PACKAGE BODY 732         IF v_Prev_As_Of_Date = TO_DATE(’12/31/1801′,’MM/DD/YYYY’) THEN
ODS PKGINF_IN_COMPARE PACKAGE BODY 747         SELECT NVL(MAX(as_of_date), TO_DATE(’12/31/9999′,’MM/DD/YYYY’))
ODS PKGINF_IN_COMPARE PACKAGE BODY 756         IF v_Next_As_Of_Date = TO_DATE(’12/31/9999′,’MM/DD/YYYY’) THEN –#Chg 10/26/2005 (#) Jana – Changed from v_Prev_As_Of_Date to v_Next_As_Of_Date to correct a silly copy and paste mistake.
ODS PKGINF_IN_COMPARE PACKAGE BODY 809         SELECT NVL(MAX(as_of_date), TO_DATE(’12/31/9999′,’MM/DD/YYYY’))
ODS PKGINF_IN_COMPARE PACKAGE BODY 817         IF v_Latest_InfCompDS_AsOfDate TO_DATE(’12/31/9999′,’MM/DD/YYYY’) THEN
  ….. more results not shown

Towards the middle, it also showed code like this:

ODS PKG_REPORTS PACKAGE BODY 1260 v_Inforce_Date := ’31-DEC-9999′;

The code relies on Oracle to do an implicit conversion from string to date for the date variable v_Inforce_Date. This is a bad coding practice and a subject of another discussion. Explicit format should have been specified like this – TO_DATE(’31-DEC-9999′, ‘DD-MON-YYYY’)

This tip is about regular expressions though. Without regular expressions, it would have been very hard to do something like this. The magic is done by the function REGEXP_LIKE. In a later tip, we will look at the various regular expression options available.

Although regular expressions are very powerful, it is also easy to make mistakes with expressions and without a supporting function-based index, full-table scans will be in order.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s