Best practices and recommendations for preparing SQL Scripts

We have code release for our Oracle databases frequently. Typically it is implemented by executing various SQL or PL/SQL scripits on servers from SQL*Plus.  Many times I have observed there are simple syntax errors in the scripts we received from development team and also the format of the script is not consistent. I thus tried to compile a list of best practices and recommendations for preparing the release scripts for the team. Here are some points:

• Add spool to every script in the following format as minimum:

             set echo on timing on
             spool log/script_name.log
              ...
             spool off

• Separate DDL and DML SQL statements into different scripts.

              Recommend using suffix to distinguish DDL and DML script: e.g. script_ddl.sql, script_dml.sql.

• Separate different type of object creation into different scripts:

           i.e. table, index, package etc. Recommend using the following suffix to distinguish different script in   the format: script_xxx.sql. The ‘xxx’can be:

                      Package specification              pks
                      Package body                         pkb
                      Package sepc and body          pkg
                      Procdueure                             pro
                      Trigger                                    trg
                      Function                                  fun
                      Create table script(s)               tab or ddl
                      Synonym creation statements   syn
                      Index definition                        idx
                      Constraint definitions               con

• Use lower case ".sql" as a suffix for all scripts

        [Mar 16,2010 Update - I tried to do: grep -i create *.sql yesterday, I failed to find a new table creation  due to the script ended with ".SQL". ]

• Recommend consolidating the script in the execution sequence of DDL first followed by DML.

• Recommend using some GUI tools such as TOAD, SQL Developer etc to minimize the syntax errors.

                   At minimum, everyone should do the following:
                   - Check if missing "schema" prefix for table, index names etc.
                   - Check if missing ";" in SQL
                   - Check if missing "/" as the end the PL/SQL begin/end block
                   - Check if comma used properly in the table column list for example
                   - Check if upper or lower cases are used consistently

• Recommend including the back out method (CTAS) for DML script

• Recommend formatting and aligning SQL statements properly whenever possible for improving readability

• Add "show error;" at the end of PL/SQL procedure, function, packages units.

• Avoid using ampersand character ‘&’ within any comment lines in the SQL scripts.

• Avoid using space in the script file name.

• Avoid adding storage clause in the table or index creation statement

           Note: defer to primary DBA to make such a decision if defaults are not desirable

• Avoid using more than 30 characters for table name or column name.

• Avoid using system-generated constraint names.

           For example the following method to add a primary key constraint is not acceptable:
              alter table myschema.t modify (id primary key);

• Recommend using the following methods to create primary key

         (1) Within CREATE TABLE statement

              CREATE TABLE t
              ( id number,
               val varchar2(40),
                constraint t_pk primary key (id) using index tablespace tablespace_name
              ) tablespace  tablespace_name;

        (2) Through ALTER TABLE statement

              CREATE TABLE t
              ( id number,
               val varchar2(40)
               ) tablespace tablespace_name ;

              ALTER TABLE t
              add constraint t_pk primary key(id) using index tablespace tablespace_name;