1 Reply Latest reply: Oct 25, 2011 10:04 AM by odie_63 RSS

    Query in 10g DB gives error when size of element exceeds 4000 characters?

    user49914949919675123
      I used this query give in this thread Need help in reading a _fmb.XML and writing the item properties to a table
      select x1.item_name
           , x1.item_type
           , x2.property
           -- to convert back entities such as 
       to their character values : 
           , utl_i18n.unescape_reference(x2.property_value) as property_value
           -- parent information : 
           , x1.parent_item_name
           , x1.parent_item_type
      from xmltable(
             xmlnamespaces(default 'http://xmlns.oracle.com/Forms', 'http://xmlns.oracle.com/Forms' as "def")
           , 'for $i in /Module/descendant::*[@def:Name]
              return element item {
                attribute item_name {data($i/@def:Name)}
              , attribute item_type {local-name($i)}
              , attribute parent_item_name {data($i/parent::*/@def:Name)}
              , attribute parent_item_type {local-name($i/parent::*)}
              , $i 
              }'
             passing xmltype(bfilename('TEST_DIR','length_test_fmb.xml'), nls_charset_id('AL32UTF8'))
             columns item_name         varchar2(50) path '@item_name'
                   , item_type         varchar2(50) path '@item_type'
                   , parent_item_name  varchar2(50) path '@parent_item_name'
                   , parent_item_type  varchar2(50) path '@parent_item_type'
                   , item              xmltype      path '.'
           ) x1
         , xmltable(
             xmlnamespaces(default 'http://xmlns.oracle.com/Forms', 'http://xmlns.oracle.com/Forms' as "def")
           , 'for $i in /item/*/attribute::def:*
              let $propname := local-name($i)
              where $propname != "Name"
              return element p {
                element name {$propname}
              , element value {data($i)}
              }'
             passing x1.item
             columns property       varchar2(50)  path 'name'
                   , property_value varchar2(4000) path 'value'
          ) x2
      ;
      This works perfectly, but I tried a form which has a program unit which exceeds 4000 characters, i.e. I converted a fmb to xml, I.E. PROPERTY VALUE. When you run the query then you get error "function returned value too large".

      I tried changing the VARCHAR2 to CLOB but then you get another error.

      We cannot migrated to 11g as yet, so how to handle this in 10g?

      Any help would be greatly appreciated.

      Edited by: Channa on Oct 25, 2011 6:40 AM
        • 1. Re: Query in 10g DB gives error when size of element exceeds 4000 characters?
          odie_63
          Hi Channa,

          I should have mentioned it in the previous thread, a sound approach to the overall requirement would be to use object-relational storage for loading XML documents in the database.
          You can read more here : http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb03usg.htm#g1055369

          However, that's not possible if we use the DUMP=ALL option to convert Forms files to XML because the generated files do not conform to the Forms XML schema.
          So unless you decide to use DUMP=OVERRIDDEN, you're stuck with the current situation.

          If I remember correctly, the ability to project large strings as CLOB with XMLTable was added in version 10.2.0.4.

          You're not out of options though.
          Here are two, one being "dirtier" than the other...

          1) Divide the property value into multiple chunks of 4000 characters (or less if the db uses a multi-byte character set), then rebuild the string as CLOB in the SELECT clause :
          select x1.item_name
               , x1.item_type
               , x2.property
               , to_clob(utl_i18n.unescape_reference(x2.property_value1)) || 
                 to_clob(utl_i18n.unescape_reference(x2.property_value2)) as property_value
               , x1.parent_item_name
               , x1.parent_item_type
          from xmltable(
                 xmlnamespaces(default 'http://xmlns.oracle.com/Forms', 'http://xmlns.oracle.com/Forms' as "def")
               , 'for $i in /Module/descendant::*[@def:Name]
                  return element item {
                    attribute item_name {data($i/@def:Name)}
                  , attribute item_type {local-name($i)}
                  , attribute parent_item_name {data($i/parent::*/@def:Name)}
                  , attribute parent_item_type {local-name($i/parent::*)}
                  , $i 
                  }'
                 passing xmltype(bfilename('TEST_DIR','module2.xml'), nls_charset_id('AL32UTF8'))
                 columns item_name         varchar2(50) path '@item_name'
                       , item_type         varchar2(50) path '@item_type'
                       , parent_item_name  varchar2(50) path '@parent_item_name'
                       , parent_item_type  varchar2(50) path '@parent_item_type'
                       , item              xmltype      path '.'
               ) x1
             , xmltable(
                 xmlnamespaces(default 'http://xmlns.oracle.com/Forms', 'http://xmlns.oracle.com/Forms' as "def")
               , 'for $i in /item/*/attribute::def:*
                  let $propname := local-name($i)
                  let $propval := data($i)
                  where $propname != "Name"
                  return element p {
                    element name {$propname}
                  , element value1 {substring($propval,1,4000)}
                  , element value2 {substring($propval,4001,4000)}
                  }'
                 passing x1.item
                 columns property        varchar2(50)   path 'name'
                       , property_value1 varchar2(4000) path 'value1'
                       , property_value2 varchar2(4000) path 'value2'
              ) x2
          ;
          2) Output the property value as a text() node (XMLType datatype) and serialize as CLOB in the SELECT :
          select x1.item_name
               , x1.item_type
               , x2.property
               , dbms_xmlgen.convert(x2.property_value.getclobval(),1) as property_value
               , x1.parent_item_name
               , x1.parent_item_type
          from xmltable(
                 xmlnamespaces(default 'http://xmlns.oracle.com/Forms', 'http://xmlns.oracle.com/Forms' as "def")
               , 'for $i in /Module/descendant::*[@def:Name]
                  return element item {
                    attribute item_name {data($i/@def:Name)}
                  , attribute item_type {local-name($i)}
                  , attribute parent_item_name {data($i/parent::*/@def:Name)}
                  , attribute parent_item_type {local-name($i/parent::*)}
                  , $i 
                  }'
                 passing xmltype(bfilename('TEST_DIR','module2.xml'), nls_charset_id('AL32UTF8'))
                 columns item_name         varchar2(50) path '@item_name'
                       , item_type         varchar2(50) path '@item_type'
                       , parent_item_name  varchar2(50) path '@parent_item_name'
                       , parent_item_type  varchar2(50) path '@parent_item_type'
                       , item              xmltype      path '.'
               ) x1
             , xmltable(
                 xmlnamespaces(default 'http://xmlns.oracle.com/Forms', 'http://xmlns.oracle.com/Forms' as "def")
               , 'for $i in /item/*/attribute::def:*
                  let $propname := local-name($i)
                  where $propname != "Name"
                  return element p {
                    element name {$propname}
                  , element value {data($i)}
                  }'
                 passing x1.item
                 columns property        varchar2(50)   path 'name'
                       , property_value  xmltype        path 'value/text()'
              ) x2
          ;
          Edited by: odie_63 on 25 oct. 2011 17:03

          Edited by: odie_63 on 25 oct. 2011 17:04