Publishing SQL and DML as Web Service

This morning I have been talking about the Oracle Database and Web Services. If you are Oracle 10g developer (Database or Application Server) you probably already know that you can publish PL/SQL stored procedure as Web Services... One of the new feature of our OracleAS 10.1.3 release is the fact that now you can publish SQL and DML as well. In this post I am quickly explaining the basic steps to publish a query as Web Service.

Oracle Web Service Assembler

If you are not familiar with the Oracle Web Services tools, or if you are using Oracle JDeveloper to generate your services and client, I would like to use the Oracle WS command line utility to do the work here. The Oracle Web Services Assembler, aka WSA, allows you to generate client and server using Oracle JAX-RPC implementation and extensions.

WSA is a Java utility that can be used as command line or with Apache Ant, basically it is a Jar file located in $ORACLE_HOME/webservices/lib/wsa.jar. Type the following command in a terminal to learn more about WSA.

java -jar $ORACLE_HOME/webservices/lib/wsa.jar -help
Usage:
java -jar wsa.jar -<command> -debug -help
where command can be one of:
        analyze
        annotationAssemble
        aqAssemble
        assemble
        corbaAssemble
        dbJavaAssemble
        ejbAssemble
        fetchWsdl
        genApplicationDescriptor
        genConcreteWsdl
        genDDs
        genGatewayService
        genInterface
        genProxy
        genQosWsdl
        genValueType
        genWsdl
        help
        jmsAssemble
        plsqlAssemble
        sqlAssemble
        topDownAssemble
        version

As you can see with the different command options, Oracle WAS allows you to do "everything" Web Services related, for example creating a WS from an EJB using ejbAssemble, from a stored procedure using plsqlAssemble... and I let you guess, we will be using sqlAssemble for this specific demo.

To have the detail of all the parameters of each command you can just enter

java -jar $ORACLE_HOME/webservices/lib/wsa.jar -[command] -help

Generating the Web Service from a SQL

So you can easily generate a Web Service using WSA from multiple SQL statements, to do it, just use the following command:

java -jar wsa.jar -sqlAssemble
                       -appName my-soaql-application
                       -dataSource jdbc/MyDBServices
                       -sqlStatement "getAllEmp=select ename, sal from emp"
                       -sqlStatement "getEmpByDept=select ename, sal from emp where DEPTNO = :{dept NUMBER}"
                       -dbConnection jdbc:oracle:thin:@localhost:1521:orcl
                       -dbUser scott/tiger

So WSA has created the different classes needed by the Web Service but also packaged in a EAR file that you can now deploy to your OracleAS instance. The appName parameter is used to generate the different application name and files (EAR and WAR). The sqlStatement used to specify the different queries you want to publish as operation.

Deploying the application

You can either use Oracle EM Application Server Control to deploy the application or using the command line utility, admin.jar

> java -jar $ORACLE_HOME/j2ee/home/admin.jar ormi://hostName[:ormiPort] oc4jadmin password -deploy -file my-soaql-application -deploymentName my-soaql-application

> java -jar $ORACLE_HOME/j2ee/home/admin.jar ormi://hostName[:ormiPort] oc4jadmin password  -bindWebApp my-soaql-application my-soaql-application-web default-web-site /soaql

Also be sure that you have a datasource defined in your application server that match the parameters set when you ran the WSA command, in my case it will be jdbc/MyDBServices that connection to my local database using the SCOTT schema.

You should now be able to access the service using the following URL:

• http://youserver:port/soaql/my-soaql-application

What is going on?

When you are running the Web Service that is deployed inside OracleAS the flow is quite simple:

1. A client is sending a request to the server using SOAP. So it uses the different typed as defined in the payload
2. The JAX-RPC Servlet processes the request and deserializes the message
3. The generated classes use the OC4J DataSource to connect to the database and execute the statement using JDBC
4. The database sends the data to the classes, and servlet that creates a SOAP response

