Managing reference tables using Oracle Dynamic SQL and metadata

Reference tables are designed to support applications using a relational database management system like Oracle or Sybase. The information in these tables changes over the time. Although a separate user interface can be designed to make changes to these reference tables, many software applications allow the DBA to make changes to these tables. We present a metadata and Oracle Dynamic SQL (MODSQL) based database to manage reference tables. Each table is analyzed and stored as metadata. Changes to each reference table are performed by executing Oracle dynamic SQL passed with corresponding metadata parameters dynamically. Basic codes for Insert, Delete and Update are written once in Oracle Dynamic SQL in PL/SQL and packaged into the Oracle stored procedures.