CREATE OR REPLACE TYPE qwerty_element_t AS OBJECT
(str VARCHAR2(10)
,num number
,str_big clob
,dt date
);
/
CREATE OR REPLACE TYPE qwerty_tt AS TABLE OF qwerty_element_t;
/
CREATE OR REPLACE TYPE qwerty_t AS OBJECT
(id VARCHAR2(1000)
,element qwerty_element_t
,elements qwerty_tt
);
/
create or replace
function getQwertyAsXml(p_id varchar2) return clob as
v_qwerty qwerty_t;
v_xml XMLTYPE;
begin
-- init object instance
v_qwerty := ...;
--convert object to xml
v_xml := XMLTYPE (v_qwerty);
-- xml to clob
return v_xml.getClobVal();
end;
/
OracleType2XSD.groovy
class OracleType2XSD {
static String clob2String(java.sql.Clob clob) {/*...*/}
static Sql getSql() {/*...*/}
static String oracleType2XSD(Sql sql, String oracleType, String rootTag) {
String res
sql.call("""{ ${Sql.CLOB} = call dbms_xmlschema.generateSchema(
user,
${Sql.VARCHAR(oracleType)},
${Sql.VARCHAR(rootTag)},
TRUE,FALSE,TRUE
).getClobVal() }""") { xsd ->
res = clob2String(xsd)
}
res
}
static void main(args) {
new File('qwerty.xsd').withWriter {
it << oracleType2XSD(getSql(), 'QWERTY_T', 'qwerty')
}
}
}
execute
set CLASSPATH=...
java -cp %CLASSPATH% groovy.lang.GroovyShell OracleType2XSD.groovy
product qwerty.xsd
<?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="qwerty" type="QWERTY_TType" />
<xsd:complexType name="QWERTY_TType">
<xsd:sequence>
<xsd:element name="ID">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="1000" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="INNER" type="QWERTY_INNER_TType" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="QWERTY_INNER_TType">
<xsd:sequence>
<xsd:element name="STR">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="10" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="NUM" type="xsd:double" />
<xsd:element name="STR_BIG" type="xsd:string" />
<xsd:element name="DT" type="xsd:date" />
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
execute
%JAVA_HOME%/bin/xjc qwerty.xsd -p ru.olimpiks.oracle.model
product java beans:
ru.olimpiks.oracle.model.QWERTYELEMENTTType.java
@XmlAccessorType(XmlAccessType.FIELD)
@XmlType(name = "QWERTY_ELEMENT_TType", propOrder = {
"str",
"num",
"strbig",
"dt"
})
public class QWERTYELEMENTTType {
@XmlElement(name = "STR", required = true)
protected String str;
@XmlElement(name = "NUM")
protected double num;
@XmlElement(name = "STR_BIG", required = true)
protected String strbig;
@XmlElement(name = "DT", required = true)
@XmlSchemaType(name = "date")
protected XMLGregorianCalendar dt;
//...setters and getters
}
ru.olimpiks.oracle.model.QWERTYTType.java
@XmlAccessorType(XmlAccessType.FIELD)
@XmlType(name = "QWERTY_TType", propOrder = {
"id",
"element",
"elements"
})
@XmlRootElement(name="qwerty")//!!!to further correct unmarshall object, you must add this line
public class QWERTYTType {
@XmlElement(name = "ID", required = true)
protected String id;
@XmlElement(name = "ELEMENT", required = true)
protected QWERTYELEMENTTType element;
@XmlElement(name = "ELEMENTS")
protected List<QWERTYELEMENTTType> elements;
//...setters and getters
}
static QWERTYTType getQwerty(Sql sql, String id) {
String res
sql.call("""{ ${Sql.CLOB} = call getQwertyAsXml(${Sql.VARCHAR(id)}) }""") { p_res ->
res = clob2String(p_res)
}
JAXBContext context = JAXBContext.newInstance(QWERTYTType.class);
Unmarshaller u = context.createUnmarshaller();
return (QWERTYTType)u.unmarshal(new java.io.StringReader(res));
}
