I am hoping to implement validations in my pages which would allow me to configure custom validations for different subscribers (this is a multi-tenant SaaS solution). I have run some tests and it does seem to work, but I still have concerns about my approach. At a high level, this is what I am doing:
Created a Validations process configured as "PL/SQL Function Body Returning Error Text" as"
RETURN PDS$_DYN_SQL_PKG.validation_error('CHECK_LICENSE');
This function looks up a PL/SQL block (stored in a table) for the specific subscriber and executes it as dynamic sql (using DBMS_SQL). The PL/SQL block has a single VARCHAR2 output which is either NULL or has some value. It contains calls to v() ( or nv() )such as v('P3_LICENSE'). My concern is regarding the ability to consistently access the APEX session variables in a dynamic sql session. My understanding is that when I execute dynamic sql it is in it's own database session separate from the calling session. I half expected that my test would not work but it worked perfectly. So, I am looking for guidance as to whether this is a valid approach before I pour more development effort into it.