When you access the test page ( http://youserver:port/soaql/my-soaql-application )  or when you are viewing the generated WSDL you probably notice that each query is published with 3 different operations. These operations return the same data but using different formats:

• <operationName>Bean : returns the data as serialize Javabean
• <operationName>XML : returns the data in the SOAP body as Rowset/Row structure
• <operationName>XMLRowSet : returns the data in the SOAP body using the WebRowset format (JSR-114)

Summary

In this small article you have learnt how to pulish a SQL statement as Web Service. It is interesting to take a look closely to the WSA tools that give you several way of building Web Services, starting from SQL, Stored Procedure, Java, EJB, or using a contract based approach starting from the WSDL.

Oracle JDeveloper 10g (10.1.3) EA 1 available

You can now downlaod the Early Access 1 version of Oracle JDeveloper 10g (10.1.3) from OTN.

Oracle Developer Day: September/October Sessions

Oracle Developer Day is a free, hands-on workshop that will give you insight into the latest technologies in SOA and Java. You can choose from one of two topics:

• Track 1: Emerging SOA and J2EE technologies: EJB 3.0, JavaServer Faces and BPEL
• Track 2: Rapid J2EE Application Development for Forms and Designer Developers

Here the list of dates and cities:

• New York, NY – Thursday, September 29, 2005 – New York Metro Area Oracle User Group Day
• Pasadena, CA – Tuesday, October 11, 2005
• Toronto, Ontario – Tuesday October 18, 2005
• Montreal, Quebec – Thursday, October 20, 2005
• Columbus, OH – Monday, October 24, 2005
• Minneapolis, MN – Thursday, October 27, 2005
• Calgary, Alberta – Tuesday, November 1, 2005

Oracle Open World: Monday

Another nice day in San Francisco; I have been in booth duty most of the day and I have finished the day with a first presentation, with George Trujillo about J2EE Deployment strategy. This presentation I hope was interested for the quite big audience, the long Q&A session -1h, it is nice to be the last in the room- was very good since I talk with various Oracle customer about J2EE management in perspective of the database management. The benefits of Oracle Grid Control for Database and AppServer administrators... I would invite you if you are not familiar with Oracle Grid Control to take a look to OTN.
Some of the interesting point is the annoucement of the new OracleAS 10g R3, that enhanced the Fusion Middleware to deliver SOA, beside the support of J2EE 1.5, Java 5, EJB 30, JSF, I was very pleased to spend my day demonstrating our new UDDI V3 registry that continue to expend our Web Services stack by giving new services in addition to BPEL and Web Services Manager.

Oracle Open World: Sunday

The first Oracle World Day for me has started with finishing the various demonstrations of the J2EE Core OC4J and Web Services demonstration Pods. Tomorrow Monday, we will be showing some very cool stuff around Web Services development and management, with some good news around Web Services Registry. Also on the J2EE Core, nice demonstrations of the different services but some of the new features of the OracleAS 10.1.3 such as the new clustering framework and the usage of scripting technologies, especially Groovy, to administer and monitor OracleAS, thanks to JMX!

What about the rest of the day, I was invited to the Web Services Special Interest Group, WS-SIG, - no.. this is not a new acronym in the WS-* world-. This new SIG has been created in the beginning of the year and on the behalf of IOUG, please take a look to the WSSIG web site. . One of the very interesting things of this SIG is, like the J2EE SIG (Organized by OTUG, IOUG, OAUG and Oracle), developers, architects, and administrators of Fusion Middleware, the Database and Oracle Fusion applications are talking together. This reunion of people is good since we all from development to operations have a different view of systems. So during this panel we have introduced quickly the concepts, and then the audience jumped in for the Q&A session, as you can guess, we add the classical questions around Web Services for the enterprise such as what about security? Management? Life Cycle ?... and so on. We answered obviously, but I would say if you are lucky enough to be at San Francisco this week, please come to the Demo Ground especially Web Services, Web Services Manager and BPEL where you will see the Oracle products in action and you will be able to find experts to help you to answer your questions.

The main auditorium was packed to see the first keynote/show of Oracle Open World 2005, where Oracle presented some of the numerous charity efforts that it does, and offer some fun to the attendees with a nice show from Dana Carvey

What about Oracle Blog dinner?

Mark Rittman is organizing a dinner for the different Oracle bloggers, it will be at the Paragon Restaurant at Second Street on Townsend at 8.00pm on Tuesday 20th. ( I will be there for sure...)

Administer and Monitor OC4J using JConsole

OC4J 10g (10.1.3) is a J2EE 1.4 containers, so as part of the specifications, it supports JMX for management and deployment. One of the benefits of JMX is the fact that finally Java applications, and in our case the J2EE containers, have a standard based interface to be administered. OC4J exposes using its MBean Server system and application lever management beans (MBeans) that you can monitor and control from the Oracle Application Server Control (ASC) that is pre-deployed; but you can use any JMX client application. Sun has included as part of Java 5 JMX but also provides a standard JMX client called JConsole. This post is simply explaining how you can use the Sun's JConsole with OC4J.

1. Set the environment:
   $ORACLE_HOME to the OC4J home
   $JAVA_HOME your JDK home
   
2. Start the OC4J with the following property set
   
   $JAVA_HOME/bin/java  -Dcom.sun.management.jmxremote -jar oc4j.jar
   
   The -Dcom.sun.management.jmxremote system property creates an RMI connector to the MBeanServer, we will use this RMI connector from the console iself.
   
   
3. Start the JConsole, with the following command, adding the OC4J administration class to the classpath.
   
   $JAVA_HOME/bin/jconsole -J-Djava.class.path=$JAVA_HOME/lib/jconsole.jar:$JAVA_HOME/lib/tools.jar;$ORACLE_HOME/lib/adminclient.jar
   
4. The console will automatically ask you to connect to the OC4J process and you can start to monitor and administer your OC4J instance
   
   
   
   Connection to the OC4J MBean Server
   
   
   
   
    Java 5 JConsole browsing the OC4J MBeans
   

Beta Version of Zend Core for Oracle available

Zend Core for Oracle, developed in partnership with Zend Technologies, supports businesses using PHP with Oracle Database for mission-critical Web applications. It provides a seamless out-of-the-box experience delivering a stable, high performance, easy-to-install and supported PHP development and production environment fully integrated with the Oracle Database. Zend Core for Oracle will be available as a free download from Zend in late 2005; it is currently available in Beta. Support and updates for Zend Core for Oracle will be made available directly from Zend. Find more and download link from OTN.