Skip navigation.

ORACLE OOP SQL APPROACH TO COMPLEX DATA TRANSFORMATION: OVERCOME PL/SQL STRIING FUNCTION INEFFICIENCIES

This Blog is to be serve as a compilation of my research in preparation for a paper on
The extreme performance issues with PL/SQL String Functions and how to circumvent their usage using Straight SQL. The Object Oriented approach allows SQL to be virtually executed against "Anything" not just tables - data fields, variables, even a single character!

OOP and abstract data types are the foundation for a non-procedural approach to "fuzzy" data pattern matching and data scrubbing where procedural code, even when effectively modularized, becomes a maintenance nightmare due to the burden of heavy dependencies on
conditional logic and complex process branching.

Aside from readibility, "learnability", and maintenance advantages, Oracle OOP lends easily to coding for business rules very dynamically. Its polymorphism capabilites allow for extreme flexibility in processing to rule changes, additions, and deletions.

Learning Object Oriented programming in Oracle does not encompass a steep learning curve as there is minimal new syntax to grasp. Oracle implements its equivalent of Object Classes and Methods through stored procedures and functions identical in structure to their PL/SQL format.

I will start with an overview of my current work projects and the limitations I hit in performance - severe degadation - in using String functions such as INSTR, REPLACE, TRANSLATE, SUBSTR, in parsing inload data such as name and address fields that required much cleansing before it could be matched with any accuracy to master data in our system.

The business arena is Healthcare processing of Insurance claims where provider (doctor) data on the claim must be matched against our provider database to obtain the correct contract that links the provider to their network fee schedule so that the claim can be repriced and transmited back to the client (Insurance Carriers) correctly.

The number of inaccuracies in data entry on an insurance claim is monsterous. So begins my journey.....