11 Replies Latest reply on Sep 12, 2017 9:40 PM by rp0428

    bulk collect hash md5 odcivarchar2list to clob

    1932140

      Hi

       

      I'm trying to find a way to load a result set from a collection, odcivarchar2list, into a clob. I'm using 11gr2

      Is there a way I can do so without using any for loops ?

       

      drop table fruit;
      create table fruit (id number, descr varchar2(20), expire_date date, price number(4,2));
      insert into fruit values (1, 'apple', sysdate + 10, 12.22);
      insert into fruit values (2, 'banana', sysdate + 12, 0.22);
      insert into fruit values (3, 'orange',sysdate + 8, 0.99);
      insert into fruit values (4, 'kiwi', sysdate + 7, 99.99);
      commit;
      
      
      set serveroutput on
      declare
        l_clob clob;
        l_list sys.odcivarchar2list;
      begin
        select cast(multiset
                (select lower(rawtohex(dbms_crypto.hash(utl_raw.cast_to_raw(id||descr||to_char(expire_date, 'yyyymmdd')||to_char(price,'FM99.99') ),2)))  as hash_val
                 from ( select id,descr,expire_date,price from fruit ) order by 1
                 )  as sys.odcivarchar2list)
        into l_list
        from dual;
      
        for t in (
       select column_value as val from table(l_list) ) loop
          dbms_output.put_line(t.val);
        end loop;
      
       --  dbms_output.put_line(l_clob);
      
      end;
      /
      

       

      So how how can I replace lines 22 to 25 with a bulk insert into the clob (l_clob)? Is that at all possible ?

       

      Thanks

        • 1. Re: bulk collect hash md5 odcivarchar2list to clob
          Paulzip

          What is the UNDERLYING reason for doing this?  Are you trying to get the hash of a table row?  You might be approaching your problem incorrectly.

          • 2. Re: bulk collect hash md5 odcivarchar2list to clob
            Mustafa KALAYCI

            in addition to Paul, if you want to get this data as clob, you don't need to get it as odcivarchar2list first, you can put your data into clob directly like:

             

            declare

              x_mylob clob;

            begin

              for rec in (select lower(rawtohex(dbms_crypto.hash(utl_raw.cast_to_raw(id||descr||to_char(expire_date, 'yyyymmdd')||to_char(price,'FM99.99') ),2)))  as hash_val 

                       from ( select id,descr,expire_date,price from fruit ) order by 1 )

              loop

                x_mylob := x_mylob  || rec.hash_val;

              end loop;

            end;

            • 3. Re: bulk collect hash md5 odcivarchar2list to clob
              John Thorton

              1932140 wrote:

               

              Hi

               

              I'm trying to find a way to load a result set from a collection, odcivarchar2list, into a clob. I'm using 11gr2

              Is there a way I can do so without using any for loops ?

               

              1. droptablefruit;
              2. createtablefruit(idnumber,descrvarchar2(20),expire_datedate,pricenumber(4,2));
              3. insertintofruitvalues(1,'apple',sysdate+10,12.22);
              4. insertintofruitvalues(2,'banana',sysdate+12,0.22);
              5. insertintofruitvalues(3,'orange',sysdate+8,0.99);
              6. insertintofruitvalues(4,'kiwi',sysdate+7,99.99);
              7. commit;
              8. setserveroutputon
              9. declare
              10. l_clobclob;
              11. l_listsys.odcivarchar2list;
              12. begin
              13. selectcast(multiset
              14. (selectlower(rawtohex(dbms_crypto.hash(utl_raw.cast_to_raw(id||descr||to_char(expire_date,'yyyymmdd')||to_char(price,'FM99.99')),2)))ashash_val
              15. from(selectid,descr,expire_date,pricefromfruit)orderby1
              16. )assys.odcivarchar2list)
              17. intol_list
              18. fromdual;
              19. fortin(
              20. selectcolumn_valueasvalfromtable(l_list))loop
              21. dbms_output.put_line(t.val);
              22. endloop;
              23. --dbms_output.put_line(l_clob);
              24. end;
              25. /

               

              So how how can I replace lines 22 to 25 with a bulk insert into the clob (l_clob)? Is that at all possible ?

               

              Thanks

              you can't do SQL against PL/SQL object.

              • 4. Re: bulk collect hash md5 odcivarchar2list to clob
                rp0428

                So how how can I replace lines 22 to 25 with a bulk insert into the clob (l_clob)? Is that at all possible ?

                No - it is NOT possible to 'bulk insert' into a scalar.

                 

                The target of a BULK operation needs to be a collection. A 'clob' is NOT a collection.

                • 5. Re: bulk collect hash md5 odcivarchar2list to clob
                  1932140

                  I'm in fact trying to get to a single hash, based on the list of varchar2's , something like this:

                   

                  SQL> set serveroutput on
                  SQL> declare
                    2    l_clob clob;
                    3    l_list sys.odcivarchar2list;
                    4    l_md5  clob;
                    5  begin
                    6
                    7    select cast(multiset
                    8        (select lower(rawtohex(dbms_crypto.hash(utl_raw.cast_to_raw(id||descr||to_char(expire_date, 'yyyymmdd')||to_char(price,'FM99.99') ),2)))  as hash_val
                    9  from ( select id,descr,expire_date,price from fruit )
                  10  )  as sys.odcivarchar2list)
                  11    into l_list
                  12    from dual;
                  13
                  14    for t in (
                  15        select distinct column_value as val from table(l_list) ) loop
                  16  l_clob := l_clob || t.val;
                  17    end loop;
                  18
                  19    select lower(rawtohex(dbms_crypto.hash(utl_raw.cast_to_raw(l_clob ),2)))
                  20    into l_md5
                  21    from dual;
                  22
                  23    dbms_output.put_line(l_md5);
                  24
                  25  end;
                  26  /
                  35af2aba089ea9a0eb73d3eb514658bd
                  

                   

                  Was just wondering is there is a better way.

                  • 6. Re: bulk collect hash md5 odcivarchar2list to clob
                    John Thorton

                    1932140 wrote:

                     

                    I'm in fact trying to get to a single hash, based on the list of varchar2's , something like this:

                     

                    1. SQL>setserveroutputon
                    2. SQL>declare
                    3. 2l_clobclob;
                    4. 3l_listsys.odcivarchar2list;
                    5. 4l_md5clob;
                    6. 5begin
                    7. 6
                    8. 7selectcast(multiset
                    9. 8(selectlower(rawtohex(dbms_crypto.hash(utl_raw.cast_to_raw(id||descr||to_char(expire_date,'yyyymmdd')||to_char(price,'FM99.99')),2)))ashash_val
                    10. 9from(selectid,descr,expire_date,pricefromfruit)
                    11. 10)assys.odcivarchar2list)
                    12. 11intol_list
                    13. 12fromdual;
                    14. 13
                    15. 14fortin(
                    16. 15selectdistinctcolumn_valueasvalfromtable(l_list))loop
                    17. 16l_clob:=l_clob||t.val;
                    18. 17endloop;
                    19. 18
                    20. 19selectlower(rawtohex(dbms_crypto.hash(utl_raw.cast_to_raw(l_clob),2)))
                    21. 20intol_md5
                    22. 21fromdual;
                    23. 22
                    24. 23dbms_output.put_line(l_md5);
                    25. 24
                    26. 25end;
                    27. 26/
                    28. 35af2aba089ea9a0eb73d3eb514658bd

                     

                    Was just wondering is there is a better way.

                    Do not do in PL/SQL that which can be done in plain SQL.

                    • 7. Re: bulk collect hash md5 odcivarchar2list to clob
                      Paulzip

                      Here's a quick way.

                       

                      select dbms_crypto.hash(dbms_xmlgen.getXML('select * from fruit'), 2) MD5 from dual

                       

                      MD5

                      BF79CBB7A0FF8B3ABC3F677080426222

                      • 8. Re: bulk collect hash md5 odcivarchar2list to clob
                        1932140

                        thanks for your suggestion.

                        the reason for the formatting is so that the resulting hash can be compared to results from databases other than Oracle.

                        If I were to be comparing Oracle to Oracle databases, then your suggestion would be fine, but as it stands data exists in SQL Anywhere and PostgreSQL.

                        So far the matching is successful, but only if columns are formatted.

                        • 9. Re: bulk collect hash md5 odcivarchar2list to clob
                          Paulzip

                          You'll need to order your data exactly the same between DBs to get the same hash.  Also, you should be using a separator character between fields, I tend to use chr(31) as that is what the character was designed for (field delimiter char), otherwise you could get false matches.

                           

                          In the simplest example, a table containing one row with the following data would all give the same hash, if concatenated without a delimiter.

                           

                          1, 123, A

                           

                          11, 23, A

                           

                          112, 3, A

                          • 10. Re: bulk collect hash md5 odcivarchar2list to clob
                            1932140

                            sure, hence in my first post I had order by 1

                            • 11. Re: bulk collect hash md5 odcivarchar2list to clob
                              rp0428

                              How does knowing the overall hash for a set of data help you solve your 'unknown to us' problem?

                               

                              If the hash is different between DBs all the work you did doing full table scans and sorting the data in multiple DBs was pretty much wasted.

                               

                              It doesn't help at all identify exactly WHAT data is different or HOW MUCH data is different. Could be one data item in one row or it could be the entire table.

                               

                              Why not use a 'solution' that actually helps identify the data that is different so you can then actually take some sort of action?