Tuesday, December 1, 2015

PL/SQL Best Practices

A blog after many years :-)

My best practices for PL/SQL are below:
  • Always use Exception Block within each block, function, procedure
  • Send email to your development team for any error which occurs in Production
  • Give top priority to error email
  • Make sure you will never get any error email :-)
  • Write reusable procedures/functions and SQL (If you use APEX you can use List of values)
  • Avoid using Triggers except audit
  • Make sure you add below 6 columns to important tables for Audit.
    1. Created by User
    2. Creation Date
    3. Updated by User
    4. Update Date
    5. User Agent
    6. IP Address
  • Avoid using Stateful Package i.e. do not use package level variables/constants and instead create a DB table which stores all constants. This will avoid causing "ORA-06508: PL/SQL: could not find program unit being called" if you need to do a minor change in package then this could heavily affect where the application has thousands of users.

Thanks to Steven Feuerstein.