ICCS.MD.090a - Application Code SQL

	OMG = Oracle Method Guideline
	OMS = Oracle Method Standard

General

OMG-10042:

Document any unusual design decisions including the reasons in the Notes property of the respective object.

Rationale	This helps your team members understand the decisions that have been made. It is also valuable information for anyone performing QA on the project.

OMS-61518:

When a secondary access element or secondary access controlled element (SAC) is deleted, this should be recorded in the notes of the primary access element or Primary access controlled (PAC) element.

Rationale

Analysts or developers might want to know what happened to the SAC. If this kind of information is not recorded, the person that can explain, might not be available on the project, if at all.

Example	The deletion of an attribute is recorded in the notes of the entity.

OMS-61537:

Document any deviations of the CDM Standards in the respective Notes, including the OMS Standard code and the reason.

Rationale	Documenting these deviations helps your team members understand the decisions that have been made. This is also valuable information for anyone performing QA on the project.

Note: If you deviate from any OMS standards on a project level, do not include the deviation for every object. If there is only one Application System involved, then document these deviations in the Description property of the Application System. When there are more Application Systems involved, then it is recommended to document the deviations in a separate document.

Code Layout

OMS-52850:

Use the following layout for your SELECT statements: select..col1...alias1 ,.......col2...alias2 ,.......(...) from....table1...alias1 ,.......table2...alias2 where...condition1 and.....(condition2a ........or .........condition2b ........) (...) group by col1 ,........col2 order by col1 ,........col2 ; Note: The dots indicate the required number of spaces to properly layout the statement.

Rationale	This makes the code easier to read.

OMS-52851:

Use the following layout for a subquery: ...(select..col1 ....from....table1 ....where...condition1 ...) Note: The dots indicate the required number of spaces to properly layout the statement.

Rationale	This makes the code easier to read.

Inline Documentation

OMS-51050:

Use inline documentation only to document or clarify individual parts of a SQL statement.

Rationale	Too much inline documentation might make the SQL statement harder to read. The purpose of commenting is the opposite; to improve the understanding of the statement.

OMS-51051:

Place comments within SQL statements on the right-hand side of the SQL text, at the end of the line. Right-align the end of comment markers. Use complete lines within the SQL statement if the comment lines become too large.

Rationale	This makes it easier to read the statement

OMS-51052:

Document the lines of a statement that you (re)write to enable index use, specifying the original condition. This rule also applies to the hints you provide the cost-based optimizer.

Rationale	This makes it easier to understand why the statement has been written the way it is.

OMS-51053:

Document the lines of a statement that you (re)write to disable index use. This rule also applies to the hints you provide the cost-based optimizer.

Rationale

This makes it easier to understand why the statement has been written the way it is.

Example	select..emp.ename ,.......emp.sal from....oms_employees emp where...emp.job||''= 'CLERK' -- disabled index-use on JOB

OMS-51054:

Document the lines of a statement where you have to use a conversion function that disables index use, specifying the original condition.

Rationale	This makes it obvious that the conversion function is used intentionally to disable index use.

Datatype Conversion

OMS-51150:

Specify an explicit datatype conversion function where Oracle would otherwise perform implicit datatype conversion

Rationale	When datatype conversion is explicitly coded it is clear that it is intended. Implicit datatype conversion can unintentionally disable the use of indexes. In addition , the algorithms for implicit datatype conversion can be subject to change across releases of the Oracle Server.

OMS-51151:

When you compare an indexed column to a constant, apply any necessary datatype conversion to the constant.

Rationale	This ensures that the Cost-Based Optimizer will continue to consider the use of the index in the execution plan.

OMS-51152:

Do not store graphic images in a LONG column.

Rationale	The resulting implicit RAW_TO_HEX conversion takes up a lot of resources (memory and disk space). Use LOB column instead.

SQL Functions

OMS-51200:

Avoid the DECODE expressions that contain return arguments of more than one datatype.

Rationale

This may lead to confusing results as the datatype returned by the DECODE function is always the type of the first output variable in the decode list. If you cannot avoid this, explicitly convert all return arguments to a single datatype.

Example	select ename from...oms_employees emp where (deptno is null) or....(empno >= 1000 .......and .......deptno <= 30 .......);

OMS-51201:

Do not use the MAX function to retrieve a new highest value for a column; (for example, for key generation).

