Saturday, December 8, 2007

11g plsql web service

Making a plsql web service in 11g is very easy but you have to think about your function, procedure and parameters names else it can result in a strange wsdl method and parameter names.
Here is a example of a function.

create or replace package scott_ws is
function get_current_time ( date_format varchar2)
return varchar2
end scott_ws;

create or replace package body scott_ws is
function get_current_time ( date_format varchar2)
return varchar2
v_time varchar2(20);
if date_format is null
return 'empty format';
end if;
select to_char(sysdate,date_format) into v_time from dual;
return v_time;
when others
return 'error';
end scott_ws;

The package name is not used in the wsdl creation. If you want to have a capital character then you have to use a underscore _ . Jdelevoper automatically remove the underscore and capitalize the following character. In Oracle plsql is common to use p_format as a parameter of a function but this results in pFormat.
So we are ready to create a plsql web service.

Here we make a database connection and we select the oracle package.

Select our function. And we are ready with our webservice. Here is how the wsdl looks like

Let's make it a bit more interesting by using security and logging.

Let's start the webservice and look at the result. As username I use a standard account oc4jadmin. You can also use an another account but you have to edit the jazn of the embedded oc4j.


  1. I dont know whether this is the right to ask my question. But I found you who has ADF and Flex blogs.
    Can you guide me how to call a webservice(done in adf) through flex.

  2. give me a week and I will try to make an example

  3. Hi,

    We have a requirement to consume a webservice to get some data updates and then use this data in PL/SQL procedures of an existing application.

    Can I use "PLSQL Webservice" in this situation?



  4. yes that can work. You have to deploy this plsql webservice to an application server and the you can use this ws

  5. As far as I know, Jdeveloper generates jax-rpc webservices. Can you generate jax-ws from pl/sql packages somehow?

  6. What about functions or procedures that need to return result sets. All examples I see are for a single record result - but there are many cases when I need to return multiple records.

  7. Hi, I think it is possible to return a table record type.
    Off course you can return a xmltype.

    I will try this tommorrow.


  8. Hi,

    I tried to make a new ws with complex return type but in jdev 11g they removed the plsql ws.

    I will make a new example based on toplink or jdbc.


  9. Here is a new example

    where I return all the record of the dept table of scott


  10. Hey Edwin....
    I am a new one 2 Jdeveloper. I have created a data source (MySQL) in weblogic default server which is running on 7101. I want to create a simple session bean which accessess this data source. can u please help me to develop this in JDeveloper. my email is

  11. Hi,

    just create a ejb or toplink project.
    make an mysql connection in your project.
    add entities from tables to your project.
    add an session bean.
    deploy this, make sure you add the datasource on wls
    and make a test client on the session bean viola.

    or you can take a look at how eclipselink 11g it does , it is almost the same