Using the UTL_DBWS Package

In my previous post, I mentioned the UTL_DBWS package, that provides a ready to use method to consume web services from the database itself, using pure PL/SQL.

Although you only need PL/SQL to consume, the package itself is based on a couple of java classes.

If you’re using a 10.1 or 10.2 database, the PL/SQL code is already installed, but the Java package isn’t! This will cause an error when using the package to establish a connection to the web services, complaying that a particular Java class does not exist

To correct this, get the dbws callout utility. You can download it here.

Then, apply it to the database using the following commands:

loadjava -u user/userpass -r -v -f -s -grant public -noverify -genmissing dbwsa.jar

loadjava -u user/userpass -r -v -f -s -grant public -noverify -genmissing dbwsclient.jar

After this, the PL/SQL package should now function correctly.

Here are a couple of web services to test your code, if do not have any one available:

• Shakespeare Web Service
• mailboxvalidator Web Service
• getJoke Web Service
• periodictable Web Service
• Number Conversion Service

Consuming Web Services using PL/SQL

For the last couple of days..I’ve been studying how to consume web services within the Oracle database using PL/SQL..

A quick glance at my personal library (Google!)…led me to conclude that there are two major ways to do this:

• Build your own SOAP API using the UTL_HTTP package for the HTTP requests (mandatory for anyone using 9i or older) ;
• Use the UTL_DBWS package available in 10g that provides all the essential methods to consume web services

In my particular case I’m building my own API, solely because I couldn’t consume the desired web service via HTTPS.

I lost a lot of time searching how to consume a web service over HTTPS using the UTL_DBWS package, with little success, and frustrated I decided to go with the first solution.

Using the UTL_HTTP package with a Oracle Wallet, I successfully consumed web service over HTTPS with little effort. This was achieved using the following procedure, before the HTTP request:

utl_http.SET_WALLET(your_wallet_path, your_wallet_pass);

Pretty simple and straight forward…

I’ll post more related code tomorrow from my PC at work..

Meanwhile I started working on FTP and SFTP (not confuse with FTPS) from the database, which I’ll post some examples in the next days!

Any doubts or feedback be sure to comment or send me an e-mail!

1 Comment