Strategies to Write Better PL/SQL Packages

Anthony Noriega, Independent Consultant


This paper was inspired through my comprehensive Oracle experience as a Developer, Software Engineer, and DBA/Architect, but mainly as an OCP instructor at FDU, where I was able to observe my students' difficulties in package programming, in spite of their extended development experience which also involved object-oriented programming. A technique that I named Easy Package Writing (EPW) is introduced.

Exhibit 2 . Testing your package with the SQL*Plus interface and using a popular editor.

Abstract

As a PL/SQL expert, the author will discuss a simplified approach to organize package methods as procedures and functions, the usage of global variables, and initialization procedures, and object orientation when appropriate. While approaching PL/SQL packages as static classes, in comparison to Java, the focus will also be understand patterns that allow the consistent enhancement of the package being written as it grows. The understanding of object-oriented concepts such as abstraction, encapsulation, polymorphism, inheritance, coupling, and cohesion, among others will also be emphasized in achieving the perfect components nuance to achieve a quality PL/SQL implementation.

1. Introducing Easy Package Writing (EPW)

The key factors affecting the quality and consistency of well-written package are:

· Planning your package structure accordingly
· Having a clear goal or end in mind, with a good knowledge and understanding of the means to achieve it
· Providing flexibility to substantially modify, overload, and upgrade the code as needed.

The other aspects have to do with the target production environment where the package will run, including the ability to pin the code in shared memory, the memory utilization itself, and how well the code is laid out. Besides, an important aspect of package consolidation conveys the idea of balancing code efficiency with overall resource utilization, which comes to the fact of writing good SQL, especially when using a driving main cursor. Fundamentally, building dynamic SQL is a good aspect to look at, in particular, when using REF Cursor types regardless of whether they are passed as parameters or not.

A good strategy that I have used extensively to consistently succeed when writing packages is to have pre-compiled code in the form of an anonymous block or stored procedure, which is to be used or slightly modified in a final version when attached to the package. However, most importantly a critical aspect of package writing is to have a very good layout of your package specification components, i.e., your global variables, functions and procedures without necessarily having a final version of how many parameters, if any, will be needed or whether overloading a function or procedure will be necessary.

2. Making a Package from existing procedures, functions, and anonymous blocks

In order to start your package, follow these steps:

1. Establish a hierarchy among the functions and procedures you plan to use in your logic, setting up global variables, if any, first, followed by the functions to be called, if any, and the calling procedures, or called procedures finally followed by main calling procedures. You may consider using one or several entry points, i.e., your main program in comparison to other languages such as Java or C++[1].

2. Start by writing your package skeletal identified your foreseen function prototypes in your package specification, without worrying too much about how they will use formal parameters at all.

3. Consistently, cut and paste these prototypes to create your package body from your package specification.

4. Transform your package body based on your specification using functions and procedures with a null statement in them.

5. If you have never written a package before, write your first function or procedure that you need as an anonymous block or independent stored procedure. If you use this approach, you need to be ready to modify your code at a later stage in the same fashion.

6. Plan accordingly to maintain this resilient approach and technique as your code grows. This technique allows you to control the size of your package, and does not limit your package size except for the language or hardware resources or operating system settings. The technique allows developers to:

· Modify, upgrade, or re-factor a package code much faster than using any other best practices techniques.

· Maintain an updated package structure consistent at all times. For instance, if you modify a function prototype in your package specification, just cut and paste it accordingly in your package body, since they must be literally identical. Similarly, if you change the order in your package specification, you should proceed to do the same in your package body. There are many PL/SQL packages in corporate production whose specification and body structural order does not match, which makes them very difficult to maintain when the code is routinely updated. This is very common in organizations that depend in more than one development team, which must share the responsibility of code maintenance. This is also certain when outsourcing is used at different chronologic maintenance times.

7. Establish a package version control strategy from the very beginning, and maintain each comment with the appropriate comments as your requirements engineering changes.

8. Use a naming convention to name your functions and procedures, beyond any technique that you have used to name your variables, literals, etc. This is particularly important when your package becomes quite large. For instance, if your package has reached a couple of thousand lines, it is time to have a package component naming convention that allows you to best maintain the package, its structure, and related hierarchy.

