Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: publish a pl/sql pkg

Re: publish a pl/sql pkg

From: Job Miller <>
Date: Mon, 17 Sep 2007 10:04:54 -0700 (PDT)
Message-ID: <>

The Database Adapter via the ESB, which is an included component of the application server, makes this pretty simple to do. Than this service has a layer of abstratction between the caller and the pl/sql procedure via the ESB.

if you don't want that layer of abstraction and the benefits of an ESB for your overall SOA infrastructure, or this is the only service your organization has or will ever have, than just generate the web service with JDev.

Even if you just generate it with JDev, you could still create a routing service on the ESB to abstract the endpoint of it from developers, which is an equivalent of being able to easily overload the procedure later at the middle-tier.

from a database perspective, these adapters are very "performance" oriented, so while it may be more efficient to call the PL/SQL proc directly, rather than calling a web service, the database doesn't care whether it is the web service invocation on the ESB, or the direct calling of the PL/SQL. It just may take your client longer to process the data from the web service with the benefit of the fact that every client that needs to use this has a much better chance of generating a web service client based on the wsdl, than generating JDBC to access a PL/SQL method directly.

I am sure there are numerous coding frameworks that could perform the functional equivalent, however, the Oracle tools are designed to make this very easier than many other of the "custom" approaches.


Doug Gernaat <> wrote: no problem...

i just have two functions in this package that i want to make available. one returns an integer and the other a string. i'm getting feedback that it sounds like a good candidate for a web service. even so... i'm just wondering what options i might have.


>>> "Niall Litchfield" 9/17/2007 7:25:44 AM >>>
I'll admit that I got into some of the sidetracks and didn't deal with your questions. sorry.

you don't provide - probably for perfectly good reasons - a description of what the code does. Web services work, reasonably, well for small scale remote invocations of well designed small resultsets obtained from performant code. They work badly - in my experience - for large resultsets, for ill designed arbitrary resultsets from arbitrary code. So for example a webservice that answers the question "What's the customer address for customer reference 'XXXY123'" will likely be quite a useful component of a CRM database. One that answers the question "What was the average length of time customers with names that sound like smith spent on the phone to our customer service centre in the last 63.4 days and what products were they asking about anyway?" (i.e selected from a friendly GUI with a combination of check and textboxes) will likely perform poorly - but might make a great report.

On 9/14/07, Doug Gernaat wrote:
> i got a pl/sql pkg with some functions and procedures in it.
> it's working beautifully for other programs that use it within
> the same database environment.
> if i were to make this available to other depts within our
> enterprise, what route might you take? these other depts
> are using diff programming languages and databases for
> their web apps.
> not sure if this is a web service candidate or what.

Niall Litchfield
Oracle DBA


Got a little couch potato? 
Check out fun summer activities for kids.
Received on Mon Sep 17 2007 - 12:04:54 CDT

Original text of this message