Rationale	With multiple users, and/or sessions this may lead to duplicate values. Instead, use a sequence generator.

Operators

OMS-51270:

Always use parentheses when an expression involves two or more operators.

Rationale	This makes it easier to read and maintain the code.

OMS-51271:

Always specify parentheses around the arguments of an OR operator.

Rationale	This makes it easier to read and maintain the code.

OMS-51273:

Avoid the use of the LIKE operator with columns of DATE and NUMBER datatype.

Rationale	This prevents implicit datatype conversion and thereby ensure that the Cost-Based Optimizer will continue to consider the use of the index in the execution plan.

OMS-51274:

Only use the NOT EXISTS operator when the subquery can only return the value TRUE or FALSE.

Rationale

The NOT EXISTS operator does not differentiate between UNK(nown) or FALSE. Therefore, under certain circumstances NOT EXISTS may yield an incorrect result.

Example	In a given company the bonus for a department depends on the fact that there are no salesmen who have not met their target. Assume that the comm column of the OMS_EMPLOYEES table signifies whether a target has or has not been met. A SQL statement to find all departments that do not contain Salesmen without Commission could read: select..'department #'||dpt.deptno from....dept dpt where...not exists (select..'x' ....................from....emp ....................where...emp.deptno..= dpt.deptno ....................and.....emp.job.....= 'SALESMAN' ....................and.....emp.comm....<= 0 ....................) The subquery will evaluate to TRUE for the departments where all salesmen have a commission entered larger than zero. If this is not the case, then either FALSE or UNKNOWN is returned. Assume the following salesmen exist in department #30: ENAME.........COMM -------.......--------- ALLEN.........300 WARD..........500 TURNER MARTIN........1400 The subquery returns the value UNKNOW, as Turner has an unspecified commission. When applying the EXISTS operator on an UNKNOWN result, the result is converted into FALSE, and when then applying the NOT operator on FALSE, the result is converted into TRUE. Therefore for the sole purpose of establishing a department's bonus, using this particular SQL statement, the result is in fact incorrect, as the result is actually UNKNOWN.

OMS-51275:

Only use the NOT IN operator where the expression will either result in TRUE or FALSE.

Rationale

The NOT IN operator returns the statement to false when at least one of the values are UNK(nown). This may yield an incorrect result.

Example	select..'TRUE' from....emp where...deptno not in (5,15,null) This example returns no rows because the WHERE clause condition evaluates to: deptno != 5 AND deptno != 15 AND deptno != null Because all conditions that compare a null result in a null, the entire expression results in a null. This behavior can easily be overlooked, especially when the NOT IN operator references a subquery.

SQL Statements

	Select

OMS-51301:

Specify a full column list in a SELECT statement. Do not use the asterisk (*) shorthand in SELECT statements in application programs.

Rationale	The asterisk implies all columns, while most of the time you will be selecting data into a limited number of variables. Each column you select passes through the interface layers of the Oracle Server, which requires extra memory for the context area.

OMS-51302:

Only select those columns that you actually use in your program.

Rationale	Each column you select passes through the interface layers of the Oracle Server, which requires extra memory for the context area.

OMS-51303:

Prefix each column in the SELECT list with a table alias, even in a statement that selects rows from a single table or view.

Rationale	Aliases make it easier to build and maintain SQL statements. An alias makes a SQL statement more readable and easier to expand without becoming sensitive to errors.

OMS-51304:

Specify a column alias for expressions, columns that are used more than once in a select list and for columns that are modified by a function. Use a meaningful column alias that indicates what the expression does.

Rationale

Aliases makes it easier to reference to these columns. An alias makes a SQL statement more readable and easier to expand without becoming sensitive to errors. Using an alias that indicates what the expression does, makes the code less prone to errors due to misunderstandings.

Example	select..emp.name...empname ,.......mgr.name...mgrname from....emp emp ,.......emp mgr where...emp.mgr = mgr.empno;

OMS-51305:

Never use the original column name for a column alias.

Rationale	If, later in your program, you refer to a field named after the column alias, you may have forgotten that you are not referring to the original column. This might be a problem for example when the column is converted to another datatype to obtain specific date formatting with the original datatype. If you use the original column name for the column alias, you will not be able to see that it has a different datatype than the original column. This may cause implicit datatype conversion.

OMS-52846:

When using expressions with grouping operations in a select list, then first perform the grouping operation and the expression.

Rationale

This performs better than the other way around.