9. Important aspects of package understanding as it grows in size are related to the ability to use and expand on, but not limited to, concepts such as ref cursors and PL/SQL pragmas. If they become a bottleneck, the most relevant actions to take are having a good quality assurance plan, and this implies having the appropriate tools to do so, such as your favorite IDE tool or editor, or just the SQL prompt, if your prefer to do so.

10. Enhance your package with an initialization procedure or entry point: If you need to add an entry-point[2] module or initialization procedure, let it happen at the end when your package is fully completed and compiled.
If you have previously used, plan to, or intend to use any available best practices resources, there should be no strategic conflict in using the above listed methodology steps, which being transient in nature, will lead to software robustness and reliability upon deployment.

3. Applying creativity to package implementation

The impact of having to compile each module individually within the package each time, rather than inserting a ready-to-go component is usually a factor in order to improve project timeliness to attain optimal project optimization, conveying maximized overall team performance.
In order to attain maximum creativity in package implementation, a developer or software engineer needs to have a consistent elicitation of requirements engineering, independently from all resources available and relying on all package project stakeholders involved. Therefore, good software engineering practices will require additional organizational techniques to layout, design, and implement either a prototype of the package or an outlined version of the desirable final package.

Creativity means that the package engineering is subject to initial, intermediate, current, and future requirements engineering, and it should be resilient to gradual changes.

4. Iterative package re-engineering

The methodology presented in this paper may suggest that writing quality packages could be relatively easy. However, in practicality, new and existing developers and software engineers encounter a large number of difficulties in identifying constraints and overcoming technical bottlenecks, which can be represented as insufficient language knowledge or understanding, lack of flexibility to change a coding habit, or fear to test and apply a viable option, which could derive a better and more robust solution. For instance, the interdependency of modules could convey testing a piece of logic earlier in a module or afterwards, and interchanging and where to verify and validate that logic could prove to be critical to the robustness of the package, based on dynamic constraints such as the data and the large number of options to handle all possible exceptions.

When applying the step-by-step methodology presented, one could think that at some point the method could lead to confusion or error. However, keeping version control in a steady fashion makes this strategy quite reliable, and there is always a way to overcome any constraints. For instance, if a module would require retrieving a list of employees and some other information, such as salaries, bonuses, and promotion information, and it has been implemented as a REF cursor in the outmode in one procedure and retrieved into another module, but for some reason such as handling of exceptions or software reliability or robustness are threatened by this module, a different layout that returns the appropriate information into a specific variables, PL/SQL tables, or user-defined object implementation is possible to attain the desirable goals. Usually, REF cursors are foundational to accomplish complex information retrieval when bind variables or query optimization may require maximum flexibility to dynamically implement the cursor at any time. Indeed, the capability of this methodology to easily assimilate new changes and maintain overall control of the package structure means that any structural changes are made through this technique by inserting compiled code, and that the number of hierarchical levels involving human interaction and consistent analysis is actually minimized to a foreseen level requiring little manipulation rather than the whole or a large portion of package itself.

Although the entire package could change, the application of the present methodology in conjunction with commenting specification and body matching components, or using the PL/SQL debug mode conveys the realization of the best option a developer, software engineer, and sometimes a development DBA may have.

5. Assimilating patterns in the enhanced package

Pattern-oriented programming is critical to PL/SQL best practices. In particular, software re-factoring, and the application of the Extreme Programming (XP) software requirements engineering paradigms meets the criteria that the main goal of package writing and re-engineering is focused on unit testing. Therefore, design patterns and reusability in software re‑factoring make PL/SQL development successful, when this easy-step methodology is used.
If a package uses a large number of user-define structures, collections (such as VARRAYS, PL/SQL indexed tables, or nested tables), then the design pattern of such as package is rather more complex than one using straightforward PL/SQL data type variables. The usage of REF cursors in the out-mode may lead to a significant deal of abstraction when establishing the package structure and hierarchy, making it difficult for a novice developer to test or redesign. However, good version control can actually lead to easy package understanding and validation for package modification. Besides, the understanding of object-oriented programming concepts such as abstraction, encapsulation, polymorphism, inheritance, coupling, and cohesion, among other of her core aspects, lead not only to a comprehensive approach to apply these techniques discussed at the advanced level but also to attain both the robustness, and reliability, and required quality assurance at all stages of the business process and software engineering model utilized. This means that upon developing packages, object‑oriented programming is the basic paradigm in place, and further expanding on the underlying programming foundations conveys, in most cases, accomplishing better coding techniques from all points of view.

