This discussion is archived
11 Replies Latest reply: Apr 5, 2013 12:53 PM by MarcoGralike RSS

Does XE have Native Oracle DB XML Web Services feature?

1000463 Newbie
Currently Being Moderated
Hi, Does Oracle Express Edition (XE) have Native Oracle DB XML Web Services feature :
http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb_web_services.htm
  • 1. Re: Does XE have Native Oracle DB XML Web Services feature?
    1000463 Newbie
    Currently Being Moderated
    Someone, please assist!!
  • 2. Re: Does XE have Native Oracle DB XML Web Services feature?
    clcarter Expert
    Currently Being Moderated
    Yes. See the note on the licensing page, features section, quote "XML support in the database ... Yes" at http://docs.oracle.com/cd/E17781_01/license.112/e18068/toc.htm#autoId3
  • 3. Re: Does XE have Native Oracle DB XML Web Services feature?
    orafad Oracle ACE
    Currently Being Moderated
    But for Database Web services, availibility is "No".

    Could be native web services is another thing that relies on the db jvm.
  • 4. Re: Does XE have Native Oracle DB XML Web Services feature?
    mdrake Expert
    Currently Being Moderated
    Yes, at least the 11.2.x version (And I am the PM so I should know)...

    There is NO JVM dependency

    Enable the WebServices Servlet and enjoy
  • 5. Re: Does XE have Native Oracle DB XML Web Services feature?
    orafad Oracle ACE
    Currently Being Moderated
    From the Licensing guide, I had Database Web services in mind, which I'm pretty sure requires Java. And is also clearly not available in XE.


    Now that I've spent some time actually reading the thread, it seems op is after XML DB specific/existing web services.

    (The features table also mentions no "servlet support", but maybe the xdb service is an exception.)
  • 6. Re: Does XE have Native Oracle DB XML Web Services feature?
    1000463 Newbie
    Currently Being Moderated
    Thanks for your replies. The link says

    Database Web services : No

    This is what I was looking for. Exposing DB tables as web service. Is this available only in full edition? Even for some testing/checking out the feature?
  • 7. Re: Does XE have Native Oracle DB XML Web Services feature?
    1000463 Newbie
    Currently Being Moderated
    So you say that I can expose the DB Schema as Web Service? If so, can you please point me to some doc which I can refer? I searched, but couldn't find it.
  • 8. Re: Does XE have Native Oracle DB XML Web Services feature?
    orafad Oracle ACE
    Currently Being Moderated
    Doesn't the doc link you posted originally help? How so?

    We don't know exactly what sort of web service you are looking for, so an explanation could make it easier to provide more helpful response.

    See also previous discussions in the dedicated XML DB forum.
  • 9. Re: Does XE have Native Oracle DB XML Web Services feature?
    mdrake Expert
    Currently Being Moderated
    Oracle Database Native Web Services...

    Allow most PL/SQL package methods and procedures and functions to be invoked via SOAP..

    Require Database HTTP Server enabled. Oracle WebServices Servlet (Which is written in 'C' and already linked into the database) to be enabled.

    Also provides a simple SQL / XQUERY query only service..
    SQL> --
    SQL> connect / as sysdba
    Connected.
    SQL> --
    SQL> -- Configure Webservices Servlet
    SQL> --
    SQL> call DBMS_XDB.DELETESERVLET(NAME => 'orawsv')
      2  /
    
    Call completed.
    
    SQL> call DBMS_XDB.DELETESERVLETMAPPING(NAME => 'orawsv')
      2  /
    
    Call completed.
    
    SQL> call DBMS_XDB.DELETESERVLETSECROLE(SERVNAME  => 'orawsv', ROLENAME  => 'XDB_WEBSERVICES' )
      2  /
    
    Call completed.
    
    SQL> call DBMS_XDB.ADDSERVLETMAPPING(PATTERN => '/orawsv/*', NAME    => 'orawsv')
      2  /
    
    Call completed.
    
    SQL> call DBMS_XDB.ADDSERVLET
      2         (
      3            NAME     => 'orawsv',
      4            LANGUAGE => 'C',
      5            DISPNAME => 'Oracle Database Native Web Services',
      6            DESCRIPT => 'Servlet for issuing queries as a Web Service'
      7         )
      8  /
    
    Call completed.
    
    SQL> call DBMS_XDB.ADDSERVLETSECROLE
      2  (
      3       SERVNAME  => 'orawsv',
      4       ROLENAME  => 'XDB_WEBSERVICES',
      5       ROLELINK  => 'XDB_WEBSERVICES'
      6  )
      7  /
    
    Call completed.
    
    SQL> set define on
    SQL> set timing on
    SQL> --
    SQL> define USERNAME = SCOTT
    SQL> --
    SQL> def PASSWORD = tiger
    SQL> --
    SQL> def HOSTNAME = localhost
    SQL> --
    SQL> -- Set up network ACL to permit database to call back to itself via HTTP
    SQL> --
    SQL> begin
      2  $IF DBMS_DB_VERSION.VER_LE_11_2 $THEN
      3    dbms_network_acl_admin.drop_acl('EnableHttpAccess.xml');
      4  $ELSE
      5    dbms_network_acl_admin.remove_host_ace(
      6        host =>  '&HOSTNAME',
      7        ace  =>  xs$ace_type(privilege_list => xs$name_list('resolve'),
      8                     principal_name => '&USERNAME',
      9                     principal_type => xs_acl.ptype_db));
     10  
     11  $END
     12  end;
     13  /
    old   6:     host =>  '&HOSTNAME',
    new   6:     host =>  'localhost',
    old   8:                        principal_name => '&USERNAME',
    new   8:                        principal_name => 'SCOTT',
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.01
    SQL> begin
      2  $IF DBMS_DB_VERSION.VER_LE_11_2 $THEN
      3    dbms_network_acl_admin.create_acl('EnableHttpAccess.xml', 'ACL for &HOSTNAME', '&USERNAME', true, 'connect');
      4    dbms_network_acl_admin.assign_acl('EnableHttpAccess.xml', '&HOSTNAME');
      5  $ELSE
      6    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
      7        host => '&HOSTNAME',
      8        ace  =>  xs$ace_type(privilege_list => xs$name_list('connect', 'resolve'),
      9                        principal_name => '&USERNAME',
     10                        principal_type => xs_acl.ptype_db));
     11  $END
     12  end;
     13  /
    old   3:   dbms_network_acl_admin.create_acl('EnableHttpAccess.xml', 'ACL for &HOSTNAME', '&USERNAME', true, 'connect');
    new   3:   dbms_network_acl_admin.create_acl('EnableHttpAccess.xml', 'ACL for localhost', 'SCOTT', true, 'connect');
    old   4:   dbms_network_acl_admin.assign_acl('EnableHttpAccess.xml', '&HOSTNAME');
    new   4:   dbms_network_acl_admin.assign_acl('EnableHttpAccess.xml', 'localhost');
    old   7:     host => '&HOSTNAME',
    new   7:     host => 'localhost',
    old   9:                           principal_name => '&USERNAME',
    new   9:                           principal_name => 'SCOTT',
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.31
    SQL> commit
      2  /
    
    Commit complete.
    
    Elapsed: 00:00:00.40
    SQL> --
    SQL> -- Grant permissiont to target user.
    SQL> --
    SQL> GRANT XDB_WEBSERVICES TO &USERNAME
      2  /
    old   1: GRANT XDB_WEBSERVICES TO &USERNAME
    new   1: GRANT XDB_WEBSERVICES TO SCOTT
    
    Grant succeeded.
    
    Elapsed: 00:00:00.01
    SQL> GRANT XDB_WEBSERVICES_OVER_HTTP TO &USERNAME
      2  /
    old   1: GRANT XDB_WEBSERVICES_OVER_HTTP TO &USERNAME
    new   1: GRANT XDB_WEBSERVICES_OVER_HTTP TO SCOTT
    
    Grant succeeded.
    
    Elapsed: 00:00:00.00
    SQL> GRANT XDB_WEBSERVICES_WITH_PUBLIC TO &USERNAME
      2  /
    old   1: GRANT XDB_WEBSERVICES_WITH_PUBLIC TO &USERNAME
    new   1: GRANT XDB_WEBSERVICES_WITH_PUBLIC TO SCOTT
    
    Grant succeeded.
    
    Elapsed: 00:00:00.01
    SQL> connect &USERNAME/&PASSWORD
    Connected.
    SQL> --
    SQL> -- declare the XSL used to generate the Request document from the WSDL
    SQL> VAR XSL CLOB;
    SQL> --
    SQL> begin
      2            :XSL :=
      3  '<?xml version="1.0" encoding="UTF-8"?>
      4  <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/">
      5            <xsl:output method="xml" indent="yes"/>
      6            <xsl:variable name="BINDING_QNAME" select="/wsdl:definitions/wsdl:service/wsdl:port/@binding"/>
      7            <xsl:variable name="BINDING" select="substring-after($BINDING_QNAME,'':'')"/>
      8            <xsl:variable name="TYPE_QNAME" select="/wsdl:definitions/wsdl:binding[@name=$BINDING]/@type"/>
      9            <xsl:variable name="TYPE" select="substring-after($TYPE_QNAME,'':'')"/>
     10            <xsl:variable name="PORTTYPE_QNAME" select="/wsdl:definitions/wsdl:portType[@name=$TYPE]/wsdl:operation/wsdl:input/@message"/>
     11            <xsl:variable name="PORTTYPE" select="substring-after($PORTTYPE_QNAME,'':'')"/>
     12            <xsl:variable name="TARGETNAMESPACE" select="/wsdl:definitions/@targetNamespace"/>
     13            <xsl:variable name="INPUTMESSAGE_QNAME" select="/wsdl:definitions/wsdl:message[@name=$PORTTYPE]/wsdl:part/@element"/>
     14            <xsl:variable name="INPUTMESSAGE" select="substring-after($INPUTMESSAGE_QNAME,'':'')"/>
     15            <xsl:template match="/">
     16                 <xsl:element name="Envelope" namespace="http://schemas.xmlsoap.org/soap/envelope/">
     17                      <xsl:element name="Body" namespace="http://schemas.xmlsoap.org/soap/envelope/">
     18                           <xsl:element name="{$INPUTMESSAGE}" namespace="{$TARGETNAMESPACE}">
     19                                <xsl:choose>
     20                                     <xsl:when test="$TARGETNAMESPACE=''http://xmlns.oracle.com/orawsv''">
     21                                          <!-- Special Processing for the SQL/XQuery Service WSDL -->
     22                                          <xsl:for-each select="/wsdl:definitions/wsdl:types/xsd:schema/xsd:element[@name=$INPUTMESSAGE]/xsd:complexType/xsd:sequence/xsd:element[not(@name = ''DDL_text'')]">
     23                                               <xsl:element name="{@name}" namespace="{$TARGETNAMESPACE}">
     24                                                    <xsl:if test="@name=''query_text''">
     25                                                         <xsl:attribute name="type"><xsl:text>SQL</xsl:text></xsl:attribute>
     26                                                    </xsl:if>
     27                                                    <xsl:for-each select="xsd:complexType/xsd:sequence/xsd:any">
     28                                                         <xsl:element name="P_XMLTYPE-EMPTY"/>
     29                                                    </xsl:for-each>
     30                                               </xsl:element>
     31                                          </xsl:for-each>
     32                                     </xsl:when>
     33                                     <xsl:otherwise>
     34                                          <xsl:for-each select="/wsdl:definitions/wsdl:types/xsd:schema/xsd:element[@name=$INPUTMESSAGE]/xsd:complexType/xsd:sequence/xsd:element">
     35                                               <xsl:element name="{@name}" namespace="{$TARGETNAMESPACE}">
     36                                                    <xsl:for-each select="xsd:complexType/xsd:sequence/xsd:any">
     37                                                         <xsl:element name="P_XMLTYPE-EMPTY"/>
     38                                                    </xsl:for-each>
     39                                               </xsl:element>
     40                                          </xsl:for-each>
     41                                     </xsl:otherwise>
     42                                </xsl:choose>
     43                           </xsl:element>
     44                      </xsl:element>
     45                 </xsl:element>
     46            </xsl:template>
     47  </xsl:stylesheet>';
     48  end;
     49  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.00
    SQL> --
    SQL> -- Declare target namespace for service
    SQL> --
    SQL> -- Use TARGET to identify PL/SQL SCHEMA PACKAGE FUNCTION in form SCHEMA/PACKAGE/METHOD or /SCHEMA/PROCEUDRE or /SCHEMA/FUNCTION when invoking PL/SQL
    SQL> --
    SQL> VAR TARGET VARCHAR2(4000)
    SQL> --
    SQL> begin
      2            :TARGET := '';
      3  end;
      4  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.00
    SQL> --
    SQL> undef TARGET_NAMESPACE
    SQL> --
    SQL> column TARGET_NAMESPACE new_value TARGET_NAMESPACE
    SQL> select case
      2             when :TARGET is null
      3            then 'http://xmlns.oracle.com/orawsv'
      4            else 'http://xmlns.oracle.com/orawsv/' || :TARGET
      5           end TARGET_NAMESPACE
      6    from dual
      7  /
    
    TARGET_NAMESPACE                                                                
    --------------------------------------------------------------------------------
    http://xmlns.oracle.com/orawsv                                                  
    
    Elapsed: 00:00:00.00
    SQL> VAR URL     VARCHAR2(1024)
    SQL> VAR RESULT CLOB
    SQL> --
    SQL> -- Configure WebService URL
    SQL> --
    SQL> BEGIN
      2    :URL := 'http://&USERNAME:&PASSWORD@localhost:' || dbms_xdb.getHttpPort() || '/orawsv';
      3  end;
      4  /
    old   2:   :URL := 'http://&USERNAME:&PASSWORD@localhost:' || dbms_xdb.getHttpPort() || '/orawsv';
    new   2:   :URL := 'http://SCOTT:tiger@localhost:' || dbms_xdb.getHttpPort() || '/orawsv';
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.01
    SQL> print url
    
    URL                                                                             
    --------------------------------------------------------------------------------
    http://SCOTT:tiger@localhost:8080/orawsv                                        
    
    SQL> --
  • 10. Re: Does XE have Native Oracle DB XML Web Services feature?
    mdrake Expert
    Currently Being Moderated
    SQL> set long 100000 pages 0 lines 256
    SQL> --
    SQL> -- Print the WSDL for the service
    SQL> --
    SQL> select httpuritype( :URL || '?wsdl' ).getXML()
      2    from dual
      3  /
    <definitions name="orawsv" targetNamespace="http://xmlns.oracle.com/orawsv" xmlns="http://schemas.xmlsoap.org/wsdl/" xmlns:tns="http://xmlns.oracle.com/orawsv" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.xmlsoap.org/wsdl/ http://schemas.xmlsoap.org/wsdl/">
      <types>
        <xsd:schema targetNamespace="http://xmlns.oracle.com/orawsv" elementFormDefault="qualified">
          <xsd:element name="query">
            <xsd:complexType>
              <xsd:sequence>
                <xsd:element name="DDL_text" type="xsd:string" minOccurs="0" maxOccurs="unbounded"/>
                <xsd:element name="query_text">
                  <xsd:complexType>
                    <xsd:simpleContent>
                      <xsd:extension base="xsd:string">
                        <xsd:attribute name="type">
                          <xsd:simpleType>
                            <xsd:restriction base="xsd:NMTOKEN">
                              <xsd:enumeration value="SQL"/>
                              <xsd:enumeration value="XQUERY"/>
                            </xsd:restriction>
                          </xsd:simpleType>
                        </xsd:attribute>
                      </xsd:extension>
                    </xsd:simpleContent>
                  </xsd:complexType>
                </xsd:element>
                <xsd:choice minOccurs="0" maxOccurs="unbounded">
                  <xsd:element name="bind">
                    <xsd:complexType>
                      <xsd:simpleContent>
                        <xsd:extension base="xsd:string">
                          <xsd:attribute name="name" type="xsd:string"/>
                        </xsd:extension>
                      </xsd:simpleContent>
                    </xsd:complexType>
                  </xsd:element>
                  <xsd:element name="bindXML">
                    <xsd:complexType>
                      <xsd:sequence>
                        <xsd:any/>
                      </xsd:sequence>
                    </xsd:complexType>
                  </xsd:element>
                </xsd:choice>
                <xsd:element name="null_handling" minOccurs="0">
                  <xsd:simpleType>
                    <xsd:restriction base="xsd:NMTOKEN">
                      <xsd:enumeration value="DROP_NULLS"/>
                      <xsd:enumeration value="NULL_ATTR"/>
                      <xsd:enumeration value="EMPTY_TAG"/>
                    </xsd:restriction>
                  </xsd:simpleType>
                </xsd:element>
                <xsd:element name="max_rows" type="xsd:positiveInteger" minOccurs="0"/>
                <xsd:element name="skip_rows" type="xsd:positiveInteger" minOccurs="0"/>
                <xsd:element name="pretty_print" type="xsd:boolean" minOccurs="0"/>
                <xsd:element name="indentation_width" type="xsd:positiveInteger" minOccurs="0"/>
                <xsd:element name="rowset_tag" type="xsd:string" minOccurs="0"/>
                <xsd:element name="row_tag" type="xsd:string" minOccurs="0"/>
                <xsd:element name="item_tags_for_coll" type="xsd:boolean" minOccurs="0"/>
              </xsd:sequence>
            </xsd:complexType>
          </xsd:element>
          <xsd:element name="queryOut">
            <xsd:complexType>
              <xsd:sequence>
                <xsd:any/>
              </xsd:sequence>
            </xsd:complexType>
          </xsd:element>
        </xsd:schema>
      </types>
      <message name="QueryInput">
        <part name="body" element="tns:query"/>
      </message>
      <message name="XMLOutput">
        <part name="body" element="tns:queryOut"/>
      </message>
      <portType name="ORAWSVPortType">
        <operation name="XMLFromQuery">
          <input message="tns:QueryInput"/>
          <output message="tns:XMLOutput"/>
        </operation>
      </portType>
      <binding name="ORAWSVBinding" type="tns:ORAWSVPortType">
        <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
        <operation name="XMLFromQuery">
          <soap:operation soapAction="http://localhost:8080/orawsv"/>
          <input>
            <soap:body use="literal"/>
          </input>
          <output>
            <soap:body use="literal"/>
          </output>
        </operation>
      </binding>
      <service name="ORAWSVService">
        <documentation>Oracle Web Service</documentation>
        <port name="ORAWSVPort" binding="tns:ORAWSVBinding">
          <soap:address location="http://localhost:8080/orawsv"/>
        </port>
      </service>
    </definitions>
    
    
    Elapsed: 00:00:00.03
    SQL> --
    SQL> --  Generate The Requerst document for the Service by applying a XSL transformation to the WSDL.
    SQL> --
    SQL> select XMLTransform( httpuritype(:URL || '?wsdl' ).getXML(), XMLTYPE(:XSL))
      2    from dual
      3  /
    <?xml version="1.0" encoding="US-ASCII"?>
    <Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
      <Body>
        <query xmlns="http://xmlns.oracle.com/orawsv">
          <query_text type="SQL"/>
          <null_handling/>
          <max_rows/>
          <skip_rows/>
          <pretty_print/>
          <indentation_width/>
          <rowset_tag/>
          <row_tag/>
          <item_tags_for_coll/>
        </query>
      </Body>
    </Envelope>
    
    
    Elapsed: 00:00:00.02
    SQL> VAR RESULT CLOB
    SQL> VAR RESPONSE CLOB
    SQL> VAR REQUEST CLOB
    SQL> --
    SQL> DECLARE
      2    V_SOAP_REQUEST        XMLTYPE;
      3    V_SOAP_REQUEST_TEXT CLOB;
      4    V_REQUEST        UTL_HTTP.REQ;
      5    V_RESPONSE        UTL_HTTP.RESP;
      6    V_BUFFER         VARCHAR2(32000);
      7    V_RESPONSE_TEXT        CLOB;
      8    V_SOAP_RESPONSE        XMLTYPE;
      9    V_RESPONSE_XML        XMLTYPE;
     10    V_WSDL             XMLTYPE;
     11  BEGIN
     12            DBMS_LOB.CREATETEMPORARY(V_RESPONSE_TEXT, TRUE);
     13
     14            select XMLTransform( httpuritype(:URL || '?wsdl' ).getXML(), XMLTYPE(:XSL))
     15              into V_SOAP_REQUEST
     16              from dual;
     17
     18            select /*+ NO_XML_QUERY_REWRITE */
     19                XMLQUERY(
     20                  'declare namespace soapenv = "http://schemas.xmlsoap.org/soap/envelope/"; (: :)
     21                   declare namespace tns      = "&TARGET_NAMESPACE"; (: :)
     22             copy $NEWREQUEST := $REQUEST modify (
     23                        let $TARGET := $NEWREQUEST/soapenv:Envelope/soapenv:Body/tns:*
     24                           return (
     25                             replace value of node $TARGET/tns:query_text with $STATEMENT
     26                            ,replace value of node $TARGET/tns:query_text/@type with "SQL"
     27                           )
     28                         )
     29                return $NEWREQUEST'
     30                passing V_SOAP_REQUEST as "REQUEST",
     31                     'select * from EMP' as "STATEMENT"
     32                returning content
     33             )
     34        into V_SOAP_REQUEST
     35        from dual;
     36
     37
     38    select XMLSERIALIZE(DOCUMENT V_SOAP_REQUEST as CLOB)
     39        into V_SOAP_REQUEST_TEXT
     40        from DUAL;
     41
     42    :REQUEST := V_SOAP_REQUEST_TEXT;
     43
     44    begin
     45        V_REQUEST := UTL_HTTP.BEGIN_REQUEST(URL => :URL, METHOD => 'POST');
     46        UTL_HTTP.SET_HEADER(V_REQUEST, 'User-Agent', 'Mozilla/4.0');
     47        V_REQUEST.METHOD := 'POST';
     48        UTL_HTTP.SET_HEADER (R => V_REQUEST, NAME => 'Content-Length', VALUE => DBMS_LOB.GETLENGTH(V_SOAP_REQUEST_TEXT));
     49        UTL_HTTP.WRITE_TEXT (R => V_REQUEST, DATA => V_SOAP_REQUEST_TEXT);
     50        V_RESPONSE := UTL_HTTP.GET_RESPONSE(V_REQUEST);
     51         LOOP
     52          UTL_HTTP.READ_LINE(V_RESPONSE, V_BUFFER, TRUE);
     53          if (LENGTH(V_BUFFER) > 0) then
     54            DBMS_LOB.WRITEAPPEND(V_RESPONSE_TEXT,LENGTH(V_BUFFER),V_BUFFER);
     55          end if;
     56         END LOOP;
     57         UTL_HTTP.END_RESPONSE(V_RESPONSE);
     58    EXCEPTION
     59        WHEN UTL_HTTP.END_OF_BODY THEN
     60          UTL_HTTP.END_RESPONSE(V_RESPONSE);
     61    END;
     62
     63    V_SOAP_RESPONSE := XMLTYPE(V_RESPONSE_TEXT);
     64
     65    :RESPONSE := V_RESPONSE_TEXT;
     66
     67    select XMLQUERY(
     68            'declare namespace SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"; (::)
     69             declare namespace SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"; (::)
     70             declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance"; (::)
     71             declare namespace xsd="http://www.w3.org/2001/XMLSchema"; (::)
     72             declare namespace tns="&TARGET_NAMESPACE"; (::)
     73             $resp/SOAP-ENV:Envelope/SOAP-ENV:Body/tns:*'
     74             passing V_SOAP_RESPONSE as "resp"
     75             returning content
     76             )
     77        into V_RESPONSE_XML
     78        from DUAL;
     79
     80    select XMLSERIALIZE(DOCUMENT V_RESPONSE_XML AS CLOB)
     81        into :RESULT
     82        from dual;
     83
     84            DBMS_LOB.FREETEMPORARY(V_RESPONSE_TEXT);
     85
     86  END;
     87  /
    old  21:                declare namespace tns     = "&TARGET_NAMESPACE"; (: :)
    new  21:                declare namespace tns     = "http://xmlns.oracle.com/orawsv"; (: :)
    old  72:             declare namespace tns="&TARGET_NAMESPACE"; (::)
    new  72:             declare namespace tns="http://xmlns.oracle.com/orawsv"; (::)
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.04
    SQL> select XMLPARSE(DOCUMENT :REQUEST WELLFORMED)
      2    from dual
      3  /
    <?xml version="1.0" encoding="US-ASCII"?>
    <Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
      <Body>
        <query xmlns="http://xmlns.oracle.com/orawsv">
          <query_text type="SQL">select * from EMP</query_text>
          <null_handling/>
          <max_rows/>
          <skip_rows/>
          <pretty_print/>
          <indentation_width/>
          <rowset_tag/>
          <row_tag/>
          <item_tags_for_coll/>
        </query>
      </Body>
    </Envelope>
    
    
    Elapsed: 00:00:00.00
    SQL> select XMLPARSE(DOCUMENT :RESPONSE WELLFORMED)
      2    from dual
      3  /
    <?xml version="1.0"?>
    <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
      <soap:Body>
        <queryOut xmlns="http://xmlns.oracle.com/orawsv">
          <ROWSET>
            <ROW>
              <EMPNO>7369</EMPNO>
              <ENAME>SMITH</ENAME>
              <JOB>CLERK</JOB>
              <MGR>7902</MGR>
              <HIREDATE>17-DEC-80</HIREDATE>
              <SAL>800</SAL>
              <DEPTNO>20</DEPTNO>
            </ROW>
            <ROW>
              <EMPNO>7499</EMPNO>
              <ENAME>ALLEN</ENAME>
              <JOB>SALESMAN</JOB>
              <MGR>7698</MGR>
              <HIREDATE>20-FEB-81</HIREDATE>
              <SAL>1600</SAL>
              <COMM>300</COMM>
              <DEPTNO>30</DEPTNO>
            </ROW>
            <ROW>
              <EMPNO>7521</EMPNO>
              <ENAME>WARD</ENAME>
              <JOB>SALESMAN</JOB>
              <MGR>7698</MGR>
              <HIREDATE>22-FEB-81</HIREDATE>
              <SAL>1250</SAL>
              <COMM>500</COMM>
              <DEPTNO>30</DEPTNO>
            </ROW>
            <ROW>
              <EMPNO>7566</EMPNO>
              <ENAME>JONES</ENAME>
              <JOB>MANAGER</JOB>
              <MGR>7839</MGR>
              <HIREDATE>02-APR-81</HIREDATE>
              <SAL>2975</SAL>
              <DEPTNO>20</DEPTNO>
            </ROW>
            <ROW>
              <EMPNO>7654</EMPNO>
              <ENAME>MARTIN</ENAME>
              <JOB>SALESMAN</JOB>
              <MGR>7698</MGR>
              <HIREDATE>28-SEP-81</HIREDATE>
              <SAL>1250</SAL>
              <COMM>1400</COMM>
              <DEPTNO>30</DEPTNO>
            </ROW>
            <ROW>
              <EMPNO>7698</EMPNO>
              <ENAME>BLAKE</ENAME>
              <JOB>MANAGER</JOB>
              <MGR>7839</MGR>
              <HIREDATE>01-MAY-81</HIREDATE>
              <SAL>2850</SAL>
              <DEPTNO>30</DEPTNO>
            </ROW>
            <ROW>
              <EMPNO>7782</EMPNO>
              <ENAME>CLARK</ENAME>
              <JOB>MANAGER</JOB>
              <MGR>7839</MGR>
              <HIREDATE>09-JUN-81</HIREDATE>
              <SAL>2450</SAL>
              <DEPTNO>10</DEPTNO>
            </ROW>
            <ROW>
              <EMPNO>7788</EMPNO>
              <ENAME>SCOTT</ENAME>
              <JOB>ANALYST</JOB>
              <MGR>7566</MGR>
              <HIREDATE>19-APR-87</HIREDATE>
              <SAL>3000</SAL>
              <DEPTNO>20</DEPTNO>
            </ROW>
            <ROW>
              <EMPNO>7839</EMPNO>
              <ENAME>KING</ENAME>
              <JOB>PRESIDENT</JOB>
              <HIREDATE>17-NOV-81</HIREDATE>
              <SAL>5000</SAL>
              <DEPTNO>10</DEPTNO>
            </ROW>
            <ROW>
              <EMPNO>7844</EMPNO>
              <ENAME>TURNER</ENAME>
              <JOB>SALESMAN</JOB>
              <MGR>7698</MGR>
              <HIREDATE>08-SEP-81</HIREDATE>
              <SAL>1500</SAL>
              <COMM>0</COMM>
              <DEPTNO>30</DEPTNO>
            </ROW>
            <ROW>
              <EMPNO>7876</EMPNO>
              <ENAME>ADAMS</ENAME>
              <JOB>CLERK</JOB>
              <MGR>7788</MGR>
              <HIREDATE>23-MAY-87</HIREDATE>
              <SAL>1100</SAL>
              <DEPTNO>20</DEPTNO>
            </ROW>
            <ROW>
              <EMPNO>7900</EMPNO>
              <ENAME>JAMES</ENAME>
              <JOB>CLERK</JOB>
              <MGR>7698</MGR>
              <HIREDATE>03-DEC-81</HIREDATE>
              <SAL>950</SAL>
              <DEPTNO>30</DEPTNO>
            </ROW>
            <ROW>
              <EMPNO>7902</EMPNO>
              <ENAME>FORD</ENAME>
              <JOB>ANALYST</JOB>
              <MGR>7566</MGR>
              <HIREDATE>03-DEC-81</HIREDATE>
              <SAL>3000</SAL>
              <DEPTNO>20</DEPTNO>
            </ROW>
            <ROW>
              <EMPNO>7934</EMPNO>
              <ENAME>MILLER</ENAME>
              <JOB>CLERK</JOB>
              <MGR>7782</MGR>
              <HIREDATE>23-JAN-82</HIREDATE>
              <SAL>1300</SAL>
              <DEPTNO>10</DEPTNO>
            </ROW>
          </ROWSET>
        </queryOut>
      </soap:Body>
    </soap:Envelope>
    
    
    Elapsed: 00:00:00.01
    SQL> select XMLPARSE(DOCUMENT :RESULT WELLFORMED)
      2    from dual
      3  /
    <queryOut xmlns="http://xmlns.oracle.com/orawsv">
      <ROWSET>
        <ROW>
          <EMPNO>7369</EMPNO>
          <ENAME>SMITH</ENAME>
          <JOB>CLERK</JOB>
          <MGR>7902</MGR>
          <HIREDATE>17-DEC-80</HIREDATE>
          <SAL>800</SAL>
          <DEPTNO>20</DEPTNO>
        </ROW>
        <ROW>
          <EMPNO>7499</EMPNO>
          <ENAME>ALLEN</ENAME>
          <JOB>SALESMAN</JOB>
          <MGR>7698</MGR>
          <HIREDATE>20-FEB-81</HIREDATE>
          <SAL>1600</SAL>
          <COMM>300</COMM>
          <DEPTNO>30</DEPTNO>
        </ROW>
        <ROW>
          <EMPNO>7521</EMPNO>
          <ENAME>WARD</ENAME>
          <JOB>SALESMAN</JOB>
          <MGR>7698</MGR>
          <HIREDATE>22-FEB-81</HIREDATE>
          <SAL>1250</SAL>
          <COMM>500</COMM>
          <DEPTNO>30</DEPTNO>
        </ROW>
        <ROW>
          <EMPNO>7566</EMPNO>
          <ENAME>JONES</ENAME>
          <JOB>MANAGER</JOB>
          <MGR>7839</MGR>
          <HIREDATE>02-APR-81</HIREDATE>
          <SAL>2975</SAL>
          <DEPTNO>20</DEPTNO>
        </ROW>
        <ROW>
          <EMPNO>7654</EMPNO>
          <ENAME>MARTIN</ENAME>
          <JOB>SALESMAN</JOB>
          <MGR>7698</MGR>
          <HIREDATE>28-SEP-81</HIREDATE>
          <SAL>1250</SAL>
          <COMM>1400</COMM>
          <DEPTNO>30</DEPTNO>
        </ROW>
        <ROW>
          <EMPNO>7698</EMPNO>
          <ENAME>BLAKE</ENAME>
          <JOB>MANAGER</JOB>
          <MGR>7839</MGR>
          <HIREDATE>01-MAY-81</HIREDATE>
          <SAL>2850</SAL>
          <DEPTNO>30</DEPTNO>
        </ROW>
        <ROW>
          <EMPNO>7782</EMPNO>
          <ENAME>CLARK</ENAME>
          <JOB>MANAGER</JOB>
          <MGR>7839</MGR>
          <HIREDATE>09-JUN-81</HIREDATE>
          <SAL>2450</SAL>
          <DEPTNO>10</DEPTNO>
        </ROW>
        <ROW>
          <EMPNO>7788</EMPNO>
          <ENAME>SCOTT</ENAME>
          <JOB>ANALYST</JOB>
          <MGR>7566</MGR>
          <HIREDATE>19-APR-87</HIREDATE>
          <SAL>3000</SAL>
          <DEPTNO>20</DEPTNO>
        </ROW>
        <ROW>
          <EMPNO>7839</EMPNO>
          <ENAME>KING</ENAME>
          <JOB>PRESIDENT</JOB>
          <HIREDATE>17-NOV-81</HIREDATE>
          <SAL>5000</SAL>
          <DEPTNO>10</DEPTNO>
        </ROW>
        <ROW>
          <EMPNO>7844</EMPNO>
          <ENAME>TURNER</ENAME>
          <JOB>SALESMAN</JOB>
          <MGR>7698</MGR>
          <HIREDATE>08-SEP-81</HIREDATE>
          <SAL>1500</SAL>
          <COMM>0</COMM>
          <DEPTNO>30</DEPTNO>
        </ROW>
        <ROW>
          <EMPNO>7876</EMPNO>
          <ENAME>ADAMS</ENAME>
          <JOB>CLERK</JOB>
          <MGR>7788</MGR>
          <HIREDATE>23-MAY-87</HIREDATE>
          <SAL>1100</SAL>
          <DEPTNO>20</DEPTNO>
        </ROW>
        <ROW>
          <EMPNO>7900</EMPNO>
          <ENAME>JAMES</ENAME>
          <JOB>CLERK</JOB>
          <MGR>7698</MGR>
          <HIREDATE>03-DEC-81</HIREDATE>
          <SAL>950</SAL>
          <DEPTNO>30</DEPTNO>
        </ROW>
        <ROW>
          <EMPNO>7902</EMPNO>
          <ENAME>FORD</ENAME>
          <JOB>ANALYST</JOB>
          <MGR>7566</MGR>
          <HIREDATE>03-DEC-81</HIREDATE>
          <SAL>3000</SAL>
          <DEPTNO>20</DEPTNO>
        </ROW>
        <ROW>
          <EMPNO>7934</EMPNO>
          <ENAME>MILLER</ENAME>
          <JOB>CLERK</JOB>
          <MGR>7782</MGR>
          <HIREDATE>23-JAN-82</HIREDATE>
          <SAL>1300</SAL>
          <DEPTNO>10</DEPTNO>
        </ROW>
      </ROWSET>
    </queryOut>
    
    
    Elapsed: 00:00:00.01
    SQL> select *
      2    from XMLTABLE(
      3             XMLNAMESPACES(
      4            default '&TARGET_NAMESPACE'
      5             ),
      6             '/queryOut/ROWSET/ROW'
      7             passing XMLTYPE(:RESULT)
      8             COLUMNS
      9             EMPNO       NUMBER(4)
     10            ,ENAME       VARCHAR2(32)
     11            ,JOB       VARCHAR2(32)
     12            ,MGR       NUMBER(4)
     13            ,HIREDATE VARCHAR2(10)
     14            ,SAL       NUMBER(8,2)
     15            ,DEPTNO   NUMBER(4)
     16            )
     17  /
    old   4:            default '&TARGET_NAMESPACE'
    new   4:            default 'http://xmlns.oracle.com/orawsv'
          7369 SMITH                            CLERK                                  7902 17-DEC-80         800         20
          7499 ALLEN                            SALESMAN                               7698 20-FEB-81        1600         30
          7521 WARD                             SALESMAN                               7698 22-FEB-81        1250         30
          7566 JONES                            MANAGER                                7839 02-APR-81        2975         20
          7654 MARTIN                           SALESMAN                               7698 28-SEP-81        1250         30
          7698 BLAKE                            MANAGER                                7839 01-MAY-81        2850         30
          7782 CLARK                            MANAGER                                7839 09-JUN-81        2450         10
          7788 SCOTT                            ANALYST                                7566 19-APR-87        3000         20
          7839 KING                             PRESIDENT                                   17-NOV-81        5000         10
          7844 TURNER                           SALESMAN                               7698 08-SEP-81        1500         30
          7876 ADAMS                            CLERK                                  7788 23-MAY-87        1100         20
          7900 JAMES                            CLERK                                  7698 03-DEC-81         950         30
          7902 FORD                             ANALYST                                7566 03-DEC-81        3000         20
          7934 MILLER                           CLERK                                  7782 23-JAN-82        1300         10
    
    14 rows selected.
    
    Elapsed: 00:00:00.05
    SQL> quit
    The only bit that might not work on XE is the use of XQUERY Update to populate the request document. This can be achieved using the legacy updateXML and insertChildXML operators.
  • 11. Re: Does XE have Native Oracle DB XML Web Services feature?
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    The term "servlet support" is misleading.

    The XDB Protocol Server which is an extension to listener functionality supports 3 types of "servlets" is defined in the xdbconfig.xsd XML Schema file (what else in an XML environment)

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/apphxdb.htm#i643401

    See the following options for "servlet-language"
         <element name="servlet-list">
            <complexType><sequence>
                  <element name="servlet" minOccurs="0" maxOccurs="unbounded">
                    <complexType><sequence>
                      <element name="servlet-name" type="string"/>
                      <element name="servlet-language">
                        <simpleType>
                          <restriction base="string">
                            <enumeration value="C"/>
                            <enumeration value="Java"/>
                            <enumeration value="PL/SQL"/>
                          </restriction>
                        </simpleType>
                      </element>
                      <element name="icon" type="string" minOccurs="0"/>
                      <element name="display-name" type="string"/>
                      <element name="description" type="string" minOccurs="0"/>
                      <choice>
                        <element name="servlet-class" type="string" minOccurs="0"/>
                        <element name="jsp-file" type="string" minOccurs="0"/>
                        <element name="plsql" type="xdbc:plsql-servlet-config" minOccurs="0"/>
                      </choice>
                      <element name="servlet-schema" type="string" minOccurs="0"/>
                      <element name="init-param" minOccurs="0"
                               maxOccurs="unbounded" type="xdbc:param"/>
                      <element name="load-on-startup" type="string" minOccurs="0"/>
                      <element name="security-role-ref" minOccurs="0"
                            maxOccurs="unbounded">
                      <complexType><sequence>
                          <element name="description" type="string" minOccurs="0"/>
                          <element name="role-name" type="string"/>
                          <element name="role-link" type="string"/>
                      </sequence></complexType>
                      </element>
                    </sequence></complexType>
                  </element>
            </sequence></complexType>
          </element>
    For example, the APEX so called PL/SQL Gateway makes use of the PL/SQL entry point (although you could/can write your own alternatives like a REST implementation http://ora-00001.blogspot.nl/2009/07/creating-rest-web-service-with-plsql.html)

    Oracle Native Database Web Services is based, as Mark mentioned and demonstrated on a embedded C implementation. No need for Java (the third option to extent the XDB protocol server funtionality). The Java "servlets" will not work due to that the JVM has not been installed in Express.

    Edited by: Marco Gralike on Apr 5, 2013 9:50 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points