Update: UTL_DBWS and Consuming Web services in Oracle

Using UTL_DBWS to make a Database Callout to a Document Style Web service.
Update of the UTL_DBWS – Consuming Web services in Oracle 


First, download the latest copy of the dbwsclient.jar file:

Pre 10g: dbws-callout-utility.zip (10.1.2)
10g: dbws-callout-utility-10R2.zip (10.1.3.0)
10g & 11g latest: dbws-callout-utility-10131.zip (10.1.3.1)
Extract the jar file from the zip file into the $ORACLE_HOME/sqlj/lib directory.

The jar file can be loaded into the SYS schema for everyone to access, or into an individual schema that needs access to the web client.



# Load into the SYS schema.

export PATH=/u01/app/oracle/product/10.2.0/db_1/bin:$PATH
cd /u01/app/oracle/product/10.2.0/db_1/sqlj/lib

# 10gR2

loadjava -u sys/password -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb102.jar

# 11g

loadjava -u sys/password -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb11.jar

# Load into an individual schema.

export PATH=/u01/app/oracle/product/10.2.0/db_1/bin:$PATH
cd /u01/app/oracle/product/10.2.0/db_1/sqlj/lib

# 10gR2

loadjava -u scott/tiger -r -v -f -genmissing dbwsclientws.jar dbwsclientdb102.jar

# 11g

loadjava -u scott/tiger -r -v -f -genmissing dbwsclientws.jar dbwsclientdb11.jar

SELECT owner, status, count(*) FROM DBA_OBJECTS
  WHERE OBJECT_TYPE='JAVA CLASS'
  GROUP BY owner, status;

Execute as sys user

execute dbms_java.grant_permission('DBAPRD4','SYS:java.util.PropertyPermission','http.proxySet','write');
execute dbms_java.grant_permission('DBAPRD4','SYS:java.util.PropertyPermission','http.proxyHost', 'write');
execute dbms_java.grant_permission('DBAPRD4','SYS:java.util.PropertyPermission','http.proxyPort', 'write');
execute dbms_java.grant_permission('DBAPRD4','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','');
execute dbms_java.grant_permission('DBAPRD4','SYS:java.lang.RuntimePermission','getClassLoader','');
execute dbms_java.grant_permission('DBAPRD4','SYS:java.net.SocketPermission','*','connect,resolve');
execute dbms_java.grant_permission('DBAPRD4','SYS:java.util.PropertyPermission','*','read,write');
execute dbms_java.grant_permission('DBAPRD4','SYS:java.lang.RuntimePermission','setFactory','');

Connect as DBAPRD4 user

CREATE OR REPLACE FUNCTION get_joke RETURN VARCHAR2
 AS
   service_    dbaprd4.utl_dbws.SERVICE;
   call_        dbaprd4.utl_dbws.CALL;
   service_qname   dbaprd4.utl_dbws.QNAME;
   port_qname     dbaprd4.utl_dbws.QNAME;
   xoperation_qname  dbaprd4.utl_dbws.QNAME;
   xstring_type_qname  dbaprd4.utl_dbws.QNAME;
   response    sys.XMLTYPE;
   request     sys.XMLTYPE;
 BEGIN
   service_qname := dbaprd4.utl_dbws.to_qname(null, 'getJoke');
   service_      := dbaprd4.utl_dbws.create_service(service_qname);
   call_         := dbaprd4.utl_dbws.create_call(service_);
   dbaprd4.utl_dbws.set_target_endpoint_address(call_, 'http://interpressfact.net/webservices/getjoke.asmx');
   dbaprd4.utl_dbws.set_property( call_, 'SOAPACTION_USE', 'TRUE');
   dbaprd4.utl_dbws.set_property( call_, 'SOAPACTION_URI', 'http://interpressfact.net/webservices/getJoke');
   dbaprd4.utl_dbws.set_property( call_, 'OPERATION_STYLE', 'document');
   request := sys.XMLTYPE(''||'Excuses-10 ');
   response :=dbaprd4.utl_dbws.invoke(call_, request);
   return response.extract('//getJokeResult/child::text()', 'xmlns="http://interpressfact.net/webservices/"').getstringval();
 END;
 /

