Create PL/SQL Webservice in JDeveloper 11g and eclipselink

In Late 2007 I already made a blog about creating a webservice in JDeveloper 11g TP based on a plsql package. Too bad the plsql web service creation option didn't make it in the final release of 11g. Maybe in the next release of 11g the plsql webservice will make a comeback. But creating a plsql webservice in the first jdeveloper 11g release is still possible. We will use eclipselink and some smart jdeveloper wizards to create this web service. This way of creating a web service gives you a lot of options how this plsql web service is created.

This is the pl/sql package I will use in this example. The package contains a current time funtion and a procedure which returns all the dept records of the scott scherma. Add this package to the scott schema

create or replace package scott_ws is

TYPE cursor_type IS REF CURSOR;

function get_current_time(date_format varchar2) return varchar2;

procedure get_all_department(dept_recordset OUT scott_ws.cursor_type);

end scott_ws;

create or replace package body scott_ws is

function get_current_time(date_format varchar2) return varchar2 is
v_time varchar2(20);
begin
if date_format is null then
  return 'empty format';
end if;
select to_char(sysdate, date_format) into v_time from dual;
return v_time;

exception
when others then
  return 'error';
end;

procedure get_all_department(dept_recordset OUT scott_ws.cursor_type) is
begin
OPEN dept_recordset FOR SELECT * FROM dept;
end get_all_department;


end scott_ws;

Now we can create a new TopLink project



We will use the eclipselink provider and use or create the scott database connection
JDeveloper creates a new project with a Toplink project file and a session xml. Open this xml where we use the default profile and select the Login link.
If you want to use a datasource then select managed datasource option and provide the datasource name ( this datasource has to be created in WebLogic )

Next we can create a session bean which wil contain the code which make a connection to the database using the default profile of the session xml and the eclipselink code for our plsql procedure / function

Give this bean a name and use the default values.

Implement a remote and local interface on the session bean

The next option is only necessary when we want to map the dept record to a dept java class. So the web services can use this class to transform these departments to xml.
Select the java objects from tables option in the TopLink/JPA category

Use the scott connection again

Select the dept table


The dept class looks like this.

package wsscott.definitions;

import java.io.Serializable;

import java.math.BigDecimal;

/**
*  ###  Generated by Oracle JDeveloper 11g 11.1.1.0.31.51.88 - Mon Jan 12 23:08:08 CET 2009.  ###
*/

public class Dept implements Serializable {

 private BigDecimal deptno;
 private String dname;
 private String loc;

 public Dept() {
 }

 public Dept(BigDecimal deptno, String dname, String loc) {
     this.dname = dname;
     this.deptno = deptno;
     this.loc = loc;
 }

 public BigDecimal getDeptno() {
     return this.deptno;
 }

 public String getDname() {
     return this.dname;
 }

 public String getLoc() {
     return this.loc;
 }

 public void setDeptno(BigDecimal deptno) {
     this.deptno = deptno;
 }

 public void setDname(String dname) {
     this.dname = dname;
 }

 public void setLoc(String loc) {
     this.loc = loc;
 }

}

Here an overview of the toplink project.

Open your sessionbean class and add the following code to this bean

This code is necessary to create a connection to the database. In the sessions.xml file and in the default profile is our scott database connection or datasource defined

public class ScottSessionEJBBean implements ScottSessionEJB,
                                         ScottSessionEJBLocal {

 private SessionFactory sessionFactory;

 // constructor
 public ScottSessionEJBBean() {
     this.sessionFactory = new SessionFactory("META-INF/sessions.xml", "default");
 }
}

Here the code for the get_current function. See the eclipselink wiki for more plsql examples. Because we are calling an function we need to use StoredFunctionCall

 public String plsqlCurrentTime(String format) {
     StoredFunctionCall functionCall = new StoredFunctionCall();

     // function name in package
     functionCall.setProcedureName("scott_ws.get_current_time");
     // input parameter name
     functionCall.addNamedArgument("date_format");
     // function result datatype
     functionCall.setResult("FUNCTION_RESULT", String.class);

  
     ValueReadQuery query = new ValueReadQuery();
     query.setCall(functionCall);
     query.addArgument("date_format"); // input

     // create an argument list and add a value for the date_format
     List args = new ArrayList();
     args.add("YYYY-MM-DD");

     // get an database session
     Session session = sessionFactory.getSharedSession();
   
     // execute function and return the result
     return (String)session.executeQuery(query, args);
 }

The java code for the procedure is a bit more complex then the function. This methods return a list of dept classes.

 public List plsqlDeptFindAll() {

     // create a database session     
     Session session = sessionFactory.getSharedSession();
  
     StoredProcedureCall spcall = new StoredProcedureCall();
     spcall.setProcedureName("scott_ws.get_all_department");
     // this procedure has only one output parameter
     spcall.useNamedCursorOutputAsResultSet("dept_recordset");
  
     // make a new list of departments classes
     List department = new ArrayList();
     // execute the procedure.
     List list = session.executeSelectingCall(spcall);
  
     // make an iterator of the ref cursor result
     ListIterator litr = ((List)list).listIterator();
     while(litr.hasNext()) {
      
         // when a the iterator has a next value then create
         // a new dept class and fill its attributes
         Dept dept = new Dept();
         DatabaseRecord record = litr.next();
         dept.setDeptno( (BigDecimal)record.get("DEPTNO") );
         dept.setDname(  (String)record.get("DNAME") );
         dept.setLoc(    (String)record.get("LOC") );
         department.add(dept);
     }
     return department;
 }

Add these two plsql methods to the remote and local ejb interface. After this we can select the EJB session bean and create a new web service

Choose a web service deployment profile

Give this web service a name
Just select our two plsql methods


The wizard will also create a new web service deployment. Use this deployment profile to deploy your ejb web service to WebLogic
When the deployment is successfull then we can take a look in the weblogic console. Go to deployments tab and open you enterprise application.

Select your Web Service to see the wsdl.

That's all, just generate a web service proxy to test this web service.
Here is the 11g project code.