December 1, 2015 APEX No Comments

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.

Written by Sohilkumar Bhavsar
Cloud Architect at Zero Integration Ltd. 14+ years of experience with Oracle, Linux, Networking, Data Centre and Public Clouds. Played major role in development, implementation and management of SaaS CRM using AI and ML. Integrated 15+ Client CRM across United Kingdom and Australia.