SELECT get_joke FROM dual;

-- # Test webservice address http://interpressfact.net/webservices/getjoke.asmx
-- on 11g issue with ACL
/*
-- Issue with ACL used sys then no issue with acl
  DBMS_NETWORK_ACL_ADMIN
  DBMS_NETWORK_ACL_UTILITY

  ERROR at line 1:
  ORA-29532: Java call terminated by uncaught Java exception: HTTP transport
  error: javax.xml.soap.SOAPException: java.security.PrivilegedActionException:
  javax.xml.soap.SOAPException: Message send failed:
  HTTPClient.AuthSchemeNotImplException: NTLM
  ORA-06512: at "DBAPRD4.UTL_DBWS", line 404
  ORA-06512: at "DBAPRD4.UTL_DBWS", line 401
  ORA-06512: at "DBAPRD4.GET_JOKE", line 25
*/

Solving the security issue in Oracle by setting up an ACL
or use the user sys to execute the function.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'test_acl_file.xml', 
    description  => 'A test of the ACL functionality',
    principal    => 'DBAPRD4',
    is_grant     => FALSE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  COMMIT;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'test_acl_file.xml',
    host        => '*', 
    lower_port  => NULL,
    upper_port  => NULL); 

  COMMIT;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.unassign_acl (
    acl         => 'test_acl_file.xml',
    host        => '192.168.2.3', 
    lower_port  => 80,
    upper_port  => NULL); 

  COMMIT;
END;
/

An other function with shakespeare phrase

CREATE OR REPLACE  FUNCTION GET_SHAKESPEARE (p_phrase in varchar2)
    RETURN varchar2
AS
  l_service                 utl_dbws.SERVICE;
  l_call                    utl_dbws.CALL;
  l_service_qname           utl_dbws.QNAME;
  l_port_qname              utl_dbws.QNAME;
  l_operation_qname         utl_dbws.QNAME;
  l_string_type_qname       utl_dbws.QNAME;
  l_namespace               varchar2(1000);
  l_retx                    sys.xmltype;
  l_xml_string              sys.xmltype;
Begin
  l_namespace       := 'http://xmlme.com/WebServices';
  l_service_qname   := sys.utl_dbws.to_qname(l_namespace, 'Shakespeare');
  l_service         := sys.utl_dbws.create_service(HTTPURITYPE('http://www.xmlme.com/WSShakespeare.asmx?WSDL'), l_service_qname);
  l_port_qname      :=  sys.utl_dbws.to_qname(l_namespace, 'ShakespeareSoap');
  l_operation_qname := sys.utl_dbws.to_qname(l_namespace, 'GetSpeech');
  l_call            := sys.utl_dbws.create_call(l_service, l_port_qname, l_operation_qname);
  sys.utl_dbws.set_property(l_call, 'SOAPACTION_USE', 'TRUE');
  sys.utl_dbws.set_property(l_call, 'SOAPACTION_URI', 'http://xmlme.com/WebServices/GetSpeech');
  sys.utl_dbws.set_property(l_call, 'ENCODINGSTYLE_URI', 'http://schemas.xmlsoap.org/soap/encoding/');
  sys.utl_dbws.set_property(l_call, 'OPERATION_STYLE', 'document');
  l_string_type_qname := sys.utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
  sys.utl_dbws.add_parameter(l_call, 'Request', l_string_type_qname, 'ParameterMode.IN');
  sys.utl_dbws.set_return_type(l_call, l_string_type_qname);
  l_xml_string := xmltype('
    
      '||p_phrase||'  -- the searche for text --
    ');
  l_retx := sys.utl_dbws.invoke(call_Handle => l_call,request => l_xml_string);
  return l_retx.extract('/*').getstringval();
  sys.utl_dbws.release_service(l_service);
end;
/

Examples:

SELECT dbaprd4.get_shakespeare('To be, or not to be') FROM dual;
SELECT dbaprd4.get_shakespeare('O Romeo, Romeo! wherefore art thou Romeo') FROM dual;
SELECT dbaprd4.get_shakespeare('Let's kill all the lawyers') FROM dual;
SELECT dbaprd4.get_shakespeare('Good night sweet prince') FROM dual;