PL/SQL Package Conversion for Dynamic Data Source Selection
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing software systems face challenges in continuously delivering improvements without regressing existing functionality, particularly due to the tight coupling of data access and data processing code, which leads to maintenance difficulties and economic damages.
Innovation Solution
A method to dynamically select data sources at runtime by transforming existing PL/SQL packages into new definitions that separate data manipulation code from data processing code, using parameterized views and object-oriented constructs to maintain a consistent public interface while allowing changes to data manipulation code without affecting the rest of the program.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If data access code and data processing code are tightly coupled in existing PL/SQL packages, then the code structure is simple and easy to implement, but maintenance difficulty increases and regression risk increases when making improvements
Solution Approach 1:
The patent segments the monolithic PL/SQL package into multiple separate layers: data access layer (handling database operations), business logic layer (processing data), and presentation layer (user interface). This segmentation isolates changes to specific layers, preventing regressions from propagating throughout the entire system while maintaining reliability.
Solution Approach 2:
The patent extracts data access code from the main business logic and places it in separate data access objects (DAOs) and stored procedures. This extraction allows independent modification of data access operations without affecting business logic, reducing maintenance difficulty and regression risk while preserving overall system functionality.
2Productivity
If software is continuously improved in a continuous delivery model, then software quality and functionality improve, but the probability of regressing existing functionality increases
Solution Approach 1:
The patent implements dynamic configuration through parameterized queries and configurable data access objects that can adapt to different data sources and schemas. This dynamic approach allows continuous improvement of software functionality while maintaining stability through flexible, reusable components that can be modified without breaking existing functionality.
Solution Approach 2:
The patent uses parameterized queries and configurable parameters to allow software behavior to be changed through parameter adjustment rather than code modification. This enables continuous delivery of improvements while maintaining functional stability, as changes can be made by modifying parameters rather than restructuring core code.
3Ease of manufacture
If data manipulation code is embedded within package procedures, then the code is compact and easy to deploy, but changes to data manipulation code affect the entire package and increase maintenance burden
Solution Approach 1:
The patent segments data manipulation code into separate stored procedures and data access objects that can be independently developed, tested, and deployed. This segmentation maintains deployment simplicity through modular units while greatly improving maintenance ease, as changes to data manipulation code no longer require repackaging the entire application.
Solution Approach 2:
The patent introduces data access objects (DAOs) as intermediaries between the application logic and database operations. These DAOs serve as a stable interface layer that simplifies deployment while isolating data manipulation changes, allowing maintenance of data access code without affecting the broader application package.
Data Source
AI summary
An existing package definition that statically accesses data sources may be used to generate a new definition of the package that dynamically selects between available data sources at runtime. Read-only operations in the new package can be replaced with parameterized views that select between data sources using a session variable. Data manipulation code in the existing package definition can be moved from the package to a new object type. The new definition of the package may keep the same public interface, but the function/procedure definitions can call corresponding functions/procedures in the new object type. Data manipulation code in these functions in the object can be moved to new member functions that separate data manipulation code from data processing code. Child object types can be defined for each data source that override these member functions to access different data sources.