4 Replies Latest reply: Nov 22, 2009 11:07 AM by Hari_639 RSS

    Interactive Report NLS Language

    707841
      Hi again,

      Apex 3.2.0
      Oracle XE

      I was wondering if you could help.

      I have a combo box on a form region that selects a language. The idea of this is to change the number and date formats when displaying data in reports and on forms.

      On my reports and forms my format strings are set to a page item e.g. :P10_CURRENCY_FORMAT which contains the oracle currency format I need, e.g. FML999G999G999G999G990D00.

      I can then set the NLS_CURRENCY value by either altering the session or via the apex.util:

      ALTER SESSION SET NLS_CURRENCY='£';
      or
      APEX_UTIL.SET_SESSION_STATE('FSP_LANGUAGE_PREFERENCE','en-gb');

      and the correct currency symbol appears in front of the number in the report.

      This all works fine until I get to filtering interactive reports.

      The problem is when I want to filter interactive reports all the currency settings are set back to the APEX defaults set in APPLICATION/GLOBALIZATION.

      I then created a BEFORE HEADER process to alter the session, however when filtering interactive reports, the page isn't refreshed so this takes no effect.

      So I have ended up with £ symbols in my report columns and when I filter it, they all change to $ symbols, the next page reverts back to £ until I filter again.

      Am I going about it all the wrong way, or does anybody have any ideas?

      Thanks,

      Blackstone.
        • 1. Re: Interactive Report NLS Language
          fac586
          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.
          • 2. Re: Interactive Report NLS Language
            707841
            fac586,

            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?


            Blackstone.
            • 3. Re: Interactive Report NLS Language
              fac586
              [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.
              • 4. Re: Interactive Report NLS Language
                Hari_639
                Hi Guys,

                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..
                CREATE OR REPLACE FUNCTION f_set_nls
                RETURN INTEGER
                AS 
                BEGIN
                EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_CURRENCY=''£''';
                RETURN 1;
                END f_set_nls;
                And change your IR Report Query as..
                SELECT *
                FROM TBL_EMP
                WHERE f_set_nls = 1;
                So where ever you want to alter the session, you may just call the function ;-)

                Cheers,
                Hari