6. Sizing your package

The size limit for PL/SQL package coding varies from Oracle version and it is rather usually bounded in practicality by hardware and software resources, or by the OFA architecture implemented by a DBA. While I do not oppose the usage of Very Large Packages (VLP), I conceivable estimate that pinning a large package in the shared pool (library cache) will have both pros and cons in a production environment, but for the most part it is a successful strategy when a serially reusable object is pinned accordingly, involving version, structural, hardware resources, and overall production performance control.

7. Case Studies and Sample Code

The following package was written as a solution for a package writing skill test.

CREATE OR REPLACE PACKAGE pkg_tiers IS
TYPE tierCur_t IS REF CURSOR RETURN tier%ROWTYPE;
PROCEDURE prcGetTiers(
dept_id_in IN department.dept_id%TYPE,
ts_OUT OUT TIMESTAMP,
tierCur_Out IN OUT tierCur_t
);
PROCEDURE prcGetSecondSalary(
tier_id_in IN tier.tier_id%TYPE,
salary_out OUT employee.salary%TYPE
);
END pkg_tiers;
/

CREATE OR REPLACE PACKAGE BODY pkg_tiers IS
PROCEDURE prcGetTiers(
dept_id_in IN department.dept_id%TYPE,
ts_OUT OUT TIMESTAMP,
tierCur_Out IN OUT tierCur_t
) IS
CURSOR tier_cur(cv_dept_id department.dept_id%TYPE) IS
SELECT tier_id
FROM employee
WHERE dept_id = cv_dept_id;
l_dept_id NUMBER;
BEGIN
ts_OUT := SYSTIMESTAMP;
l_dept_id := dept_id_in;
OPEN tierCur_Out FOR SELECT * FROM tier WHERE tier_id IN (SELECT tier_id FROM employee WHERE dept_id = l_dept_id);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SQLERRM);
END;
PROCEDURE prcGetSecondSalary(
tier_id_in IN tier.tier_id%TYPE,
salary_out OUT employee.salary%TYPE
) IS
CURSOR maxSalCur(cv_tier_id tier.tier_id%TYPE) IS
SELECT MAX(salary) max_salary
FROM employee
WHERE SALARY < (SELECT MAX(SALARY) FROM employee ) AND tier_id = cv_tier_id; maxSalRec maxSalCur%ROWTYPE; BEGIN OPEN maxSalCur(tier_id_in); FETCH maxSalCur INTO maxSalRec; CLOSE maxSalCur; salary_out := maxSalRec.max_salary; EXCEPTION WHEN OTHERS THEN IF maxSalCur%ISOPEN THEN close maxSalCur; END IF; DBMS_OUTPUT.put_line(SQLERRM); END; END; / Exhibit 1. A Simple Package Code
Although this package does not convey any level of significant complexity, it contains all the elements to understanding what the methodology is attempting to introduce in this paper. This package does not contain any functions. There are only two procedures in the package, which are independent from one another. Therefore, no specific level of hierarchy is required other than that imposed by the package structure, meaning that both the specification and the body must have a one-to-one correspondence with identical prototypes or headers in both the specification and matching body. Each component may have been written as an individual anonymous block prior to its consolidation into the package as a procedure (or function in some other case). This technique is crucial to optimize the timeliness required to systematically get the package compiled. Usually, actual parameters are provided as declared and initialized variables as they would be provided in the standard module as congruent formal parameters accepting actual parameters.

A second package shown in the appendix of this paper suggests a much higher level of managed complexity. Originally, the package was written hierarchically, by implementing full components sequentially until growing to a more complex package. The PKG_MAINTAIN_STATS was prepared with the purpose of further optimizing database performance tuning from a DBA viewpoint, and establishing a corporate statistics gathering strategy useful to various versions, in particular, Oracle9i, Oracle10g Release 2, and Oracle11g Release 1. This package can be further enhanced with additional customization, and uses an entry point. A third package related to trade monitoring is presented in the final Appendix section.

8. Demonstration Using the Sample Code Provided

