JDeveloper 11g – Creating PL/SQL Webservice and deploying to Weblogic Server


You can expose PL/SQL stored procedures as Webservice via JDeveloper(which wraps the PL/SQL code into Java wrapper), and you can deploy it into weblogic server.

1. Open JDev 11g and create a database connection to HR schema(a sample schema that contains the PL/SQL procedure).

1
2

2. Create a generic application PL_SQL_WS and project name GetEmployee.

3
4

3. From the database connnection navigator right click the PL/SQL package and publish as webservice. Click next and accept the default

6
6
6

4. Accpet the default and start once finished start the Weblogic Server, here im using the embedded weblogic server in the JDeveloper 11g.

5. Login into Weblogic console and create a datasource connection.

Name – hrconnDS
JNDI Name – jdbc/hrconnDS
Donforget to DS suffix.
6

6. Deploy the application to the Weblogic Server from JDeveloper11g.

7. Test the Webservice from Weblogic console, through the Weblogic test client.

27 comments

  3. Hi,

    I was waiting this feature for a while. Do you know if this feature works fine if the database es Oracle 9i? Thanks.

    Reply

  4. Hello,

    I want to Publish the Webservice having complex structure say given a department code ( from dept table) the web service should send the employees (emp table) of the dapartment and qualifications ( emp_qualification table each emp may have more than one) of each employee as PL sql web service. I am only finding the examples like given emp code, send the salary. I am not able to get a example of complex one in PL sql. Is it possible to publish above said one?

    Thanks

    Reply

  6. Hello,

    I could publish Web serivce of question raised by me on Sep 18th 2009 using Jdeveloper and oc4j.

    Now I am looking for PL Sql procedure and/or JSP which will consume web service and store the consumed data into table(s).

    Can I get any example for this type.

    Thanks in advance.

    Reply

  7. Hi,

    I’m new developing web services and using jdeveloper. I followed your tutorial and now i want to secure this web services requesting username and password. Can you help me out?

    Thanks

    Reply

  9. Hai,,

    First of all so many thanks for your blog about Webservices Deployment, It was very helpful for me …as i am new to this…

    Do you know if this feature works fine oracle AS 10gr3 and Jdeveloper 10g

    Also I would like to know to get some help or scope regarding to apply the web sevices for data entry,

    loging to database schema , making tabular reports using oracle reports using webservice etc…

    Thanks

    Faisal

    Reply

  10. Hello,

    I am new to Jdeveloper11g. I need some help from you. I am lookig to use Jdeveloper as a front-end, experss database as a back-end and integrate the RFID device for automatic access and save the information such as ID, time loggin and logout to the database.

    A typical example of this can be a student attendance application.

    Would you help me on this? thank you for your assistance.

    Reply

  11. Is it possible to deploy the PL/SQL Web Services to Web Logic as both SOAP and REST, as we currently do when deploying to OC4J? We’ve ported some Web services to WebLogic successfully using SOAP but cannot see any REST deploy option.

    Reply

  12. Hi Arun,

    This is suresh kamma , i am working on jdeveloper 11g,I have a XSD shema’s, i want to do develope java web services.do you have any idea about XSD’s binding with JAXB.i want to develope web services using Schema’s and deploying in weblogic server

    Regards,
    Suresh

    Reply

  13. I tried this sample. Here is the error when I tried
    http://10.172.20.70:7101/PL-SQL-Application-getBillingList-context-root

    Error 403–Forbidden
    From RFC 2068 Hypertext Transfer Protocol — HTTP/1.1:
    10.4.4 403 Forbidden
    The server understood the request, but is refusing to fulfill it. Authorization will not help and the request SHOULD NOT be repeated. If the request method was not HEAD and the server wishes to make public why the request has not been fulfilled, it SHOULD describe the reason for the refusal in the entity. This status code is commonly used when the server does not wish to reveal exactly why the request has been refused, or when no other response is applicable.

    Reply

  14. Hi Arun, I want to create a web service using PL/sql & jdev

    and return list of all the emp no and names of the employee,
    how do i do this, ref curser type is not supported in Jdev.

    Thanks in advance.

    Reply

    1. Hi,
      I’m trying the same. The PL/SQL proc returns a Table type. There is either compilation error in generated code or deployment is failing. I have tried all options like RPC/Literal and Document/Wrapped. Have you been able to do it? If so, please help.
      I’m using Jdeveloper 11.1.1.5 and I think the key lies in the step 6 of the wizard.

      Reply

  15. Hi Arun,

    I am new this world of SOA. Seeking help so as to learn Deploying a Web Service.
    I have crated one Java Web Service using JDeveloper 10.1.3, I am connected to local OC4J so I am able to test it successfully.
    Now I need to deploy the same Web Service to real time Oracle Application Server.

    Please let me know if you have any pointers for this.

    Thanks!

    Reply

  16. Hi,
    I was able to generate the webservices for the stored proc’s which are having primitive java types, as described in this site

    JDeveloper 11g – Creating PL/SQL Webservice and deploying to Weblogic Server

    But I want to expose PL/SQL stored procedures which are having complex input & output structures as Webservice via JDeveloper, complex meaning non-primitive java types.

    When I try to generate the web services for the below stored procedures, I am not able to select the stored procedures from the wizard as they were greyed out and when I click on the why not button it showed me the below error message.

    *The following types used by the program unit do not have an XML Schema mapping and/or serializer specified:*
    *IPGBALANCETRANSFER.TRANSFERITEMS_T*

    _*Below is my stored proc definition.*_

    TYPE TransferItemRec_t IS RECORD
    (
    external_service ipgPaymentTransferDef.External_Service%TYPE,
    discount_quantity INTEGER
    );

    TYPE TransferItems_t IS VARRAY(200) OF TransferItemRec_t;

    PROCEDURE BalanceCarryIn(
    pCustomerRef IN customer.customer_ref%TYPE,
    pSubscriptionPS IN custhasproduct.subs_product_seq%TYPE,
    pExternalOfferID IN ipgPaymentTransferDef.External_Offer_Id%TYPE,
    pIRBOfferID IN ipgPaymentTransferDef.Irb_Offer_Id%TYPE,
    pTransactionDtm IN DATE,
    pTransactionID IN ipgtransactionaudit.transaction_id%TYPE,
    pTransferItems IN TransferItems_t
    );

    PROCEDURE BalanceCarryOut(
    pCustomerRef IN customer.customer_ref%TYPE,
    pSubscriptionPS IN custhasproduct.subs_product_seq%TYPE,
    pExternalOfferID IN ipgPaymentTransferDef.External_Offer_Id%TYPE,
    pIRBOfferID IN ipgPaymentTransferDef.Irb_Offer_Id%TYPE,
    pTransactionDtm IN DATE,
    pTransactionID IN ipgtransactionaudit.transaction_id%TYPE,
    pTransferItems OUT TransferItems_t
    );

    please help me.
    Thanks in advance.

    Reply

  17. Hi,

    I created the web service and call the web service from plsql. The following is the response. Can anyone tell me how to get ride of name space xmlns:typ=”http://hrconn/EmpWebService.wsdl/types
    Thanks,

    — response XML —

    100
    King
    AD_PRES

    2003-06-17T00:00:00.000+02:00
    90

    24000

    Reply

  18. Hi Arun,

    We are trying to interface a Microsoft .NET application to Oracle applications (ERP). Invoice information will be sent from .NET application to Oracle ERP. Both the application are in two different places (Hosted in two different sources and a firewall is separating them). Now that I have a PLSQL Storeprocedure created as webservice. What are the things I should do to make the .NET application a calls this webservice.

    Regards,
    Arun Andavar

    Reply

  19. @Arun

    Once you have the StoredProcedure exposed as a webservice, you would need to make a webservice call from the .Net application. Firewall shouldn’t been an issue as the webservice request would be sent via soap over http.

    Reply

  20. Hi

    I have a PL SQL package with Associative Array, but whenever I am trying to deploy the webservice on server, It gives me error and when I comment out the procedure using the collection it gets deployed successfully, but I have to use collection. Is there any restriction in webservices that it should not have collections? Is there any way I can create webservice out of it?

    Thanks,
    Sid

    Reply

  21. Hai Arun Thanks for sharing your knowledge …. I am new to SOA am trying to deploy rar file in weblogic server than i got error please provide details for webservice deployment

    Reply

  22. Hi,
    I have published my pl\sql code as per your example but if the procedure has an oracle data type as a return parameter it fails when I try call the Web Service. I get below error:
    Failed to invoke end component servitor11g.MyWebService1User (POJO), operation=authgetwebservicelinksV10 -> Failed to invoke method

    If I call method that does not have Oracle type as a return parameter this works fine. Any idea what I am missing? Any help would be much appreceated?

    Thanks

    Reply

  23. Hi,
    I am a newbie. I am trying to publish procedures which return refcursor as webservice. As webservices do not support ref cursor return type so I chnaged the return type to pl/sql table. I created the jax rpc in Jdeveloper studio 11.1.2.4.0 and tested it. This runs ok. I need to deploy it on tomcat server and am failing completely. I copied the war file generated by Jdeveloper to the webapps directory in Tomcat and started Tomcat but this didnot work. java.lang.ClassCastException: omsdevconn.MyWebServicetomcatUser cannot be cast to javax.servlet.Servlet

    If someone has done it then can I get the steps for the process to deploy it on Tomcat 6.0.37

    Thanks in advance Subodh

    Reply

  24. Hi,

    I generate the Web Service in oracle 10g and deployed in OC4J server. Now we have to upgrade the application server from 10g to 11g R2. I created the service and deployed in the server and it is working fine.

    But, I since the name space has modified the client application request are getting failed.

    Is there any option to use the old name space in the new service too.

    I generated the new service in jDeveloper 11.

    Thanks in advance.

    Reply

Leave a reply to Tatha Cancel reply