Documentation#

• Must always be a purpose noted within every Package body, procedure, and function. Including functions and procedures within packages.
• Must always be a Modification history section with date, initials, defect #, what the problem was and what the solution was.
• The "END" of a procedure or function should include the name, especially within packages - see P2K_PMGEN for more samples.
  • END get_mex_id;
• Needs to be internal documentation throughout programs explain what and why things are being done, especially in complex areas/cursors.

Cleanup#

• Any code commented out over 5 years ago should be deleted now.
• Remove the word EDITIONABLE from the CREATE OR REPLACE line of generated code when it appears.

Naming and Variables#

• Internal variables should always start with v_
• External package variables (those specified in the spec) should always start with k_
• Only variables required to be accessed by other programs should be in the spec of a package, internal only variables should be in the body.
• Parameters should always start with p_
• Cursor names should always start with c_
• Cursor record names should be the same as the cursor but replacing the c_ with r_
• All packages, procedures, and functions that are not internal to a package must start with P2K_ and all other character positions naming must be followed as well.
  • Procedures and functions internal to packages should be descriptive of what they do.
• Procedures and functions should NOT contain other procedures and functions.

Packaging#

• All specific procedures/functions for a process should be contained in a package.
• There should NEVER be procedures/functions embedded in other procedures/functions.
• Procedures/functions should be relative small and specific to the task they need to perform.
• General functionality should be in general packages.
• We want to eliminate standalone procedures/functions wherever possible.

Trace#

• Write SMERL trace that is >= EXCEPTION LEVEL prompt
• It has been found that sometimes when EXCEPTION level = 0, program still writes level 1 trace, since EXCEPTION = 0, the level 1 trace is not showing on report but still exists in database
• Avoid personal comments in trace, e.g. display your name or like ‘I am here’
• Remove unconditional trace before releasing software

Program Version#

• Version should be a variable at the beginning of the program/package and the variable is then used for display in internal displays.
• Every package, procedure, or function should have the v_version line as the first line in the code.
• This line should have -- !!! VERSION on it so the source can be queried easily to find code versions.
  • For example:

	v_version     VARCHAR2(16) := '19-Aug-2015';  -- !!! VERSION

• For packages there should also be a small function (GET_VERSION) that returns the version of the package in a SELECT statement.

Module independence#

• All modules (AT/BE/PR/SA/etc) can reference Common module PL/SQL (CM module), but cannot cross reference different module software
  • e.g. P2K_PPxxx or P2K_PBxxx can reference P2K_PMxxx, but cannot cross reference each other, so that each module can be released independently

Alignments, etc.#

• 2 character indent for all levels of PL/SQL
• SELECT statements should align to the end of the first word to give an indent with a clean look, for example:

• Oracle reserved words should be in UPPER case and most other things should be in lower case.

Database Version Differences#

• Rather than NOT doing something in a current DB version because we have to support an older version use:

-- For a 11g Database
$IF DBMS_DB_VERSION.ver_le_11   $THEN
…
-- For a 12g Database
$ELSE
…
$END

Exception Handling#

• When wanting to display the DB error there is a new function P2K_PMGEN.ERROR_STACK which will give a standard display which will include the code name and line number.
• Instead of using something like:

	WHEN OTHERS THEN  --JA20141111
	  p2k_smerl(p_mex_id,'INIT',1,'',NULL,NULL,NULL,'Exception: '||sqlerrm(sqlcode));  

• Now use:

	WHEN OTHERS THEN  --JA20141111
	  p2k_smerl(p_mex_id,'INIT',1,'',NULL,NULL,NULL,p2k_pmgen.error_stack);  --JA20150819

• Abnormal termination
  • display Empl# and/or useful information for debugging at level 0
  • for batch job, do not raise application error unless if must stop immediately which most shouldn't

Warning/Error Message #

• use IMMS message number

Normal termination#

• display useful statistics

Backward compatibility#

• all PL/SQL codes must be backward compatible to support existing live clients
• do not remove existing functionality, rather add new functionality, clients may be using any functionality
• if using new feature of PL/SQL, ensure the new codes are releasable to existing clients on a prior version
• do not remove package spec variables/functions that may be used by existing software
• compile errors may occur when software are not backward compatible at client site

Performance #

• avoid huge cursor reads
• Use index read, bind variables etc

… Need more inputs here …..

Test data / Test example #

• For complicated test scenarios, put in comment
  • e.g I put down the GEO codes when testing Symmetry scenarios
• always test with a normal scenario after testing a specific scenario to ensure majority scenarios are still working with new change
• test with volume if possible