1 2 Previous Next 25 Replies Latest reply on Apr 13, 2018 12:22 PM by AndrewSayer Go to original post
      • 15. Re: Hash value generated does not match
        user1356432

        Hi rp0428,

         

        There is no confusion about this. We have tested this in more than one systems many times before posting the issue here.

         

        This is a partitioned table(purge_test_fi_1). For the testing purpose we are selecting only one partition that contains only one row.

         

        For the sake of company policy I have removed some part of the query.

         

        Hash result in SQL *PLUS

         

        Hash result in SQL Developer

         

         

        Regards;

        Gopal

        • 16. Re: Hash value generated does not match
          AndrewSayer

          Show us the result of the query (without using dbms_sqlhash) in your sql*plus. Does the date column get displayed differently?

          Is that because nls_date_format is different?

           

          It’s always best to explicitly give a date format when treating a date as a string (which is what the package will do), so replace your query with

          select id,code,description,to_char(created_date,’yyyymmsshh24miss’) from table..partition..

           

          If you are going to use this for multiple rows then you will need to order by something to give a deterministic order

          • 17. Re: Hash value generated does not match

            And sql*plus is likely NOT going to give you those same results - especially for the DATE column.

            • 18. Re: Hash value generated does not match
              Paulzip

              Run

              alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS'

               

              On both clients before doing the hash. I suspect you'll get the same result.

              • 19. Re: Hash value generated does not match
                user1356432

                Yes setting the NLS_DATE_FORMAT to required format, it gave the same hash value in both the cases. Thanks for this.

                • 20. Re: Hash value generated does not match
                  user1356432

                  Hi Jonathan, You are correct. NLS_DATE_FORMAT was not same in both. By setting the NLS_DATE_FROMAT to required format, we got the same hash value in both the cases. Thanks. Regards; Gopal

                  • 21. Re: Hash value generated does not match
                    AndrewSayer

                    user1356432 wrote:

                     

                    Yes setting the NLS_DATE_FORMAT to required format, it gave the same hash value in both the cases. Thanks for this.

                    I strongly suggest you remove this dependency and replace the projection of the date in your query with a to_char call with explicit format mask.

                    • 22. Re: Hash value generated does not match
                      Paulzip

                      user1356432 wrote:

                       

                      Yes setting the NLS_DATE_FORMAT to required format, it gave the same hash value in both the cases. Thanks for this.

                      Which is what padders and I said to you in the first two replies in this whole thread!

                      • 23. Re: Hash value generated does not match
                        user1356432

                        Yes Pualzip. Appreciate your help on this. Regards; Gopal

                        • 24. Re: Hash value generated does not match
                          Jonathan Lewis

                          Andrew Sayer wrote:

                           

                          Yes setting the NLS_DATE_FORMAT to required format, it gave the same hash value in both the cases. Thanks for this.

                          I strongly suggest you remove this dependency and replace the projection of the date in your query with a to_char call with explicit format mask.

                          This makes me wonder how many other NLS parameters could affect the outcome, and whether all of them can be bypassed by some explicit conversion calls in the code.

                          e.g. nls_sort would, presumably impact an "order by" clause, leaving two apparently identical queries returning different hash values until you spotted the cause.

                           

                          (Just did a quick and dirty test with nls_date_format - may have found a bug in that bit of the nls code)

                           

                          Regards

                          Jonathan Lewis

                          • 25. Re: Hash value generated does not match
                            AndrewSayer

                            Jonathan Lewis wrote:

                             

                            Andrew Sayer wrote:

                             

                            Yes setting the NLS_DATE_FORMAT to required format, it gave the same hash value in both the cases. Thanks for this.

                            I strongly suggest you remove this dependency and replace the projection of the date in your query with a to_char call with explicit format mask.

                            This makes me wonder how many other NLS parameters could affect the outcome, and whether all of them can be bypassed by some explicit conversion calls in the code.

                            e.g. nls_sort would, presumably impact an "order by" clause, leaving two apparently identical queries returning different hash values until you spotted the cause.

                             

                            (Just did a quick and dirty test with nls_date_format - may have found a bug in that bit of the nls code)

                             

                            Regards

                            Jonathan Lewis

                            The order by example is something I've not typically considered, another possibility would be nls_language. E.g.

                             

                            sql>alter session set nls_language='GERMAN';

                             

                             

                            Session altered.

                             

                             

                            sql>select my_col from (select ''||rownum my_col from dual connect by rownum <5 union all select 'a' from dual union all select 'A' from dual) order by my_col;

                             

                             

                            MY_COL

                            ________________________________________________________________________________________________________________________________________________________________

                            a

                            A

                            1

                            2

                            3

                            4

                             

                             

                            6 rows selected.

                             

                             

                            sql>alter session set nls_language='AMERICAN';

                             

                             

                            Session altered.

                             

                             

                            sql>select my_col from (select ''||rownum my_col from dual connect by rownum <5 union all select 'a' from dual union all select 'A' from dual) order by my_col;

                             

                             

                            MY_COL

                            ________________________________________________________________________________________________________________________________________________________________

                            1

                            2

                            3

                            4

                            A

                            a

                             

                             

                            6 rows selected.

                             

                            These are things I wouldn't expect to be set differently on different client setups using the same DB, but there's no reason why not.

                             

                            Since the dbms_sqlHash code is just projecting the columns of the source SQL into varchar2 variables it should just be doing cast, unfortunately the functions evaluated to do these implicit conversions aren't fully documented (at least I can't find them), so it's hard to get a complete list of inputs.

                             

                            I also forgot about implicit conversion that could be happening inside views etc. Perhaps the advice should be always use explicit conversion but then also always use the exact same nls environment.


                            Incidentally, the function doesn't seem to do a great job when it comes to spaces

                            sys@xe> select dbms_sqlhash.getHash('select ''One'' col1, '' Two'' col2 from dual',2) from dual;

                             

                             

                            DBMS_SQLHASH.GETHASH('SELECT''ONE''COL1,''TWO''COL2FROMDUAL',2)

                            _______________________________________________________________________________________________________

                            614FF183867D6C4E3BDD77E0E2C32751

                            sys@xe> select dbms_sqlhash.getHash('select ''One '' col1, ''Two'' col2 from dual',2) from dual;

                             

                             

                            DBMS_SQLHASH.GETHASH('SELECT''ONE''COL1,''TWO''COL2FROMDUAL',2)

                            _______________________________________________________________________________________________________

                            614FF183867D6C4E3BDD77E0E2C32751

                             

                            Easy to guess what the internals of the function look like from that.

                            1 2 Previous Next