Oracle安装UTL_DBWS

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

               http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

               

               

Below are the steps you can follow to setup Database Webservice Callout :

1. Check to see if the database webservice utilities are installed in sys schema, check the status of the java classes and set the OJVM pool size.

    a. Check and see if callout utilities are installed

SQL> desc sys.utl_dbws 

ERROR: 

ORA-04043: object sys.utl_dbws does not exist

    b. Check the status of the java classes

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

OWNER STATUS COUNT(*) 

------------------------------ ------- ---------- 

MDSYS VALID 531 

SYS VALID 20444 

EXFSYS VALID 47 

ORDSYS VALID 1898

    c. Set the OJVM pool size. If this is not set, loading and resolving java classes (jars) will occur errors:

SQL> show parameter SHARED_POOL_SIZE 

NAME TYPE VALUE 

------------------------------------ ----------- ------------------------------ 

shared_pool_size big integer 0

SQL> show parameter JAVA_POOL_SIZE 

NAME TYPE VALUE 

------------------------------------ ----------- ------------------------------ 

java_pool_size big integer 0

SQL>  alter system set SHARED_POOL_SIZE=132M scope=both; 

System altered. 

SQL> alter system set JAVA_POOL_SIZE=80M scope=both; 

System altered. 

SQL>  show parameter JAVA_POOL_SIZE 

NAME TYPE VALUE 

------------------------------------ ----------- ------------------------------ 

java_pool_size big integer 80M 

2. Download JPublisher & Database Web Services Callout (UTL_DBWS) Utilities 

3. Extract the downloaded files:

    a. Take a backup of sqlj folder using the command "mv $ORACLE_HOME/sqlj $ORACLE_HOME/sqlj.org"

    b. Extract Jpub using the command "unzip jpub_102.zip -d $ORACLE_HOME"

    c. Extract Callout Utilities using the command "unzip dbws-callout-utility-10131.zip sqlj\* -d $ORACLE_HOME"

    d. Confirm the extracted files, and see if dbwsclient.jar is available or not. There is however dbwsclientws.jar. Please note dbwsclientws.jar is the same as dbwsclient.jar. 

        Jpub looks for this file when you run the jpub command. To avoid the error: 'Error loading dbwsclient.jar, please check the database trace file and make sure dbwsclient.jar exists in ORACLE_HOME/sqlj/lib.' 

        when running the jpub command, you can make a copy of this file using the command "cp dbwsclientws.jar dbwsclient.jar"

4. Set the execute permission on jpub:

$ cd $ORACLE_HOME/sqlj/bin  

chmod +x jpub  

5. Check the schema has CONNECT, RESOURCE and CREATE PUBLIC SYNONYM grants. These are the minimum grants required.

SQL>  SELECT grantee, granted_role FROM dba_role_privs where GRANTEE = 'SCOTT'; 

GRANTEE GRANTED_ROLE 

------------------------------ ------------------------------ 

SCOTT RESOURCE 

SCOTT CONNECT 

SQL> select * from dba_sys_privs where GRANTEE='SCOTT'; 

GRANTEE PRIVILEGE ADM 

------------------------------ ---------------------------------------- --- 

SCOTT UNLIMITED TABLESPACE NO 

SQL> grant CREATE PUBLIC SYNONYM to scott; 

SQL> select * from dba_sys_privs where GRANTEE='SCOTT'; 

GRANTEE PRIVILEGE ADM 

------------------------------ ---------------------------------------- --- 

SCOTT UNLIMITED TABLESPACE NO 

SCOTT CREATE PUBLIC SYNONYM NO

6. Load the database webservice callout utility into the scott schema. The jar's are found under the $ORACLE_HOME/sqlj/lib folder. Check the log and see if it completed successfully.

$ cd $ORACLE_HOME/sqlj/lib 

$ loadjava -u scott/tiger -r -v -f -s -grant public -genmissing dbwsclientws.jar dbwsclientdb11.jar >& loadjava.txt 

7. Once the database webservice call out classes have been loaded, We need to run the PL/SQL wrappers that will call theses classes. The scripts are found in $ORACLE_HOME/sqlj/lib folder. Since we installed the jar in scott, scott needs to run these scripts:

$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 15 11:08:50 2009

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @utl_dbws_decl.sql

Package created.

SQL> @utl_dbws_body.sql