Example	select..max(emp.sal) * 1.5 max_sal from....oms_employees emp instead of: select..max(emp.sal*1.5) max_sal from....oms_employees emp

	From

OMG-10091:

Do not use subqueries in the FROM clause of a SQL statement.

Rationale

SQL statements with subqueries on the FROM clause can often be rewritten into a SQL statement that is easier to understand.

Example	select..emp10.ename ,.......emp10.dname ,.......salgrd.grade from...(select emp.ename ........,......emp.sal ........,......dept.dname ........from...oms_employees....emp ........,......oms_departments..dept ........where..dept.deptno......= 10 ........and....emp.deptno.......= dept.deptno ........)............emp10 ,.......oms_salgrade salgrd where...emp10.sal between salgrd.losal ..................and.....salgrd.hisal ; This statement above gives the same result as: select..emp.ename ,.......dept.dname ,.......salgrd.grade from....oms_employees....emp ,.......oms_departments..dept ,.......oms_salgrade.....salgrd where...dept.deptno = 10 and.....emp.deptno..= dept.deptno and.....emp.sal.....between salgrd.losal ....................and.....salgrd.hisal ;

OMS-51320:

Specify an alias, preferably its short name as defined in the Oracle Designer Repository, for each table or view in the FROM clause, even if the SELECT statement only uses one table or view. If the table or view is used more than once in the SQL statement, then , preferably use a three character long table alias to give a logical indication of what each table usage represents. If this is difficult, then add a number to the table short name to make the table alias unique.

Rationale

Aliases make it easier to build and maintain SQL statements. An alias makes a SQL statement more readable and easier to expand without becoming sensitive to errors.

Example	select..emp.name ,.......mgr.name from....emp emp ,.......emp mgr where...emp.mgr = mgr.empno; This is easier to understand than: select..emp1.name ,.......emp2.name from....emp emp1 ,.......emp emp2 where...emp1.mgr = emp2.empno

OMS-51321:

Do not prefix table names in the FROM clause with schema owner names.

Rationale	Using schema owner names as table name prefix makes the SQL code less portable from one environment to another. The schema owner name may be different in another environment. Use synonyms instead.

	Where

OMG-10090:

Use bind variables when the SQL statements used are identical with the only exception of a comparison value.

Rationale

Bind variables can be used to ensure that SQL statements are shared across repeated executions. Therefore, when using bind variables in this way you prevent the SQL statement to be parsed on every execution only because the supplied value has changed.

Example	select emp1.ename from...oms_employees emp1 where..emp1.deptno = 10; and select emp1.ename from...oms_employees emp1 where..emp1.deptno = 20; can be changed into: select emp1.ename from...oms_employees emp1 where..emp1.deptno = b_deptno;

OMS-51340:

Write any join conditions directly following the reserved word WHERE.

Rationale	This is good programming practice.

OMS-52841:

Declare bind variables used in SQL statements with the type and length identical to the column definition with which they are compared.

Rationale	This prevents implicit conversion while comparing values. Also, inconsistent bind types can cause multiple child cursors to be created for the same SQL statement.

OMS-52845:

Never compare a column with an empty string in a where clause. Compare it with NULL instead.

Rationale

Comparing a column with an empty string always gives the result 'no rows selected '.

Example	select ename from...emp where..job is null; delivers all the employees where no job has been entered, while select ename from...emp where..job = ''; delivers 'no rows selected '.

OMS-52848:

Only use the BETWEEN operator when the boundaries are of the same type.

Rationale	Implicit datatype conversion may disable index use or result in incorrect value comparisons.

OMS-52849:

Avoid using a constant in front of the BETWEEN operator.

Rationale

When this is done it is hard to understand what is actually meant.

Example	Do not use: select..emp.ename from....oms_employees emp where...2500 between emp.sal ............and.....emp.comm

	Group By

OMS-51360:

Do not group on columns modified by other expressions or functions other than those stated in the SELECT list.

Rationale	If you do, this may lead to confusing query results.

OMS-51361:

Place as many conditions as possible in the WHERE clause instead of in the HAVING clause.

Rationale	In this way, the number of rows that need to be sorted is limited. The grouped data that is sorted by the HAVING clause is by definition, unindexed.

	Connected By

OMS-51380:

Always specify a START WITH clause in combination with a CONNECT BY clause.

Rationale

