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;
/
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]:
