PL/SQL client to consume the GSIS (ΓΓΠΣ) web service

This is the code for a plsql client, using the UTL_DBWS package, that consumes the web service for legal entities' details offered by Greece's Ministry of Finance General Secretariat of Information Systems (GSIS-ΓΓΠΣ).

I understand that you might wonder why use plsql to consume a web service instead of using a more convenient / classic approach (e.g. java, php). Well , for those that either feel better with Oracle plsql or are just fans of Oracle forms or even better Oracle APEX, this could be an interesting alternative :

The following example has been successfully implemented on Oracle 10g ( 10.2.0.3.0) database , on windows 2003 server.

The first step is the appropriate setup in database in order to be able to call web services as described in http://www.oracle-base.com/articles/10g/utl_dbws-10g.php.

Note: When tested on 11g db I had to load the jar file into sys schema as well (or else i got error: oracle/jpub/runtime/dbws/DbwsProxy does not exist):
   loadjava -u sys/<password> -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb11.jar

If test code in above example works (SELECT add_numbers(1, 5) FROM dual;) then we know that the db has a working connection to internet and to http web services.

Create function gsis_user_data (p_afm IN varchar2, p_field IN varchar2) which returns the value the web service returned for field name "p_field" for the specific legal entity with AFM "p_afm".

CREATE OR REPLACE FUNCTION gsis_user_data (p_afm IN varchar2, p_field IN varchar2)
RETURN varchar2
AS
l_service UTL_DBWS.service;
l_call UTL_DBWS.call;

l_wsdl_url VARCHAR2(32767);
l_namespace VARCHAR2(32767);
l_service_qname UTL_DBWS.qname;
l_port_qname UTL_DBWS.qname;
l_operation_qname UTL_DBWS.qname;

l_xmltype_in SYS.XMLTYPE;
l_xmltype_out SYS.XMLTYPE;
l_return VARCHAR2(32767);

BEGIN
  l_wsdl_url        := 'http://www.gsis.gr/wsnp/RgWsBasStoixN_version2.wsdl'; 
 
  l_namespace       := 'http://gr/gsis/rgwsbasstoixn/RgWsBasStoixN.wsdl';

  l_service_qname   := UTL_DBWS.to_qname(l_namespace, 'RgWsBasStoixN'); 
  l_port_qname      := UTL_DBWS.to_qname(l_namespace, 'RgWsBasStoixNSoapHttpPort');
  l_operation_qname := UTL_DBWS.to_qname(l_namespace, 'rgWsBasStoixEpit'); 

  l_service := UTL_DBWS.create_service (
    wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),
    service_name           => l_service_qname);

  l_call := UTL_DBWS.create_call (
    service_handle => l_service,
    port_name      => l_port_qname,
    operation_name => l_operation_qname);

    --in order to find the correct xml you can try the apex version that automatically generates soapenv, and adjust accordingly
  l_xmltype_in := SYS.XMLTYPE('<?xml version="1.0" encoding="utf-8"?>
    <ns1:rgWsBasStoixEpit xmlns:ns1="' || l_namespace || '"
                      xmlns:typ= "http://gr/gsis/rgwsbasstoixn/RgWsBasStoixN.wsdl/types/"
    >
      <pAfm>' || p_afm || '</pAfm>    
      <pBasStoixEpitRec_out>
            <typ:residenceParDescription></typ:residenceParDescription>
            <typ:assTxpActualAfm></typ:assTxpActualAfm>
            <typ:actLongDescr></typ:actLongDescr>
            <typ:postalZipCode></typ:postalZipCode>
            <typ:INiFlagDescr></typ:INiFlagDescr>
            <typ:registDate>2011-01-01</typ:registDate>
            <typ:stopDate>2011-01-01</typ:stopDate>
            <typ:parDescription></typ:parDescription>
            <typ:doyDescr></typ:doyDescr>
            <typ:residenceZipCode></typ:residenceZipCode>
            <typ:deactivationFlagDescr></typ:deactivationFlagDescr>
            <typ:legalStatusDescr></typ:legalStatusDescr>
            <typ:firmPhone></typ:firmPhone>
            <typ:firmFax></typ:firmFax>
            <typ:afm></typ:afm>
            <typ:facActivity>0</typ:facActivity>
            <typ:countOfBranches>0</typ:countOfBranches>
            <typ:deactivationFlag>1</typ:deactivationFlag>
            <typ:postalAddressNo></typ:postalAddressNo>
            <typ:firmFlagDescr></typ:firmFlagDescr>
            <typ:postalAddress></typ:postalAddress>
            <typ:doy></typ:doy>
            <typ:onomasia></typ:onomasia>
            <typ:commerTitle></typ:commerTitle>
         </pBasStoixEpitRec_out>
       <pCallSeqId_out>0</pCallSeqId_out>
       <pErrorRec_out>
            <typ:errorDescr></typ:errorDescr>
            <typ:errorCode></typ:errorCode>
       </pErrorRec_out>
    </ns1:rgWsBasStoixEpit>');

  l_xmltype_out := UTL_DBWS.invoke(call_Handle => l_call,
                                   request     => l_xmltype_in);
 
  UTL_DBWS.release_call (call_handle => l_call);
  UTL_DBWS.release_service (service_handle => l_service);

  select
     extractValue(value(t),'/*/' || p_field,'xmlns="http://gr/gsis/rgwsbasstoixn/RgWsBasStoixN.wsdl/types/"')
    into l_return
   from table(xmlsequence(extract(l_xmltype_out,'//pBasStoixEpitRec_out'))) t;  

  RETURN l_return;
END;
/

Call example:

SELECT gsis_user_data('099324100','onomasia') FROM dual;

Update for 11g :

Note: all commands of type: call dbms_java.grant_permission(...) , were NOT NEEDED. As stated in:
http://www.oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1.php
Oracle 11g introduces fine grained access to network services using access control lists (ACL) in the XML DB repository, allowing control over which users access which network resources, regardless of package grants.
so:

    BEGIN
      DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('local-access-users.xml',
                 'ACL that lets power users to connect to everywhere',   'GSIS', TRUE, 'connect');
      DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','*');
     COMMIT; --do not forget
    end;

    BEGIN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'local-access-users.xml',
                                           principal => 'GSIS',
                                           is_grant  => true,
                                           privilege => 'connect');
      COMMIT;
    END;
    /

    BEGIN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'local-access-users.xml',
                                           principal => 'GSIS',
                                           is_grant  => true,
                                           privilege => 'resolve');
      COMMIT;
    END;
    /

