Type Your Title Here - Oracle
Web Services in Oracle Database 10g and Beyond
Kuassi Mensah, Oracle Corporation
Stephen Jones, Oracle Corporation
Introduction
The promise of Web Services is to allow client applications to easily access remote content and applications over the Web, irrespective of the provider’s or consumer’s platform or language. Web services are pervading every layer of enterprise computing, from packaged e-business applications (e.g., ERP, CRM) to middle-tier (e.g., J2EE, .NET) and database infrastructure.
This Oracle University paper describes how concretely you can develop and deploy basic database Web services. Combined with the Oracle Application Server’s Web Service stack, the Oracle Database 10g can act both as a Web Service consumer and as a Web Service provider. In the former case, database resources such as PL/SQL packages, Java stored procedures, SQL queries, DML statements, and XML data are published through the Oracle Application Server as Web Services. In the latter case you can call out from database queries, triggers and applications, whether written in PL/SQL or in Java, to external Web Services. JDeveloper is Oracle’s main tool for developing J2EE and Web services applications, including Database Web Services, however, in order to give you a feel of what’s going on under the covers, we will be only looking at command-line tools and utilities, namely -- the Web Services Assembler command line utility, which generates Database Web Services -- and the JPublisher tool, which generates Java wrappers for database resources, such as PL/SQL packages, queries, SQL DML statements, or Java stored procedures. Additionally, you can use JPublisher to simplify calling-out to external Web Services from the database. We briefly introduced the main concepts of Web Services. Then we look in detail the steps required for publishing a SQL query, a SQL DML and a PL/SQL package as web service. Next we describe how you can simply invoke external Web Services from inside the Oracle Database.
Web Services Concepts
Web Services are software applications or components identified by a URI that provide a description of their interfaces and binding in XML, and that can interact directly with other Web Services through XML via Internet-based protocols.
Web Services’ use of standard formats and protocols provides independence from a particular implementation language (Java, Managed C++, JScript, Perl, , C#, J#), object model (EJB, COM, and so on), and platform (J2EE, .NET, and so on). At its core, Web Services transmit information represented in XML format over various transports, such as HTTP, FTP, SMTP, JMS, etc. Currently, most Web Services accept the firewall-friendly HTTP or HTTPS format. The XML itself is formatted according to the XML Schema Definition [3]. The SOAP messaging protocol [4] defines the general shape as well as processing rules for messages that flow between Web Services. For a given Web Services endpoint (or port), the format of messages that are transmitted to it and with which it may replies are set down it its Web Services Description Language (WSDL [5]) document. Specifically, this includes the permitted operations with parameter and return types and instructions on how to represent programming language bindings in XML format. Typically, you utilize the WSDL for a particular Web Service in order to interact with that service, either via a dynamic invocation capability in your programming platform, or via a generated static client proxy for your programming language. When creating a Web Service based on a programming language artifact, such as a Java class or a PL/SQL package, the WSDL will typically be generated for you. Finally, the UDDI standard [6] provides a standardized registry facility for locating Web Services and their descriptions. At one level you can treat Web Services as a specific flavor of XML messaging. However, given the complexity and the evolving nature of the Web Services practices and standards, you will typically rely on a Web Services technology stack and rely on tools to publish Web Services and their clients. This allows you to minimize changes to your own code while maximizing interoperability with other Web Services.
Database As Web Services Provider
The Oracle Database provides a number of resources and capabilities, ranging from –of course– SQL to PL/SQL and Java stored procedures to the XML capabilities of the XML Database (XDB) to Advanced Queueing (AQ) and Streams. In order to expose those resources to Web Services we generate the corresponding Java wrappers and deploy these wrappers to the Oracle Application Server 10g, which provides a standard, scalable J2EE container together with a full J2EE Web Service Framework.
Setting up OC4J
As explained, we leverage the J2EE and Web Service stack, OC4J, in Oracle Application Server – even a stand-alone OC4J is sufficient. If the database is not running locally, modify the J2EE_HOME/data-sources.xml file so that the data-source "jdbc/OracleDS" points to the running database, for instance,
Go to the J2EE_HOME directory and start the OC4J instance standalone.
% java -jar oc4j.jar
the Web Services Assembler
The Oracle Application Server permits you to create J2EE flavored Web Services using J2EE components, as well as on Java classes and on database resources, such as PL/SQL packages, SQL queries and DML statements, and Java stored procedures. The Web Services Assembler tool in the 10g Oracle Application Server is used for creating Web Services as J2EE compliant EAR files that are deployed in OC4J. It transparently invokes the JPublisher utility for generating Java code corresponding to the respective Web Service.
Web Services Assembler Configuration Files
Inputs to the Web Service Assembler are provided through XML-based configuration files: service-config.xml and client-config.xml.
Service Configuraation File
WHENEVER YOU WANT TO EXPOSE A DATABASE RESOURCE, YOU WOULD NEED TO PROVIDE INFORMATION SUCH AS THE FOLLOWING IN THE CONFIGURATION FILE.
/statelessSP
scott/tiger
jdbc:oracle:thin:@localhost:5521:sqlj
jdbc/OracleDS
jpub.omit_schema_names
jpub.tostring=true
The database resource that is published as a Web Service …
Client Configuration File
SIMILARLY TO THE WEB SERVICE CONFIGURATION FILE, A CLIENT CONFIGURATION FILE IS USED FOR GENERATING THE JAVA CLIENT STUBS.
mapping.xml
build/src/client
oracle.demo.db.query.stub
build/classes/client
Building your First SQL Query and SQL DML Web Services
Motivations
The sky's the limit here. SQL offers amazing possibilities for retrieving, updating, and storing any data that can be held in your database, including relational, text, spatial, multimedia, and XML data. Consider Amazon Web services ( webservices) or Google Web services (apis/index.html). These let client applications discover and interact with their catalogs or search engines using standard Web services protocols (WSDL, SOAP). You should consider implementing coarse-grain services using SQL statements only when the benefits outweigh the SOAP overhead. SQL database Web services will allow client applications and lightweight SOAP enabled appliances to, for example, query a catalog or retrieve the map of a location based on the ZIP code.
Step #1-a Assemble the Query Web Service
In order to create a Web Service based on a SQL query statement, you place your SQL code in the configuration file. The following fragment of service-config.xml describes the query in question and database information.
./build/query.ear
/query
jdbc/OracleDS
scott/tiger
oracle.demo.db.query
jdbc:oracle:thin:@OW-pc.us.:1521:odb
getEmp
select ename from emp
where ename=:{myname VARCHAR}
Step#1-b Assemble DML Web Services
Similarly to SQL Query Web Services, to create a Web Service based on a SQL DML statement (INSERT, UPDATE, DELETE), you place your SQL code in the service configuration file. The following fragment of service-config.xml describes the DML operation in question.
updateEmp
update emp SET sal=sal+500
where ename={myname VARCHAR}
Web Services in general –and Database Web Services in particular– are right now, mostly stateless, the generated JPublisher code must automatically either commit a successful DML operation or roll it back in the case of failure.
Batch execution of multiple DML operations can be specified in service-config.xml. Both single or multiple-update operations return information on success or failure and –if the former– on the number of rows affected.
Both Query and DML web services operations can be specified in the same service configuration file.
getEmp
select ename from emp
where ename=:{myname VARCHAR}
updateEmp
update emp SET sal=sal+500
where ename={myname VARCHAR}
Step#2 Generating the Web Service ear file
The following command will generate an ear file containing the Java wrapper corresponding to the database operation.
% java -jar /webservices/lib/wsa.jar -config service-config.xml
The following files will be generated.
• dist/query.ear, the webservices application
• build/src, subdirectory holding client proxy source code
• build/classes, subdirectory holder client proxy .class files.
Note: the bind variables {myname VARCHAR} in the SQL statements will be turned into corresponding string parameters of the Web Service operations. Note that the query is returned as a result set, which can be materialized as an array of structured row values or in various generic XML formats.
Step#3 Deploy the Generated .EAR File to OC4J
The following command will deploy the ear file generated in the previous step, to OC4J.
% java -jar /j2ee/home/admin.jar ormi://: admin -deploy -file build/query.ear -deploymentName query
hostname : is the host where OC4J is running
port is the RMI port
Step#4 Binding the Deployed Application
OC4J has the notion of http-web-site, to which servlets and web applications need to be bound. The following command binds the application to http-web-site
% java -jar /j2ee/home/admin.jar ormi://: admin -bindWebApp query query-web http-web-site query
At this stage, the web service has been created; however, in order to use it or see it in action, you need a client application. J2EE-based Web Services frameworks such as Oracle Application Server, simplify web service programming by generating a client-proxy from the WSDL; this proxy is used by the client-application.
Step#5 Generate the Client Proxy
Client Configuration File
SIMILARLY TO THE WEB SERVICE CONFIGURATION FILE, A CLIENT CONFIGURATION FILE IS USED FOR GENERATING THE JAVA CLIENT STUBS.
mapping.xml
build/src/client
oracle.demo.db.query.stub
build/classes/client
The following WebServices assembler command, generates the client proxy in the subdirectory build/classes/client
% java -jar $ORACLE_HOME/webservices/wsa.jar -config client-config.xml
Step #6 Compiling and Running the Client Application
Compile and run the sample client program in src/client/oracle/demo/db/query/QueryClient.java
using the ANT commands:
% ant compile-client
% ant run-client
This client program uses the client proxy, generated in Step #4 to invoke the WebServices operations.The target "run-client" will give the following output:
[java] *** Query Emp Rows by ID returns 1 rows
[java] *** Query Emp Rows by ID returns
Step#7 Access the Services via Web Browser
Alternatively, for testing purposes, the services can also be accessed via a browser
• The getEmpBeans service returns the information about an employee by name. Enter "SCOTT" and press Invoke to see the returned message.
• The getEmpByNoBeans service returns the information about an employee by employee id. Enter "7900" and press Invoke to see the returned message.
• The getEmpBySalBeans service returns the information about employees with salaries beyond the specified amount. Enter "3500" and press Invoke to see the returned message.
• The getEmpCountBeans service returns the number of employees recorded in the table. Press Invoke. The return message should give 14 as the count of the emp table.
Building Your First PL/SQL or Java Web Services
Motivations
Stored procedures are an essential database-programming model that allows a clean separation of data-centric logic that runs in the database from business logic that runs in the middle tier. Stored procedures can be programmed in a database-specific language, e.g. PL/ SQL, or in a portable and database-independent language such as Java. The ANSI SQLJ Part 1 specifies the ability to invoke static Java methods from SQL as procedures or functions. The beauty of Web services is that you can leverage your investment in stored procedures and expose these as Web services without having to worry about the language in which the stored procedures are implemented.
Step#1-A Assemble PL/SQL Web Services
For exposing a PL/SQL package as a Web Service we require the name of the database package to be published. Optionally we can provide a list of method names if only a subset of its functions and procedures should be included.
Company
method1
method2
Step#1-B Assemble DB-Java Web Services
Usually you would base your Web Service on a Java class that is running in the Application Server middle tier. What can you do if you want to base your service on Java code that is running in the Oracle Database JavaVM? While it is possible to expose your code through PL/SQL call specs and then publish that as a PL/SQL Web Service, it is more convenient to expose the Java class directly as a Web Service. In this case you would have to specify a tag similar to the following to describe the database resource in your Web Services Assembler configuration file.
foo.bar.Baz
method1
method2
JPublisher, and thus Web Services Assembler, can only publish Java methods that meet the following criteria. They must be public static methods. Any Java type that is used in a parameter, a return, or an exception must be serializable. Additionaly, all such types must exist and be defined in the same way in both, client and the server Java environments. Taking advantage of native Java invocation simplifies use of Java array and Java Bean types in method signatures – there is no need to provide PL/SQL call specs or to invent special SQL object or VARRAY types to represent such values.
Steps#2-7
Same as SQL query Web Services.
Database as Web Service Consumer
In order for code running in the database to access other Web Services, such as stock quotes, weather information, Web search results, scientific data, or enterprise data available through Web Services we need to be able to call out to these services.
Build Your First “Database as Web Services Consumer” Application
In order to consume or call-out to an external Web Service from within the database, you can deploy your own service as described in Part-I of this paper or use an existing one.
Step# 1 Setting Up the Database
In Oracle Database 10g, the libraries sqljutl.jar and utl_dbws_jserver.jar from
[Oracle Home]/sqlj/lib, as well as the SYS.UTL_DBWS package come preloaded into the database at install time.
If you are running an Oracle database prior to 10g production release, you need to load ${ORACLE_HOME}/sqlj/lib/sqljutl.jar and ult_dbws_jserver.jar into the database. One way to determine whether sqljutl.jar has been loaded is to describe the procedure "utl_dbws_validate" under SYS, which should have the following response:
SQL> conn / as sysdba
Connected.
SQL> describe utl_dbws_validate;
PROCEDURE utl_dbws_validate
To load the jar file into the server, perform the following steps. Make sure the database has java_pool_size and shared_pool_size equal or greater than 96M and 80M respectively. If not, modify the database pfile, e.g., init.ora, for the two entries, and restart the database.
Run the script sql/initdbws.sql or sql/initdbws9.sql under SYS:
1. For 9i databases, run initdbws9.sql
SQL>conn / as sysdba
SQL>@initdbws9
2. For 10g Beta databases, run initdbws.sql
SQL>conn / as sysdba
SQL>@initdbws
3. Run the script [ORACLE_HOME]/sqlj/lib/sqljutl.sql under SYS, i.e.,
SQL> conn / as sysdba
SQL> @sqljutl.sql
If you are using Java, you may want to program with the JAX-RPC Dynamic Invocation Interface (DII) API directly. In this case you just have to place your web service client code into the server-side JavaVM and are done. Alternatively, you may prefer to invoke the external Web Service through statically generated client proxy code.
Step#2 Generating and Loading Client Proxy and Wrapper
JPublisher [10] fully automates the tasks of generating the client proxy code, compiling it, jarring it up, and loading it into the database. For example, it is sufficient to provide the following command line.
jpub -proxywsdl=URL_of_Web_Service_WSDL -user=username/password
Additionally, you can specify –endpoint=external_Web_Services_URL if this is different from what is provided in the WSDL itself, and –url=JDBC_database_URL if different from the default database instance accessed with the OCI driver.
% jar xvf dist/javacallout.jar META-INF/HelloServiceEJB.wsdl
% jpub -proxywsdl=META-INF/HelloServiceEJB.wsdl -dir=genproxy -package=javacallout -user=scott/scott -endpoint=
JPublisher will generate the static Java proxy classes and PL/SQL scripts, under the subdirectory genproxy, and automatically load the underlined files into the database.
1. genproxy/javacallout: Java classes under this directory are generated by the wscompile tool called by JPublisher.
2. genproxy/HelloServiceEJBJPub.java: The HelloServiceEJBJPub.java class defines the method sayHello as a static Java method, in order to be invoked from the PL/SQLwrapper.
3. genproxy/plsql_wrapper.sql: The PL/SQL wrapper for the web service client (i.e., HelloServiceEJBJPub.java.)
4. genproxy/plsql_dropper.sql: remove PL/SQL types and packages defined by plsql_wrapper.sql.
5. genproxy/plsql_grant.sql: when run under SYS, grant necessary priviliges for the Java client proxy to call the Web Services.
6. genproxy/plsql_revoke.sql: revoke the privileges granted by plsql_grant.sql.
7. genproxy/jpub_proxyload.log: the log file for loading those generated Java files and install plsql_wrapper.sql script.
To prevent JPublisher from loading the generated code into the database you can specify the option -proxyopts=noload. The advantage of using static client proxies is that you can simply reference port-type instance methods in your Java code without worrying about how to marshal or unmarshal individual arguments from the SOAP message.
Step#3 Granting Privileges
The script plsql_grant.sql is for SYS to grant necessary privileges for SCOTT to execute the loaded client proxy.
SQL> conn / as sysdba
SQL> @genproxy/plsql_grant.sql
Step#4 Calling-Out the External Web Services
To invoke the external WebServices from the database, declare and run the sql/run-plsql-proxy.sql script or run the PL/SQL block below.
SQL> set serveroutput on
SQL> declare
x varchar2(100);
begin
x:=JPUB_PLSQL_WRAPPER.sayHello('Hello from database');
dbms_output.put_line(x);
end;
/
Step#5 Clean Up
Remove the PL/SQL wrapper and revoke the granted privileges using SQLPLUS.
SQL> conn scott/tiger
SQL> @genproxy/plsql_dropper.sql
SQL> conn / as sysdba
SQL> @genproxy/plsql_revoke.sql
Remove the loaded Java classes from the database using the dropjava tool.
dropjava -u scott/tiger genproxy/wsdlGenerated.jar
Conclusion
We have provided an introduction to the main concepts of Web Services We then explored in detail how to expose a SQL Query, SQL DML, PL/SQL package and Java-in-the-database as Web Services. Next, we examined how external Web Services can be invoked from Java, SQL, and PL/SQL code inside the Oracle Database. We invite you to attend the “Web Services in Oracle Database 10g and Beyond” session for a more complete coverage of Database Web Services. .In addition, the Oracle Technology Network [15,16] for more information on Database Web Services and Grid Services.
References
[1] Database Web Services, An Oracle White Paper; November 2002. Available at:
[2] Web Services Journal Feature: Web Services Enable Your Database; Kuassi Mensah; Web Services Journal, vol.3 no.4. Available at:
[3] W3C XML Schema Specification.
[4] W3C Recommendation: SOAP Version 1.2.
[5] W3C Note: Web Services Description Language (WSDL) 1.1.
[6] Oasis UDDI Version 3.0.
[7] GGF: Open Grid Services Infrastructure.
[8] JAX-RPC Specification.
[9] Technical How To: Loading SOAP Libraries.
[10] Oracle9i JPublisher.
[11] Technical How To: Using Table Functions.
[12] JSR 114: JDBC Rowset Implementations.
[13] INCITS H2.3 Task Group.
[14] Oracle XML SQL Utility – XSU.
[15] OTN Web Services Center.
[16] Database Web Services.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.