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.