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.
- Created by User
- Creation Date
- Updated by User
- Update Date
- User Agent
- 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.