Package body created.

Grant succeeded.

SQL> desc utl_dbws

PROCEDURE ADD_PARAMETER

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

CALL_HANDLE NUMBER IN

XML_NAME VARCHAR2 IN

Q_NAME VARCHAR2(4096) IN

...

...

8. Give the next grant to scott. (or whichever schema your using. Ensure the schema name is in capital letters. i.e. 'SCOTT') :

SQL>  conn /as sysdba

Connected.

SQL>  call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission', 'shutdownHooks', '' );

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

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

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

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

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

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

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

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

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

Example 1 :

Using the example code from note 841183.1, We will now use the UTL_DBWS package loaded in scott to call the webservice. The PL/SQL is created as follows:

    a. Here is the PL/SQL code using UTL_DBWS to call an external webservice :

CREATE OR REPLACE FUNCTION celciusToFahrenheit(temperature NUMBER) RETURN VARCHAR2 AS

  service_ scott.utl_dbws.SERVICE;

  call_ scott.utl_dbws.CALL;

  service_qname scott.utl_dbws.QNAME;

  port_qname scott.utl_dbws.QNAME;

  response sys.XMLTYPE;

  request sys.XMLTYPE;

BEGIN

  scott.utl_dbws.set_http_proxy('myproxy.com:80');

  service_qname := scott.utl_dbws.to_qname(null, 'CelciusToFahrenheit');

  service_ := scott.utl_dbws.create_service(service_qname);

  call_ := scott.utl_dbws.create_call(service_);

  scott.utl_dbws.set_target_endpoint_address(call_, 'http://webservices.daehosting.com/services/TemperatureConversions.wso');

  scott.utl_dbws.set_property( call_, 'OPERATION_STYLE', 'document');

  request := sys.XMLTYPE('<celciustofahrenheit xmlns="http://webservices.daehosting.com/temperature"><ncelcius>'||temperature||'</ncelcius></celciustofahrenheit>');

  response := scott.utl_dbws.invoke(call_, request);

  return response.extract('//CelciusToFahrenheitResult/child::text()', 'xmlns="http://webservices.daehosting.com/temperature"').getstringval();

END;

/

show errors;

    b. We can change the line 11 with proxy server details. If your environment does not use a proxy server leave this line out.

    c. Since the database webservice utilities have been installed in 'scott', We have to change all the 'sys' references to webservice utilities to 'scott'

    d. Ensure you can access the webservice used in this example:  http://webservices.daehosting.com/services/TemperatureConversions.wso

    e: Now run the example:

SQL> @celciusToFahrenheit.sql

Function created.

No errors.

SQL> SELECT celciusToFahrenheit(30) from dual;

CELCIUSTOFAHRENHEIT(30)

--------------------------------------------------------------------------------

86

Example 2 :

Now using the same webservice, We will use jpub to create and load the java webservice proxy classes to the database.

    a. Run the jpub to create and load your java webservice client proxy. Here is the command used:

jpub -u scott/tiger -sysuser sys/welcome1 -proxywsdl=http://webservices.daehosting.com/services/TemperatureConversions.wso?WSDL -endpoint=http://webservices.daehosting.com/services/TemperatureConversions.wso -httpproxy=myproxy.com:80

We have specified my proxy again as the last parameter. If your environment does not use a proxy server leave this out.

When using jpub we can avoid coding PL/SQL code. The PL/SQL files can be found in the current local directory where jpub is run from. They have been executed while running the jpub command.

    b. Now call the web Service as in sqlplus.

SQL> conn scott/tiger

Connected.

SQL> select jpub_plsql_wrapper.celciusToFahrenheit(30) as "Celcius To Fahrenheit(30)" from dual;

Celcius To Fahrenheit(30)

-------------------------

86

Running jpub has created the java webservice proxy for all the methods available from the webservice:

SQL> select jpub_plsql_wrapper.fahrenheitToCelcius(86) as "Fahrenheit To Celcius(86)" from dual;

Fahrenheit To Celcius(86)

-------------------------

30

 

For any further clarification, see the following documentation:

Oracle Database JPublisher User's Guide

http://download.oracle.com/docs/cd/B28359_01/java.111/b31226/toc.htm

Database Web Service Callout Utility 10.1.3.1

http://www.oracle.com/technology/sample_code/tech/java/jsp/callout_users_guide.htm