1 2 Previous Next 23 Replies Latest reply on Nov 21, 2009 8:09 AM by Sarah QA

    Date initialization Problem

    433935
      Dear Friend

      Date initialization Problem
      I have this initialization statement in WHEN-VALIDATE-ITEM
      :H_TRANS_DATE := TO_DATE(TO_CHAR(SYSDATE,'DD/MM/YYYY HH12:MM:SS'));

      And I am getting this message
      FRM-40735 WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-01843

      Best Regards

      Jamil Alshaibani
        • 1. Re: Date initialization Problem
          Sarah QA
          hi

          try to use something like this.
          :H_TRANS_DATE := TO_CHAR(SYSDATE,  'DD/MM/YYYY HH24:MI:SS'));
          sarah
          • 2. Re: Date initialization Problem
            Dora
            Hi,

            Set H_TRANS_DATE 's datatype as DateTime

            re-write the When-validate-item as

            :H_TRANS_DATE := sysdate;

            This will not cause any invalid month error even if your format mask changes..

            regards

            Edited by: Dora on Nov 18, 2009 1:29 PM
            • 3. Re: Date initialization Problem
              Sarah QA
              hi
              u may try this too.
              :H_TRANS_DATE := TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS');
              • 4. Re: Date initialization Problem
                433935
                Hi
                Actually what I want the system date and time should be initializes automatically at the insert mode and the user can renter the date but time should be initialize from the oracle server, and what I notice the second is changing but the minute is not changing always the same for example 18/11/2009 03:11:59 it became as in second insert as 18/11/2009 03:11:35 that the minute is not changing ,and what is the different between $$dbdatetime$$ and $$datetime$$ in the initial value of the property

                Best regards
                jamil
                • 5. Re: Date initialization Problem
                  527733
                  Use $$DATE$$, $$DATETIME$$, and $$TIME$$ to obtain the local system date/time; use $$DBDATE$$, $$DBDATETIME$$, and $$DBTIME$$ to obtain the database date/time, which may differ from the local system date/time if, for example, you're connecting to a remote database in a different time zone.

                  http://oraclet.blogspot.com/2008/02/system-variables-toutorial.html
                  • 6. Re: Date initialization Problem
                    Sarah QA
                    check this.

                    [Date and Time|http://download.oracle.com/docs/cd/E11882_01/server.112/e10729/ch4datetime.htm#i1006333]

                    sarah
                    • 7. Re: Date initialization Problem
                      433935
                      Hi Sarah

                      Thank you very much for your cooperation and help it was very helpful
                      And I want in the second field, if the user only enters the date without the time then it will take it from this script

                      DECLARE
                           LOC_VAR VARCHAR2(20);
                      BEGIN     
                      SHOW_MESSAGE(':H_EXIT_DATE'||:H_EXIT_DATE);
                      IF :H_EXIT_DATE IS NOT NULL THEN
                      LOC_VAR := TO_CHAR(:H_EXIT_DATE,'DD/MM/YYYY')||''||TO_CHAR(SYSDATE,'HH12:MI:SS');
                      SHOW_MESSAGE('LOC_VAR'||LOC_VAR);
                      :H_EXIT_DATE :=TO_DATE(LOC_VAR);
                      SHOW_MESSAGE(':H_EXIT_DATE'||:H_EXIT_DATE);
                      END IF;
                      END;

                      And the above script is write in WHEN-VALIDATE-ITEM trigger
                      But I am getting the following message when I press enter key

                      FRM-40735 WHEN-VALIDATE-ITEM TRIGGER RAISED UNHANDLED EXCEPTION ORA-01843

                      And also I want some time if the user enter the date and time, how can I check if the user have been enter the time with the date, so that I do not want the above script to bee executed if he enter the time with date,and I want it to be executed when he enter only the date without the time.

                      Best regards

                      Jamil
                      • 8. Re: Date initialization Problem
                        Sarah QA
                        hi

                        is the data type is Date or char please check.

                        sarah
                        • 9. Re: Date initialization Problem
                          433935
                          Hi

                          Yes the data type is Date, but I want the data type as datetime,but when I make it as datetime I am getting this message
                          FRM-50025 Date/time must be entered in a format like DD/MM/YYYY HH24:MI[:SS]

                          Best regards

                          Jamil
                          • 11. Re: Date initialization Problem
                            Sarah QA
                            hi

                            You need to set the Forms default date mask, not just the Database default date mask.

                            From the online help, under "About format masks for dates":

                            Types of date format masks

                            Form Builder uses the following date format masks:

                            1 User-created date format masks for individual items

                            2 PLSQL date format mask

                            3 Builtin date format mask

                            4 Database date format mask

                            5 Input date format mask

                            6 Output date format mask

                            7 Error date format mask

                            1. Individual item's date format mask

                            You create and set these masks through the Format_Mask property. As noted above, avoid creating these individual masks if the general-purpose masks will suffice.

                            2. PLSQL date format mask

                            You set this mask in the PLSQL_Date_Format property. It is STRONGLY RECOMMENDED that for new applications, you set this property to a mask containing full century and time information. It is also recommended that you use the same setting for the Builtin date format mask. (See the example below.)

                            3. Builtin date format mask

                            You set this mask in the Builtin_Date_Format property . It is STRONGLY RECOMMENDED that for new applications, you set this property to a mask containing full century and time information. It is also recommended that you use the same setting for the PLSQL date format mask. (See the example below.)

                            4. Database date format mask

                            Each database session within a Forms application has a single Database date format mask. This mask is used by the database server to convert a string to a date value, or vice versa, in the course of evaluating a query.

                            A default value for this mask is established by the Oracle Server's NLS_DATE_FORMAT (or NLS_LANG or NLS_TERRITORY) initialization parameter. This can be overridden in all new database sessions for a particular client, by setting the client's NLS_LANG and NLS_DATE_FORMAT environment variables.

                            Within a Forms application, the mask value can be further overridden on a session-by-session basis, by issuing an ALTER SESSION. It may simplify an application's logic if it is set to the same format mask as the application's PLSQL_Date_Format and Builtin_Date_Format properties. (See the following example.)

                            Note that if you do an OPEN_FORM with the SESSION option specified, then the opened form will have a new database session; therefore, you might want to alter its session, as well.

                            Example for PLSQL, Builtin, and Database mask creation

                            To create and coordinate these masks, the PRE-FORM trigger in the application's initial form could contain:

                            SET_APPLICATION_PROPERTY(PLSQL_DATE_FORMAT, 'YYYY/MM/DD HH24:MI:SS');

                            SET_APPLICATION_PROPERTY(BUILTIN_DATE_FORMAT, 'YYYY/MM/DD HH24:MI:SS');

                            FORMS_DDL('ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY/MM/DD HH24:MI:SS''');

                            5. Input date format mask

                            The Input date format mask (actually a set of masks) is used to extract a value from an item which has no format mask. That is, the mask is used to convert a user-entered string into a native-format date value.

                            The system administrator or end user may set the environment variables FORMS60_USER_DATE_FORMAT and FORMS60_USER_DATETIME_FORMAT to specify the format masks used to extract values from items of type DATE and DATETIME, respectively. In each case, multiple format masks may be specified, separated by vertical bars. When converting a user-entered string, the format masks are used in the order specified, until a conversion succeeds or until the format masks are exhausted.

                            For example, these runtime environment variables might be set as follows:

                            FORMS60_USER_DATE_FORMAT FXFMDD-MM-RRRR

                            FORMS60_USER_DATETIME_FORMAT FXFMDD-MM-RRRR HH24:MI:SS|FXFMDD-MM-RRRR HH24:MI

                            This would force the end user to enter values into DATE items (with no format mask) in the format exemplified by 31-6-97. (The RRRR enables years between 1950 and 2049 to be entered with the century omitted). But for DATETIME items, the end user could enter values either in the format exemplified by 31-6-97 13:45:30 or else in the format exemplified by 31-6-97 13:45 (which would be interpreted as 31-6-97 13:45:00).

                            If either of these two runtime environment variables is not defined, then Form Builder will construct default format masks, as explained below.

                            6. Output date format mask

                            The Output date format mask is used to display a value in an item which has no format mask, or in an LOV column. There are actually two such masks, one for DATE items and LOV columns, and one for DATETIME items. (DATETIME LOV columns do not exist.). The system administrator or end user may set the environment variables FORMS60_OUTPUT_DATE_FORMAT and FORMS60_OUTPUT_DATETIME_FORMAT to specify these format masks explicitly.

                            If FORMS60_OUTPUT_DATE_FORMAT is not defined, but FORMS60_USER_DATE_FORMAT is defined, then the Output date format mask for DATE items is derived from FORMS60_USER_DATE_FORMAT by taking the first format mask (the value up to the first vertical bar) and stripping out all occurrences of FX and FM.

                            For example, if FORMS60_OUTPUT_DATE_FORMAT is not defined and FORMS60_USER_DATE_FORMAT is defined to be FXFMDD-MM-RRRR|FXFM-MON-RRRR, then the Output date format mask for DATE items is DD-MM-RRRR.

                            If neither FORMS60_OUTPUT_DATE_FORMAT nor FORMS60_USER_DATE_FORMAT is defined, then Form Builder will construct default format masks, as explained below.

                            There are similar rules for DATETIME items.

                            7. Error date format mask

                            There is also an Error format mask, which Form Builder uses in producing certain error messages that are issued when an end user enters an invalid value into a date item. (There are actually two such masks -- one for DATE items and one for DATETIME items.)

                            For example:

                            FRM-50012: Date must be entered in a format like <error_date_format>

                            FRM-50025: Date/time must be entered in a format like <error_datetime_format>

                            The system administrator or end user may set the environment variables FORMS60_ERROR_DATE_FORMAT and FORMS60_ERROR_DATETIME_FORMAT to specify these format masks explicitly.

                            If FORMS60_ERROR_DATE_FORMAT is not defined, but FORMS60_USER_DATE_FORMAT is defined, then the Error date format mask for DATE items is simply taken to be the entire value FORMS60_USER_DATE_FORMAT, including vertical bars.

                            If neither FORMS60_ERROR_DATE_FORMAT nor FORMS60_USER_DATE_FORMAT is defined, then Form Builder will construct default format masks, as explained below.

                            There are similar rules for DATETIME items.

                            Default values for the Input, Output, and Error masks

                            If the runtime environment variables FORMS60_USER_DATE_FORMAT and FORMS60_USER_DATETIME_FORMAT are not both set, then the rules above do not specify all of the Input, Output, and Error format masks for DATE items and DATETIME items. It is recommended that these environment variables both be set. That will avoid having the complex rules below come into play.

                            In general, Form Builder assigns default values that provide compatibility with Release 4.5 and earlier. The behavior in those releases may actually differ slightly in some cases, but in such cases the behavior in prior releases is considered to be erroneous.

                            The following rules distinguish between DATE2 and DATE4 items (see Builtin_Date_Format property for details), with the additional proviso that an LOV column whose datatype is DATE is considered to be a DATE2 object. Note that there are no DATETIME LOV columns, and LOV columns do not directly participate in builtins.

                            Form Builder starts with the format mask derived from the current NLS environment. This may be specified explicitly by setting the environment variable NLS_DATE_FORMAT. Otherwise, NLS will compute a default format mask based on the current language. Call this NLS format mask "<YY_mask>".

                            Next, Form Builder computes a second format mask (call it "<YYYY_mask>") from <YY_mask> as follows: If <YY_mask> contains "YY" but not "YYYY", then <YYYY_mask> is set to <YY_mask>, but with the "YY" replaced by "YYYY". Similarly, "yy", "RR", or "rr" will be replaced by "yyyy", "RRRR", or "rrrr" in <YYYY_mask>. Otherwise, <YYYY_mask> is set to <YY_mask>.

                            Given the above, the remaining unspecified Input format masks for DATE2, DATE4, and DATETIME objects become:

                            DATE2: FXFM<YY_mask>

                            DATE4: FXFM<YYYY_mask>

                            DATETIME: FXFM<YYYY_mask> HH24:MI:SS

                            FXFM<YYYY_mask> HH24:MI

                            The leading FXFM is omitted if the format mask already contains an FX or FM.

                            Next, any occurrences of FX and FM are removed from <YY_mask> and <YYYY_mask>.

                            Given the above, the remaining unspecified Output format masks become:

                            DATE2: <YY_mask>

                            DATE4: <YYYY_mask>

                            DATETIME: <YYYY_mask> HH24:MI:SS

                            Finally, for the remaining unspecified Error format masks, the first occurrence (if any) of RR, rr, RRRR, or rrrr in <YY_mask> and <YYYY_mask> is replaced by the corresponding number of Ys or ys.

                            Given the above, the remaining unspecified Error format masks become:

                            DATE2: <YY_mask>

                            DATE4: <YYYY_mask>

                            DATETIME: <YYYY_mask> HH24:MI[:SS]


                            Sarah
                            • 12. Re: Date initialization Problem
                              Sarah QA
                              Or

                              try this.

                              In the registry :

                              HKEY_LOCAL_MACHINE\Software\Oracle

                              NLS_DATE_FORMAT=DD-MON-YYYY


                              If its Correct/Helpful please Mark it thanks.

                              sarah

                              Edited by: S@R@h on Nov 19, 2009 12:34 AM
                              • 13. Re: Date initialization Problem
                                433935
                                I already set NLS_DATE_FORMAT=DD-MON-YYYY
                                In my registry

                                But I am getting same message

                                Jamil
                                • 14. Re: Date initialization Problem
                                  Sarah QA
                                  hi

                                  do u have any attach library?

                                  sarah
                                  1 2 Previous Next