Oracle Native web services and SOAPUI

You can find several examples on the net to enable native webservices in an Oracle 11g Database. E.g. http://www.liberidu.com/blog/2008/07/08/howto-create-a-native-database-webservice/ is a good starting point to accomplish this.

I tried to test my webservice in SOAPUI and it didn’t work… at first.

I used the testscript from Mark Drake that can be found on OTN

clear screen
clear buffer

connect / AS sysdba

-- call dbms_xdb.sethttpport(80);
-- alter system register;

grant dba, xdbadmin to scott;

alter user scott identified by tiger account unlock;

set echo on
set termout on
set feed on
cle scre
---------------------------------------------------------
-- Show User and "orawsv" new xdbconfig.xml entry
----------------------------------------------------------

show user
--
DECLARE
  SERVLET_NAME VARCHAR2(32) := 'orawsv';
BEGIN
  DBMS_XDB.deleteServletMapping(SERVLET_NAME);
  DBMS_XDB.deleteServlet(SERVLET_NAME);
  DBMS_XDB.addServlet
    ( NAME => SERVLET_NAME
    , LANGUAGE => 'C'
    , DISPNAME => 'Oracle Query Web Service'
    , DESCRIPT => 'Servlet for issuing queries as a Web Service'
    , SCHEMA   => 'XDB'
    );
  DBMS_XDB.addServletSecRole
    ( SERVNAME => SERVLET_NAME
    , ROLENAME => 'XDB_WEBSERVICES'
    , ROLELINK => 'XDB_WEBSERVICES'
    );
  DBMS_XDB.addServletMapping
    ( PATTERN => '/orawsv/*'
    , NAME => SERVLET_NAME
    );
END;
/

pause
--
def USERNAME=SCOTT
def PASSWORD=tiger
--
pause
clear screen
----------------------------------------------------------
-- Drop the Access Control List XML file if it exists
----------------------------------------------------------
begin
  dbms_network_acl_admin.drop_acl('/public/localhost.xml');
end;
/
commit;
pause
clear screen
----------------------------------------------------------
-- Create and Assign ACL's to localhost
----------------------------------------------------------
begin
  dbms_network_acl_admin.create_acl
    ( '/public/localhost.xml'
    , 'ACL for 127.0.0.1'
    , '&USERNAME'
    , true
    , 'connect'
    );
  dbms_network_acl_admin.assign_acl
    ( '/public/localhost.xml'
    , '127.0.0.1'
    );
end;
/
COMMIT
/
pause
clear screen
----------------------------------------------------------
-- Show and grant NDWS Roles to a user account
----------------------------------------------------------

-- Only HTTPS allowed
GRANT XDB_WEBSERVICES TO &USERNAME
/
-- HTTP also allowed
GRANT XDB_WEBSERVICES_OVER_HTTP TO &USERNAME
/
-- Access also allowed that are accessible to PUBLIC.
GRANT XDB_WEBSERVICES_WITH_PUBLIC TO &USERNAME
/
pause
clear screen

connect &USERNAME/&PASSWORD

set echo on
set termout on
set feed on
SET long 10000
clear screen

----------------------------------------------------------
-- Show the now active WSDL URL
----------------------------------------------------------

show user

var url varchar2(700)

BEGIN
  :url := 'http://&USERNAME:&PASSWORD@localhost:'|| dbms_xdb.getHttpPort()|| '/orawsv?wsdl';
end;
/

print url

pause
SET long 10000 pages 0
cle scre

----------------------------------------------------------
-- Show the output via PL/SQL when WSDL URL called
----------------------------------------------------------
SELECT httpuritype( :url ).getXML()
FROM dual
/
pause
cle scre

----------------------------------------------------------
-- Create simple function that can be called via the NDWS
----------------------------------------------------------
create or replace function GET_SQRT
  ( INPUT_VALUE number
  ) return number
as
begin
  return SQRT(INPUT_VALUE);
end;
/

pause
SET long 10000 pages 0
cle scre
----------------------------------------------------------
-- Setting the URL as input for consumption
----------------------------------------------------------
BEGIN
  :url := 'http://&USERNAME:&PASSWORD@localhost:'||dbms_xdb.getHttpPort()||'/orawsv/SCOTT/GET_SQRT';
end;
/
print url

SELECT httpuritype( :url||'?wsdl' ).getXML()
FROM   dual
/
--cle scre

----------------------------------------------------------
-- Consume NDWS function call via PL/SQL
----------------------------------------------------------
set serveroutput on
--
DECLARE
  V_SOAP_REQUEST XMLTYPE := XMLTYPE(
    '<SOAP-ENV:Envelope 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">
      <SOAP-ENV:Body>
        <m:SNUMBER-GET_SQRTInput xmlns:m="http://xmlns.oracle.com/orawsv/&USERNAME/GET_SQRT">
        <m:INPUT_VALUE-NUMBER-IN>2>/m:INPUT_VALUE-NUMBER-IN>
        </m:SNUMBER-GET_SQRTInput> 
      </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>'
  );
  V_SOAP_REQUEST_TEXT CLOB := V_SOAP_REQUEST.getClobVal();
  V_REQUEST           UTL_HTTP.REQ;
  V_RESPONSE          UTL_HTTP.RESP;
  V_BUFFER            VARCHAR2(1024);
BEGIN
  V_REQUEST := UTL_HTTP.BEGIN_REQUEST(URL => :URL, METHOD => 'POST');
  UTL_HTTP.SET_HEADER(V_REQUEST, 'User-Agent', 'Mozilla/4.0');
  V_REQUEST.METHOD := 'POST';
  UTL_HTTP.SET_HEADER 
    ( R     => V_REQUEST
    , NAME  => 'Content-Length'
    , VALUE => DBMS_LOB.GETLENGTH(V_SOAP_REQUEST_TEXT)
    );
  UTL_HTTP.WRITE_TEXT 
    ( R    => V_REQUEST
    , DATA => V_SOAP_REQUEST_TEXT
    );
  --
  V_RESPONSE := UTL_HTTP.GET_RESPONSE(V_REQUEST);
  LOOP
    UTL_HTTP.READ_LINE(V_RESPONSE, V_BUFFER, TRUE);
    DBMS_OUTPUT.PUT_LINE(V_BUFFER);
  END LOOP;
  UTL_HTTP.END_RESPONSE(V_RESPONSE);
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY 
  THEN
    UTL_HTTP.END_RESPONSE(V_RESPONSE);
END;
/

So in SOAPUI I created a new project. As as base-WSDL I used the new http://SCOTT:TIGER@localhost:8080/orawsv/SCOTT/GET_SQRT.
In the properties of the request I entered scott and tiger (lowercase)

And it didn’t work.

Analyzing the http-log of the request I found out that two requests where send. The first one lacked “Authorization: Basic …..” key in the HTTP-header. The response from the server was a 401 HTTP-error indicating the need for a username/pwd. A second request was send with the Authorization Basic key and the server didn’t respond.

The solution is to include the key in the first request.
In the SOAPUI preference in thet HTTP section you have to check the “Adds authentication information to outgoing request”.

Restart SOAPUI and it works.

Advertisements

4 thoughts on “Oracle Native web services and SOAPUI

  1. You wrote “Analyzing the http-log …”. Where can i find this “file” or further information for this topic?
    Best regards

  2. 401 Unauthorized
    Unauthorized

    I Have implementedOracle Native web services
    As mentioned above but I’m receiving above response. Can anyone help on this

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s