SQL Coding Standards

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 reddyvijayk@gmail.com

SQL Coding Standards

1. Standard keywords, standard or custom package/function names, Table alias, operators are in upper case (e.g. SELECT, DBMS_OUTPUT etc.)
2. Column names, column alias, table names, view names are in lower case.
3. 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
   ,RCT.trx_date invoice_date
   ,SUM(APS.amount_due_original) invoice_amount
   .........
   FROM ra_customer_trx RCT
   ,ar_payment_schedules APS
   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
   ,RCT.trx_date
   ORDER BY RCT.trx_number;
4. Only one column/expression, table or selectivity condition appears on one line.
5. Comma separating the columns, tables or parameters comes at beginning of lines, not at end.
6. SELECT clause of sub-query starts on a new line.
7. No more than 3 parameters of a function (standard or custom) per line.
8. If parameter is an expression or nested function, then each expression or nested function appears on new lines.
9. Only one join condition appears in one line.
10. Keywords: AND or OR appears at the beginning of line (and not at the end).
11. The column names are not unnecessarily abbreviated.
12. Column name are inter-connected using ‘_’ (e.g. error_code instead of errorcode)
13. Each table (except for dual) has a table alias.
14. The table alias is formed using the first characters of ‘parts’ of table name.