oracle10g 中 运用axis包

在oracle中开发java 存储过程早已不是新鲜的事情了。最近因要在oracle10g中开发java存储过程，通过java存储过程调用webservice。在此过程中要用到axis的包，将这些包导入oracle过程中碰到了大量的问题，现在总结一下，希望对同仁有些帮助。

 

一、安装JSSE

为了运用axis，不但是只需要axis的包，同时还需要jsse的包。到sun去下载jsse1.0.3_04极其samples，步骤如下：

 

1. Download JSSE version 1.0.3 from the Sun Web site http://java.sun.com/products/jsse/index-103.html. Save and unzip the file on your local disk, D:\jsse1.0.3_01/lib. Locate the three JSSE JAR files, jnet.jar, jcert.jar, and jsse.jar, under the directory

2. Copy the certificates to the database security folder.
Copy the file jsse1.0.3_01/samples/samplecacerts to $ORACLE_HOME/javavm/lib/security/cacerts

3. Copy the JSSE JAR files, jnet.jar, jcert.jar, and jsse.jar to $ORACLE_HOME/javavm/lib.（$ORACLE_HOME在我的windows下的路径是D:\oracle\Ora_as10.1.2\）

4.创建一个schema:UTA,同时授权（作为测试用，你也可以创建其他 的名字）

GRANT connect, resource, unlimited tablespace, create public synonym, drop public synonym, javasyspriv TO UTA IDENTIFIED BY JSSE;

 

5.grant permission。以sysdba的身份登录oralce，执行如下命令

set serveroutput on
call dbms_java.set_output(50000);
CALL dbms_java.grant_permission( 'UTA', 'SYS:java.security.SecurityPermission', 'insertProvider.SunJSSE', '' );
CALL dbms_java.grant_permission( 'UTA', 'SYS:java.security.SecurityPermission', 'putProviderProperty.SunJSSE', '' );
CALL dbms_java.grant_permission( 'UTA', 'SYS:java.security.SecurityPermission', 'getProperty.ssl.ServerSocketFactory.provider', '' );
call dbms_java.grant_permission( 'UTA', 'SYS:java.security.SecurityPermission', '*', '' );

CALL dbms_java.grant_permission( 'UTA', 'SYS:java.util.PropertyPermission', '*', 'write' );

call dbms_java.grant_permission( 'UTA', 'SYS:java.util.logging.LoggingPermission', 'control','' );
call dbms_java.grant_permission('UTA','oracle.aurora.security.JServerPermission','*','*'); 

 

call dbms_java.grant_permission( 'UTA', 'SYS:java.lang.RuntimePermission', 'accessDeclaredMembers', '' )
call dbms_java.grant_permission( 'UTA', 'SYS:java.lang.RuntimePermission', '*', '' )

call dbms_java.grant_permission('UTA','oracle.aurora.rdbms.security.PolicyTablePermission','*','*');  

call Dbms_Java.Grant_Permission('VIO','java.io.FilePermission', '*','read ');

 

其中最后四条语句单独执行。如果全部一起执行出错的话

 

 

6. From SQL*PLUS, load the JSSE JAR files to the JSSE schema, using DBMS_JAVA.LOADJAVA (assuming that your JAR files are on the $ORACLE_HOME/javavm/lib directory).
SQL> CONNECT UTA/jsse
SQL> SET serveroutput on
SQL> CALL dbms_java.set_output(100000);
SQL> call dbms_java.loadjava(' -r -v -definer -g public D:/oracle/Ora_as10.1.2/javavm/lib/jcert.jar D:/oracle/Ora_as10.1.2/javavm/lib/jnet.jar D:/oracle/Ora_as10.1.2/javavm/lib/jsse.jar');

7. Connected as UTA user, create PUBLIC synonym for the HTTPS handler and the SSL Provider.
SQL>
DROP PUBLIC SYNONYM "com/sun/net/ssl/internal/www/protocol/https/Handler";
CREATE PUBLIC SYNONYM "com/sun/net/ssl/internal/www/protocol/https/Handler" for "com/sun/net/ssl/internal/www/protocol/https/Handler";
DROP PUBLIC SYNONYM "com/sun/net/ssl/internal/ssl/Provider";
CREATE PUBLIC SYNONYM "com/sun/net/ssl/internal/ssl/Provider" for "com/sun/net/ssl/internal/ssl/Provider";

 

