1 2 Previous Next 17 Replies Latest reply: Dec 19, 2013 5:42 AM by Marco Gralike RSS

    Problems with LPX-00245 (character set problem?)

    Matze1969

      Hi all,

       

      I've got a problam with ORA-19202 and LPX-00245 (extra data after end of document) when querying my xmltype table. The table contains one large xml document. This xml document is valid, I've checked it against the corresponding XSD (using JDeveloper and also Notepad++, no validation errors).

       

      I gues it has something to do with the encoding of the document. The original encoding is ISO-8859-1 (<?xml version="1.0" encoding="ISO-8859-1"?>). When I load the document to the database it is autoamtically changed to UTF-8 (<?xml version="1.0" encoding="UTF-8"?>) maybe because the character setting of my database is AL32UTF8.

       

      I use the following statement to store my XML:

       

            insert into my_table

            values( my_seq_spp.nextval,

                 r_get_files.file_name,

                    xmltype(

                    bfilename(p_directory, r_get_files.file_name) -- p_directory is the name of an oracle dircetory

                    , nls_charset_id('WE8ISO8859P1')

                    )

                  ); 

       

      Nevertheless the retrieved charset id 31 is ignored. Also if II use csid = 0, it doesn't work...

       

      Any idea how to enforce using ISO-8859-1 instead UTF-8 as character set?

       

      Best regards

      Matthias

        • 1. Re: Problems with LPX-00245 (character set problem?)
          odie_63

          Hi Matthias,

          Any idea how to enforce using ISO-8859-1 instead UTF-8 as character set?

           

          The document is stored in the DB charset, there's no workaround to that.

          The charset you specify in the XMLType constructor is there to instruct Oracle about the real encoding of the input file so that a correct conversion could take place.

          UTF-8 being a strict superset of ISO-8859-1, there shouldn't be any conversion problem when the file is loaded in the table.

           

          - What query is giving you the error? Just a simple SELECT of the XMLType column ?

          - Is there any processing instruction in the XML ?

          • 2. Re: Problems with LPX-00245 (character set problem?)
            Matze1969

            Hi Odie,

             

            the "real life" select looks like this:

             

            select m.version

            , sp.Betriebsstelle

            , Betriebsstellenfahrwege

               from imp_spurplan t

              ,     xmltable

                   ('/XmlIssDaten' passing t.xml_document

                     COLUMNS

                         Version       varchar2(6)   path 'Version/Name'

                       , Spurplanbetriebsstellen     xmltype  path 'Spurplanbetriebsstellen'

                   ) m

                   ,     xmltable

                   ('/Spurplanbetriebsstellen/Spurplanbetriebsstelle' passing m.Spurplanbetriebsstellen

                     COLUMNS

                       Spurplanbetriebsstelle     xmltype  path '/'

                       ,Betriebsstelle     varchar2(6)  path 'Betriebsstelle'

                   ) sp

                   ,  xmltable

                   ('/Spurplanbetriebsstelle/Betriebsstellenfahrwege' passing sp.Spurplanbetriebsstelle

                     COLUMNS

                       Betriebsstellenfahrwege xmltype path '/'

                   ) spa

                   where sp.Betriebsstelle = 'NWH'

                   ;

             

            It just fails for this where clause. If I specify another "Betriebsstelle" (e.g. NWI) it works fine. The select statement works for 99% of all Betriebsstellen, only 5 fail with this error.

             

            Also the simpler select works:

             

            select m.version

            , sp.Betriebsstelle

               from imp_spurplan t

              ,     xmltable

                   ('/XmlIssDaten' passing t.xml_document

                     COLUMNS

                         Version       varchar2(6)   path 'Version/Name'

                       , Spurplanbetriebsstellen     xmltype  path 'Spurplanbetriebsstellen'

                   ) m

                   ,     xmltable

                   ('/Spurplanbetriebsstellen/Spurplanbetriebsstelle' passing m.Spurplanbetriebsstellen

                     COLUMNS

                       Spurplanbetriebsstelle     xmltype  path '/'

                       ,Betriebsstelle     varchar2(6)  path 'Betriebsstelle'

                   ) sp

                   where sp.Betriebsstelle = 'NWH'

                   ;

             

            Any idea?

             

            Best regards

            Matthias

            • 3. Re: Problems with LPX-00245 (character set problem?)
              Marco Gralike

              Regarding column imp_spurplan.xml_document, is this a XMLType Binary XML or XMLType Object Relational stored column...

               

              ALSO have a look at the FAQ URL regarding displaying encoding in the prolog of an XML document here: Conversion UTF-8 -> WINDOWS-1250

              • 4. Re: Problems with LPX-00245 (character set problem?)
                Matze1969

                XMLType is stored as CLOB.

                • 5. Re: Problems with LPX-00245 (character set problem?)
                  Marco Gralike

                  So...I am guessing, you are still on Oracle 10gR2 ...? (10.2.0.3/4 ??)

                  • 6. Re: Problems with LPX-00245 (character set problem?)
                    Marco Gralike

                    Following the example given in the URL (FAQ), you should be able to "enforce" the encoding by using a client NLS_LANG setting that reflects the needed encoding, for example via:

                     

                     

                    > set NLS_LANG=.ISO-8859-1

                    • 7. Re: Problems with LPX-00245 (character set problem?)
                      Marco Gralike

                      ...at least, as long you don't use something like getclobval(), see FAQ URL

                       

                      SQL> select object_value from test;

                      OBJECT_VALUE
                      --------------------------------------------------------------------------------
                      <?xml version="1.0" encoding="WINDOWS-1252"?>
                      <FOO/>


                      SQL> select x.object_value.getClobVal() from test x;

                      X.OBJECT_VALUE.GETCLOBVAL()
                      --------------------------------------------------------------------------------
                      <?xml version="1.0" encoding="UTF-8"?><FOO/>

                      • 8. Re: Problems with LPX-00245 (character set problem?)
                        Marco Gralike

                        That said, Oracle 10.2 or 11.x database (please mention all digits)

                        • 9. Re: Problems with LPX-00245 (character set problem?)
                          odie_63

                          Hi Marco,

                           

                          I don't think it has anything to do with encoding (client-side or not).

                          I'd be more inclined to say it's related to XML fragments manipulation.

                           

                          @Matthias :

                          Does this work better :

                          select m.version

                               , sp.Betriebsstelle

                               , spa.Betriebsstellenfahrwege

                          from imp_spurplan t

                             , xmltable('/XmlIssDaten'

                                 passing t.xml_document

                                 COLUMNS

                                   Version                  varchar2(6) path 'Version/Name'

                                 , Spurplanbetriebsstellen  xmltype     path 'Spurplanbetriebsstellen'

                               ) m

                             , xmltable('/Spurplanbetriebsstellen/Spurplanbetriebsstelle'

                                 passing m.Spurplanbetriebsstellen

                                 COLUMNS

                                   Betriebsstellenfahrwege_xml xmltype      path 'Betriebsstellenfahrwege'

                                 , Betriebsstelle              varchar2(6)  path 'Betriebsstelle'

                               ) sp

                             , xmltable('/Betriebsstellenfahrwege'

                                 passing sp.Betriebsstellenfahrwege_xml

                                 COLUMNS

                                   Betriebsstellenfahrwege  xmltype path '.'

                               ) spa

                          where sp.Betriebsstelle = 'NWH'

                          ;

                          • 10. Re: Problems with LPX-00245 (character set problem?)
                            Matze1969

                            Hi Marco,

                             

                            I used "set NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1" (because NLS_LANG=.ISO-8859-1 forced ORA-12705 when invoking SQLplus). Unfortunately it didn't made any difference. The xml file is still stored using <?xml version="1.0" encoding="UTF-8"?>.

                             

                            Best regards

                            Matthias

                            • 11. Re: Problems with LPX-00245 (character set problem?)
                              Marco Gralike

                              > I don't think it has anything to do with encoding (client-side or not).

                               

                              I wanted this to be ruled out, before hand. That said the LPX error, indeed, follows your reasoning. Thats all.

                              • 12. Re: Problems with LPX-00245 (character set problem?)
                                Marco Gralike

                                BTW Marc, do you know how where there is some info about how properly show code in this annoying new forum format?

                                 

                                Thx

                                • 14. Re: Problems with LPX-00245 (character set problem?)
                                  Matze1969

                                  Sorry for the delay because I was a little bit busy. Just a quick reply regarding the database version. It's 11g Rel. 2 (no patch sets installed) on Windows 7 64bit.

                                   

                                  I just select the xmltype column without getClobVal() or something similar.

                                   

                                  I'll test the modified statement latest tomorrow morning!

                                   

                                  Best regards

                                  Matthias

                                  1 2 Previous Next