1 2 Previous Next 16 Replies Latest reply: Dec 14, 2011 8:16 AM by 723980 RSS

    XQuery tag case function?

    723980
      Hi,

      We are currently using DBMS_XMLQUERY to build XML files from a complex, nested object type we have created. Unfortunately, it seems that DBMS_XMLQUERY is not the preferred way to do so, the Oracle 11g XML developer's guide points that we should use DBMS_XMLGEN instead. That's not an alternative, as it seems like XMLGEN lacks the "setTagCase" function of XMLQUERY and the resulting XML doc gets all it's tags in caps, which apparently is a big no-no.

      I've been toying with XQuery today to see if it can work as an alternative, but I'm stuck at the same point. I can't seem to find any way of having the tag names in lower case. I've tried with fn:lower-case, but it sets the case for the entire content of the object, not just the tags (besides, it doesn't even seem to work with my object type...it sets the values in lower case AND removes the tags, go figure). I can't find any answer to this anywhere. Anybody know some easy way to do it?

      This is the query I'm using:

      select xmlquery (
      'for $j in 1 return
      ( for $i in ora:view("TAB_OBJ_INMVAL") /ROW
      return (<something>{$i/COL_OBJX_INMVAL}</something>)
      )'
      RETURNING CONTENT
      )
      from dual
      /

      Thanks!
        • 1. Re: XQuery tag case function?
          AlexAnd
          may be DBMS_XMLQuery.LOWER_CASE
          SQL> set serveroutput on
          SQL> select * from v$version;
           
          BANNER
          --------------------------------------------------------------------------------
          Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
          PL/SQL Release 11.2.0.2.0 - Production
          CORE     11.2.0.2.0     Production
          TNS for HPUX: Version 11.2.0.2.0 - Production
          NLSRTL Version 11.2.0.2.0 - Production
           
          SQL> 
          SQL> DECLARE
            2    v_ctx   DBMS_XMLQuery.ctxType;
            3    v_xml   CLOB;
            4  begin
            5    v_ctx := dbms_xmlquery.newcontext('SELECT empno, ename FROM scott.emp WHERE rownum<3');
            6    DBMS_XMLQuery.setRowsetTag(v_ctx, 'emps');
            7    dbms_xmlquery.setrowtag(v_ctx, 'HM');
            8    --DBMS_XMLQuery.setTagCase(v_ctx, DBMS_XMLQuery.LOWER_CASE);
            9    v_xml := DBMS_XMLQuery.GetXML(v_ctx);
           10    DBMS_XMLQuery.closeContext(v_ctx);
           11    dbms_output.put_line(v_xml);
           12  end;
           13  /
           
          <?xml version = '1.0'?>
          <emps>
             <HM num="1">
                <EMPNO>7369</EMPNO>
                <ENAME>SMITH</ENAME>
             </HM>
             <HM num="2">
                <EMPNO>7499</EMPNO>
                <ENAME>ALLEN</ENAME>
             </HM>
          </emps>
          
           
          PL/SQL procedure successfully completed
           
          SQL> 
          SQL> DECLARE
            2    v_ctx   DBMS_XMLQuery.ctxType;
            3    v_xml   CLOB;
            4  begin
            5    v_ctx := dbms_xmlquery.newcontext('SELECT empno, ename FROM scott.emp WHERE rownum<3');
            6    DBMS_XMLQuery.setRowsetTag(v_ctx, 'emps');
            7    dbms_xmlquery.setrowtag(v_ctx, 'HM');
            8    DBMS_XMLQuery.setTagCase(v_ctx, DBMS_XMLQuery.LOWER_CASE);
            9    v_xml := DBMS_XMLQuery.GetXML(v_ctx);
           10    DBMS_XMLQuery.closeContext(v_ctx);
           11    dbms_output.put_line(v_xml);
           12  end;
           13  /
           
          <?xml version = '1.0'?>
          <emps>
             <hm num="1">
                <empno>7369</empno>
                <ename>SMITH</ename>
             </hm>
             <hm num="2">
                <empno>7499</empno>
                <ename>ALLEN</ename>
             </hm>
          </emps>
          
           
          PL/SQL procedure successfully completed
           
          SQL> 
          • 2. Re: XQuery tag case function?
            odie_63
            Hi,

            First off, the fact that you're using objects to store your data does not give much latitude. Everything would be easier with relational data and SQL/XML functions.

            DBMS_XMLGEN, XQuery ora:view (deprecated in 11.2 btw), or XMLType constructor applied on objects generate a canonical XML format using attributes and object names to derive the tag names.
            There's not much you can do before the whole XML is created, but you can always transform it afterwards, for example with an XSL transformation or the equivalent XQuery function.

            But actually, if possible for you, I think the easiest way is to give the objects and attributes case-sensitive names in the first place.
            SQL> create type my_obj as object ("item" varchar2(30));
              2  /
             
            Type created
             
            SQL> select xmlserialize(document xmltype(my_obj('ABC'))) from dual;
             
            XMLSERIALIZE(DOCUMENTXMLTYPE(M
            --------------------------------------------------------------------------------
            <MY_OBJ><item>ABC</item></MY_OBJ>
             
            • 3. Re: XQuery tag case function?
              odie_63
              Just remembered another interesting approach : XML schema mapping.

              Here's an example with a fairly simple structured object :

              1) Set up...
              SQL> create type my_obj is object (
                2    id    number
                3  , name  varchar2(30)
                4  , qty   number(7)
                5  );
                6  /
               
              Type created
               
              SQL> create type my_obj_tab is table of my_obj;
                2  /
               
              Type created
               
              SQL> create type my_top_obj is object (
                2    dt     date
                3  , items  my_obj_tab
                4  );
                5  /
               
              Type created
               
              2) Generating the schema from the object definition :
              SQL> select dbms_xmlschema.generateSchema(user, 'MY_TOP_OBJ') from dual;
              
              <?xml version="1.0"?>
              <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xdb="http://xmlns.oracle.com/xdb" xsi:schemaLocation="http://xmlns.oracle.com/xdb http://xmlns.oracle.com/xdb/XDBSchema.xsd">
               <xsd:element name="MY_TOP_OBJ" type="MY_TOP_OBJType" xdb:SQLType="MY_TOP_OBJ" xdb:SQLSchema="DEV"/>
               <xsd:complexType name="MY_TOP_OBJType" xdb:SQLType="MY_TOP_OBJ" xdb:SQLSchema="DEV" xdb:maintainDOM="false">
                <xsd:sequence>
                 <xsd:element name="DT" type="xsd:date" xdb:SQLName="DT" xdb:SQLType="DATE"/>
                 <xsd:element name="ITEMS" type="MY_OBJType" maxOccurs="unbounded" minOccurs="0" xdb:SQLName="ITEMS" xdb:SQLCollType="MY_OBJ_TAB" xdb:SQLType="MY_OBJ" xdb:SQLSchema="DEV" xdb:SQLCollSchema="DEV"/>
                </xsd:sequence>
               </xsd:complexType>
               <xsd:complexType name="MY_OBJType" xdb:SQLType="MY_OBJ" xdb:SQLSchema="DEV" xdb:maintainDOM="false">
                <xsd:sequence>
                 <xsd:element name="ID" type="xsd:double" xdb:SQLName="ID" xdb:SQLType="NUMBER"/>
                 <xsd:element name="NAME" xdb:SQLName="NAME" xdb:SQLType="VARCHAR2">
                  <xsd:simpleType>
                   <xsd:restriction base="xsd:string">
                    <xsd:maxLength value="30"/>
                   </xsd:restriction>
                  </xsd:simpleType>
                 </xsd:element>
                 <xsd:element name="QTY" type="xsd:double" xdb:SQLName="QTY" xdb:SQLType="NUMBER"/>
                </xsd:sequence>
               </xsd:complexType>
              </xsd:schema>
              That's the canonical XML schema representing the object type.
              Now we can edit it to change target element names to our liking :
              <?xml version="1.0"?>
              <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
               <xsd:element name="root" type="MY_TOP_OBJType" xdb:SQLType="MY_TOP_OBJ"/>
               <xsd:complexType name="MY_TOP_OBJType" xdb:SQLType="MY_TOP_OBJ">
                <xsd:sequence>
                 <xsd:element name="order_date" type="xsd:date" xdb:SQLName="DT" xdb:SQLType="DATE"/>
                 <xsd:element name="item" type="MY_OBJType" maxOccurs="unbounded" minOccurs="0" xdb:SQLName="ITEMS" xdb:SQLCollType="MY_OBJ_TAB" xdb:SQLType="MY_OBJ"/>
                </xsd:sequence>
               </xsd:complexType>
               <xsd:complexType name="MY_OBJType" xdb:SQLType="MY_OBJ">
                <xsd:sequence>
                 <xsd:element name="id" type="xsd:double" xdb:SQLName="ID" xdb:SQLType="NUMBER"/>
                 <xsd:element name="item_name" xdb:SQLName="NAME" xdb:SQLType="VARCHAR2">
                  <xsd:simpleType>
                   <xsd:restriction base="xsd:string">
                    <xsd:maxLength value="30"/>
                   </xsd:restriction>
                  </xsd:simpleType>
                 </xsd:element>
                 <xsd:element name="quantity" type="xsd:double" xdb:SQLName="QTY" xdb:SQLType="NUMBER"/>
                </xsd:sequence>
               </xsd:complexType>
              </xsd:schema>
              3) Registering the modified schema :
              begin
               dbms_xmlschema.registerSchema(
                 schemaURL => 'my_obj.xsd',
                 schemaDoc => '<?xml version="1.0"?>
              <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
               <xsd:element name="root" type="MY_TOP_OBJType" xdb:SQLType="MY_TOP_OBJ"/>
               <xsd:complexType name="MY_TOP_OBJType" xdb:SQLType="MY_TOP_OBJ">
                <xsd:sequence>
                 <xsd:element name="order_date" type="xsd:date" xdb:SQLName="DT" xdb:SQLType="DATE"/>
                 <xsd:element name="item" type="MY_OBJType" maxOccurs="unbounded" minOccurs="0" xdb:SQLName="ITEMS" xdb:SQLCollType="MY_OBJ_TAB" xdb:SQLType="MY_OBJ"/>
                </xsd:sequence>
               </xsd:complexType>
               <xsd:complexType name="MY_OBJType" xdb:SQLType="MY_OBJ">
                <xsd:sequence>
                 <xsd:element name="id" type="xsd:double" xdb:SQLName="ID" xdb:SQLType="NUMBER"/>
                 <xsd:element name="item_name" xdb:SQLName="NAME" xdb:SQLType="VARCHAR2">
                  <xsd:simpleType>
                   <xsd:restriction base="xsd:string">
                    <xsd:maxLength value="30"/>
                   </xsd:restriction>
                  </xsd:simpleType>
                 </xsd:element>
                 <xsd:element name="quantity" type="xsd:double" xdb:SQLName="QTY" xdb:SQLType="NUMBER"/>
                </xsd:sequence>
               </xsd:complexType>
              </xsd:schema>',
                 local => true,
                 genTypes => false,
                 genTables => false
               );
              end;
              /
              4) Creating the XML out of the object

              Here we use the schema-based XMLType constructor with :
              - the object instance
              - the schema url
              - the name of the target root element
              SQL> select xmlserialize(document
                2           xmltype(
                3             my_top_obj(
                4               sysdate
                5             , my_obj_tab(
                6                 my_obj(1, 'Item #1', 12)
                7               , my_obj(2, 'Item #2', 99)
                8               , my_obj(3, 'Item #3', 1456)
                9               )
               10             )
               11           , 'my_obj.xsd'
               12           , 'root'
               13           )
               14         ) as xml_output
               15  from dual
               16  ;
               
              XML_OUTPUT
              --------------------------------------------------------------------------------
              <root>
                <order_date>2011-12-01</order_date>
                <item>
                  <id>1</id>
                  <item_name>Item #1</item_name>
                  <quantity>12</quantity>
                </item>
                <item>
                  <id>2</id>
                  <item_name>Item #2</item_name>
                  <quantity>99</quantity>
                </item>
                <item>
                  <id>3</id>
                  <item_name>Item #3</item_name>
                  <quantity>1456</quantity>
                </item>
              </root>
               
              • 4. Re: XQuery tag case function?
                723980
                First, thanks all.

                Alex, I may not be understanding your example, but we are currently using DBMS_XMLQuery to create the XML and it already does what I need, with the setTagCase function. However, what I need to know if we could drop DBMS_XMLQuery for something else, as it Oracle advises to use XMLGEN or something else instead.

                odie, yes, you are right, I could (and indeed we do) transform it afterwards with an XSL. I see no problem in doing that, but for reasons that elude me, my boss wants the XML already in low case. Regarding giving them case sensitive names, it's sound but unpractical at this point. Too many objects and apps using them to make it a viable choice. Your second approach looks better, though my XML is precarious and I'm not sure it would be worth the effort. I mean, probably future XML generating process will be written in JAVA or done with other tools, so it's not that critical if the current one stays as is, with DBMS_XMLQuery. The advantage of it is that I can take an already generated object and stored in a table and with a single query and 4 lines of code, I get the XML, like this:
                -- set up the query context...!
                    varsel := 'select ' ||
                              ' col_objx_inmval inmval '||
                              ' from tab_obj_inmval' ||
                              ' where usuario = user';
                --  setting tags
                    contexto := DBMS_XMLQuery.newContext(varsel);
                    DBMS_XMLQuery.setTagCase(contexto, 1);
                    DBMS_XMLQuery.setRowSetTag(contexto,'');
                    DBMS_XMLQuery.setRowTag(contexto,'');
                    DBMS_XMLQuery.setRowIdAttrName(contexto, '');
                    v_xml_res := DBMS_XMLQuery.getXML(contexto);
                    v_posclob := 1;
                    v_tamclob := dbms_lob.getlength(v_xml_res);
                I was searching for an alternate way of doing it that didn't involve lots of recoding, don't know if it's possible...

                Edited by: user11933086 on 02-dic-2011 5:05
                • 5. Re: XQuery tag case function?
                  odie_63
                  Well, you can even do it with a single SQL query directly from the table :
                  SQL> select xmlserialize(document
                    2           xmlquery(
                    3            'declare function local:transform($e as element()) as element()
                    4             {
                    5               element {lower-case(name($e))}
                    6               {
                    7                 $e/@*
                    8               , for $c in $e/node()
                    9                 return if ($c instance of element())
                   10                        then local:transform($c)
                   11                        else $c
                   12               }
                   13             }; local:transform(*)'
                   14            passing xmltype(col_objx_inmval)
                   15            returning content
                   16           )
                   17           as clob indent
                   18         ) as xml_output
                   19  from tab_obj_inmval
                   20  where usuario = 'DEV'
                   21  ;
                   
                  XML_OUTPUT
                  --------------------------------------------------------------------------------
                  <my_top_obj>
                    <dt>02/12/11</dt>
                    <items>
                      <my_obj>
                        <id>1</id>
                        <name>Item #1</name>
                        <qty>12</qty>
                      </my_obj>
                      <my_obj>
                        <id>2</id>
                        <name>Item #2</name>
                        <qty>99</qty>
                      </my_obj>
                      <my_obj>
                        <id>3</id>
                        <name>Item #3</name>
                        <qty>1456</qty>
                      </my_obj>
                    </items>
                  </my_top_obj>
                   
                  And on 11.2.0.3, with XQuery Update Facility :
                  select xmlserialize(document 
                           xmlquery(
                            'copy $d := /*
                             modify ( 
                               for $i in $d/descendant-or-self::*
                               return rename node $i as lower-case(name($i))
                             )
                             return $d'
                             passing xmltype(col_objx_inmval)
                             returning content
                           )
                         ) as xml_output 
                  from tab_obj_inmval
                  where usuario = 'DEV'
                  ;
                  • 6. Re: XQuery tag case function?
                    723980
                    First, sorry for the delay, had a few days off and couldn't check the thread.

                    Odie, that's exactly what I had in mind, thanks a lot, although I get this error:

                    ORA-19134: XQDY0074: It is a dynamic error if the value of the name expression in a computed element constructor cannot be converted to an expanded QName (for example, because it contains a namespace prefix not found in statically known namespaces.) Detail: FORG0001: Invalid value was passed to a type constructor or a cast expression.

                    Given it does seem to work in your example and the examples of FORG0001 I've seen, I'm guessing it has to do with some specific data in my object type, but I'm really lost, it hasn't given me any trouble before. I'm not sure I'm even understanding the error message correctly, all this stuff about XQuery is quite new to me. Do you have any idea what am I looking for?

                    ps: I can't try the second sample you gave, we are on 11.1.0.7.0.

                    Edited by: user11933086 on 12-dic-2011 2:56

                    Edited by: user11933086 on 12-dic-2011 2:57
                    • 7. Re: XQuery tag case function?
                      odie_63
                      Given it does seem to work in your example and the examples of FORG0001 I've seen, I'm guessing it has to do with some specific data in my object type
                      Possibly.

                      Could you try to reproduce the error on a small sample of data so that you can post it here?
                      • 8. Re: XQuery tag case function?
                        723980
                        I'm trying with the smallest object I can generate and still get the same error.

                        The type in question is made of 6 nested object types, some of which are themselves collection types, while other are different object types, which, in turn, can be anything from other objects to native types or any kind of combination.

                        A simple query from that object column filled with just that one nearly empty object, looks like this in SQL Plus (not in Toad, however, where the NULL values are blank and fields and objects are separated by ';' ) :
                        USER     COL_OBJX_INMVAL
                        SIC1      OBJX_INMVAL(OBJX_IDENTINM(OBJX_BI_DOC(OBJX_IDCAT('UR', OBJX_RC('0347920', 'TF8304N', NULL, NULL, NULL), OBJX_LOCAT(NULL, NULL)), OBJX_DT_DOC(NULL, NULL, NULL, NULL, NULL), OBJX_DEBI_DOC(NULL, NULL, NULL)), NULL), NULL, OBJX_DGVAL(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), OBJX_VALSUELO(NULL, NULL, NULL, NULL, NULL, NULL, NULL), OBJX_VALCONST(NULL, NULL, NULL, NULL), OBJX_VALORES(NULL))
                        This returns the same error than if I go with an object full of data, so I'm thinking that the error must be somewhere else. That object type (and that same object with those values) works fine with DBMS_XMLQuery in the example I posted above, so I don't know what is it that xmlquery doesn't like...
                        • 9. Re: XQuery tag case function?
                          odie_63
                          Hi,
                          I'm trying with the smallest object I can generate and still get the same error.
                          Could you give the DDLs for those objects?

                          Thanks.
                          • 10. Re: XQuery tag case function?
                            723980
                            Sure.

                            I'm including the single object that I have currently stored in the table, as appears in the SQL Plus spool. I've tried to indent it a bit to improve readability. I haven't included the DDL for every single object (like objx_B07, for example), as most are null in the example and it will make the list a bit of a mess, but if you think they could give you a hint, I will add them. In general, all object types we use follow the same lines of those posted here... they will be composed of native types (and nothing to fancy, number, varchar2 and some date here and there), other object types and/or tables of object types.
                            objx_inmval(
                                 objx_identinm(
                                      objx_bi_doc(
                                           objx_idcat('ur', 
                                                objx_rc('0347920', 'TF8304N', null, null, null), 
                                                objx_locat(null, null)), 
                                           objx_dt_doc(null, null, null, null, null), 
                                           objx_debi_doc(null, null, null)), 
                                      null), 
                                 null, -- This should be object objx_ltit
                                 objx_dgval(null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null), 
                                 objx_valsuelo(null, null, null, null, null, null, null), 
                                 objx_valconst(null, null, null, null), 
                                 objx_valores(null))
                            
                            create type objx_inmval as object (
                              identinm  objx_identinm,
                              ltit      objx_ltit,    
                              dgval     objx_dgval,   
                              valsuelo  objx_valsuelo,
                              valconst  objx_valconst,
                              valores   objx_valores) 
                            /
                                 create type objx_identinm as object (
                                   bi                      objx_bi_doc,
                                   datosbice               objx_datosbice)
                                 /
                                      create type objx_bi_doc as object (
                                        idcat             objx_idcatidcat,
                                        dt                objx_dt_doc,
                                        debi              objx_debi_doc)
                                      /
                                           create type objx_idcat as object (
                                             cn                varchar2(2), 
                                             rc                objx_rc,
                                             locat             objx_locat) 
                                           /
                                                     create type objx_rc as object (
                                                     pc1                    varchar2(7), 
                                                     pc2                    varchar2(7), 
                                                     car                    varchar2(4), 
                                                     cc1                    varchar2(4), 
                                                     cc2                    varchar2(1)) 
                                                     /
                                                     create type objx_locat as object (
                                                     cd                    varchar2(2), 
                                                     cmc                    varchar2(3)) 
                                                     /
                                           
                                           create type objx_dt_doc as object (
                                             cmc                varchar2(3), 
                                             np                 varchar2(25),
                                             nm                 varchar2(40),
                                             nem                varchar2(30),
                                             locs               objx_locs_doc)
                                           /
                                           
                                           CREATE TYPE objx_debi_doc as object (
                                             luso                varchar2(15),
                                             sfs                 varchar2(10),
                                             cpt                 varchar2(10))
                                           /
                                      
                                           create type objx_datosbice as object (
                                        grupo                    varchar2(1),
                                        rbice                    objx_rbice) 
                                      /     
                                           
                                 create type objx_dgval as object (
                                   mbc                  varchar2(13),
                                   mbrs                 varchar2(13),
                                   mbcci                varchar2(13),
                                   mbrcni               varchar2(13),
                                   mbrci                varchar2(13),
                                   vuzc                 varchar2(9),
                                   vup                  varchar2(9),
                                   vrp                  varchar2(9),
                                   gb                   varchar2(6),
                                   nbop                 varchar2(4),
                                   fbop                 varchar2(10),
                                   nbovm                varchar2(4), 
                                   fbovm                varchar2(10),
                                   aantv                varchar2(4), 
                                   gntv                 varchar2(1), 
                                   fboom                varchar2(10),
                                   nboom                varchar2(4), 
                                   cicir                varchar2(5), 
                                   mbpe                 varchar2(13),
                                   mbpg                 varchar2(13),
                                   mbpr                 varchar2(13),
                                   mcua                 varchar2(13))
                                 /
                                 
                                 create type objx_valsuelo as object (
                                   B03                         objx_B03, 
                                   B04                         objx_B04, 
                                   B05                         objx_B05, 
                                   BC6                         objx_BC6, 
                                   BS6                         objx_BS6, 
                                   BA5                         objx_BA5, 
                                   tvcs                        varchar2(15))
                                 /     
                                 
                                 create type objx_valconst as object (
                                   B07                    objx_B07, 
                                   B08                    objx_B08, 
                                   BA7                    objx_BA7, 
                                   tvc                    varchar2(15))
                                 /
                                 create type objx_valores as object (
                                   B09                    objx_B09)
                                 /
                            Thanks again, your help is much appreciated!
                            • 11. Re: XQuery tag case function?
                              odie_63
                              I haven't included the DDL for every single object (like objx_B07, for example), as most are null in the example and it will make the list a bit of a mess, but if you think they could give you a hint, I will add them.
                              Yes, please do.

                              Thanks in advance.
                              • 12. Re: XQuery tag case function?
                                723980
                                Ok, I'm including the entire script then
                                /*
                                SET TERM OFF
                                SET FEED OFF
                                */
                                
                                --SPOOL objetos_xml_doc.log
                                -- 
                                
                                drop table tab_obj_inmval
                                /
                                drop type objx_inmval
                                /
                                drop type objx_valores
                                /
                                drop type objx_valconst
                                /
                                drop type objx_valsuelo
                                /
                                drop type objx_dgval
                                /
                                drop type objx_ltit
                                /
                                drop type objx_identinm
                                /
                                drop type objx_B09
                                /
                                drop type objx_totval
                                /
                                drop type objx_iibi
                                /
                                drop type objx_B07
                                /
                                drop type objx_lvcep
                                /
                                drop type objx_vcep
                                /
                                drop type objx_lvcec
                                /
                                drop type objx_vcec
                                /
                                drop type objx_ccvc
                                /
                                drop type objx_B08
                                /
                                drop type objx_lvcs
                                /
                                drop type objx_vcs
                                /
                                drop type objx_cdc
                                /
                                drop type objx_ccvcs
                                /
                                drop type objx_BA7
                                /
                                drop type objx_lvcpa
                                /
                                drop type objx_vcpa
                                /
                                drop type objx_evcons
                                /
                                drop type objx_BA5
                                /
                                drop type objx_lvspa
                                /
                                drop type objx_vspa
                                /
                                drop type objx_evs
                                /
                                drop type objx_BS6
                                /
                                drop type objx_lvsrp
                                /
                                drop type objx_vsrp
                                /
                                drop type objx_ccvsc
                                /
                                drop type objx_BC6
                                /
                                drop type objx_lvsrrc
                                /
                                drop type objx_vsrrc
                                /
                                drop type objx_lvsrrp
                                /
                                drop type objx_vsrrp
                                /
                                drop type objx_ccvsr
                                /
                                drop type objx_B05
                                /
                                drop type objx_lvsu
                                /
                                drop type objx_vsu
                                /
                                drop type objx_ccvs
                                /
                                drop type objx_B04
                                /
                                drop type objx_lvsrc
                                /
                                drop type objx_vsrc
                                /
                                drop type objx_cccsc
                                /
                                drop type objx_B03
                                /
                                drop type objx_lvspr
                                /
                                drop type objx_vspr
                                /
                                drop type objx_tit_doc
                                /
                                drop type objx_bi_doc
                                /
                                drop type objx_debi_doc
                                /
                                drop type objx_dt_doc
                                /
                                drop type objx_locs_doc
                                /
                                drop type objx_lous_doc
                                /
                                drop type objx_lors_doc
                                /
                                drop type objx_lorus_doc
                                /
                                drop type objx_datosbice
                                /
                                drop type objx_rbice
                                /
                                ------------------------------------------------------------------------------------------
                                ---------------------------------     CREATE     -----------------------------------------
                                ------------------------------------------------------------------------------------------
                                ---------------------------------
                                ---------    IDENTINM   ---------
                                ---------------------------------
                                -----
                                CREATE TYPE objx_rbice as object (
                                  rc                   objx_rc,           
                                  desb                 varchar2(65))      
                                /
                                -----
                                CREATE TYPE objx_datosbice as object (
                                  grupo                    varchar2(1),   
                                  rbice                    objx_rbice)    
                                /
                                -----
                                CREATE TYPE objx_lorus_doc as object (
                                  cma                  varchar2(3),       
                                  czc                  varchar2(2),       
                                  cpp                  objx_cpp,          
                                  npa                  varchar2(30),      
                                  cpaj                 varchar2(5))       
                                /
                                -----
                                CREATE TYPE objx_lors_doc as object (
                                  lorus               objx_lorus_doc,
                                  lourb               objx_lourb)
                                /
                                -----
                                CREATE TYPE objx_lous_doc as object (
                                  lourb               objx_lourb,
                                  lorus               objx_lorus_doc)
                                /
                                -----
                                CREATE TYPE objx_locs_doc as object (
                                  lous               objx_lous_doc,
                                  lors               objx_lors_doc)
                                /
                                -----
                                CREATE TYPE objx_dt_doc as object (
                                  cmc                varchar2(3),            
                                  np                 varchar2(25),           
                                  nm                 varchar2(40),    
                                  nem                varchar2(30),    
                                  locs               objx_locs_doc)   
                                /
                                -----
                                CREATE TYPE objx_debi_doc as object (
                                  luso                varchar2(15),   
                                  sfs                 varchar2(10),   
                                  cpt                 varchar2(10))   
                                /
                                -----
                                CREATE TYPE objx_bi_doc as object (
                                  idcat             objx_idcat,       
                                  dt                objx_dt_doc,      
                                  debi              objx_debi_doc)    
                                /
                                ---------------------------------
                                ---------      LTIT     ---------
                                ---------------------------------
                                CREATE TYPE objx_tit_doc as object (
                                  der                objx_der,       
                                  idpa               objx_idpa,      
                                  idcbf              objx_idcbf,     
                                  iatit              objx_iatit)     
                                /
                                ---------------------------------
                                ---------    VALSUELO   ---------
                                ---------------------------------
                                CREATE TYPE objx_vspr as object (
                                  cspr                varchar2(4),   
                                  ssp                 varchar2(10),  
                                  ccc                 varchar2(2),   
                                  dcc                 varchar2(40),  
                                  ip                  varchar2(5),   
                                  ate                 varchar2(4),    
                                  te                  varchar2(14),   
                                  pctrep              varchar2(10),   
                                  vsp                 varchar2(15))   
                                /
                                -----
                                CREATE TYPE objx_lvspr as table of objx_vspr
                                /
                                -----
                                CREATE TYPE objx_B03 as object (
                                  lvspr              objx_lvspr,      
                                  tvsp               varchar2(15))    
                                /
                                -----
                                CREATE TYPE objx_cccsc as object (
                                  vccdf                varchar2(4),    
                                  vccvli               varchar2(4),    
                                  vcccs                varchar2(4),    
                                  vcce                 varchar2(4),    
                                  vccad                varchar2(4))    
                                /
                                -----
                                CREATE TYPE objx_vsrc as object (
                                  cuc                 varchar2(4),    
                                  so                  varchar2(7),    
                                  duso                varchar2(20),   
                                  coef                varchar2(7),    
                                  mbr                 varchar2(2),    
                                  cccsc               objx_cccsc,     
                                  rm                  varchar2(4),    
                                  imbr                varchar2(14),   
                                  vuscr               varchar2(13),   
                                  vs                  varchar2(15))   
                                /
                                -----
                                CREATE TYPE objx_lvsrc as table of objx_vsrc
                                /
                                -----
                                CREATE TYPE objx_B04 as object (
                                  lvsrc              objx_lvsrc,        
                                  tvsrc              varchar2(15))      
                                /
                                -----
                                CREATE TYPE objx_ccvs as object (
                                  vccvf               varchar2(4),     
                                  vcclf               varchar2(4),     
                                  vccfi               varchar2(4),     
                                  vccfe               varchar2(4),     
                                  vccsdm              varchar2(4),     
                                  vccit               varchar2(4),     
                                  vccpp               varchar2(4),  
                                  vcces               varchar2(4),  
                                  vccca               varchar2(4))  
                                /
                                -----
                                CREATE TYPE objx_vsu as object (
                                  subp                varchar2(4),  
                                  sup                 varchar2(7),  
                                  zv                  varchar2(5),  
                                  ccvs                objx_ccvs,    
                                  cccsc               objx_cccsc,   
                                  gb                  varchar2(6),  
                                  rm                  varchar2(4),  
                                  vus                 varchar2(9),  
                                  vsp                 varchar2(15)) 
                                /
                                -----
                                CREATE TYPE objx_lvsu as table of objx_vsu
                                /
                                -----
                                CREATE TYPE objx_B05 as object (
                                  lvsu               objx_lvsu)      
                                /
                                -----
                                CREATE TYPE objx_ccvsr as object (
                                  vccvf                varchar2(4),  
                                  vcclf                varchar2(4),  
                                  vccca                varchar2(4))  
                                /
                                -----
                                CREATE TYPE objx_vsrrp as object (
                                  noec                varchar2(4),   
                                  loint               objx_loint,    
                                  stl                 varchar2(11),  
                                  cdes                varchar2(3),   
                                  zv                  varchar2(5),   
                                  tvr                 varchar2(15),  
                                  ccvsr               objx_ccvsr,    
                                  cccsc               objx_cccsc,    
                                  gbc                 varchar2(6),   
                                  rm                  varchar2(4),   
                                  vrs                 varchar2(9),   
                                  vsp                 varchar2(15))  
                                /
                                -----
                                CREATE TYPE objx_lvsrrp as table of objx_vsrrp
                                /
                                -----
                                CREATE TYPE objx_vsrrc as object (
                                  noec                varchar2(4),     
                                  loint               objx_loint,      
                                  stl                 varchar2(11),    
                                  cdes                varchar2(3),     
                                  zv                  varchar2(5),     
                                  tvr                 varchar2(15),    
                                  pctrep              varchar2(10),    
                                  ccvsr               objx_ccvsr,      
                                  cccsc               objx_cccsc,      
                                  gbc                 varchar2(6),     
                                  rm                  varchar2(4),     
                                  vrs                 varchar2(9),     
                                  vsp                 varchar2(15))    
                                /
                                -----
                                CREATE TYPE objx_lvsrrc as table of objx_vsrrc
                                /
                                -----
                                CREATE TYPE objx_BC6 as object (
                                  lvsrrp             objx_lvsrrp,       
                                  lvsrrc             objx_lvsrrc)       
                                /
                                -----
                                CREATE TYPE objx_ccvsc as object (
                                  vccvf                varchar2(4),     
                                  vcclf                varchar2(4))     
                                /
                                -----
                                CREATE TYPE objx_vsrp as object (
                                  subp                varchar2(4),    
                                  sup                 varchar2(7),    
                                  zv                  varchar2(5),    
                                  tvr                 varchar2(15),
                                  ccvsc               objx_ccvsc,  
                                  cccsc               objx_cccsc,  
                                  gb                  varchar2(6), 
                                  rm                  varchar2(4), 
                                  vrs                 varchar2(9), 
                                  vsp                 varchar2(15))
                                /
                                -----
                                CREATE TYPE objx_lvsrp as table of objx_vsrp
                                /
                                -----
                                CREATE TYPE objx_BS6 as object (
                                  lvsrp              objx_lvsrp,            
                                  tvr                varchar2(15))          
                                /
                                -----
                                CREATE TYPE objx_evs as object (
                                  vus                varchar2(9),           
                                  icca               varchar2(10),          
                                  vtccvs             varchar2(8),           
                                  vtccvsc            varchar2(8),           
                                  gbs                varchar2(6))           
                                /
                                -----
                                CREATE TYPE objx_vspa as object (
                                  sup                 varchar2(7),          
                                  evs                 objx_evs)             
                                /
                                -----
                                CREATE TYPE objx_lvspa as table of objx_vspa
                                /
                                -----
                                CREATE TYPE objx_BA5 as object (
                                  lvspa             objx_lvspa)
                                /
                                ---------------------------------
                                ---------    VALCONST   ---------
                                ---------------------------------
                                CREATE TYPE objx_evcons as object (
                                  vrs                   varchar2(9),
                                  vmctcc                varchar2(13),
                                  vmctcb                varchar2(13),
                                  icca                  varchar2(10),
                                  vtccvs                varchar2(8),
                                  vtccvc                varchar2(8),
                                  vtccvsc               varchar2(8),
                                  ddes                  varchar2(15),
                                  gbc                   varchar2(6))
                                /
                                -----
                                CREATE TYPE objx_vcpa as object (
                                  loint               objx_loint,                 
                                  stl                 varchar2(11),               
                                  evcons              objx_evcons,                
                                  vuc                 varchar2(1))                
                                /
                                -----
                                CREATE TYPE objx_lvcpa as table of objx_vcpa
                                /
                                -----
                                CREATE TYPE objx_BA7 as object (
                                  lvcpa              objx_lvcpa,              
                                  sup                varchar2(10))
                                /
                                -----
                                CREATE TYPE objx_ccvcs as object (
                                  vccev                varchar2(4),           
                                  vccac                varchar2(4),           
                                  vccot                varchar2(4),           
                                  vccps                varchar2(4),           
                                  vccca                varchar2(4))           
                                /
                                -----
                                CREATE TYPE objx_cdc as object (
                                  mbc                   varchar2(2),          
                                  tip                   varchar2(5),          
                                  cf                    varchar2(10))         
                                /
                                -----
                                CREATE TYPE objx_vcs as object (
                                  noec               varchar2(4),             
                                  loint              objx_loint,              
                                  stl                varchar2(11),            
                                  uvbi               varchar2(8),
                                  ant                varchar2(4),             
                                  ddes               varchar2(20),            
                                  cdc                objx_cdc,                
                                  ccvcs              objx_ccvcs,              
                                  rm                 varchar2(4),             
                                  immbc              varchar2(10),            
                                  vcc                varchar2(15))            
                                /
                                -----
                                CREATE TYPE objx_lvcs as table of objx_vcs
                                /
                                -----
                                CREATE TYPE objx_B08 as object (
                                  lvcs               objx_lvcs)               
                                /
                                -----
                                CREATE TYPE objx_ccvc as object (
                                  vccac                varchar2(4),           
                                  vccec                varchar2(4),
                                  vccca                varchar2(4))           
                                /
                                -----
                                CREATE TYPE objx_vcep as object (
                                  noec                varchar2(4),            
                                  loint               objx_loint,             
                                  stl                 varchar2(11),           
                                  cdes                varchar2(3),            
                                  ant                 varchar2(4),            
                                  ccec                varchar2(1),            
                                  cdc                 objx_cdc,               
                                  ccvc                objx_ccvc,              
                                  cccsc               objx_cccsc,             
                                  gbc                 varchar2(6),            
                                  rm                  varchar2(4),            
                                  immbc               varchar2(10),         
                                  vcc                 varchar2(15))         
                                /
                                ----
                                CREATE TYPE objx_lvcep as table of objx_vcep
                                /
                                -----
                                CREATE TYPE objx_vcec as object (
                                  noec                varchar2(4),       
                                  loint               objx_loint,        
                                  stl                 varchar2(11),      
                                  cdes                varchar2(3),       
                                  ant                 varchar2(4),       
                                  ccec                varchar2(1),       
                                  pctrep              varchar2(10),      
                                  cdc                 objx_cdc,          
                                  ccvc                objx_ccvc,         
                                  cccsc               objx_cccsc,        
                                  gbc                 varchar2(6),       
                                  rm                  varchar2(4),       
                                  immbc               varchar2(10),      
                                  vcc                 varchar2(15))      
                                /
                                -----
                                CREATE TYPE objx_lvcec as table of objx_vcec
                                /
                                -----
                                CREATE TYPE objx_B07 as object (
                                  lvcep              objx_lvcep,         
                                  lvcec              objx_lvcec)         
                                /
                                ---------------------------------
                                ---------    VALORES   ---------
                                ---------------------------------
                                CREATE TYPE objx_iibi as object (
                                  evcr                varchar2(4),       
                                  cicir               varchar2(9),       
                                  bimp                varchar2(15),      
                                  vb                  varchar2(15),      
                                  ired                varchar2(15),      
                                  uared               varchar2(4),       
                                  bl                  varchar2(15))      
                                /
                                -----
                                CREATE TYPE objx_totval as object (
                                  avc                   varchar2(4),     
                                  vcsa                  varchar2(15),    
                                  vcsrc                 varchar2(15),    
                                  vcs                   varchar2(15),    
                                  vcc                   varchar2(15),    
                                  vcat                  varchar2(15),    
                                  pav                   varchar2(15))    
                                /
                                -----
                                CREATE  TYPE objx_B09 as object (
                                  totval             objx_totval,        
                                  iibi               objx_iibi)          
                                /
                                ----------------------------
                                --------- INMVAL -----------
                                ----------------------------
                                create type objx_identinm as object (
                                  bi                      objx_bi_doc,
                                  datosbice               objx_datosbice)
                                /
                                ---- 
                                CREATE TYPE objx_ltit as table of objx_tit_doc
                                /
                                ---- 
                                create type objx_dgval as object (
                                  mbc                  varchar2(13),     
                                  mbrs                 varchar2(13),     
                                  mbcci                varchar2(13),     
                                  mbrcni               varchar2(13),     
                                  mbrci                varchar2(13),     
                                  vuzc                 varchar2(9),      
                                  vup                  varchar2(9),      
                                  vrp                  varchar2(9),      
                                  gb                   varchar2(6),      
                                  nbop                 varchar2(4),      
                                  fbop                 varchar2(10),     
                                  nbovm                varchar2(4),      
                                  fbovm                varchar2(10),     
                                  aantv                varchar2(4),      
                                  gntv                 varchar2(1),      
                                  fboom                varchar2(10),     
                                  nboom                varchar2(4),      
                                  cicir                varchar2(5),      
                                  mbpe                 varchar2(13),     
                                  mbpg                 varchar2(13),     
                                  mbpr                 varchar2(13),     
                                  mcua                 varchar2(13))     
                                /
                                ---- 
                                create type objx_valsuelo as object (
                                  B03                         objx_B03,     
                                  B04                         objx_B04,     
                                  B05                         objx_B05,   
                                  BC6                         objx_BC6,   
                                  BS6                         objx_BS6,   
                                  BA5                         objx_BA5,   
                                  tvcs                        varchar2(15)) 
                                /
                                ---- 
                                create type objx_valconst as object (
                                  B07                    objx_B07,          
                                  B08                    objx_B08,          
                                  BA7                    objx_BA7,          
                                  tvc                    varchar2(15))      
                                /
                                -----
                                create type objx_valores as object (
                                  B09                    objx_B09)
                                /
                                --------------------------------------
                                ----------- TABLA INMVAL -------------
                                --------------------------------------
                                create type objx_inmval as object (
                                  identinm              objx_identinm,              
                                  ltit                  objx_ltit,                  
                                  dgval                 objx_dgval,                 
                                  valsuelo              objx_valsuelo,              
                                  valconst              objx_valconst,              
                                  valores               objx_valores)               
                                /
                                --------------------------------------
                                -----------    TABLA     -------------
                                --------------------------------------
                                create table tab_obj_inmval (
                                 usuario                varchar2(40),
                                 col_objx_inmval        objx_inmval)
                                
                                 nested table col_objx_inmval.valconst.BA7.lvcpa store as tab_obj_lvcpa
                                 nested table col_objx_inmval.valconst.B08.lvcs store as tab_obj_lvcs
                                 nested table col_objx_inmval.valconst.B07.lvcep store as tab_obj_lvcep
                                 nested table col_objx_inmval.valconst.B07.lvcec store as tab_obj_lvcec
                                 nested table col_objx_inmval.valsuelo.BA5.lvspa store as tab_obj_lvspa
                                 nested table col_objx_inmval.valsuelo.BS6.lvsrp store as tab_obj_lvsrp
                                 nested table col_objx_inmval.valsuelo.BC6.lvsrrp store as tab_obj_lvsrrp
                                 nested table col_objx_inmval.valsuelo.BC6.lvsrrc store as tab_obj_lvsrrc
                                 nested table col_objx_inmval.valsuelo.B05.lvsu store as tab_obj_lvsu
                                 nested table col_objx_inmval.valsuelo.B04.lvsrc store as tab_obj_lvsrc
                                 nested table col_objx_inmval.valsuelo.B03.lvspr store as tab_obj_lvspr
                                 nested table col_objx_inmval.ltit store as tab_obj_ltit
                                /
                                drop public synonym tab_obj_inmval
                                /
                                create public synonym tab_obj_inmval for sic.tab_obj_inmval
                                /
                                ------------------------------
                                grant all on tab_obj_inmval to public
                                /
                                ------------------------------
                                grant execute on objx_inmval to public
                                /
                                grant execute on objx_valores to public
                                /
                                grant execute on objx_valconst to public
                                /
                                grant execute on objx_valsuelo to public
                                /
                                grant execute on objx_dgval to public
                                /
                                grant execute on objx_ltit to public
                                /
                                grant execute on objx_identinm to public
                                /
                                grant execute on objx_B09 to public
                                /
                                grant execute on objx_totval to public
                                /
                                grant execute on objx_iibi to public
                                /
                                grant execute on objx_B07 to public
                                /
                                grant execute on objx_lvcep to public
                                /
                                grant execute on objx_vcep to public
                                /
                                grant execute on objx_lvcec to public
                                /
                                grant execute on objx_vcec to public
                                /
                                grant execute on objx_ccvc to public
                                /
                                grant execute on objx_B08 to public
                                /
                                grant execute on objx_lvcs to public
                                /
                                grant execute on objx_vcs to public
                                /
                                grant execute on objx_cdc to public
                                /
                                grant execute on objx_ccvcs to public
                                /
                                grant execute on objx_BA7 to public
                                /
                                grant execute on objx_lvcpa to public
                                /
                                grant execute on objx_vcpa to public
                                /
                                grant execute on objx_evcons to public
                                /
                                grant execute on objx_BA5 to public
                                /
                                grant execute on objx_lvspa to public
                                /
                                grant execute on objx_vspa to public
                                /
                                grant execute on objx_evs to public
                                /
                                grant execute on objx_BS6 to public
                                /
                                grant execute on objx_lvsrp to public
                                /
                                grant execute on objx_vsrp to public
                                /
                                grant execute on objx_ccvsc to public
                                /
                                grant execute on objx_BC6 to public
                                /
                                grant execute on objx_lvsrrc to public
                                /
                                grant execute on objx_vsrrc to public
                                /
                                grant execute on objx_lvsrrp to public
                                /
                                grant execute on objx_vsrrp to public
                                /
                                grant execute on objx_ccvsr to public
                                /
                                grant execute on objx_B05 to public
                                /
                                grant execute on objx_lvsu to public
                                /
                                grant execute on objx_vsu to public
                                /
                                grant execute on objx_ccvs to public
                                /
                                grant execute on objx_B04 to public
                                /
                                grant execute on objx_lvsrc to public
                                /
                                grant execute on objx_vsrc to public
                                /
                                grant execute on objx_cccsc to public
                                /
                                grant execute on objx_B03 to public
                                /
                                grant execute on objx_lvspr to public
                                /
                                grant execute on objx_vspr to public
                                /
                                grant execute on objx_tit_doc to public
                                /
                                grant execute on objx_bi_doc to public
                                /
                                grant execute on objx_debi_doc to public
                                /
                                grant execute on objx_dt_doc to public
                                /
                                grant execute on objx_locs_doc to public
                                /
                                grant execute on objx_lous_doc to public
                                /
                                grant execute on objx_lors_doc to public
                                /
                                grant execute on objx_lorus_doc to public
                                /
                                grant execute on objx_datosbice to public
                                /
                                grant execute on objx_rbice to public
                                /
                                • 13. Re: XQuery tag case function?
                                  odie_63
                                  Thanks!

                                  The script is missing the definitions of :
                                  objx_loint
                                  objx_der
                                  objx_idpa
                                  objx_idcbf
                                  objx_iatit
                                  objx_rc
                                  objx_locat
                                  objx_lourb
                                  objx_cpp
                                  objx_idcat
                                  So I had to create dummy ones with VARCHAR2(30) attributes in order to use the object instance you provided.

                                  The query works fine in 11.2.0.2, but gives various errors in 10.2.0.4. I can't test on in-between versions.
                                  After playing around on 10.2.0.4, I ended up with this working (and simplified) query :
                                  SQL> select * from v$version;
                                   
                                  BANNER
                                  ----------------------------------------------------------------
                                  Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
                                  PL/SQL Release 10.2.0.4.0 - Production
                                  CORE     10.2.0.4.0     Production
                                  TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
                                  NLSRTL Version 10.2.0.4.0 - Production
                                   
                                  SQL> set long 500
                                  SQL> select xmlserialize(document
                                    2           xmlquery(
                                    3            'declare function local:transform($e as element()) as element()
                                    4             {
                                    5               element {lower-case(local-name($e))}
                                    6               {
                                    7                 for $c in $e/node()
                                    8                 return if ($c instance of element())
                                    9                        then local:transform($c)
                                   10                        else $c
                                   11               }
                                   12             }; local:transform(/child::*)'
                                   13            passing xmltype(col_objx_inmval)
                                   14            returning content
                                   15           )
                                   16         ) as xml_output
                                   17  from tab_obj_inmval
                                   18  where usuario = 'DEV'
                                   19  ;
                                   
                                  XML_OUTPUT
                                  --------------------------------------------------------------------------------
                                  <objx_inmval>
                                     <identinm>
                                        <bi>
                                           <idcat>
                                              <val1>ur</val1>
                                              <val2>
                                                 <val1>0347920</val1>
                                                 <val2>TF8304N</val2>
                                              </val2>
                                              <val3/>
                                           </idcat>
                                           <dt/>
                                           <debi/>
                                        </bi>
                                     </identinm>
                                     <dgval/>
                                     <valsuelo/>
                                     <valconst/>
                                     <valores/>
                                  </objx_inmval>
                                   
                                  Could you try that on your db?
                                  • 14. Re: XQuery tag case function?
                                    723980
                                    Still getting an error, but a different one:

                                    ORA-00932: inconsistent datatypes: expected - got -
                                    ORA-06512: in "XDB.SYSFNs+KJk/rUDQPgRAAiZAJ8pA==", line 1

                                    I get the same with three different object, the one I've post as an example and 2 equal types but filled with more data. The only difference is the string behind XDB.SYSFNs* which varies with time or record (I'm guessing it's pointing to a memory adress or rowid? )
                                    BANNER
                                    --------------------------------------------------------------------------------
                                    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
                                    PL/SQL Release 11.1.0.7.0 - Production
                                    CORE    11.1.0.7.0      Production
                                    TNS for HPUX: Version 11.1.0.7.0 - Production
                                    NLSRTL Version 11.1.0.7.0 - Production
                                    Regarding the missing DDLs, yes, looks like the script is not updated. Here is the rest, just in case:
                                    drop type objx_loint
                                    /
                                    drop type objx_der
                                    /
                                    drop type objx_idpa
                                    /
                                    drop type objx_idcbf
                                    /
                                    drop type objx_iatit
                                    /
                                    drop type objx_rc
                                    /
                                    drop type objx_locat
                                    /
                                    drop type objx_lourb
                                    /
                                    drop type objx_cpp
                                    /
                                    drop type objx_idcat
                                    /
                                    
                                    CREATE TYPE objx_loint as object(
                                    BQ          varchar2(4), 
                                    ES          varchar2(2), 
                                    PT          varchar2(3), 
                                    PU          varchar2(3))
                                    /
                                    
                                    CREATE TYPE objx_der as object(
                                    CDR          varchar2(2), 
                                    PCT          varchar2(6))
                                    /
                                    
                                    CREATE TYPE objx_idpa as object(
                                    NIF          varchar2(9), 
                                    ANIF     varchar2(1), 
                                    NOM          varchar2(62))
                                    /
                                    
                                    CREATE TYPE objx_idcbf as object(
                                    BQ          varchar2(4), 
                                    ES          varchar2(2), 
                                    PT          varchar2(3), 
                                    PU          varchar2(3))
                                    /
                                    
                                    CREATE TYPE objx_iatit as object(
                                    NIFCB     varchar2(9), 
                                    NOMCB     varchar2(62))
                                    /
                                    
                                    CREATE TYPE objx_rc as object(
                                    PC1          varchar2(7), 
                                    PC2          varchar2(7), 
                                    CAR          varchar2(4), 
                                    CC1          varchar2(1), 
                                    CC2          varchar2(1))
                                    /
                                    
                                    CREATE TYPE objx_locat as object(
                                    CD          varchar2(2), 
                                    CMC          varchar2(3))
                                    /
                                    
                                    CREATE TYPE objx_lourb as object(
                                    DIR          objx_dir, 
                                    LOINT     objx_loint, 
                                    DP          varchar2(5), 
                                    DM          varchar2(2))
                                    /
                                    
                                    CREATE TYPE objx_cpp as object(
                                    CPO          varchar2(3), 
                                    CPA          varchar2(5))
                                    /
                                    
                                    CREATE TYPE objx_idcat as object(
                                    CN          varchar2(2), 
                                    RC          objx_rc, 
                                    LOCAT     objx_locat)
                                    /
                                    
                                    grant execute on objx_loint to public
                                    /
                                    grant execute on objx_der to public
                                    /
                                    grant execute on objx_idpa to public
                                    /
                                    grant execute on objx_idcbf to public
                                    /
                                    grant execute on objx_iatit to public
                                    /
                                    grant execute on objx_rc to public
                                    /
                                    grant execute on objx_locat to public
                                    /
                                    grant execute on objx_lourb to public
                                    /
                                    grant execute on objx_cpp to public
                                    /
                                    grant execute on objx_idcat to public
                                    /
                                    1 2 Previous Next