Assume that you need to write a package that will perform the following tasks presented in the PKG_TIERS package, namely:
· Retrieve a list of tiers associated with a particular department upon providing a department id (dept_id) as an input parameter.
· Obtain the second largest salary.
The writing of the package will require the following sequence, based on the methodology presented:
· First run the DDL creating tables, sequences, synonyms, and user-defined object types and any other involved.
· Test that each procedure will compile individually or at least modified as an anonymous block, for which you could remove the create or replace procedure clause and add a declare clause instead.

• Create the package specification with just one working module in it.



• Create the package body with the matching body only.



• Add the second procedure to the package specification, and create a matching procedure in the package body.
  

· After recompiling the package, you can now add the second procedure and recompile both the package specification and the package body. You could use the following to statements to emphasize the package valid status:

· SQL> ALTER PACKAGE PKG_TIERS COMPILE;
· SQL> ALTER PACKAGE PKG_TIERS COMPILE BODY;

· In fact, using this technique in an iterative way allows developers and software engineers to successfully optimize compile and overall development turnaround time.
Although it appears trivial at a small scale, writing more complex code can require special focus as the package grows to design a pattern that easily supports future maintenance
Finally, testing your package may not require a sophisticated IDE. In many instances, SQL*Plus, iSQLPlus, in conjunction with your favorite editor could suffix. For many developers, it is a lot easier to identify and debug errors in the code from this human interface than doing so from more complex tools such as SQL Developer or Toad. IDE tools have the advantage that they can provide developers and software engineers with various views of the package structure and therefore do not constrain them from using other tools concurrently for debugging purposes.

For those experiencing an Oracle11g database environment, Rapid Application Testing (RAT) should brilliantly allow them to get the most out of PL/SQL’s quality assurance and make software reliability and robustness the clear outcome for the best possible package design, implementation, and development.

9. Concluding Remarks

Upon reviewing every possible application of the methodology and techniques presented in this paper, the author has arrived at the following conclusions, namely:

· The easy step-by-step methodology can be systematically applicable to build from small to large packages supporting very complex information systems, which could follow enhanced best practices such as codewise module naming conventions.

· The application of this methodology reduces the time needed to design, implement or re-engineer as PL/SQL package and it constitutes an application of an XP programming technique and unit testing.

· Testing time is also optimized, regardless of the software engineering methodology used or the business process model used, such the strategy is also valid for usage with the Capability Maturity Model (CMM), Capability Maturity Integration (CMMI), Six Sigma’s (DMAIC)

[3] strategy, and can expand unit testing into complex systems on a per module basis.

· The methodology introduced allows for consistent best practices on:
· Version Control
· Package modification, upgrade, re-engineering, or re-factoring.
· The presented methodology also guarantees time optimization, and cost optimization, as closely proposed by the COCOMO[4] II cost management model.

APPENDIX

REQUIRED DDL CODE FOR THE PKG_TIERS PACKAGE

CREATE TABLE department
( dept_id NUMBER NOT NULL,
location VARCHAR2(20),
CONSTRAINT PK_department primary key(dept_id) using index
);


CREATE TABLE tier
(tier_id NUMBER,
tier_name VARCHAR2(30),
CONSTRAINT pk_tier PRIMARY KEY(tier_id) using index);


CREATE TABLE employee
(
emp_id NUMBER,
dept_id NUMBER,
salary NUMBER,
tier_id NUMBER,
CONSTRAINT pk_emp PRIMARY KEY(emp_id) using index,
CONSTRAINT fk_dept_ID FOREIGN KEY (dept_id) REFERENCES department(dept_id),
CONSTRAINT fk_tier_id FOREIGN KEY (tier_id) REFERENCES tier(tier_id)
);


rem creating sequences

CREATE SEQUENCE tier_seq START WITH 1 NOCYCLE NOMAXVALUE;

CREATE SEQUENCE dept_seq START WITH 1 NOCYCLE NOMAXVALUE;

CREATE SEQUENCE emp_seq START WITH 1 NOCYcLE NOMAXVALUE;

REM Populating the department table

INSERT INTO department(dept_id,location)
VALUES
(dept_seq.nextval,'London');

INSERT INTO department(dept_id,location)
VALUES
(dept_seq.nextval,'New York');

INSERT INTO department(dept_id,location)
VALUES
(dept_seq.nextval,'Singapore');

INSERT INTO department(dept_id,location)
VALUES
(dept_seq.nextval,'Tokyo');

