I am using oracle 9.2.0.4. I am facing problem retrieving actual value
for enumeration columns which is stored as column of XDB$ENUM_T type

I created two XML schemas as

DECLARE

doc VARCHAR2(32000) := '<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb" elementFormDefault="qualified"
attributeFormDefault="unqualified">
<xs:simpleType name="TESTSTATUS" >
<xs:restriction base="xs:string">
<xs:enumeration value="TEST Received"/>
<xs:enumeration value="Received"/>
<xs:enumeration value="Processing"/>
<xs:enumeration value="Query"/>
<xs:enumeration value="Ready For Batching"/>
<xs:enumeration value="Batched And Sent"/>
<xs:enumeration value="Batched And Received"/>
<xs:enumeration value="Batched And Not
Received"/>
<xs:enumeration value="Rejection Received"/>
</xs:restriction>
</xs:simpleType>
<xs:element name="AslaidStatusDescription" type="TESTSTATUS">
</xs:element>
</xs:schema>';
BEGIN
dbms_xmlschema.registerSchema('http://xmlns.oracle.com/xdb/TESTSTATUS.xs
d',doc);
end;
/

DECLARE
doc VARCHAR2(32000) := '<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb" elementFormDefault="qualified"
attributeFormDefault="unqualified">
<xs:include
schemaLocation="http://xmlns.oracle.com/xdb/TESTSTATUS.xsd"/>
<xs:element name="TEST" type="TESTING">
</xs:element>
<xs:complexType name="TESTING">
<xs:sequence>
<xs:element name="FromStatus" type="TESTSTATUS"
minOccurs="0">
</xs:element>
<xs:element name="StatusDescription">
<xs:simpleType>
<xs:restriction
base="xs:string">
<xs:maxLength
value="40"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:schema>';
BEGIN
dbms_xmlschema.registerSchema('http://xmlns.oracle.com/xdb/TESTING.xsd',
doc);
end;
/

Created table
CREATE TABLE TESTENUM OF XMLTYPE XMLSCHEMA
"http://xmlns.oracle.com/xdb/TESTING.xsd" ELEMENT "TEST";

And inserted values

INSERT INTO TESTENUM VALUES(XMLType(
'<TEST xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://xmlns.oracle.com/xdb/TESTING.xsd">
<FromStatus>Ready For Batching</FromStatus>
<StatusDescription>Some Description</StatusDescription>
</TEST>'
))

INSERT INTO TESTENUM VALUES(XMLType(
'<TEST xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://xmlns.oracle.com/xdb/TESTING.xsd">
<FromStatus>Query</FromStatus>
<StatusDescription>Second Description</StatusDescription>
</TEST>'
))

When I issue a query, I am getting

SQL> select x.xmldata from testenum x;

XMLDATA(SYS_XDBPD$, FromStatus(VALUE), StatusDescription)
------------------------------------------------------------------------
--------
TESTING8938_T(XDB$RAW_LIST_T('1303020084000088010027687474703A2F2F786D6C
6E732E6F
7261636C652E636F6D2F7864622F54455354494E472E7873640001'),
XDB$ENUM_T('04'), 'Som
e Description')

TESTING8938_T(XDB$RAW_LIST_T('1303020084000088010027687474703A2F2F786D6C
6E732E6F
7261636C652E636F6D2F7864622F54455354494E472E7873640001'),
XDB$ENUM_T('03'), 'Sec
ond Description')


I can see the Actual values stored using <FromStatus>Query</FromStatus>
as XDB$ENUM_T('03'). Can any one help me in getting the actual string
"Query" instead of '03'

Thanks in advance.

Regards
Justin

Oracle LazyDBA home page