1 Reply Latest reply: Oct 2, 2012 10:39 AM by 110583 RSS

    Wrong XML OraDbType code for database column type CHAR

    932094
      Hi there!

      I have a strange issue with the XML retrieved from an Oracle DB. I'm using OraDB11g and my problem is described next:

      When I create a table with a CHAR type columns and get the XML from a query the returns a cursor with the table rows, I get the supposed right OraDbType code 104:

      Table creation:
      create table AA_Table1
      (
      ID number(2,0) not null,
      ColumnA         char(1)                    null,
      ColumnB         char(1)   default 'S'   not null,
      constraint PK_AA_Table1
      primary key ( ID )
      )
      tablespace "X"
      storage
      (
      ...
      )
      compress for all operations
      ;

      and the returned XML:

      <?xml version="1.0" encoding="utf-16"?>
      <xs:schema id="o_ListaTable" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop">
      <xs:element name="o_ListTable" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
      <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
      <xs:element name="o_List" msprop:REFCursorName="REFCursor">
      <xs:complexType>
      <xs:sequence>
      <xs:element name="ID" msprop:OraDbType="111" type="xs:short" minOccurs="0" />
      *<xs:element name="COLUMNA" msprop:OraDbType="104" type="xs:string" minOccurs="0" />*
      *<xs:element name="COLUMNB" msprop:OraDbType="104" type="xs:string" minOccurs="0" />*
      </xs:sequence>
      </xs:complexType>
      </xs:element>
      </xs:choice>
      </xs:complexType>
      </xs:element>
      </xs:schema>

      BUT...when I insert another column on the table, changing it, after that, I get a wrong OraDBType code on the returned XML for the new added column...

      Added new column:
      alter table AA_Table1 add ColumnC char(1) default 'S' not null;

      The returned XML is:

      <?xml version="1.0" encoding="utf-16"?>
      <xs:schema id="o_ListaTable" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop">
      <xs:element name="o_ListTable" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
      <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
      <xs:element name="o_List" msprop:REFCursorName="REFCursor">
      <xs:complexType>
      <xs:sequence>
      <xs:element name="ID" msprop:OraDbType="111" type="xs:short" minOccurs="0" />
      <xs:element name="COLUMNA" msprop:OraDbType="104" type="xs:string" minOccurs="0" />
      <xs:element name="COLUMNB" msprop:OraDbType="104" type="xs:string" minOccurs="0" />
      *<xs:element name="COLUMNC" msprop:OraDbType="126" type="xs:string" minOccurs="0" />*
      </xs:sequence>
      </xs:complexType>
      </xs:element>
      </xs:choice>
      </xs:complexType>
      </xs:element>
      </xs:schema>

      My question is: why is this OraDbType code different? Check that the same column type CHAR(1) NOT NULL DEFAULT 'S' is created on table create and the OraDbType is ok, so I think this can be a bug...Because of this, I cannot do a right XML parser to convert CHAR(1) columns in other type I want, in this case, to convert it to .Net bool type. My parser now is ignoring the OraDbType code 126, because this is also the code used for nvarchar2, and I won't convert nvarchar2 to bool.

      How can I solve this problem?

      Some additional tests I've done:
      Have also inserted new columns nullable/not nullable and with default value:

      alter table AA_Table1 add ColumnD char(1);
      alter table AA_Table1 add ColumnE char(1) not null;
      alter table AA_Table1 add ColumnF char(1) default 'S';

      And the returned XML was:

      <?xml version="1.0" encoding="utf-16"?>
      <xs:schema id="o_ListTable" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop">
      <xs:element name="o_ListTable" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
      <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
      <xs:element name="o_Lista" msprop:REFCursorName="REFCursor">
      <xs:complexType>
      <xs:sequence>
      <xs:element name="COLUMNA" msprop:OraDbType="104" type="xs:string" minOccurs="0" />
      <xs:element name="COLUMNB" msprop:OraDbType="104" type="xs:string" minOccurs="0" />
      <xs:element name="COLUMNC" msprop:OraDbType="126" type="xs:string" minOccurs="0" />
      *<xs:element name="COLUMND" msprop:OraDbType="104" type="xs:string" minOccurs="0" />*
      *<xs:element name="COLUMNE" msprop:OraDbType="104" type="xs:string" minOccurs="0" />*
      *<xs:element name="COLUMNF" msprop:OraDbType="104" type="xs:string" minOccurs="0" />*
      </xs:sequence>
      </xs:complexType>
      </xs:element>
      </xs:choice>
      </xs:complexType>
      </xs:element>
      </xs:schema>

      All OraDbTypes are right, except that one for the column not null and with default value. Any solution for this issue?

      If I change the type of the column that returns the wrong code (ColumnC) to CHAR(1) null default 'S' and then change it again to the original type CHAR(1) not null default 'S', I get the right ORaDbType code 104 as expcted and not the 126. This behaviour is strange - can this be an Oracle bug?

      I am using the following Oracle DLL and respective version:
      oci.dll (version 11.2.0.1)
      Oracle.DataAccess.dll (version 4.112.3.0)
      orannzsbb11.dll (version 11.0.0.1)
      oraocci11.dll (version 11.2.0.3)
      OraOps11w.dll (version 2.112.3.0)

      Edited by: 929091 on 23/Abr/2012 2:44