Thursday, July 12, 2012

Web services callouts from Oracle DB 10g and 11g PL/SQL

In order to perform Web Service calls using PL/SQL use must install the UTL_DBWS PL/SQL package.


First connect as sys and check if has been installed (In 11g its isntaleld by default).


SQL> describe sys.utl_dbws


If this command does not return the package declarations the you must install it manual:


1. Run the following scripts under SYS:


$ORACLE_HOME/sqlj/lib/utl_dbws_decl.sql
$ORACLE_HOME/sqlj/lib/utl_dbws_body.sql

2. The dbwsclientws.jar must be loaded into the database for Web services call-outs and you can verify whether it is already loaded by running the following query in the SYS schema:


select status, object_type
from   all_objects
where  dbms_java.longname(object_name)='oracle/jpub/runtime/dbws/DbwsProxy$1';

The following result indicates that the file is already loaded:



STATUS OBJECT_TYPE
------- -------------------
VALID JAVA CLASS
VALID SYNONYM


If its not loaded use the follwoing commands to load it on the DB:



cd $ORACLE_HOME/sqlj/lib 

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

Then grant to above username the following permissions as sys:


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



It is recommended that a user created schema be used for this, (CONNECT, RESOURCE and CREATE PUBLIC SYNONYM are the minimum grants that have to be performed on this schema). In the 11g version of the database, it has been deteremined that loading the jars into ths SYS schema will cause conflicts with existing classes already loaded in this schema by default, so it is best to not use SYS for loading the jars. 





REFERENCES:
Oracle Support Note: 1166206.1



1 comment:

  1. Path Infotech is in the field of oracle training program from past several years.

    For more info : Ocp Certification in Noida

    ReplyDelete