DevOps, CI/CD and Automation

I'm trying to setup an Oracle Native web service in PL/SQL under Oracle 11.2.0.2.0 running on Red Hat 5 Linux 64-bit.

We already have native PL/SQL web services running successfully (the database servlet works fine) from a previous project we did, however that project used the following approach:

1. Start off without an XSD
2. Hand-code the SQL scripts to create all the Oracle types needed to pass in the XML via web services
3. Now write the PL/SQL package to use the TYPE for what will be the top-level XML element
4. Call the native webservices servlet to find out what the WSDL is.

What I'm trying to do is write a PL/SQL web service that accepts a reasonably-complex XML document as input, and passes back a different XML document as a response. Our new "standard approach" is to use native web services for this.

So unlike the earlier approach above, I'm trying to start with an XSD instead, as follows:

1. Write the XSD using Altova XMLSpy.
2. Edit the XSD to add Oracle XDB extensions such as xdb:SQLType and xdb:SQLName, eg: to control the naming of the types (if I don't do this, RegisterSchema() [below] will generate its own random unique names)
2. Load it into the Oracle DB using DBMS_XMLSCHEMA.registerSchema (), with gentypes=>TRUE
3. Create the new PL/SQL package to use the TYPE for the top-level element.
4. Check the service is OK by retrieving the WSDL back from the database using Internet Explorer

You'd think this would work reasonably intuitively (I've been coding PL/SQL for over 15 years), but instead it's been very difficult to actually get working. The Oracle TYPEs generated by RegisterSchema() seem to make Oracle webservices very unhappy: even though the XSD loads OK and the TYPEs get created, and the package compiles fine, the WSDL that gets returned by Oracle for this new package is actually gibberish (it's not even syntactically correct) and causes Internet Explorer and SoapUI to complain loudly.

I've done the usual Googling, but cannot seem to find any articles describing anyone creating a native PL/SQL web service from an XSD that has actually been loaded into the database. Most examples seem to be someone trying to expose an existing stored package (one that has very basic in/out parameters) as a web service. We can do this already no problems.


Any help would be hugely appreciated..I'm on the verge of giving up and hand-coding all the oracle types manually to get it working.

Regards