1 2 Previous Next 25 Replies Latest reply on Apr 6, 2005 7:57 PM by joelkallman-Oracle

    HTMLDB - HOWTO Work with Dates

      I would like to document some issues I had with the HOWTO document called "Work with Dates".


      The first issue is in regards to the section called "Using an Application Level Format Mask to Customise a Date Picker". In the example, the user wants to change the date format in input/output screens to the Date format of "YYYY-MM-DD" (also known as the Canadian National Standard Date format or the ISO Standard Date format). In the example, a substitution string is created and a substitution value of “YYYY-MM-DD’ is assigned (this is done at the Application Level under the Application Tab and then Edit Attributes and then Substitution Strings). The example then shows that when the Date Picker is used in the form, the date returned is in the YYYY-MM-DD format. What the document did not say “specifically” was that for this to work, the Substitution String has to be called “PICK_DATE_FORMAT_MASK” exactly. If you call it something else or have a spelling error in the Substitution String Name, this will not work. The substitution String must be named exactly as “PICK_DATE_FORMAT_MASK”. I believe a little clarification of this fact will save individuals a great deal of time, in the future.

      Note that this change only affects those Date Pickers that are specify as “Date Format of “Date Picker (use application format mask)”.

      The other problem falls under the “Changing the Date Format in a Form (Using a Date Picker)” section. In this section, it leads one to believe that if you have a date in one format and want to display it in another format, you simply assign a FORMAT Mask at the Item’s Source Level. This does not work.

      For example, if a call to the database (SQL or PL/SQL) returns a date in the format of DD-MON-YYYY, one cannot simply enter a value of YYYY-MM-DD in the FORMAT section of the Item’s Source to have the date somehow magically converted to the YYYY-MM-DD format. If you have created an entry for the PICK_DATE_FORMAT_MASK and set it to YYYY-MM-DD and then use the “Date Picker (use application format mask)” for a Date Picker and set the FORMAT at the Item Level to “YYYY-MM-DD” and retrieve a row from the database, you will get the following error:

      Unable to cast value "11-JUN-1921" to date format mask PICK_DATE_USING_FLOW_FORMAT_MASK

      The work-around for this is to forgo setting the FORMAT Mask at the Item level, as doing so accomplishes nothing. You want to define the format for your PICK_DATE_FORMAT_MASK at the application level and then set your date fields to “Date Picker (use application format mask)” and then before you perform any database I/O (SELECT,INSERT,UPDATE,DELETE or calls to Stored Procedures) issue an


      And set the NLS_DATE_FORMAT to the format of you Date Picker (the String Value you assigned to the Substitution String = PICK_DATE_FORMAT_MASK). This will cause the date to be in the same format of the Date Picker.
        • 1. Re: HTMLDB - HOWTO Work with Dates
          hey paul--

          thanks for your feedback. i've noted that naming the substitution string PICK_DATE_FORMAT_MASK wasn't clear enough for you in that how-to, but i was unable to reproduce your other issue. so for me, using the item-level "Format Mask" attribute worked as advertised even when i tested your exact scenario. if i had to guess as to what we're doing differently, i'd guess that your form doesn't use our automatic dml processing and mine (and the how-to's) does. it's not so clearly explained in the doc (and i've noted this, too), but that Format Mask attribute is only applied to items that are associated with htmldb auto-dml processes. so in the how-to they have you use a wizard to generate a form on EMP. that form uses the auto dml processes, so entering a format mask for hiredate works fine. if you just had a regular form that fetched out the values manually, your format mask wouldn't be applied, and you'd get that "unable to cast value" error. anyhow, i hope i'm on the right track with your scenario, and if so, i'm sorry for the confusion. if i've guessed incorrectly, though, please do let me know.

          • 2. Re: HTMLDB - HOWTO Work with Dates
            Hi, is there an easy way to do

            on every page in an application
            instead of doing this in the various processes.

            How to globally change the default format mask?

            I think it could be done via the DAD (Database Access Descriptor) but that would affect every HTMLDB application running in the same DAD.

            I need to this at the application level.

            Please advice!

            Mihail Daskalov
            • 3. HTMLDB - HOWTO Work with Dates

              You guessed correctly. I did not use a Wizard to create my form. I have existing PL/SQL Stored Procedures that will perform the data manipulation at the database level. These stored procedures have error handling and problem reporting within them and also pass "user friendly" message back to the form. So why build something new, when the existing “tried and true” code already does 90% of my work!

              I guess my next question is “why the difference between the Wizard generated "auto-dml" and the PL/SQL Stored Procedures”?

              The date information being passed in and out of the stored procedure is in the Oracle internal DATE format. However, the form seems to be handling the date as a character string. Which, if this truly is the case, is a huge step backwards for Oracle. Dates should be maintained in their internal DATE format and displayed in whatever format the end user wants to see (E.g. YYYY-MM-DD or DD-MON-YYYY).

              I have a few more thoughts about the HTML DB DATE format, but I will address that in reply to Mihail in the post below.
              • 4. HTMLDB - HOWTO Work with Dates

                I am new to HTML DB but certainly not new to Oracle. And I am asking myself some of those same questions. Like why is HTML DB treating Oracle DATE data as character? And why can’t I change the default date format at the application level or at the user level.

                I am in a Bilingual province, which means I have to show data/messages/dates according to the NLS_LANG setting of the end user. So, if I go with the DD-MON-YYYY, the month of April has to show as APR for English end users and AVR (Avril) for those that have chosen to see French.

                Added to this date requirement is the fact that my system must handle some dates, like Date of Birth, without the time component (meaning default to midnight) and others, like Date of Death, with the Hour and Minute component (with seconds set to zero). My system is full of these dates and so the more flexibility I can get, the easier it will be to build the system.

                It would be great if I could use a DATE PICKER to enter both. Or at the very least, get me started on both – meaning use it to enter the Date of Birth (without time) and to use it to get the DATE portion of the Date of Death, and then manually enter the time.

                Can you change the default date format (as in NLS_DATE_FORMAT) in the DAD (Database Access Descriptor) specifically? I had the PlsqlNLSLanguage entry changed from AMERICAN_AMERICA.WE8ISO8859P1 to ENGLISH_CANADA.WE8ISO8859P1. But that did not change the date format (my default format was still DD-MON-RR or DD-MON-YY, which of course is an unacceptable date format for my system). And as you have noted, our System Admin is hesitant to make these kinds of changes as doing so affects all HTML DB applications using the same DAD.
                • 5. Re: HTMLDB - HOWTO Work with Dates

                  Thanks for the time and effort you spent to contribute. We'll be sure to review your findings and update the How To accordingly.

                  • 6. Re: HTMLDB - HOWTO Work with Dates

                    Firstly, let me say thank you for your comments. Instead of posting something like "dates suck", your comments are well-reasoned and articulate. Very helpful.

                    There are a lot of questions raised in these postings, so I'll try to handle them in a logical order.

                    1) HTML DB intentionally makes no use of Oracle internal methods. Plain and simple, HTML DB is a pure PL/SQL application. So the option of storing dates in the internal Oracle date format is not available. When HTML DB selects date values out of the Oracle database, the storage format is no different than if you did this yourself in PL/SQL. So the real answer is not to change how we store and manipulate date values internally in HTML DB, but instead to correct the issues that result in this suggestion being made.

                    2) In HTML DB 1.5.x, a developer had to specify both the Display Type of the Date Picker item as well as the format mask for the item. This resulted in confusion among HTML DB developers, and it was completely unnecessary. There should be no situation where someone wants a Date Picker of a certain type and have the format mask be different. So this is corrected in HTML DB 1.6...specification of the format mask is not necessary when using a Date Picker. It is derived from the type of Date Picker itself.

                    3) The explicit format mask of an item and implicit format mask of a Date Picker (in HTML DB 1.6) has two purposes. It is used to convert from the database value to a display value in your HTML DB application. And it is also used to convert from the Web page back into the corresponding datatype when using HTML DB Automatic Row Processing. So if you have a Date Picker of type DD-MON-RR and it is associated with a date column, HTML DB automatically applies this format mask to the user-entered data value and converts to an internal date value.

                    4) You have the power of converting to internal Oracle dates using the TO_DATE function. So your process which calls your stored procedure could be:

                    my_procedure( p_date_value => to_date(:P10_DATE_ITEM));

                    And when the user has entered a bogus date value, you will have to deal with that as well.

                    I do not view this as a step backwards for Oracle. I view this as flexibility.

                    5) The NLS_DATE_FORMAT is ultimately derived from the language settings of your application. If you want different NLS settings based upon language, you can simply publish a translated version of your application. This is the same logic that is used for HTML DB itself for all 9 translated languages.

                    Run a simple one page application in Debug mode and you'll see the NLS settings at the top of the page. Change the Application Primary Language to 'fr' and then run the page in debug again. Run htmldb.oracle.com with browser language 'fr' and notice the differences in the results of your date-related queries.

                    6) If you want to override the NLS settings provided by HTML DB, you can simply add two application-level processes. One should be at point "On Load: Before Header (page template header)". The other should be at point "On Submit: After Page Submission - Before Computations and Validations". The process can be exactly as described, an EXECUTE IMMEDIATE 'ALTER SESSION...' or you could call dbms_session.set_nls and accomplish the same.

                    I do hope this provides some assistance. And all of the feedback in this thread is quite valuable. We will use this to continually enhance this area of HTML DB.

                    • 7. Re: HTMLDB - HOWTO Work with Dates
                      I have another date related question.

                      Can you tell me why the V$NLS_PARAMETERS that I see in the HTMLDB Application differs from that in the database?

                      I log into my HTMLDB Database via SQL*Plus and run:


                      I get a value of 'YYYY-MM-DD'.

                      In the HTMLDB Application, SQL Workshop, SQL Command Processor the same query is run:

                      I get a value of 'MM-MON-RR'.

                      I also find the values for NLS_LANGUAGE, NLS_TERRITORY, NLS_ISO_CURRENCY and NLS_SORT are not what I would expect (meaning they are not the same as the default values of the database). So, these are getting modified somewhere and I would like to know where.

                      I fully understand that I can execute ‘EXECUTE IMMEDIATE 'ALTER SESSION...’ or call dbms_session.set_nls, however, I would much prefer fixing this problem at its source.

                      I am told that we are not using the marvel.conf but a DAD. I am not certain exactly what that means and if the value of PlsqlNLSLanguage has anything to do with this. However, I figure someone at Oracle has to know where these values are being modified and why.
                      • 8. Re: HTMLDB - HOWTO Work with Dates

                        The difference in settings is most likely caused by the way the DAD is configured. Its definition can be found in either marvel.conf or dads.conf.

                        • 9. Re: HTMLDB - HOWTO Work with Dates

                          Here are the 2 entries in our dads.conf, excluding the PlsqlDatabaseConnectString and PlsqlDatabasePassword entries. I assume there are 2 entries as there are 2 services? Is it only the <Location /pls/htmldb> that concerns us?

                          # ============================================================================ # mod_plsql DAD Configuration File # ============================================================================ # 1. Please refer to dads.README for a description of this file # ============================================================================ # Note: This file should typically be included in your plsql.conf file with # the "include" directive. # Hint: You can look at some sample DADs in the dads.README file # ============================================================================
                          <Location /pls/portal>
                          SetHandler pls_handler
                          Order allow,deny
                          Allow from All
                          AllowOverride None
                          PlsqlDatabaseUsername portal
                          PlsqlNLSLanguage AMERICAN_AMERICA.WE8ISO8859P1
                          PlsqlAuthenticationMode SingleSignOn
                          PlsqlSessionCookieName portal
                          PlsqlDocumentTablename portal.wwdoc_document
                          PlsqlDocumentPath docs
                          PlsqlDocumentProcedure portal.wwdoc_process.process_download
                          PlsqlDefaultPage portal.home
                          PlsqlPathAlias url
                          PlsqlPathAliasProcedure portal.wwpth_api_alias.process_download

                          #================ HTML DB Setup ================#
                          #= Added by Stephane B. =#
                          #= March 22, 2005 =#

                          Alias /i/ "D:\OAS904_BI\Apache\Apache\images/"
                          <Location /pls/htmldb>
                          SetHandler pls_handler
                          Order deny,allow
                          Allow from All
                          AllowOverride None
                          PlsqlDatabaseUsername     HTMLDB_PUBLIC_USER
                          PlsqlDefaultPage           htmldb
                          PlsqlDocumentTablename wwv_flow_file_objects$
                          PlsqlDocumentPath docs
                          PlsqlDocumentProcedure wwv_flow_file_manager.process_download
                          PlsqlAuthenticationMode Basic
                          PlsqlNLSLanguage ENGLISH_CANADA.WE8ISO8859P1
                          • 10. Re: HTMLDB - HOWTO Work with Dates

                            Yes, /pls/htmldb is the DAD most likely affecting your NLS settings.

                            • 11. Re: HTMLDB - HOWTO Work with Dates

                              If the /pls/htmldb entry in the DAD has PlsqlNLSLanguage ENGLISH_CANADA.WE8ISO8859P1 and the database has the same setting, why is my HTMLDB application set to AMERICAN_AMERICA ?
                              • 12. Re: HTMLDB - HOWTO Work with Dates

                                Just to clarify, my question basically boils down to the fact that the National Language settings found in the "SQL Workshop" "SQL Command Processing" area are not the values found in the Oracle Database nor are they the values found in the /pls/htmldb in the DAD.

                                Where are these NLS parameters being changed/overridden?
                                • 13. Re: HTMLDB - HOWTO Work with Dates

                                  SQL Workshop, which is an HTML DB application itself, has its NLS settings determined by the browser's language. Run your query several times, varying your browser language from de to fr-ca to en-us and you'll see that the NLS language and territory (and implicitly, the date format) are being set based upon your browser language.

                                  For your own applications, you have complete control if HTML DB alters the NLS settings for every page view. But for SQL Workshop, we decided to derive this from the browser. So when any user of HTML DB issues the query SELECT SYSDATE FROM DUAL, it is presented in their localized format.

                                  • 14. Re: HTMLDB - HOWTO Work with Dates
                                    I am using Internet Explorer 6.0.2800. I have gone into TOOLS then INTERNET OPTIONS and then LANGUAGES and changed the language settings there. After doing that, I went into SQL Workshop and selected SYSDATE from DUAL and so no difference. I also checked V$NLS_PARAMETERS and saw no difference. So, what am I missing here?

                                    As for "complete control" for my applications, how do I have complete control? The application is not using the NLS parameters as defined in the database and is not using the NLS parameters as defined in the DAD. Again I ask, where is this setting being changed?
                                    1 2 Previous Next