REM Populating the tier table

INSERT INTO tier (tier_id,tier_name)
VALUES
(tier_seq.nextval,'Director');

INSERT INTO tier (tier_id,tier_name)
VALUES
(tier_seq.nextval,'Manager');

INSERT INTO tier (tier_id,tier_name)
VALUES
(tier_seq.nextval,'Analyst');

INSERT INTO tier (tier_id,tier_name)
VALUES
(tier_seq.nextval,'Intern');


REM Populating the employee table


INSERT INTO employee(emp_id,dept_id,salary,tier_id)
VALUES
(emp_seq.nextval,2,20000,4);

INSERT INTO employee(emp_id,dept_id,salary,tier_id)
VALUES
(emp_seq.nextval,4,200000,1);

INSERT INTO employee(emp_id,dept_id,salary,tier_id)
VALUES
(emp_seq.nextval,3,25000,4);

INSERT INTO employee(emp_id,dept_id,salary,tier_id)
VALUES
(emp_seq.nextval,1,60000,2);
INSERT INTO employee(emp_id,dept_id,salary,tier_id)
VALUES
(emp_seq.nextval,1,30000,3);

COMMIT;

CREATE OR REPLACE PACKAGE pkg_tiers IS
TYPE tierCur_t IS REF CURSOR RETURN tier%ROWTYPE;
PROCEDURE prcGetTiers(
dept_id_in IN department.dept_id%TYPE,
ts_OUT OUT TIMESTAMP,
tierCur_Out IN OUT tierCur_t
);
PROCEDURE prcGetSecondSalary(
tier_id_in IN tier.tier_id%TYPE,
salary_out OUT employee.salary%TYPE
);
END pkg_tiers;
/

CREATE OR REPLACE PACKAGE BODY pkg_tiers IS
PROCEDURE prcGetTiers(
dept_id_in IN department.dept_id%TYPE,
ts_OUT OUT TIMESTAMP,
tierCur_Out IN OUT tierCur_t
) IS
CURSOR tier_cur(cv_dept_id department.dept_id%TYPE) IS
SELECT tier_id
FROM employee
WHERE dept_id = cv_dept_id;
l_dept_id NUMBER;
BEGIN
ts_OUT := SYSTIMESTAMP;
l_dept_id := dept_id_in;
OPEN tierCur_Out FOR SELECT * FROM tier WHERE tier_id IN (SELECT tier_id FROM employee WHERE dept_id = l_dept_id);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SQLERRM);
END;
PROCEDURE prcGetSecondSalary(
tier_id_in IN tier.tier_id%TYPE,
salary_out OUT employee.salary%TYPE
) IS
CURSOR maxSalCur(cv_tier_id tier.tier_id%TYPE) IS
SELECT MAX(salary) max_salary
FROM employee
WHERE SALARY < (SELECT MAX(SALARY) FROM employee ) AND tier_id = cv_tier_id; maxSalRec maxSalCur%ROWTYPE; BEGIN OPEN maxSalCur(tier_id_in); FETCH maxSalCur INTO maxSalRec; CLOSE maxSalCur; salary_out := maxSalRec.max_salary; EXCEPTION WHEN OTHERS THEN IF maxSalCur%ISOPEN THEN close maxSalCur; END IF; DBMS_OUTPUT.put_line(SQLERRM); END; END; / REM adding timestamps (modified_time) ALTER TABLE employee ADD (modified_time TIMESTAMP); ALTER TABLE department ADD (modified_time TIMESTAMP); ALTER TABLE tier ADD (modified_time TIMESTAMP); ALTER PACKAGE PKG_TIERS COMPILE; ALTER PACKAGE PKG_TIERS COMPILE BODY; --DROP SEQUENCE DEPT_SEQ1; --DROP SEQUENCE EMP_SEQ1; --DROP SEQUENCE TIER_SEQ1; --DROP PACKAGE PKG_TIERS; --DROP TABLE EMPLOYEE CASCADE CONSTRAINTS; --DROP TABLE DEPARTMENT CASCADE CONSTRAINTS; --DROP TABLE TIER CASCADE CONSTRAINTS; PKG_MAINTAIN_STATS PACKAGE CODE
(Published in a previous blog)


NOTES TO USE THE PKG_MAINTAINT_STATS PACKAGE

