5 Replies Latest reply on Nov 19, 2014 1:10 PM by odie_63

    update blob column value

    User583851-OC

      hello Experts,

       

      i have a table cert_config , which has blob column "configuration_xml". now i need to update the data in the column, highlighted below.

       

      SQL> desc cert_config

      Name                                      Null?    Type

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

      IDC_ID                                    NOT NULL NUMBER(19)

      CONFIGURATION_XML                         NOT NULL BLOB >>>>>>>

      CREATEDATE                                NOT NULL DATE

      CREATEUSER                                NOT NULL VARCHAR2(50 CHAR)

      UPDATEDATE                                         DATE

      UPDATEUSER                                         VARCHAR2(50 CHAR)

       

      configuration_xml column storing xml data  and  need to update a line in xml data

       

       

       

       

      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

      <idcConfiguration>

          <aboutToExpireReminderEnabled>false</aboutToExpireReminderEnabled>

      <aboutToExpireReminderInterval>5</aboutToExpireReminderInterval>

      <allowAutoClaim>true</allowAutoClaim>

      <allowDelegation>true</allowDelegation>

      <allowFinalReview>false</allowFinalReview>

      <allowInteractiveXls>false</allowInteractiveXls>

      <automaticDeprovisioningEnabled>false</automaticDeprovisioningEnabled>  >>> now i need to change the value from false to true

      <skipRoleOwnerStep1>false</skipRoleOwnerStep1>

      <thirdReminderEnabled>false</thirdReminderEnabled>

          <thirdReminderInterval>3</thirdReminderInterval>

      <updateCertifierEmailNotificationEnabled>false</updateCertifierEmailNotificationEnabled>

      <viewSiemUserActivity>false</viewSiemUserActivity>

      </idcConfiguration>

       

       

      please let me know the best way to do update ?

       

      Thank you so much

        • 1. Re: update blob column value
          sadeesg

          hi,

          example

          create table xml_value (xml xmltype);

          insert into xml_value values(xmltype (' <PDRecord>

             <PDName>Daniel Morgan</PDName>

             <PDDOB>12/1/1951</PDDOB>

             <PDEmail>damorgan@u.washington.edu</PDEmail>

          </PDRecord>'));

          UPDATE

            xml_value

          SET

            xml.modify('replace value of (/PDRecord/PDName/text())[1] with "sadeesh"')

          WHERE

             rownum= 1;

          • 2. Re: update blob column value
            Billy~Verreynne

            Why is a binary LOB, and not a character LOB, used to store character data?

             

            And was XMLTYPE even considered?

            • 3. Re: update blob column value
              AlexAnd

              UPDATE

                xml_value

              SET

                xml.modify('replace value of (/PDRecord/PDName/text())[1] with "sadeesh"')

              ?

              • 4. Re: update blob column value
                AlexAnd

                SQL> select * from v$version;

                 

                BANNER

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

                 

                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

                PL/SQL Release 11.2.0.1.0 - Production

                CORE    11.2.0.1.0      Production

                TNS for Linux: Version 11.2.0.1.0 - Production

                NLSRTL Version 11.2.0.1.0 - Production

                 

                SQL> create table cert_config (configuration_xml blob);

                 

                Table created.

                 

                SQL> insert into cert_config values(utl_raw.cast_to_raw ('<?xml version="1.0" en

                coding="UTF-8" standalone="yes"?>

                  2  <idcConfiguration>

                  3  <aboutToExpireReminderEnabled>false</aboutToExpireReminderEnabled>

                  4  <aboutToExpireReminderInterval>5</aboutToExpireReminderInterval>

                  5  <allowAutoClaim>true</allowAutoClaim>

                  6  <allowDelegation>true</allowDelegation>

                  7  <allowFinalReview>false</allowFinalReview>

                  8  <allowInteractiveXls>false</allowInteractiveXls>

                  9  <automaticDeprovisioningEnabled>false</automaticDeprovisioningEnabled>

                10  <skipRoleOwnerStep1>false</skipRoleOwnerStep1>

                11  <thirdReminderEnabled>false</thirdReminderEnabled>

                12  <thirdReminderInterval>3</thirdReminderInterval>

                13  <updateCertifierEmailNotificationEnabled>false</updateCertifierEmailNotific

                ationEnabled>

                14  <viewSiemUserActivity>false</viewSiemUserActivity>

                15  </idcConfiguration>'));

                 

                1 row created.

                 

                SQL> select xmltype(configuration_xml, 871).extract('/idcConfiguration/automatic

                DeprovisioningEnabled/text()').getstringval() from cert_config

                  2  ;

                 

                XMLTYPE(CONFIGURATION_XML,871).EXTRACT('/IDCCONFIGURATION/AUTOMATICDEPROVISIONIN

                 

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

                 

                false

                 

                SQL> update cert_config set configuration_xml = utl_raw.cast_to_raw(updateXML(xm

                ltype(configuration_xml, 871), '/idcConfiguration/automaticDeprovisioningEnabled

                /text()', 'NEWVALUE-TRUE').getclobval()) ;

                 

                1 row updated.

                 

                SQL> select xmltype(configuration_xml, 871).extract('/idcConfiguration/automatic

                DeprovisioningEnabled/text()').getstringval() from cert_config

                  2  ;

                 

                XMLTYPE(CONFIGURATION_XML,871).EXTRACT('/IDCCONFIGURATION/AUTOMATICDEPROVISIONIN

                 

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

                 

                NEWVALUE-TRUE

                 

                SQL>

                 

                • 5. Re: update blob column value
                  odie_63

                  please let me know the best way to do update ?

                  The best way depends on your unknown database version.

                   

                  Starting from 11.2.0.3, the recommended way is via XQuery Update :

                   

                  update cert_config
                  set configuration_xml = 
                      xmlserialize(document
                        xmlquery(
                          'copy $d := .
                           modify ( 
                             replace value of node $d/idcConfiguration/automaticDeprovisioningEnabled with $new-value
                           )
                           return $d'
                          passing xmltype(configuration_xml, nls_charset_id('AL32UTF8'))
                                , 'true' as "new-value"
                          returning content
                        )
                        as blob
                        encoding 'UTF-8'
                      )
                  where idc_id = 1 ;
                  

                   

                  On previous versions, use updateXML (as Alex showed) :

                   

                  update cert_config
                  set configuration_xml = 
                      xmlserialize(document
                        updatexml(
                          xmltype(configuration_xml, nls_charset_id('AL32UTF8'))
                        , '/idcConfiguration/automaticDeprovisioningEnabled/text()'
                        , 'true'
                        )
                        as blob
                        encoding 'UTF-8'
                      )
                  where idc_id = 1 ;
                  

                   

                  And indeed, you should really consider using XMLType storage instead. It would simplify a lot of things.