1 2 Previous Next 21 Replies Latest reply: Feb 21, 2013 2:50 AM by fac586 Go to original post RSS
      • 15. Re: APEX Form Validation help
        Damir Vadas
        Hi,

        Mine example was for this case.
        Interesting would be to count number of function call your way.
        Rg
        Damir
        • 16. Re: APEX Form Validation help
          fac586
          Damir Vadas wrote:
          Hi,

          Mine example was for this case.
          Interesting would be to count number of function call your way.
          The main difference is that mine works and yours doesn't:
          PL/SQL Expression:    to_date(:p80_period_end, 'YYYY-MM-DD') >= to_date(:p80_period_start, 'YYYY-MM-DD')
                             or :p80_period_end is null
          
          Debug trace:
          
          0.01834     0.00009     ...Validation "fac586" - Type: PLSQL_EXPRESSION     4     
          
          0.01843     0.00037     ...Execute Statement: begin wwv_flow.g_boolean := to_date(:p80_period_end, 'YYYY-MM-DD') >= to_date(:p80_period_start, 'YYYY-MM-DD') or :p80_period_end is null; end;     4     
          
          0.01880     0.00002     ......Result = false     4     
          
          0.01882     0.00002     ......Did NOT pass
          PL/SQL Expression: nvl(:p80_period_end,(:p80_period_start+1)) >= :p80_period_start
          
          Debug trace:
          
          0.01782     0.00014     Perform custom validations:     4     
          
          0.01796     0.00036     ...Validation "Damir" - Type: PLSQL_EXPRESSION     
          
          0.01833     0.00187     ...Execute Statement: begin wwv_flow.g_boolean := nvl(:p80_period_end,(:p80_period_start+1)) >= :p80_period_start; end;     4     
          
          0.02020     0.00010     Add error onto error stack     4     
          
          0.02030     0.00003     ...Error data:     4     
          
          0.02032     0.00002     ......message: Error processing validation.     4     
          
          0.02035     0.00002     ......additional_info: ORA-06502: PL/SQL: numeric or value error: character to number conversion error     4     
          
          0.02037     0.00002     ......display_location: ON_ERROR_PAGE     4     
          
          0.02039     0.00002     ......is_internal_error: true     4     
          
          0.02041     0.00002     ......apex_error_code: APEX.VALIDATION.UNHANDLED_ERROR     4     
          
          0.02043     0.00002     ......ora_sqlcode: -6502     4     
          
          0.02045     0.00002     ......ora_sqlerrm: ORA-06502: PL/SQL: numeric or value error: character to number conversion error     4     
          
          0.02047     0.00002     ......error_backtrace: ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_SYS_SQL", line 1815 ORA-06512: at "SYS.WWV_DBMS_SQL", line 973 ORA-06512: at "SYS.WWV_DBMS_SQL", line 999 ORA-06512: at "APEX_040200.WWV_FLOW_DYNAMIC_EXEC", line 642 ORA-06512: at "APEX_040200.WWV_FLOW_VALIDATION", line 339 ORA-06512: at "APEX_040200.WWV_FLOW_VALIDATION", line 785 ORA-06512: at "APEX_040200.WWV_FLOW_VALIDATION", line 906     4     
          
          0.02049     0.00004     ......component.type: APEX_APPLICATION_PAGE_VAL     4     
          
          0.02053     0.00003     ......component.id: 4540910754675071     4     
          
          0.02056     0.00011     ......component.name: Period     4     
          
          0.02067     0.00011     ...Execute Error Handling Callout defined on Page or Application Level     4     
          
          0.02078     0.00041     ...Execute Statement: begin declare l_error wwv_flow_error_api.t_error; begin l_error := wwv_flow_error_api.g_error; wwv_flow_error_api.g_error_result := sample_pkg.demo_error_handling ( p_error => l_error ); end; end;     4     
          
          0.02120     0.00004     ......Values changed by callout:     4     
          
          0.02123     0.00003     .........message: An unexpected internal application error has occurred. Please get in contact with your system administrator and provide reference# for further investigation.     4     
          When you add the 3 <tt>to_date</tt> conversions required to make your example work, my version uses 2 function calls and 2 comparisons; yours requires 4 function calls, 1 arithmetic operation and 1 comparison.

          Furthermore, if null values will be common, my version can put the null comparison first:
            :p80_period_end is null
          or to_date(:p80_period_end, 'YYYY-MM-DD') >= to_date(:p80_period_start, 'YYYY-MM-DD')
          Then the most frequent scenario will take advantage of short-circuit evaluation: when the value is null none of the expensive functions will be executed at all. Yours requires the complete expression to be evaluated every time.
          • 17. Re: APEX Form Validation help
            VANJ
            Yours requires the complete expression to be evaluated every time
            Observation: In Damir's version, using coalesce instead of nvl could take advantage of the short-circuit evaluation as well.
            • 18. Re: APEX Form Validation help
              fac586
              VANJ wrote:
              Yours requires the complete expression to be evaluated every time
              Observation: In Damir's version, using coalesce instead of nvl could take advantage of the short-circuit evaluation as well.
              That would only eliminate one of the <tt>to_date</tt> calls and the addition. In:
              coalesce(to_date(:p80_period_end, 'YYYY-MM-DD'),(to_date(:p80_period_start, 'YYYY-MM-DD')+1)) >= to_date(:p80_period_start, 'YYYY-MM-DD')
              <tt>coalesce</tt>'s ability to short-circuit would remove the need to evaluate <tt>(to_date(:p80_period_start, 'YYYY-MM-DD')+1)</tt> when <tt>p80_period_end</tt> is not null, but the other 2 <tt>to_date</tt> conversions are still required for the <tt>>=</tt> comparison to work.
              • 19. Re: APEX Form Validation help
                VANJ
                This horse is long dead but there comes a point of diminishing returns with these optimizations. Two dates have to be compared so 2 date conversions and a >= comparison is necessary. Since one of them can be null, the NVL/COALESCE is necessary. Beyond that the syntax is really not very relevant. I just thought you came down too hard on Damir when he IMHO had the right idea (could have been more polished like your contributions are) and was trying to be helpful. Just saying. Going back into lurk mode.

                Cheers.
                • 20. Re: APEX Form Validation help
                  Damir Vadas
                  Hi "fac586",

                  You are trying to prove something that is not possible.
                  fac586 wrote:
                  When you add the 3 <tt>to_date</tt> conversions required to make your example work, my version uses 2 function calls and 2 comparisons; yours requires 4 function calls, 1 arithmetic operation and 1 comparison.
                  I strongly suggest to forget to think about counting things like pieces but to see what are you counting. One kernel function might be 1000 times better many other and especially PL/SQL base custom functions. Advice is to stay as close as possible to kernel calls and original Oracle made functions and in the same time, not to make too many PL/SQL-SQL context switching (now I came to point to point that IF expression should be replace with CASE function ... but this lead us to nowhere ...so I'll stop).
                  :-)

                  And last (but not least). Now you are making examples like
                  fac586 wrote:
                  to_date(:p80_period_end, 'YYYY-MM-DD')
                  which should prove that you are right?

                  Forgive me but placing date formats in any serious development, in a way you show, is a very, very bad habit (think about if you make a translation of your Apex app and date picture format is changed!?). I kindly advise you to not to do that in ANY case, not just in this one particularly.

                  I really appreciated your tedious work and excuse in front if I somehow offend you in any way. Wasn't mine thought at all when contributed to this thread.
                  :-)

                  Cheers,
                  Damir Vadas
                  http://damir-vadas.blogspot.com
                  • 21. Re: APEX Form Validation help
                    fac586
                    Damir Vadas wrote:

                    You are trying to prove something that is not possible.
                    All that's been proved is that the code you posted does not work. If a solution that actually runs and checks a date range range in an APEX validation is not provided, questions of clarity and efficiency are irrelevant.
                    fac586 wrote:
                    When you add the 3 <tt>to_date</tt> conversions required to make your example work, my version uses 2 function calls and 2 comparisons; yours requires 4 function calls, 1 arithmetic operation and 1 comparison.
                    I strongly suggest to forget to think about counting things like pieces but to see what are you counting.
                    It was you who brought up counting functions:
                    Damir Vadas wrote:
                    Hi,

                    Mine example was for this case.
                    Interesting would be to count number of function call your way.
                    One kernel function might be 1000 times better many other and especially PL/SQL base custom functions.
                    Indeed. As Vikas pointed out, <tt>coalesce</tt> is better than <tt>nvl</tt>. However <tt>to_date</tt> is a built-in Oracle function. No custom PL/SQL functions are involved here.
                    Advice is to stay as close as possible to kernel calls and original Oracle made functions and in the same time, not to make too many PL/SQL-SQL context switching
                    As can be seen from the debug trace above, PL/SQL Expression validations are (unsurprisingly) executed in a PL/SQL block. No context switches are involved here.
                    (now I came to point to point that IF expression should be replace with CASE function ... but this lead us to nowhere ...so I'll stop).
                    No IFs or CASEs are involved here.
                    And last (but not least). Now you are making examples like
                    fac586 wrote:
                    to_date(:p80_period_end, 'YYYY-MM-DD')
                    which should prove that you are right?
                    As was "proved" before you got involved, <tt>to_date</tt> is necessary to provide a solution to the problem raised in this thread. Do you actually disagree with this?
                    Forgive me but placing date formats in any serious development, in a way you show, is a very, very bad habit (think about if you make a translation of your Apex app and date picture format is changed!?). I kindly advise you to not to do that in ANY case, not just in this one particularly.
                    I have no experience of using translated apps, but a lot of experience of picking up the pieces on apps after reliance on implicit conversions and default format masks has broken them. In real applications I use Re: Validation Process:  Function Returning Error Text to enforce standards for date/time formats across the application without hard coding them everywhere. That provides the flexibility to centrally maintain and use more formats than are defined in the application's globalization parameters. That technique is beyond the scope of this thread which is about the basic necessity of using explicit conversion when working with APEX items that must be treated as date/time values, so it was omitted from the simple example provided.

                    Never say "never": how would you deal with validating a date value from an item that for some reason doesn't use the global format mask?
                    I really appreciated your tedious work and excuse in front if I somehow offend you in any way. Wasn't mine thought at all when contributed to this thread.
                    We'd all have appreciated it if you had put in the "tedious work" of actually testing your contribution to see if it worked before posting.
                    1 2 Previous Next