1. Running statistics from PKG_MAINTAIN_STATS in Development and Test Environments:

Pre-requisites: Grant the appropriate privileges to the owner[5], who should have the ANALYZE ANY PRIVILEGE and full access to the PERFSTAT's schema and DBMS_STATS package. The required privileges are the SELECT_CATALOG_ROLE, and specific select grants to each one of the SYS owned tables used, e.g., DBA_TABLES. When using ipStatsHistoryFlag, all schemas involved will be granted the ANALYZE ANY privilege which is immediately revoked upon completion. This is because DBMS_STATS only allows table statistics exports done by the schema itself.

The following are involved:

ipOpt => This option can use the following values, namely, 'S' for schema, 'T' for table, 'I' for indexed columns ;

ipChoice => can utilize the following options, namely: 'C' to compute the average column, 'A' for AUTO (automatic sizing), 'R' to repeat the sizing approach systematically, 'S' for skewed columns only.

ipEstPct => is the estimate percent, recommended values 10 through 100%. It defaults to 100%.

ipStatsHistoryFlag => will create and maintain historic statistical schemas in each schema and will replicate them via PERFSTAT. This flag is a Boolean parameter that requires careful settings.

Run:

The following options are valid usage, namely:

· Using defaults:

exec pkg_maintain_stats.std_fix_db_stats

· Calculating table average column data length to compute or estimate table statistics:

exec pkg_maintain_stats.std_fix_db_stats(ipOpt => 'T', ipChoice => 'C', ipEstPct 100, => ipStatsHistoryFlag => TRUE)

· Running statistics from PKG_MAINTAIN_STATS in Production Environments:

exec pkg_maintain_stats.std_fix_db_stats(ipOpt => 'T', ipChoice => 'C', ipEstPct 100, =>ipStatsHistoryFlag => FALSE)

or simply

exec pkg_maintain_stats.std_fix_db_stats(ipStatsHistoryFlag => FALSE)

using the default parameters.

Package Structure

The package structure is as follows:

sys@DEVADN> desc pkg_maintain_stats

PROCEDURE CREATE_STAT_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPSCHEMA VARCHAR2 IN
IPSTABOWNER VARCHAR2 IN DEFAULT
IPTBS VARCHAR2 IN DEFAULT
PROCEDURE EXPORT_SCHEMA_STATS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPSTABOWNER VARCHAR2 IN DEFAULT
IPSCHEMA VARCHAR2 IN
PROCEDURE FIX_SCHEMA_STATS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPSCHEMA VARCHAR2 IN
IPOPT VARCHAR2 IN DEFAULT
IPCHOICE VARCHAR2 IN DEFAULT
IPESTPCT NUMBER IN DEFAULT
FUNCTION FUNAVGINDCOLLENGTH RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPSCHEMA VARCHAR2 IN
IPTABLENAME VARCHAR2 IN
FUNCTION FUNAVGSCHCOLLENGTH RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPSCHEMA VARCHAR2 IN
FUNCTION FUNAVGTABCOLLENGTH RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPSCHEMA VARCHAR2 IN
IPTABLENAME VARCHAR2 IN
FUNCTION FUNCOLISINDEXED RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPSCHEMA VARCHAR2 IN
IPTABNAME VARCHAR2 IN
IPINDNAME VARCHAR2 IN
IPCOLNAME VARCHAR2 IN
FUNCTION FUNGENSIZEOPT RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPOPT VARCHAR2 IN DEFAULT
IPCHOICE VARCHAR2 IN DEFAULT
IPSIZE NUMBER(38) IN DEFAULT
FUNCTION FUNGETCOLSIZE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPSCHEMA VARCHAR2 IN
IPTABLENAME VARCHAR2 IN
IPCOLUMNNAME VARCHAR2 IN
FUNCTION FUNGETTBS RETURNS VARCHAR2(30)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPSCHEMA VARCHAR2 IN
FUNCTION FUNTABLEEXISTS RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPTABNAME VARCHAR2 IN
IPTABOWNER VARCHAR2 IN DEFAULT
PROCEDURE GRANT_OBJ_PRIV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPGRANTPRIVS VARCHAR2 IN
IPGRANTOR VARCHAR2 IN
IPOBJECT VARCHAR2 IN
IPGRANTEE VARCHAR2 IN
PROCEDURE GRANT_SYS_PRIV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPSYSPRIVS VARCHAR2 IN
IPGRANTEE VARCHAR2 IN
PROCEDURE POPSTATTAB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPSCHEMA VARCHAR2 IN
IPSTABOWNER VARCHAR2 IN DEFAULT
IPTABNAME VARCHAR2 IN
PROCEDURE POP_GRANT_REVOKE_PLSTABINIT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPOPT VARCHAR2 IN DEFAULT
IPSTABOWNER VARCHAR2 IN
IPSCHEMA VARCHAR2 IN
IPTABOBJ VARCHAR2 IN
IPGRANTEE VARCHAR2 IN DEFAULT
PROCEDURE PURGE_OLDER_STATS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPSTABOWNER VARCHAR2 IN DEFAULT
IPSCHEMA VARCHAR2 IN
IPRETRODAYS NUMBER IN DEFAULT
PROCEDURE REVOKE_SYS_PRIV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPSYSPRIVS VARCHAR2 IN
IPGRANTEE VARCHAR2 IN
PROCEDURE SETSCHMONITOR
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPSCHEMA VARCHAR2 IN
IPOPT BOOLEAN IN DEFAULT
PROCEDURE STD_FIX_DB_STATS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IPOPT VARCHAR2 IN DEFAULT
IPCHOICE VARCHAR2 IN DEFAULT
IPESTPCT NUMBER IN DEFAULT
IPSTATSHISTORYFLAG BOOLEAN IN DEFAULT