二、安装axis

下载axis1.4，按照前面的步骤将其lib是jar拷贝到$ORACLE_HOME/javavm/lib下，以UTA身份登录执行如下命令

CALL dbms_java.loadjava(' -r -v -definer -g public D:/oracle/Ora_as10.1.2/javavm/lib/axis.jar D:/oracle/Ora_as10.1.2/javavm/lib/jaxrpc.jar D:/oracle/Ora_as10.1.2/javavm/lib/wsdl4j-1.5.1.jar  D:/oracle/Ora_as10.1.2/javavm/lib/commons-logging-1.0.4.jar D:/oracle/Ora_as10.1.2/javavm/lib/commons-discovery-0.2.jar D:/oracle/Ora_as10.1.2/javavm/lib/saaj.jar');

执行过程中会有些错误产生，可以不用理会它。（可能是版本兼容的问题）

 

三、安装 oracle.xml.jaxp.JXDocumentBuilderFactory

去oracle网站下载oc4j，解压后找到lib下的xmlparserv2.jar，拷贝到oracle的javavm/lib下，进行安装

 

--install oracle.xml.jaxp.JXDocumentBuilderFactory
CALL dbms_java.loadjava(' -r -v -definer -g public  D:/oracle/Ora_as10.1.2/javavm/lib/xmlparserv2.jar');

 

 

四、开发java stored procedure

 

 

import java.io.BufferedReader;  import java.io.File;  import java.io.FileInputStream;  import java.io.InputStreamReader;  import java.security.Security;  import org.apache.axis.client.Call;  import org.apache.axis.client.Service;    public class ValidateXMLService {      public static String validateXML(String xmlContent,String username,String password){        String endpoint = "https://localhost/UTA/services/ValidationService";    try {     System.setProperty("javax.net.ssl.trustStore", "D:\\UTA\\ehc-server.keystore");            System.setProperty("java.protocol.handler.pkgs","com.sun.net.ssl.internal.www.protocol");     Security.addProvider(new com.sun.net.ssl.internal.ssl.Provider());     Service service = new Service();     Call call = (Call) service.createCall();          call.setTargetEndpointAddress(new java.net.URL(endpoint));     call.setOperationName("validation");     String result = (String) call.invoke(new Object[] { username,password,xmlContent}); //    System.out.println("------------------------------------"); //    System.out.println(result); //    System.out.println("------------------------------------");     return result;    } catch (Exception e) {     e.printStackTrace();     return null;    }   }

 

 五、开发一个Function

create or replace FUNCTION XMLSERVICE
( xmlString  VARCHAR2,username VARCHAR2,password VARCHAR2
) RETURN VARCHAR2
AS
language java name
'ValidateXMLService.validateXML(java.lang.String,java.lang.String,java.lang.String)　return　java.lang.String';

 

六、测试

set serveroutput on
call dbms_java.set_output(100000);

select uta.xmlservice('<docs><doc><a>test</a></doc></docs>','test','test') FROM dual;

 

注意：运行时可能会报某些类找不到，这时你可以再一次用loadjava命令load相应的包，不过要事先dropjava一下

如：CALL dbms_java.dropjava(' -r -v -definer -g public  D:/oracle/Ora_as10.1.2/javavm/lib/axis.jar');

CALL dbms_java.loadjava(' -r -v -definer -g public  D:/oracle/Ora_as10.1.2/javavm/lib/axis.jar');因为在导入时经常发生一些错误，多导入几次就可以了。 

 

如果用call dbms_java.loadjava导入经常出错，请在<oracle_install_dir>/bin/
的目录下用loadjava命令执行（只能在服务器端执行）：

loadjava -u sys/password -r -v -f -genmissing -s -grant public D:/oracle/Ora_as10.1.2/javavm/lib/axis.jar'

以上命令导入的axis可以被任何用户使用，若只想给一个用户使用去掉 -s -grant public 参数 sys/passwor 改成其他用户即可。