While working on development projects it’s always important to know about the coding standards before starting with the coding. There may be some client specified standards which are specific to the project; apart from them below are some generic coding standards in oracle applications. These could be even used as a check list while doing code reviews.
Oracle Apps coding standards:
Standard | Description | Comments |
OAC-0001 | Avoid hard-coding values. | In an Oracle Applications environment, hard-coding can be avoided by using value sets, lookups and/or profile options. |
OAC-0002 | Executable code and types should be located in the APPS schema. | This includes packages, procedures, functions, triggers and views. In cases where security warrants it, executable code may be contained in a custom schema. |
OAC-0003 | All other database objects should be located in the custom schema. | This includes tables, indexes (including custom indexes on Oracle Applications tables), sequences, etc. |
OAC-0004 | All objects in the custom schema should grant APPS the appropriate privileges. | For tables, “GRANT ALL” to APPS, for programs, “ |
OAC-0005 | Use a consistent style of indenting and capitalization throughout a program. | Examples include: Always indent by the same number of characters within a package (or procedure or function). Make sure each table aliases remain constant from one occurrence to another. Align keywords consistently per statement type (SELECT, INSERT, etc.). Align parameters consistently. |
OAC-0006 | Define SQL*Plus concurrent program executables to execute PL/SQL stored programs. | In general, because of the nature of global variables and concurrent program concurrency, define PL/SQL type concurrent programs using the SQL*Plus execution method. This will effectively ensure each user has their own copy of the program. |
OAC-0007 | Keep all code within the first 80 characters of a line. | This makes the code easier to read when viewing it in UNIX, where it will ultimately be installed. |
OAC-0008 | Use spaces to align code. | Some editors use TAB characters and combinations of TAB and space characters to align code. This makes the code difficult to read if using a different editor. (In TOAD, select Edit > Editor Options from the menu. Expand General Options and uncheck “Insert TABs as spaces and not #9 characters” and uncheck “Insert mix of TAB/space for optimal fill.” To validate if code is following this standard, view the code in NOTEPAD or vi and verify the alignment is intact. |
SQL coding standards:
Standard | Description | Comments |
SQL-0001 | Use EXPLAIN | Use an explain plan during development when writing complex SQL statements to make sure the statement is performing as expected. |
SQL-0002 | Use hints sparingly. | As Oracle’s SQL optimizer is always being improved upon with each new database release, use hints only when the performance of the statement warrants it. |
SQL-0003 | Use synonyms to hide database links. | Statements that contain database links are not as easily portable to different environments and are more difficult to read. |
SQL-0004 | Use EXISTS or NOT EXISTS in WHERE clauses instead of COUNT. | (NOT) EXISTS is more efficient than COUNT, especially when the table has many rows. Use COUNT only when the number of rows is needed. |
PL/SQL coding standards:
Standard | Description | Comments |
PLS-0001 | Use packages when defining procedures and functions instead of defining standalone procedures and functions. | Packages provide several advantages over standalone code: · Global variables · Modularized code · Prevents the invalidating of dependent packages if just the package body is changed |
PLS-0002 | Follow the naming standards for PL/SQL objects above. | |
PLS-0003 | Use %TYPE and %ROWTYPE when declaring variables and parameters. | Using %TYPE and %ROWTYPE anchors will ensure variables match their corresponding database columns. |
PLS-0004 | Use the CONSTANT keyword in declarations when the value of an identifier is not going to change. | Using the CONSTANT keyword will prevent accidentally changing the values via an assign statement. |
PLS-0005 | Remove unused variables from code. | |
PLS-0006 | Separate distinct words in an identifier name with underscores (_). | |
PLS-0007 | Functions should only have IN parameters. | Keep functions “pure” by avoiding output mode parameters (OUT and IN OUT). |
PLS-0008 | Boolean functions should return either | Avoid coding Boolean functions that return NULL. |
PLS-0009 | Functions should have only one RETURN statement. | Too many return statements make code difficult to read. |
PLS-0010 | Package specifications should comment programs using the template shown at the right. | /* ** NAME ** program_name – short description ** DESCRIPTION ** A paragraph or two describing the ** program’s functionality. For ** function programs, this section ** should describe the return value. ** NOTES ** This section contains technical ** implementation notes. It may be ** omitted if there is nothing ** significant. ** ASSUMPTIONS ** Clearly state the assumption on ** which your program is based. ** PARAMETERS ** p_parameter1 ** This parameter... ** p_parameter2 ** This parameter... */ |
PLS-0011 | Private programs should include comments after the IS keyword. | PROCEDURE xxx IS -- This procedure... BEGIN . . . END xxx; |
PLS-0012 | Document a package’s change history in the specification and body as appropriate. | PACKAGE [BODY] xxx IS -- Modification History: -- By Date Description -- -------- ----------- -------------- -- username DD-MON-YYYY Short descrip- -- tion of -- changes |
PLS-0013 | Document multi-line change descriptions within a block as shown at the right. | -- XX: DD-MON -- This change... . . (Changed code here.) . -- END OF CHANGES |
PLS-0014 | Document single-line change descriptions as shown at the right, either next to the line or directly above the line. | -- XX: DD-MON Description of change |
PLS-0015 | At a minimum, each block should be commented. | |
PLS-0016 | Blocks should be kept to a “page” length, approximately 60 lines of code. | If a packaged program is longer than a page, consider creating one or more private programs to encapsulate that logic. |
PLS-0017 | Use a common error package for handling errors. | |
PLS-0018 | Use a common debug package. | |
PLS-0019 | Use BULK COLLECT and FORALL when prudent. | This option may not be wise for cursors that return a lot of data. |
PLS-0020 | The main (top-level) program should have an exception handler such that no exception goes unhandled. | If a sub-program needs to raise an exception to the calling program, it should log the error in its exception handler and raise or re-raise an exception. |
PLS-0021 | Capitalize keywords and keep user-defined identifiers in lower-case. |
SQL* PLUS coding standards:
Standard | Description | Comments |
SQP-0001 | Assign all input parameters to substituiton variables at the beginning of the script. | |
SQP-0002 | Use EXEC to execute PL/SQL stored programs. | Avoid using anonymous PL/SQL in SQL*Plus as it makes the script more difficult to read and leads to less modular code. |
SQP-0003 | Explicitly code WHENEVER SQLERROR and WHENEVER OSERROR statements | |
SQP-0004 | Record changes to the script at the beginning (top), using the format as shown to the right. | REM Modification History REM By Date Description REM -------- ----------- ------------- REM username DD-MON-YYYY Short de- REM scription of REM changes |