A Sample Trade Monitoring Package and all related DDL
SQL> CREATE TABLE Tp (Id number, Price number, Ts timestamp) tablespace users;

Table created.

SQL> CREATE TABLE ttrans (TId number, Ask number, Bid number,Ts timestamp) tablespace users;

Table created.



CREATE OR REPLACE PACKAGE MYPKG_ADN1 IS

TYPE prOutlieSet_t IS TABLE OF NUMBER;


PROCEDURE getLeftRange_in ( LeftPriceLow_out IN OUT NUMBER,
LeftPriceHigh_out IN OUT NUMBER,
Ts_in IN DATE);

PROCEDURE getRightRange_in ( RightPriceLow_out IN OUT NUMBER,
RightPriceHigh_out IN OUT NUMBER,
Ts_in IN DATE
);

FUNCTION getOutlier(
LeftPriceLow_in IN NUMBER,
LeftPriceHigh_in IN NUMBER,
RightPriceLow_in IN NUMBER,
RightPriceHigh_in IN NUMBER,
Ts_in IN DATE,
outLier_out IN OUT NUMBER
) RETURN prOutlieSet_t PIPELINED;

PROCEDURE showOutlier(
LeftPriceLow_in IN NUMBER,
LeftPriceHigh_in IN NUMBER,
RightPriceLow_in IN NUMBER,
RightPriceHigh_in IN NUMBER,
Ts_in IN DATE,
outLier_out IN OUT NUMBER
);
END;

CREATE OR REPLACE PACKAGE BODY MYPKG_ADN1 IS


PROCEDURE getLeftRange_in ( LeftPriceLow_out IN OUT NUMBER,
LeftPriceHigh_out IN OUT NUMBER,
Ts_in IN DATE) IS
BEGIN
SELECT MIN(t2.ask) as min_ask_price,
MAX(t2.bid) as max_ask_price
INTO LeftPriceLow_out,
LeftPriceHigh_out
FROM ttrans t2
WHERE t2.ts BETWEEN ts_in - 10/(24*60*60) AND ts_in;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
/
PROCEDURE getRightRange_in ( RightPriceLow_out IN OUT NUMBER,
RightPriceHigh_out IN OUT NUMBER,
Ts_in IN DATE
) IS
BEGIN
SELECT MIN(t2.ask) as min_ask_price,
MAX(t2.bid) as max_ask_price
INTO RightPriceLow_out,
RightPriceHigh_out
FROM ttrans t2
WHERE t2.ts BETWEEN ts_in AND ts_in+3/(24*60*60) ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;

