Hello,
I am having a problem with what seems like a bug to me when Oracle creates the object-relational schema mapping for an "xsd:decimal" datatype:
I want to store geo codes with 6 fractional digits, so I need 3 digits to the left and 6 digits to the right of the decimal point.
In the XSD datatype restrictions, that would give me a "totalDigits" of 9 and a "fractionDigits" of 6.
Oracle should convert this to NUMBER(9, 6).
However, what Oracle does make out of that is a NUMBER(15,6)
In order to get Oracle to generate a NUMBER(9, 6), I need to define "totalDigits" as 3, which is accepted by Oracle, but makes my schema invalid for pretty much any other XML application, because it violates official XSD specs:
http://www.w3.org/TR/xmlschema-2/#rf-fractionDigits:
4.3.12.4 Constraints on fractionDigits Schema Components
Schema Component Constraint: fractionDigits less than or equal to totalDigits
It is an error for fractionDigits to be greater than totalDigits.
Is there a way to have the correct totalDigits of 9 in my schema and force Oracle to translate that into NUMBER(9,6)?
Maybe some oraxdb attribute that I don't know yet?
I am surprised that this has not been found sooner...
There has been a posting about this before, but it didn't get any real replies:
TotalDigits and fractionDigits to number conversion problem
Here is my full example code:
-- Registering the schema
BEGIN
DBMS_XMLSCHEMA.REGISTERSCHEMA(
'http://localhost/decimaltest.xsd',
'<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" xmlns:oraxdb="http://xmlns.oracle.com/xdb" oraxdb:storeVarrayAsTable="true" oraxdb:schemaURL="http://localhost/decimaltest.xsd">
<xs:element name="dataTypeTests" oraxdb:SQLName="ZZZ_DATA_TYPE_TEST" oraxdb:SQLType="ZZZ_DATA_TYPE_TEST_TYPE" oraxdb:defaultTable="" oraxdb:tableProps="TABLESPACE CATALOG NOLOGGING" oraxdb:maintainDOM="false">
<xs:complexType>
<xs:sequence>
<xs:element name="geoCode" type="geoCodeType" oraxdb:SQLName="GEO_CODE" oraxdb:maintainDOM="false"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:simpleType name="geoCodeType">
<xs:restriction base="xs:decimal">
<xs:totalDigits value="9"/>
<xs:fractionDigits value="6"/>
</xs:restriction>
</xs:simpleType>
</xs:schema>',
gentables=>FALSE);
end;
/
-- Display the auto-generated Oracle custom type
describe ZZZ_DATA_TYPE_TEST_TYPE;
-- Clean up
begin
DBMS_XMLSCHEMA.DELETESCHEMA(
'http://localhost/decimaltest.xsd',
DBMS_XMLSCHEMA.DELETE_CASCADE
);
end;
/