The START WITH identifies the row(s) to be used as the root(s) of a hierarchical query. If you omit this clause, Oracle uses all rows in the table as root rows, which will have a negative impact on performance.

Example	select..ename ,.......empno ,.......mgr ,.......job from....emp start with job = 'PRESIDENT' connect by prior empno = mgr ;

OMS-51381:

Do not reference the LEVEL pseudo-column in the START WITH clause.

Rationale	Referencing LEVEL in the START WITH condition is meaningless.

OMS-51382:

Do not use a CONNECT BY clause if circular referencing is a property of your data.

Rationale	Otherwise, this may lead to the following error message: ORA-01436: CONNECT BY loop in user data

	Order By

OMS-51400:

Use the ORDER BY clause only when your data really needs to be presented in a sorted order.

Rationale	The ORDER BY clause requires a sort operation if the Oracle Server cannot use an index to present the rows in a sorted order. This sort operation can be relatively expensive, especially when a large number of rows is processed. A sort operation can also have a negative impact on perceived response time; the user only sees the first results of the query when the complete sort operation is finished.

OMS-51401:

Do not rely on sorting that occurs by index use without specifying an ORDER BY clause.

Rationale	Using the Cost-Based Optimizer, you cannot be sure an index is always used.

OMS-51402:

Use column aliases to reference column names in the ORDER BY list with the set operators UNION, MINUS, and INTERSECT.

Rationale

Number references in the ORDER BY Clause are not SQL92 ANSI standard and may be desupported in future versions of Oracle Server.

Example	select..emp.empno ,.......emp.ename.....name from....oms_employees emp union select..prs.id ,.......prs.name......name from....oms_persons...prs order by name ;

OMS-51404:

Use the DESC keyword only if one or several columns have to be sorted in descending order.

Rationale	The default ordering is ascending.

OMS-51405:

If you use mixed ascending and descending sorting, specify both the ASC and DESC keywords for all column names in the ORDER BY list.

Rationale	This makes the code easier to understand and maintain.

	For Update

OMS-51420:

Limit the number of rows that you reserve for future update by always specifying a restrictive WHERE clause.

Rationale	The Oracle Server locks all the records you select using FOR UPDATE even before they are retrieved. Therefore, it is important to limit the number of records to the minimum.

OMS-51421:

Always specify a column list in the FOR UPDATE clause.

Rationale	Omitting the column list in a join statement results in placement of row locks on all rows of all tables selected by the statement.

OMS-51422:

Specify the primary key column(s) of a table, prefixed with the table alias, in the FOR UPDATE clause of a SELECT statement.

Rationale	This is to indicate from which table(s) rows must be locked. If you do not indicate this, then row locks will be placed on all rows of all tables selected by the statement

OMS-51423:

Only specify NOWAIT in a FOR UPDATE clause if the resulting error can be properly handled by the programming environment.

Rationale	Otherwise, unexpected and unwanted behavior may occur.

OMS-51424:

If possible, use the CURRENT OF CURSOR mechanism in combination with a FOR UPDATE clause, instead of direct references to a ROWID

Rationale	This simplifies your code and makes it more readable and easier to maintain.

	Subqueries

OMS-51440:

Only use subqueries if the desired query result cannot be achieved through a join condition

Rationale	Joins often performs better than subqueries.

OMS-51441:

Only use correlated subqueries when the result cannot be achieved through a non-correlated subquery (by replacing column references in the subquery with bind variables).

Rationale	Non-correlated subqueries are generally preferred above correlated subqueries, since then the subquery will be executed only once (unless the NOT IN operator is used). A correlated subquery however, is evaluated once for each row processed by the parent statement.

OMS-51442:

Do not use the concatenation operator when comparing a column list from a subquery to a column list from the main query.

Rationale

A VARCHAR2 concatenation disables index use in the main query and distorts the collating sequence for DATE and NUMBER datatypes, producing incorrect query results.

Example	Do not use: select..emp1.ename from....oms_employees..emp1 where...(to_char(emp1.deptno)||emp1.ename ........) in ..........(select to_char(emp2.deptno)||emp2.ename ...........from...oms_employees..emp2 ...........where..emp2.ename like 'SCO%' ..........) Use: select..emp1.ename from....oms_employees..emp1 where...(emp1.deptno ........,emp1.ename ........) in ..........(select..emp2.deptno ...........,.......emp2.ename ...........from....oms_employees..emp2 ...........where...emp2.ename like 'SCO%' ..........)

