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.
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
public ListplsqlDeptFindAll() {
// 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
Listdepartment = new ArrayList();
// execute the procedure.
List list = session.executeSelectingCall(spcall);
// make an iterator of the ref cursor result
ListIteratorlitr = ((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;
}
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.
