Pages

Tuesday, January 13, 2009

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.

29 comments:

  1. I followed exactly the instructions, but when I test whe web service, I get the error (rigth click on ScottSessionEJBBean.java - test web service) in both methods:


    faultcode S:Server
    faultstring java.lang.NullPointerException

    Do you have any idea?

    my system:
    Windows xp
    Jdeveloper Studio Edition Version 11.1.1.0.1
    Build JDEVADF_MAIN.BOXER_GENERIC_081203.1854.5188
    Database Oracle 10g 10.2.0.1.0

    ReplyDelete
  2. Hi where do you get this error.

    I know that if you try to test the ws in wls then this is a know bug.

    thanks

    ReplyDelete
  3. Hi Edwin,

    Can we able to deploy the PL/SQL webservices in weblogic server9.2?

    Is that possible to deploy the existing OAS PL/SQL webservices in weblogic server9.2?

    If so do we need any migration steps ?

    Regards,
    Latha

    ReplyDelete
  4. Hi,

    if your ws services are based on jax-rpc then it should work ,maybe you have to change the datasource name.

    ws made in jdev 11g uses jax-ws I don't think this work.

    good luck

    ReplyDelete
  5. Hi Edwin,

    Our jdeveloper version is 10.1.3.4 and weblogic server version is 9.2.

    I have installed BPEL PM in Weblogic server9.2.

    I tried to devlop & deploy the PL/SQL webservice in jdeveloper using JAX- RPC method. but i am not able to deploy.

    I am getting "NoclassDefFoundError WS_importjava/WsexchangeRtCollTypeIn" error while we deploy the PL/SQL ws in weblogic server 9.2.

    I have raised the SR also. But no use. Oracle answer is " We cannot able to deploy PL/SQL webservices in Weblogic server9.2".


    If you succeded in deploying the Pl/SQL webservices in weblogic server, please let me know the steps.


    Regards,
    Latha

    ReplyDelete
  6. Ok,

    I will give it a try to run a 10.1.3.4 plsql ws on wls 10.3. I don't have wls 9.2

    thanks

    ReplyDelete
  7. Hi,

    I Developed a plsql webservice with jdev 10.1.3.4 and made an war/ear.

    I deployed this on the wls 10.3 of jdeveloper 11g. It deploys perfectly

    now I generate a ws proxy client in 10.1.3.4 to test this ws,

    and it works perfectly

    thanks

    ReplyDelete
  8. Hi Edwin,

    Thanks and do we need any other special steps?


    Regards,
    Latha

    ReplyDelete
  9. Hi,

    No special steps are needed. Just develop in 10.1.3.4 make a ear of your ws and deploy this on a wls 10.3 with or without the ADF runtime installed ( I didn't test this with a default wls 10.3 ).

    thanks

    ReplyDelete
  10. Then i tried to call
    return (String)session.executeQuery(query,args);
    i've got
    "Error(100,33): method executeQuery(oracle.toplink.queryframework.ReadAllQuery, java.util.List) not found in interface org.eclipse.persistence.sessions.Session"

    The same result i've got using ReadAllQuery;
    Could you help me?

    ReplyDelete
  11. Hi,

    Did you add a new method to the session bean. Then you need to do this also in your local or remote sessionbean interface.

    In the ejb client you are using the the remote or local interface.

    thanks Edwin

    ReplyDelete
  12. Hi Edwin,
    Im trying to create web service from PL/SQL package and deploy it to Weblogic Server 10g. But im not able to create PL/SQL web server in Jdeveloper 11g(11.1.1.0). its giving SQL Exception. But this works perfectly in JDev10g on OC4J.

    ReplyDelete
  13. Hi Arun,

    Can you give me more details about the error. I developed a plsql ws in 10.1.3.4 and made a war and deployed this on wls server with ADF libs.
    It works perfectly

    thanks

    ReplyDelete
  14. Hi Edwin,

    This is the error im getting.

    Redeploying Application...
    [Deployer:149034]An exception occurred for task [Deployer:149026]deploy application /PL_SQL_WS-GetEmployee-context-root on DefaultServer.: [HTTP:101216]Servlet: "EmpWebServiceSoapHttpPort" failed to preload on startup in Web application: "PL_SQL_WS-GetEmployee-context-root.war".
    java.sql.SQLException: Error looking up <--java:comp/env/jdbc/hrconnDS-->:
    at hrconn.EmpWebServiceBase.<--init-->(EmpWebServiceBase.java:33)
    at hrconn.EmpWebServiceUser.<--init-->(EmpWebServiceUser.java:11)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

    ReplyDelete
  15. Hi Edwin,

    It worked finally. Its the data source config which I didnt make in Weblogic. Since in JDev 10g with OC4J the datasources are created by default.

    thanks,
    Arun

    ReplyDelete
  16. Thanks for this! I tried it already

    ReplyDelete
  17. Hi,

    there is great news with Jdeveloper 11.1.1.1. "The PL/SQL Web Services wizard that was removed from the JDeveloper 11g (Boxer) release due to incompatibilities with the WLS server have been fully restored."

    best regards,
    Andreas

    ReplyDelete
  18. Hi Andreas,

    Thanks for the tip. I downloaded the latest Jdeveloper 11.1.1.0 (JDEVADF_11.1.1.1.0_GENERIC_090615.0017.5407). The PLSQL Web Service wizard is certainly back and fairly easy to use. However I was unable to create a successful project.

    The Wizard generation exits with the following error:
    QUOTE:
    WSDL and Mapping file generation failed for the following reason.

    Java.lang.NullPointerException
    at oracle.j2ee.ws.common.tools.wscompile.CompileTool.validateSeiImplementationUsingJot(CompileTool.java:421)
    at oracle.j2ee.ws.common.tools.wscompile.CompileTool.withModelHook(CompileTool.java:382)
    at oracle.j2ee.ws.tools.wsa.AssemblerTool.withModelHook(AssemblerTool.java:69)
    at oracle.j2ee.ws.tools.wsa.AssemblerTool.run(AssemblerTool.java:129)
    at oracle.j2ee.ws.tools.wsa.JavaToWsdlTool.run(JavaToWsdlTool.java:297)
    at oracle.j2ee.ws.tools.wsa.JavaToWsdlTool.createWSDL(JavaToWsdlTool.java:191)
    at oracle.j2ee.ws.tools.wsa.Util.createWSDL(Util.java:1178)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at oracle.jdeveloper.webservices.Assembler$2$1.invoke(Assembler.java:234)
    at $Proxy35.createWSDL(Unknown Source)
    at oracle.jdeveloper.webservices.model.plsql.generator.CreateWSDL.generateWSDL(CreateWSDL.java:138)
    at oracle.jdeveloper.webservices.model.plsql.generator.CreateWSDL.createWSDL(CreateWSDL.java:53)
    at oracle.jdeveloper.webservices.model.generator.CommonCreateWSDL.action(CommonCreateWSDL.java:59)
    at oracle.jdeveloper.webservices.model.generator.GeneratorAction.run(GeneratorAction.java:148)
    at java.lang.Thread.run(Thread.java:619)
    UNQUOTE

    ReplyDelete
  19. Hi Edwin,

    Your Blog Rocks! Literally you help a ton of people here.

    We have one question. We need to kick off an 11g composite from PL/SQL. We can kick it off but not get a response back using the PL/SQL package UTL_HTTP.

    Any ideas or code examples on how we could do this?

    Thanks!

    ReplyDelete
  20. Hi,

    is your soa 11g service a synchronous web service, if not then I can explain your problem, I don't think you can call a asyn web service from the db ( maybe with dbms_ws )

    thanks Edwin

    ReplyDelete
  21. I meet error as following when testing webservice.

    '409 HTTP Analyzer Conflict'

    Have you met before?

    ReplyDelete
  22. Hi,

    Did you have the http analyzer of jdev between the test and ws.

    if so disable the http analyzer or use soapui. a great free tool

    thanks

    ReplyDelete
  23. Need help on the exception below. Appreciate your help.

    Thanks
    Sanjeev

    Hi All,

    I have created the Custom SOA Object using the Iparser.
    Now i am trying to provide the grant through the SOA repository.
    When I click create,the its failing
    I am getting the following error when I click a CREATE GRANT button for a web service that has been deployed.

    Exception Details.
    oracle.apps.fnd.framework.OAException: oracle.jbo.NoDefException: JBO-25058: Definition isOverloaded of type Attribute not found in AdminMethodsVO
    at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1247)
    at oracle.apps.fnd.framework.webui.OAPageBean.renderDocument(OAPageBean.java:3136)
    at oracle.apps.fnd.framework.webui.OAPageBean.renderDocument(OAPageBean.java:2931)
    at _OA._jspService(_OA.java:228)
    at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
    at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:379)
    at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
    at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
    at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
    at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
    at com.evermind.server.http.ServletRequestDispatcher.unprivileged_forward(ServletRequestDispatcher.java:259)
    at com.evermind.server.http.ServletRequestDispatcher.access$100(ServletRequestDispatcher.java:51)
    at com.evermind.server.http.ServletRequestDispatcher$2.oc4jRun(ServletRequestDispatcher.java:193)
    at oracle.oc4j.security.OC4JSecurity.doPrivileged(OC4JSecurity.java:284)
    at com.evermind.server.http.ServletRequestDispatcher.forward(ServletRequestDispatcher.java:198)
    at com.evermind.server.http.EvermindPageContext.forward(EvermindPageContext.java:395)
    at _OA._jspService(_OA.java:221)
    at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
    at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:379)
    at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
    at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
    at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
    at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26)
    at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
    at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:318)
    at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621)

    ReplyDelete
  24. Hi Edwin,

    I tried creating a web service from pl/sql package in 10.1.3.4. Here is my problem posted. Can you please help me?

    http://forums.oracle.com/forums/thread.jspa?threadID=2126568&tstart=0

    Thanks in advance.

    ReplyDelete
  25. I have deployed a plsql web service on weblogic 11g and it works fine. However, sometimes after shutdown/restart of the application server, the webservice fails to start automatically with error "java.sql.SQLException: Error looking up :". The manual start of the webservice also fails. What I do is to go to the configur section of the data source and save it without any changes. Then I can start the web service. Could you advise me about this problem?
    Thanks,
    Majid

    ReplyDelete
    Replies
    1. Hi,

      can you change the deployment order or enable it should wait for the resource are available. This should be options which you can configure in your weblogic console.

      thanks

      Delete
  26. Hi Edwin -

    I'm using Jdevelpoer 11g web service wizard and exposing PL/SQL package as web service.I'm using function inside package which returns XMLTYPE.

    Inside db function I'm using SQLX functions and generate XML. The web service is working fine.

    The issues is web service consumer wants WSDL file with appropriate schema and our WSDL file doesn't have one (since XMLTYPE is function output).

    How can we generate appropriate WSDL file with proper schema? Appreciate your help.

    ReplyDelete
    Replies
    1. Hi,

      you mean the client needs a XSD or is schema types inside the WDSL is not enough. And is your response an anyType or a String.

      what you can do, is to make a contract first wsdl. design your own wsdl and generate java on this wsdl and then call the already generated EJB.
      http://biemond.blogspot.nl/2011/08/contract-first-web-service-with.html

      thanks

      Delete
  27. Hi Edwin,

    I'm trying to get this to work. Thanks in advance for the blog. Just noticed a couple of typos at the beginning (funtion and scherma).

    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

    John Zamow

    ReplyDelete