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.
First we can add a new css file to the project.
We need to use CSS level 3 and enable ADF faces Extension. ADF will automatically converts this to a level 2 css file. Open the JDeveloper preferences window to change this.
There are some great reference resources for Skinning, the first resource is located in the JDeveloper help.
JDeveloper provides some css tools to help you, but these tool does not work so well. Oracle must take a look at Adobe Flex, that's is working perfectly ( maybe next version). Here is an image of the css structure window.
JDeveloper also has a property window. It will show you all the css properties and not the properties which can be used by component selector.
Here is my example workspace which you can use and test it yourself.
