Sunday, 13 January 2013

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');




No comments:

Post a Comment