1 2 Previous Next 21 Replies Latest reply on Apr 18, 2011 4:44 PM by Marco Gralike

    Problem in UPDATE with EXTRACTVALUE : ORA-00600

    domiq44
      Hi all,

      I have some trouble with this update query :
      UPDATE enveloppe_message
         SET ems_date_modification = SYSDATE,
             ems_login_modification = 'pipo',
             ems_dn_emet = EXTRACTVALUE (ems_xml, '/DataPDU/Header/Message/Sender/DN', 'xmlns="urn:swift:saa:xsd:saa.2.0"'),
             ems_dn_dest = EXTRACTVALUE (ems_xml, '/DataPDU/Header/Message/Receiver/DN', 'xmlns="urn:swift:saa:xsd:saa.2.0"'),
             ems_reference_tech = EXTRACTVALUE (ems_xml, '/DataPDU/Header/Message/SenderReference', 'xmlns="urn:swift:saa:xsd:saa.2.0"')
       WHERE ems_id = 1130;
      I get this error message :
      ORA-00600: code d'erreur interne, arguments : [koklismem111: BFILE locator], [], [], [], [], [], [], [], [], [], [], []
      But if I do this :
      SELECT EXTRACTVALUE (ems_xml, '/DataPDU/Header/Message/Sender/DN', 'xmlns="urn:swift:saa:xsd:saa.2.0"') AS a, 
             EXTRACTVALUE (ems_xml, '/DataPDU/Header/Message/Receiver/DN', 'xmlns="urn:swift:saa:xsd:saa.2.0"') AS b,
             EXTRACTVALUE (ems_xml, '/DataPDU/Header/Message/SenderReference', 'xmlns="urn:swift:saa:xsd:saa.2.0"') AS c
        FROM enveloppe_message
       WHERE ems_id = 1130;
      It's ok and I get these datas :
      a : ou=abc,ou=ghf,o=bvhdjdie,o=lkljg
      b : ou=dfg,ou=fkd,o=nbjfkkdk,o=bnfjd
      c : jkldfjsdlflsnvkllkfkfllcklqsjkckld
      and thus, this query become ok ????
      UPDATE enveloppe_message
         SET ems_date_modification = SYSDATE,
             ems_login_modification = 'pipo',
             ems_dn_emet = 'ou=abc,ou=ghf,o=bvhdjdie,o=lkljg',
             ems_dn_dest = 'ou=dfg,ou=fkd,o=nbjfkkdk,o=bnfjd',
             ems_reference_tech = 'jkldfjsdlflsnvkllkfkfllcklqsjkckld'
       WHERE ems_id = 1130;
      Does anyone have some idea ????

      Thanks.
        • 1. Re: Problem in UPDATE with EXTRACTVALUE : ORA-00600
          Herald ten Dam
          Hi,

          with Ora-600 you should get contact with Oracle Support. A search on Oracle Supports gives some notes, one regarding Transportable Tablespaces, but I don't think it is your case, or if the table is compressed. On the moment there is no patch for this last bug, except to decompress the table. Have a look at note 1073784.1 or search for koklismem11 on Oracle Support.

          Herald ten Dam
          http://htendam.wordpress.com
          • 2. Re: Problem in UPDATE with EXTRACTVALUE : ORA-00600
            Marco Gralike
            Which Swift XML schema is this...? AND on which database version are you doing this...? (ALL DIGITS please - See Main page of this forum regarding thread "rules" http://forums.oracle.com/forums/ann.jspa?annID=311)

            Edited by: Marco Gralike on Apr 15, 2011 10:20 PM
            • 3. Re: Problem in UPDATE with EXTRACTVALUE : ORA-00600
              domiq44
              Hello,

              My work is about Swift XML ISO 20022.

              Here is the version of Oracle I use.
              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 IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production               
              NLSRTL Version 11.2.0.1.0 - Production       
              • 4. Re: Problem in UPDATE with EXTRACTVALUE : ORA-00600
                domiq44
                Thanks Herald,

                I've sent this problem to our DBA to take a look a Metalink and/or send a request to out ORacle Support.
                But it will take a while, so I'm looking for a solution myself.
                Probably I'll find a work arround like the one I found in my first post for this topic.
                • 5. Re: Problem in UPDATE with EXTRACTVALUE : ORA-00600
                  Marco Gralike
                  There are a lot of Swift Schema's (subsections), do you which one specifically...?

                  Most issues, for the ones I used for a customer, where / are solved in 11.2.0.2 and/or with a backport from 11.2.0.3.
                  That said. I DO NOT use extractvalue/extract/and sorts anymore in 11.2.0.x and so shouldn't you. Please use xmlcast,
                  xmlexist, xmltable, XQuery and such, this because they will be better perform and/or will be supported.

                  ...probably the first thing you will get returned from Oracle support, will be that you will be asked to switch from extractvalue/etc towards XQuery supporting functions anyway...
                  • 6. Re: Problem in UPDATE with EXTRACTVALUE : ORA-00600
                    domiq44
                    Marco,

                    I use SQL not XQuery.
                    How can change my update SQL statment and use xmlcast/xmlexist/xmltable ?
                    I d'ont see in this case !
                    • 7. Re: Problem in UPDATE with EXTRACTVALUE : ORA-00600
                      Marco Gralike
                      Have a look at the examples and tips given in

                      Oracle XML DB : Best Practices to Get Optimal Performance out of XML Queries (PDF) Oct 2010

                      http://www.oracle.com/technetwork/database/features/xmldb/xmlqueryoptimize11gr2-168036.pdf
                      1 person found this helpful
                      • 8. Re: Problem in UPDATE with EXTRACTVALUE : ORA-00600
                        domiq44
                        Thanks Marco.
                        I'm going to have a look at this document.
                        • 9. Re: Problem in UPDATE with EXTRACTVALUE : ORA-00600
                          domiq44
                          Yes, in this document is said that EXTRACTVALUE beleive to the old syntax and one may use the new syntax xmlcast(xmlquery()).
                          But it's very verbose !
                          So, I have replaced EXTRACTVALUE in this query :
                          SELECT EXTRACTVALUE (ems_xml, '/DataPDU/Header/Message/Sender/DN', 'xmlns="urn:swift:saa:xsd:saa.2.0"')
                            FROM enveloppe_message
                           WHERE ems_id = 1130;
                          by XMLCast (XMLQuery ()) like this :
                          SELECT xmlcast(xmlquery('declare default element namespace "urn:swift:saa:xsd:saa.2.0"; $p/DataPDU/Header/Message/Sender/DN' passing ems_xml as "p" returning content) as varchar2(4000))
                            FROM enveloppe_message
                           WHERE ems_id = 1130;
                          It's ok, but I d'on't know how to use a "defined" namespace "insteed" a default one !
                          This query :
                          SELECT xmlcast(xmlquery('declare namespace toto="urn:swift:saa:xsd:saa.2.0"; $toto:p/DataPDU/Header/Message/Sender/DN' passing ems_xml as "p" returning content) as varchar2(4000))
                            FROM enveloppe_message
                           WHERE ems_id = 1130;
                          is not okay !!!???
                          So, how to do this?
                          Do someone have already used that ?
                          Thanks.
                          • 10. Re: Problem in UPDATE with EXTRACTVALUE : ORA-00600
                            Marco Gralike
                            In the FAQ of this XMLDB forum (see main page), there are some examples regarding namespace declarations. Also I posted one for XMLEXIST a short while ago on this forum if needed...
                            • 11. Re: Problem in UPDATE with EXTRACTVALUE : ORA-00600
                              Marco Gralike
                              I know its very verbose, but the W3C (and therefore XMLDB) standard for XML from 11gRx and onwards...
                              • 12. Re: Problem in UPDATE with EXTRACTVALUE : ORA-00600
                                domiq44
                                Thanks a lot Marco for the link on the FAQ.

                                But I don't understand how to use a non default namespace... sorry!

                                Finally, this query is okay :
                                SELECT xmlcast(xmlquery('declare default element namespace "urn:swift:saa:xsd:saa.2.0"; $p/DataPDU/Header/Message/Sender/DN' passing ems_xml as "p" returning content) as varchar2(4000)) AS a, 
                                       xmlcast(xmlquery('declare default element namespace "urn:swift:saa:xsd:saa.2.0"; $p/DataPDU/Header/Message/Receiver/DN' passing ems_xml as "p" returning content) as varchar2(4000)) AS b,
                                       xmlcast(xmlquery('declare default element namespace "urn:swift:saa:xsd:saa.2.0"; $p/DataPDU/Header/Message/SenderReference' passing ems_xml as "p" returning content) as varchar2(4000)) AS c
                                  FROM enveloppe_message
                                 WHERE ems_id = 1130;
                                But this one, not !
                                UPDATE enveloppe_message
                                   SET ems_date_modification = SYSDATE,
                                       ems_login_modification = 'pipo',
                                       ems_dn_emet = xmlcast(xmlquery('declare default element namespace "urn:swift:saa:xsd:saa.2.0"; $p/DataPDU/Header/Message/Sender/DN' passing ems_xml as "p" returning content) as varchar2(4000)),
                                       ems_dn_dest = xmlcast(xmlquery('declare default element namespace "urn:swift:saa:xsd:saa.2.0"; $p/DataPDU/Header/Message/Receiver/DN' passing ems_xml as "p" returning content) as varchar2(4000)),
                                       ems_reference_tech = xmlcast(xmlquery('declare default element namespace "urn:swift:saa:xsd:saa.2.0"; $p/DataPDU/Header/Message/SenderReference' passing ems_xml as "p" returning content) as varchar2(4000))
                                 WHERE ems_id = 1130;
                                I get the same error :
                                ORA-00600: code d'erreur interne, arguments : [koklismem2: insufficient locator len], [0], [86], [], [], [], [], [], [], [], [], []
                                Maybe these optimizations don't solve my initial problem !?
                                • 13. Re: Problem in UPDATE with EXTRACTVALUE : ORA-00600
                                  Marco Gralike
                                  Have a look here:

                                  Namespace declaration in XMLEXISTS
                                  Maybe these optimizations don't solve my initial problem !?
                                  Could be, but this is the syntax they will have to support. The other syntax is deprecated (see 11.2.0.2.0 new features/deprecated section)
                                  • 14. Re: Problem in UPDATE with EXTRACTVALUE : ORA-00600
                                    domiq44
                                    Marco,

                                    Yes, I'll adopt this syntax, even if it's more verbose.
                                    But I don't succeed with this non default namespace.
                                    SELECT xmlcast(xmlquery('declare namespace r="urn:swift:saa:xsd:saa.2.0"; (::) r:Resource[$p/DataPDU/Header/Message/Sender/DN]' passing ems_xml as "p" returning content) as varchar2(4000)) 
                                      FROM enveloppe_message
                                     WHERE ems_id = 1130;
                                    I get this error message :
                                    SELECT xmlcast(xmlquery('declare namespace r="urn:swift:saa:xsd:saa.2.0"; (::) r:Resource[$p/DataPDU/Header/Message/Sender/DN]' passing ems_xml as "p" returning content) as varchar2(4000)) 
                                      FROM enveloppe_message
                                     WHERE ems_id = 1130
                                           *
                                    Error at line 2
                                    ORA-19228: XPST0008 - identificateur non déclaré : préfixe '.' nom local ''
                                    In fact, I don't know and I don't understand this syntax !!!
                                    1 2 Previous Next