This content has been marked as final. Show 4 replies
This approach won't work with interactive reports as the Before Header process is not executed by the AJAX call that refreshes the report.
This is a deficiency in IR operation that we can hope will be addressed by a future enhancement to provide the ability to run processes pre- and post-IR refresh.
In the meantime, IR refreshes do run the Virtual Private Database PL/SQL call defined in the application security attributes, so putting the NLS modification code there will work. Go to Application > Shared Components > Edit Security Attribute | Virtual Private Database (VPD), and make the call from there. Due to the specific requirements of the code that has to be called from here when used for security purposes, this is probably best done by setting this up to make 2 separate calls to packages defined outside APEX, one to handle security-related details and the other to orchestrate any other code that has to be run. (For example, in this case it may not be desired to alter the NLS environment on every page, so the program could take a parameter specifying the current page and apply the ALTER SESSION conditionally on this.)
[See this thread|http://forums.oracle.com/forums/thread.jspa?threadID=936753] for a similar issue.
I have been banging my head against a brick wall for days on this, trying all sorts of different ways of getting my currency symbols to work, I searched the forums many times and didn't find the post you mentioned. At least I have learned a great deal about Oracle NLS settings.
Your solution is fantastic and I can see potential for using it for any number of default settings.
I can't thank you enough,
One query, I can't really understand what the real use of the Virtual Private Database calls are? Is this not covered in the Authentication/Authorization sections?
[Virtual Private Database (VPD)|http://download.oracle.com/docs/cd/E11882_01/server.112/e10575/tdpsg_securing_data.htm#CHDBICEI] is a database security feature for controlling access to data. As it's implemented in the database and is transparent to application SQL and DML it's useful where the rules governing access to the data can be defined in terms of the data without needing to build additional security into every application.
The rules are often constructed using [application contexts|http://download.oracle.com/docs/cd/E11882_01/network.112/e10574/app_context.htm#DBSEG66353] which have to be set in the current database session. The "real use" of the [APEX Virtual Private Database|http://download.oracle.com/docs/cd/E14373_01/appdev.32/e11838/bldr.htm#sthref417] security attribute is to provide a mechanism to set these contexts in an APEX request.
Authentication determines the identity of the user. Authorization determines the operations that a user with that identity can perform. Access control (such as VPD) determines the data that those operations can be performed on.
I can see that the question is already answered but the moment I read the issue an idea pops into my mind and just want to share with you and here it is..
Create function to alter the session..
And change your IR Report Query as..
CREATE OR REPLACE FUNCTION f_set_nls RETURN INTEGER AS BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_CURRENCY=''£'''; RETURN 1; END f_set_nls;
So where ever you want to alter the session, you may just call the function ;-)
SELECT * FROM TBL_EMP WHERE f_set_nls = 1;