FUNCTION getOutlier(
LeftPriceLow_in IN NUMBER,
LeftPriceHigh_in IN NUMBER,
RightPriceLow_in IN NUMBER,
RightPriceHigh_in IN NUMBER,
Ts_in IN DATE,
outLier_out IN OUT NUMBER
) RETURN prOutlieSet_t PIPELINED IS
CURSOR outLierCur(cv_Ts anthony.tp.ts%TYPE) IS
SELECT id,
Price,
Ts
FROM Tp
WHERE NOT( ts BETWEEN cv_ts - 10/(24*60*60) AND cv_ts + 3/(24*60*60));

BEGIN
FOR outLierRec IN outLierCur(ts_in) LOOP
PIPE ROW(outLierRec.price);
END LOOP;
RETURN;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;

PROCEDURE showOutlier(
LeftPriceLow_in IN NUMBER,
LeftPriceHigh_in IN NUMBER,
RightPriceLow_in IN NUMBER,
RightPriceHigh_in IN NUMBER,
Ts_in IN DATE,
outLier_out IN OUT NUMBER
) IS
CURSOR outLierCur(cv_Ts anthony.tp.ts%TYPE) IS
SELECT id,
Price,
Ts
FROM Tp
WHERE NOT( ts BETWEEN cv_ts - 10/(24*60*60) AND cv_ts + 3/(24*60*60));

BEGIN
FOR outLierRec IN outLierCur(ts_in) LOOP
DBMS_OUTPUT.put_line('Id => 'outLierRec.id' , ''Price => 'outLierRec.Price' , '
'TS => 'TO_CHAR(OutlierRec.ts,'DD-MON-YYYY HH24:MI:SS') );
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;


END;
/
--PKG BODY

Testing the Trade Monitoring Package and Pre-Established Anonymous Block

SQL> connect anthony/anthony
Connected.
SQL> CREATE TABLE t (PaId NUMBER, Eid Number) tablespace users;

Table created.

SQL> CREATE TABLE t_link (LinkId NUMBER, PaId NUMBER, Eid Number) tablespace users;

Table created.

SQL>
SQL> insert into t
2 values
3 (2 , 1 );

1 row created.

SQL> insert into t
2 values
3 (3 , 2);

1 row created.

SQL> insert into t
2 values
3 (null , 3);

1 row created.

SQL> insert into t
2 values
3 (6 , 5);

1 row created.

SQL> insert into t
2 values
3 (7 , 6 );

1 row created.

SQL> insert into t
2 values
3 (8 , 7);

1 row created.

SQL> insert into t
2 values
3 (9 , 8);

1 row created.

SQL> insert into t
2 values
3 (null , 9);

1 row created.

SQL> commit;

Commit complete.

SQL> DECLARE
2
3 cursor DriverCur IS
4 SELECT Eid
5 FROM t
6 WHERE PaId IS NULL
7 ORDER BY 1;
8
9 cursor nestedCur(cvPaId t.PaId%TYPE) IS
10 SELECT PaId LinkId,
11 Paid,
12 Eid
13 FROM t
14 WHERE PaId IS NOT NULL
15 START WITH PaId = cvPaid
16 CONNECT BY PRIOR t.Eid = t.PaId;
17
18 countRec INTEGER;
19 BEGIN
20 FOR DriverRec IN DriverCur LOOP
21 INSERT INTO t_link (LinkId, PaId, Eid)
22 VALUES (DriverRec.Eid,Null, DriverRec.Eid);
23 FOR nestedRec IN nestedCur(DriverRec.Eid) LOOP
24 INSERT INTO t_link (LinkId, PaId, Eid)
25 VALUES (nestedRec.LinkId,nestedRec.PaId, nestedRec.Eid);
26 END LOOP;
27 IF MOD(countRec,10000)=0 THEN
28 COMMIT;
29 END IF;
30 END LOOP;
31 END;
32 /

PL/SQL procedure successfully completed.

SQL> select * from t_link order by 1,2,3
2 /

LINKID PAID EID
---------- ---------- ----------
2 2 1
3 3 2
3 3
6 6 5
7 7 6
8 8 7
9 9 8
9 9

8 rows selected.

SQL>

Footnotes

[1] Remember that PL/SQL packages are primarily static classes, in comparison to a language like Java.
[2] This would be your main() procedure in Java, C++ or any other comparable language acting otherwise as a host language.
[3] DMAIC stands for Define, Measure, Analyze, Improve, and Control.
[4] Constructive Cost Model II
[5] PERFSTAT is recommended.