OMS-51443:

When comparing a column list from a subquery to a column list from the main query, only perform any necessary datatype conversion in the SELECT list of the subquery.

Rationale	This ensures that the Cost-Based Optimizer will continue to consider the use of indexes in the execution plan.

	Outer Join

OMS-51450:

Do not use outer-join columns in predicates that are branches of an OR.

Rationale	Using outer-join columns in predicates that are branches of an OR makes the logical expressions complicated, and therefore may not be well formed.

OMS-51451:

Limit outer join columns to the top level of the WHERE clause, or to (nested) AND predicates only.

Rationale	Otherwise, you increase the complexity of the logical expressions, and they may therefore not be well formed.

	Insert

OMS-51460:

Specify a full column list for the target table in INSERT statements.

Rationale	This makes your statement less vulnerable to changes. If a table is recreated with a different column ordering, your statements will still work.

OMS-51461:

Specify NULL values for unknown values in INSERT statements with the VALUES or SELECT FROM construct. Do not rely on the implicit insertion of NULL values in non-specified columns.

Rationale	If you do not do this, then it is not clear from your statement whether a column is deliberately left out to insert a NULL value, or was simply forgotten. Also, this prevents default values from being generated whenever a default is defined for the column.

	Update

OMS-51480:

Only use ROWID in an UPDATE statement after you have first locked the target rows using a SELECT FOR UPDATE statement in the same transaction.

Rationale	This is to make certain that you are updating the same row you intend to update. If another user has deleted the record in the mean time, a new record may get the same ROWID as the deleted record, and you would thereby update a different record than the one you initially intended to update.

	Delete

OMS-51500:

Always specify the FROM keyword in DELETE statements.

Rationale	Mixing the usage, to sometimes use FROM and sometimes not, is confusing.

OMS-51501:

Only use ROWID in a DELETE statement after you have first locked the target rows using a SELECT FOR UPDATE statement in the same transaction.

Rationale	This is to make certain that you are deleting the row you actually intend to delete. If another user has deleted the record in the mean time, a new record may get the same ROWID as the deleted record, and you would thereby physically delete a different record than the one you initially intended to delete.

	Returning Clause

OMS-52843:

Use DML returning clause if you need access to values that are server-derived as a result of a DML statement you are performing

Rationale

When using a RETURNING clause to return values from updated columns, you eliminate the need to perform a SELECT following the DML statement.

Example	The following example returns values from the updated row and stores the result in PL/SQL variables BND1, BND2, BND3: update.....oms_employees set........job ='MANAGER' ,..........sal = sal + 1000 ,..........deptno = 20 where......ename = 'JONES' returning..sal*0.25 ,..........ename ,..........deptno into.......bnd1 ,..........bnd2 ,..........bnd3 ;

	Views

OMS-51520:

Specify a full column list in a CREATE VIEW statement.

Rationale	If you use the *, the statement is transformed into a full column list. However, if a column is added to the table, then the view needs to be recreated. Therefore using the * may lead to unexpected behavior.

OMS-51521:

Prevent the use of functions or expressions in a CREATE VIEW statement, apart from group functions, if the columns based on these expressions will be searched.

Rationale	This has a negative effect on performance.

OMS-51522:

For all columns modified by expressions or functions in the SELECT list of a CREATE VIEW statement, use the column alias as a column name in the view.

Rationale	In this way, you can easily match the view columns and their source in the SELECT statement.

OMS-51523:

Avoid the use of columns in the SELECT list of a view that belong to the outer-joined table.

Rationale	This ensures that the Cost-Based Optimizer will continue to consider the use of indexes in the execution plan.

Application Systems

	Notes

OMS-61517:

Record the deletion of primary objects such as entities, modules, etc. as revision history in the Notes of the Application System. Add new entries on top.

Rationale

Analysts or developers might want to know what happened to the object. If this kind of information is not recorded, the person that can explain, might not be available on the project, if at all. Putting new entries on top gives quick insight into the latest change.

Example	REVISION HISTORY Label Date Name Description 1.1 12-11-1999 S. Muller Removed function 31 and module ASP0050F 1.0 06-07-1998 L. Jellema Removed entity STATUS 1.1 ---- Removed function 31 and module ASP0050F because their functionality was dropped as result of tighter planning. 1.0 ---- Removed Entity STATUS, functionality now in domain STATUS