7 Replies Latest reply on Feb 6, 2014 4:48 PM by rp0428

    Update column with hash values

    PVM

      Hi All,

       

      I have a requirement to add a column to a table and populate with hash values. The table contains one xml column and need hash value of that column to be populated to newly added column. I think ora_hash function will not support xml type column. Just tested with the following script and it works.

       

      update TBL_COMPONENT_XSL set hash_val = ora_hash((SELECT TCX.COMPONENT_XSL.getStringVal() FROM TBL_COMPONENT_XSL TCX where rownum =1));
      

       

      can you please suggest me how to populate the column with hash values.

       

      SQL> desc TBL_COMPONENT_XSL
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       COMPONENT_XSL_ID                          NOT NULL NUMBER(18)
       LINE_OF_BUSINESS                          NOT NULL VARCHAR2(30)
       ORDER_SOURCE                              NOT NULL VARCHAR2(10)
       COMPONENT_NAME                            NOT NULL VARCHAR2(30)
       COMPONENT_SCHEMA_VERSION                  NOT NULL NUMBER(9)
       ACCESS_LEVEL                              NOT NULL VARCHAR2(30)
       COMPONENT_XSL                             NOT NULL SYS.XMLTYPE STORAGE BINARY
       DESCRIPTION                               NOT NULL VARCHAR2(200)
       HASH_VAL                                           NUMBER
      
      

       

      HASH_VAL is the newly added column.

       

      Thanks,

      Mani

        • 1. Re: Update column with hash values
          JustinCave

          If you're saying that you've tested it and ORA_HASH works and gives you what you need, simply

           

          update TBL_COMPONENT_XSL 
             set hash_val = ora_hash( COMPONENT_XSL.getStringVal() );
          

           

          If that's not what you're looking for, it would be helpful to post a test case that we can run on our local machines that includes some sample data and the expected output.

           

          Justin

          • 2. Re: Update column with hash values
            PVM

            HI Justin,

             

            Thanks for the reply.

             

            The update gives error.

             

            SQL> update TBL_COMPONENT_XSL TCX set hash_val = ora_hash(TCX.COMPONENT_XSL.getStringVal());
            update TBL_COMPONENT_XSL TCX set hash_val = ora_hash(TCX.COMPONENT_XSL.getStringVal())
                                                                 *
            ERROR at line 1:
            ORA-06502: PL/SQL: numeric or value error: character string buffer too small
            ORA-06512: at "SYS.XMLTYPE", line 169
            
            

             

            Here is my requirement

             

            I have a table TBL_COMPONENT_XSL as below. Team needs to add a column to this table and store the hash value of xml column in to that newly added column, I believe ora_hash function will not support XML,LOB,LONG,CLOB. I mention 2 insert stmnts to this table

             

            Insert into TBL_COMPONENT_XSL (COMPONENT_XSL_ID,LINE_OF_BUSINESS,ORDER_SOURCE,COMPONENT_NAME,COMPONENT_SCHEMA_VERSION,ACCESS_LEVEL,COMPONENT_XSL,DESCRIPTION) values (3032,'15','24','BOD',1,'1','<xsl:stylesheet exclude-result-prefixes="xsl xsi xsd xalan in" version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xalan="http://xml.apache.org/xslt" xmlns:in="http://www.mci.com/oagis/9" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
               <xsl:output omit-xml-declaration="yes" method="xml" encoding="UTF-8" indent="yes" xalan:indent-amount="2"/>
               <xsl:strip-space elements="*"/>
               <xsl:template match="/">
                  <xsl:copy-of select="(*//*[local-name() = ''ProcessServiceOrder''])"/>
               </xsl:template>
            </xsl:stylesheet>','Retrieve BOD Component for Optionless CW');
            
            Insert into TBL_COMPONENT_XSL
               (COMPONENT_XSL_ID, LINE_OF_BUSINESS, ORDER_SOURCE, COMPONENT_NAME, COMPONENT_SCHEMA_VERSION, 
                ACCESS_LEVEL, COMPONENT_XSL, DESCRIPTION)
             Values
            (3151, 15, 22, 'BOD', 1, '1', 
            '<xsl:stylesheet exclude-result-prefixes="in xsl xsi xsd xalan" version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xalan="http://xml.apache.org/xslt" xmlns:in="http://www.mci.com/oagis/9" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
               <xsl:output method="xml" encoding="UTF-8" indent="yes" xalan:indent-amount="2"/>
               <xsl:strip-space elements="*"/>
               <xsl:template match="/">
                  <xsl:copy-of select="(*//*[local-name() = ''OrderGatheringRsp''])"/>
               </xsl:template>
            </xsl:stylesheet>', 'Retrieve BOD Component for Optionless FNE');
            

             

            SQL> desc TBL_COMPONENT_XSL
             Name                                      Null?    Type
             ----------------------------------------- -------- ----------------------------
             COMPONENT_XSL_ID                          NOT NULL NUMBER(18)
             LINE_OF_BUSINESS                          NOT NULL VARCHAR2(30)
             ORDER_SOURCE                              NOT NULL VARCHAR2(10)
             COMPONENT_NAME                            NOT NULL VARCHAR2(30)
             COMPONENT_SCHEMA_VERSION                  NOT NULL NUMBER(9)
             ACCESS_LEVEL                              NOT NULL VARCHAR2(30)
             COMPONENT_XSL                             NOT NULL SYS.XMLTYPE STORAGE BINARY
             DESCRIPTION                               NOT NULL VARCHAR2(200)
            

             

             

            Please let me know your thought on this. I am new to XML DB.

             

            Thanks,

            Mani

            • 3. Re: Update column with hash values
              Girish Sharma

              I don't know where you are doing wrong, but what ever you posted is working fine at my end (11.2.0.1 Windows) :

               

              SQL> create table TBL_COMPONENT_XSL

                2  (

                3  COMPONENT_XSL_ID           NUMBER(18)      NOT NULL,

                4  LINE_OF_BUSINESS           VARCHAR2(30)    NOT NULL,

                5  ORDER_SOURCE               VARCHAR2(10)    NOT NULL,

                6  COMPONENT_SCHEMA_VERSION   VARCHAR2(9)     NOT NULL,

                7  COMPONENT_NAME             VARCHAR2(30)    NOT NULL,

                8  ACCESS_LEVEL               VARCHAR2(30)    NOT NULL,

                9  COMPONENT_XSL              XMLTYPE         NOT NULL,

              10  DESCRIPTION                VARCHAR2(200)   NOT NULL,

              11  HASH_VAL                   NUMBER

              12  )

              13  XMLTYPE COMPONENT_XSL STORE AS BINARY XML

              14  /

               

              Table created.

               

              SQL> desc TBL_COMPONENT_XSL;

              Name                                                                                                      Null?    Type

              ----------------------------------------------------------------------------------------------------------------- -------- ---------------------

              COMPONENT_XSL_ID                                                                                          NOT NULL NUMBER(18)

              LINE_OF_BUSINESS                                                                                          NOT NULL VARCHAR2(30)

              ORDER_SOURCE                                                                                              NOT NULL VARCHAR2(10)

              COMPONENT_SCHEMA_VERSION                                                                                  NOT NULL VARCHAR2(9)

              COMPONENT_NAME                                                                                            NOT NULL VARCHAR2(30)

              ACCESS_LEVEL                                                                                              NOT NULL VARCHAR2(30)

              COMPONENT_XSL                                                                                             NOT NULL SYS.XMLTYPE STORAGE BINARY

              DESCRIPTION                                                                                               NOT NULL VARCHAR2(200)

              HASH_VAL                                                                                                           NUMBER

               

              I just inserted the above rows as you mentioned and ran your update command :

               

              SQL> update TBL_COMPONENT_XSL TCX set hash_val = ora_hash(TCX.COMPONENT_XSL.getStringVal());

               

              2 rows updated.

               

              SQL> select hash_val from TBL_COMPONENT_XSL;

               

                HASH_VAL

              ----------

              3661275841

              960705308

               

              No error at my end.  Even I am more new to XML DB, but did not found any error by just running your code.

               

              Regards

              Girish Sharma

              • 4. Re: Update column with hash values
                PVM

                Hi Girish,

                 

                Thanks for trying it out. I just now did the same (created temp table and inserted and it works fine for me). But i just copied two rows for testing. Actually the table contains 2000 more records. I dont know why it failing when i tried in actual table (testing in temp table TBL_COMPONENT_XSL copied from prod). Please suggest.

                 

                Thanks,

                Mani

                • 5. Re: Update column with hash values
                  Girish Sharma

                  What if you try with :

                  update TBL_COMPONENT_XSL TCX set hash_val = ora_hash(TCX.COMPONENT_XSL.getStringVal());

                  update TBL_COMPONENT_XSL TCX set hash_val = ora_hash(TCX.COMPONENT_XSL.getClobVal());


                  Clue from : http://www.orafaq.com/forum/t/70829/


                  Regards

                  Girish Sharma

                  • 6. Re: Update column with hash values
                    PVM

                    Girish..you rocksss.... Thanks Alot Girish..it worked.....

                    • 7. Re: Update column with hash values
                      rp0428
                      Thanks for trying it out. I just now did the same (created temp table and inserted and it works fine for me). But i just copied two rows for testing. Actually the table contains 2000 more records. I dont know why it failing when i tried in actual table (testing in temp table TBL_COMPONENT_XSL copied from prod). Please suggest.

                      No sure why you are denormlizing this table to begin with by storing both the data and the hash in the same location.

                       

                      I suggest that you use the DBMS_CRYPTO package and use an industry standard hash like MD5 instead of using ora_hash.

                       

                      See the API

                      http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_crypto.htm

                      DBMS_CRYPTO contains basic cryptographic functions and procedures. To use this package correctly and securely, a general level of security expertise is assumed.

                      The DBMS_CRYPTO package enables encryption and decryption for common Oracle datatypes, including RAW and large objects (LOBs), such as images and sound. Specifically, it supports BLOBs and CLOBs. In addition, it provides Globalization Support for encrypting data across different database character sets.

                      The following cryptographic algorithms are supported:

                      •   Data Encryption Standard (DES), Triple DES (3DES, 2-key and 3-key)
                      •   Advanced Encryption Standard (AES)
                      •   MD5, MD4, and SHA-1 cryptographic hashes
                      •   MD5 and SHA-1 Message Authentication Code (MAC)

                      Those methods support LOB datatypes.