Hi All.
Can anybody point me out on working example of calling web service (e.g. CXF based) from Oracle 10g stored procedure.
+3
A:
You can do this by using the utl_http package in plsql.
I don't have a self made example ready, but would recommend you have a look at the oracle documentation for utl_http.
A quick google search for "oracle plsql web service utl_http", also showed some examples on the top results, for instance this one: rapid plsql web service client development using soapui and utl_http.
As long as the database server has network access to the webservice server you can use it.
Looks like UTL_DBWS is more applicable for SOAP WS: http://www.oracle-base.com/articles/10g/utl_dbws10g.php
FoxyBOA
2009-09-08 07:23:28
A:
It's possible, but almost certainly a bad idea.
Stored procedures shouldn't be calling web services. You ought to have a service layer that calls out to other services and the persistence tier. I'd recommend that you have that service implementation call that web service and pass what's needed to the stored procedure for persistence. Don't put that call into the stored proc.
duffymo
2009-09-07 20:58:09
I agree that it's almost certainly a code smell, but if their app's business logic is already in PL/SQL (in which case we should pity them), then calling a webservice from it may be the easiest option.
skaffman
2009-09-07 22:09:38
I would still disagree, skaffman, because it sounds like the call has yet to be added to the application. If the choice of caller has yet to be made, I'd argue in favor of a service even if a stored proc already existed. Could be a good time to re-examine moving the business logic out of the stored proc if possible.
duffymo
2009-09-08 00:45:43
+1
A:
One issue you may run into: if the service requires SSL, then you'll need to have a certificate available to the database. That generally means having the Advanced Security option and using Oracle Wallet. For http communications, though, utl_http (and its simpler form, utl_dbws -- database web services -- work pretty well. Marco Gralike's blog has a good HOW TO on consuming web services in PL/SQL.
Jim Hudson
2009-09-08 13:06:26