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(''||temperature||'');
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10359218/viewspace-752385/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10359218/viewspace-752385/