This is Vijay Bhaskar Reddy, Working as a Oracle Apps Sr. Technical Consultant. I want to share my experience with all of you through this blog(www.smartoracle.blogspot.com). If you are having any queries feel free to mail me at firstname.lastname@example.org
SQL Coding Standards
- Standard keywords, standard or custom package/function names, Table alias, operators are in upper case (e.g. SELECT, DBMS_OUTPUT etc.)
- Column names, column alias, table names, view names are in lower case.
- SQL statements are indented/aligned as per coding standards doc.(e.g given below) Each ‘Clause’ of SQL statement starts in a new line.e.g.
SELECT RCT.trx_number invoice_number
FROM ra_customer_trx RCT
WHERE RCT.customer_trx_id = APS.customer_trx_id
AND APS.class = ‘INV’
AND RCT.completed_flag = ‘Y’
AND RCT.customer_trx_id IN (
SELECT DISTINCT ARA.customer_trx_id
FROM ar_receivable_applications ARA
WHERE apply_date BETWEEN ADD_MONTHS(SYSDATE,-1) AND SYSDATE
GROUP BY RCT.trx_number
ORDER BY RCT.trx_number;
- Only one column/expression, table or selectivity condition appears on one line.
- Comma separating the columns, tables or parameters comes at beginning of lines, not at end.
- SELECT clause of sub-query starts on a new line.
- No more than 3 parameters of a function (standard or custom) per line.
- If parameter is an expression or nested function, then each expression or nested function appears on new lines.
- Only one join condition appears in one line.
- Keywords: AND or OR appears at the beginning of line (and not at the end).
- The column names are not unnecessarily abbreviated.
- Column name are inter-connected using ‘_’ (e.g. error_code instead of errorcode)
- Each table (except for dual) has a table alias.
- The table alias is formed using the first characters of ‘parts’ of table name.