Installing DBMS_JAVA package in Oracle and calling UTL_DBWS web services through Oracle database

Installing DBMS_JAVA package in Oracle following  files need to be run:- 

sql> select comp_name, version, status from dba_registry where comp_name like '%JAVA%';

@?/javavm/install/initjvm.sql;

@?/xdk/admin/initxml.sql;

@?/xdk/admin/xmlja.sql;

@?/rdbms/admin/catjava.sql;

@?/rdbms/admin/catexf.sql;

and verify your java installation in Oracle Database 

sql> select comp_name, version, status from dba_registry where comp_name like '%JAVA%';

sql> select dbms_java.longname(object_name), status, object_type from all_objects where

      object_type = 'JAVA CLASS';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

calling UTL_DBWS web services through Oracle Database :- 

set the follwing parameters in .bash_profile of oracle user 

export JAVA_HOME=$ORACLE_HOME/jdk

ORACLE_BASE=/opt/oracle/product; export ORACLE_BASE

ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1; export ORACLE_HOME

PATH=$PATH:/opt/oracle/product/11.2.0/dbhome_1:$JAVA_HOME/bin:$PATH; export PATH

CLASSPATH=.:/opt/oracle/product/11.2.0/dbhome_1/jdbc/lib:$ORACLE_HOME/sqlj/lib/
translator.jar:$ORACLE_HOME/sqlj/lib/runtime12.jar:$ORACLE_HOME/sqlj/lib/dbwsa.j
ar:$ORACLE_HOME/javavm/lib/aurora.zip:$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORA
CLE_HOME/jdk/lib/dt.jar:$ORACLE_HOME/jdk/lib/tools.jar:$ORACLE_HOME/jlib/jssl
1_1.jar:$ORACLE_HOME/jlib/orai18n.jar:$ORACLE_HOME/rdbms/jlib/xdb.jar:$ORAC
LE_HOME/lib/xsu12.jar:$ORACLE_HOME/jlib/jndi.jar:$ORACLE_HOME/rdbms/jlib/aq
api.jar:$ORACLE_HOME/rdbms/jlib/jmscommon.jar:$ORACLE_HOME/lib/xmlparserv2.
jar; export CLASSPATH

LD_LIBRARY_PATH=/opt/oracle/product/11.2.0/dbhome_1:$JAVA_HOME/lib; export LD_LIBRARY_PATH

PATH=$PATH:/opt/oracle/product/11.2.0/dbhome_1/bin; export PATH

then at sql prompt :- 

show parameter SHARED_POOL_SIZE;

show parameter JAVA_POOL_SIZE ;

show parameter LARGE_POOL_SIZE;

sql>alter system set JAVA_POOL_SIZE=150M scope=both; 

sql>alter system set SHARED_POOL_SIZE=150M scope=both; 

sql>alter system set LARGE_POOL_SIZE=150M scope=both;  

sql> select comp_name, version, status from dba_registry where comp_name like '%JAVA%';

In Oracle Database 11g, the UTL_DBWS PL/SQL package is installed in the database SYS schema. To verify the installation, 

try to describe the package as follows:

SQL> describe sys.utl_dbws;

ERROR:

ORA-04043: object sys.utl_dbws does not exist

Cuase : - UTL_DBWS package was not installed in SYS schema by default in Oracle Database 11g R2.

Solution :- Manually install the UTL_DBWS package  in SYS schema by following the steps as below:

install utl_dbws :- If the output indicates that the package is not yet installed,download the latest UTL_DBWS, install them in the 

$ORACLE_HOME/sqlj/lib location  and then run the following scripts under SYS:

download site : - http://download.oracle.com/technology/sample_code/tech/java/jsp/dbws-callout-utility-10131.zip

unzip dbws-callout-utility-10131.zip sqlj/* -d $ORACLE_HOME

unzip dbws-callout-utility-10131.zip samples/* -d $ORACLE_HOME/sqlj

unzip dbws-callout-utility-10131.zip *.htm -d $ORACLE_HOME/sqlj

@$ORACLE_HOME/sqlj/lib/utl_dbws_decl.sql

@$ORACLE_HOME/sqlj/lib/utl_dbws_body.sql

at the sql prompt :- 

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

If there are classes that are listed as being in an 'INVALID' state, you can run the following script to attempt to make these objects VALID: 

$ORACLE_HOME/rdbms/admin/utlrp.sql 

Once this has been run, recheck the status of your Java objects. 

When loading the jar files in the desired schema, make sure the PATH environment variable includes the $ORACLE_HOME/bin directory.  

From a terminal window, run the following command:-

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

select owner, object_type, created, status from dba_objects where object_name='UTL_DBWS';

then run the follwoing calls :- sys as sysdba for required user - 

call dbms_java.grant_permission( '<user_name>','SYS:java.util.logging.LoggingPermission','control', '' ); 

call dbms_java.grant_permission( '<user_name>','SYS:java.lang.RuntimePermission', 'setFactory', '' ); 

execute dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','accessCl
assInPackage.sun.util.calendar','');

execute dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','getClassLoader','');

execute dbms_java.grant_permission('<user_name>','SYS:java.net.SocketPermission','*','connect,resolve');

execute dbms_java.grant_permission('<user_name>','SYS:java.util.PropertyPermission','*','read,write');

execute dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','setFactory','');

call dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','shutdow
nHooks', '' );

call dbms_java.grant_permission('<user_name>','SYS:java.util.logging.LoggingPermission', 'control', '' );

call dbms_java.grant_permission('<user_name>','SYS:java.util.PropertyPermission','http.proxySet','write');

call dbms_java.grant_permission('<user_name>','SYS:java.util.PropertyPermission','http.proxyHost', 'write');

call dbms_java.grant_permission('<user_name>','SYS:java.util.PropertyPermission','http.proxyPort', 'write');

call dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','getClassLoader','');

call dbms_java.grant_permission('<user_name>','SYS:java.net.SocketPermission','*','connect,resolve');

call dbms_java.grant_permission('<user_name>','SYS:java.util.PropertyPermission','*','read,write');

call dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','setFactory','');

call dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','accessCl
assInPackage.sun.util.calendar','');  

call dbms_java.grant_permission( '<user_name>','SYS:oracle.aurora.security.JServerPermission', 'Verifier', '' );

call dbms_java.grant_permission( '<user_name>','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','' ) ;

call dbms_java.grant_permission( '<user_name>','SYS:java.net.SocketPermission', '<<machineName>>', 'resolve' );

call dbms_java.grant_permission( '<user_name>','SYS:java.net.SocketPermission', '192.168.4.146', 'connect,resolve' );

call dbms_java.grant_permission( '<user_name>','SYS:java.lang.RuntimePermission', 'createClassLoader', '' );

call dbms_java.grant_permission( '<user_name>','SYS:java.lang.RuntimePermission', 'setFactory', '' );

call dbms_java.grant_permission( '<user_name>','SYS:java.util.PropertyPermission', 'HTTPClient.socket.idleTimeout', 'write');

call dbms_java.grant_permission( '<user_name>','SYS:java.net.SocketPermission', 'localhost', 'resolve' );

call dbms_java.grant_permission( '<user_name>','SYS:java.net.SocketPermission', '192.168.3.229:80', 'connect,resolve' );

call dbms_java.grant_permission( '<user_name>','SYS:java.util.PropertyPermission', 'HTTPClient.socket.idleTimeout', 'write');