Developing Telecommunications rating system using Oracle PL/SQL

This paper presents main steps in developing Telecommunications rating system using the Oracle PL/SQL (Procedural Language/Structured Query Language) and problems that occurred during this process. Advantage of this system is that it runs on the database server what increases maintainability and security and also enables significant reduction of the network traffic. In rating goal is to maximize the number of transactions that can be processed per second and therefore number of context switches between the SQL and PL/SQL engine had to be minimized. To avoid unnecessary queries to the database, stored procedures with table data are dynamically generated and used instead of select statements. For the same reason subscriber data like counters and parameters are loaded into memory structures (PL/SQL collections) using bulk collect and then along with results of transaction rating inserted into the database using bulk operations. Comparison of this method to pure PL/SQL without collections and dynamically generated procedures is also given.