Update for  https/ssl call:
The instructions above were OK when the gsis service was plain http. For https the following additional steps were needed, according to metalink note 443438.1:

Import the CA certificate in it's cacerts certificate stores. To achieve this, perform the following :
    List the trusted certificates stored in the JVM's keystore:
        keytool -list -v -keystore $ORACLE_HOME/javavm/lib/security/cacerts -storepass changeit

    Get the CA's Certificate file from the server hosting the Web Service , with firefox as: export x509 certificate with chain (PEM)
        Saved e.g. in: /home/oracle/pz_web_services/www.gsis.gr_chain_pem   
    Attention:
        a) keytool does not acccept chain certificates of type PKCS#7
        b) The certificate files must be retrieved for both following urls :
            -https://www1.gsis.gr/wsgsis/RgWsBasStoixN/RgWsBasStoixNSoapHttpPort
            -http://www.gsis.gr/wsnp/RgWsBasStoixN_version2.wsdl
    Import all certificates in database keystore with keytool:
        keytool -import -trustcacerts -alias gsis -file /home/oracle/pz_web_services/www.gsis.gr_chain_pem -keystore $ORACLE_HOME/javavm/lib/security/cacerts -storepass changeit
        keytool -import -trustcacerts -alias verg3gsis1 -file /home/oracle/pz_web_services/VeriSignClass3InternationalServerCA-G3_pem -keystore $ORACLE_HOME/javavm/lib/security/cacerts -storepass changeit   
        keytool -import -trustcacerts -alias gsis1 -file /home/oracle/pz_web_services/www1.gsis.gr_single_pem -keystore $ORACLE_HOME/javavm/lib/security/cacerts -storepass changeit   
    Also, add the certificate to JPublisher's JRE and/or JDK keystores using the same command.
        keytool -import -trustcacerts -alias gsis -file /home/oracle/pz_web_services/www.gsis.gr_chain_pem -keystore $ORACLE_HOME/jre/1.4.2/lib/security/cacerts -storepass changeit
        as root user : keytool -import -trustcacerts -alias gsis -file /home/oracle/pz_web_services/www.gsis.gr_chain_pem -keystore $ORACLE_HOME/jdk/jre/lib/security/cacerts -storepass changeit
        as root user : keytool -import -trustcacerts -alias verg3gsis1 -file /home/oracle/pz_web_services/VeriSignClass3InternationalServerCA-G3_pem -keystore $ORACLE_HOME/jdk/jre/lib/security/cacerts -storepass changeit
        as root user : keytool -import -trustcacerts -alias gsis1 -file /home/oracle/pz_web_services/www1.gsis.gr_single_pem -keystore $ORACLE_HOME/jdk/jre/lib/security/cacerts -storepass changeit
       
    Once the certificates have been successfully added to the keystores, restart your database.
    Now test with "Call example" created above.

References:
http://www.gsis.gr/wsnp/wsnp_ver2.html
https://www.gsis.gr/wsnp/RgWsBasStoixN_version2.wsdl
https://www1.gsis.gr/wsgsis/RgWsBasStoixN/RgWsBasStoixNSoapHttpPort
http://serafeimk.blogspot.gr/2009/05/call-web-services-plsql.html
http://www.oracle-base.com/articles/misc/utl_http-and-ssl.php
http://www.oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1.php
Metalink note: How To Invoke A Web Service from the DBWS Callout Utility over SSL / HTTPS [ID 443438.1]: