Home » SQL & PL/SQL » SQL & PL/SQL » UTL_HTTP,UTL.DBWS or Table Functions? (Oracle 10g)
icon5.gif  UTL_HTTP,UTL.DBWS or Table Functions? [message #377940] Fri, 26 December 2008 10:17 Go to next message
pmapc
Messages: 46
Registered: July 2008
Member
Hi everybody,

I am going to callout(consume) a web service which takes two date input paramaters and returns a set of rows (like a table) .During each service call it returns near 5000 rows as its result. my question is what is best way of calling this service and also using its result (insert the result in a temporary table). after some googling I found three way to do it UTL_HTTP,UTL.DBWS and Table Functions, but before getting start I'd like to know what is the most suitable way in my case?


Thanks,

[Updated on: Fri, 26 December 2008 10:19]

Report message to a moderator

Re: UTL_HTTP,UTL.DBWS or Table Functions? [message #378004 is a reply to message #377940] Sat, 27 December 2008 13:04 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I admittedly have not done a good job with UTL_DBWS, however I have done some work with all three alternatives to creating and consuming web services. I caution you that:

1) My experiece may not be the same as others
2) My own ineptitude with some of this technology may render my answer poor

But that said, here is my experience:

1) UTL_DBWS is a pain in the butt. It works, but it is difficult to code, especially by hand. I created a web serivce no sweat. But when it came time to consume that web service I had all sort of problems with the plsql code using UTL_DBWS I wrote to get it. I could not get it to work correctly. Funniest thing was that the webserivce could be read by other tools. Most notably other oracle tools (like APEX) has no issue getting data from the web service. Only my specific plsql code kept throwing errors. Oracle had a look at it but in the limited time they could spend on it they found nothing.

2) UTL_DBWS is slow. I had other simpler web services I was reading using ULT_HTTP. I decided to convert them to UTL_DBWS and they worked. But reading the web service using UTL_DBWS calls turned out to be as much as 10 times lows (yes, an entire order of magnitude). I do not know if this was an issue with my particular DBMS release. In any event, this one fact alone was sufficient for me to abandone UTL_DBWS altogether.

3) UTL_HTTP is workable and more like what programmers are used to. But there are potentially a lot of steps to it and if you are getting XML you have to do some parsing. XML in general is crap (I make no bones about the fact that I do not like it). I understand the idea behind it, and I also understand the corruption that has found its way into the original idea to such an extent that is has become to my mind one of the worst mechanisms for data exchage. On the other hand, I have found people in IM (developers/dba/analysts) are like all other groups of people: 99% of them are lazy bumbs who want to do as little work as possible. For this reason, XML has found its way into the lives of many. I guess if one looks at the reality of things XML might make some kind of sense for this reason. In any event, I blab too much sometimes.

4) table functions are excellent to work with. I use them all the time and like them a lot. If you can create a table function to do what you want, I say go with that.

So to recap:

Quote:
1) UTL_DBWS
I could not reiliably make it work
It was much slower than UTL_HTTP alternative when it did work

2) UTL_HTTP
Lots of traditinal coding
Always worked once you got the syntax of your data and webservice correct (My second choice)

3) TABLE FUNCTION
One of Oracle most Excellet Features
Easy to use and highly re-usable (My first choice)
Not sure how you get a table function to consume web service without #1 or #2 above

I would suggest you do all three. If you can write a table function to do what you want, then you should be able to take another day to write the UTL_HTTP version of it and once that is working another day to write the UTL_DBWS version.

Some people would balk at the idea of doing two additional days of work to create code components that they have been told will most likely never be used. I say you have to do this for yourself. You need to understand first hand the issues and difficulties of working with these alternatives. Only then can you rely on comments such as mine. It might well be that there is someone out there who loves UTL_DBWS, knows exactly why my coding were stupidly slow and how to correct this, and maybe even has simple routines or techniques to easily generate the necessary code for you. This would make ULT_DBWS much more ideal. But I do not have such knowledge and have never seen it.

Good luck, Kevin
Re: UTL_HTTP,UTL.DBWS or Table Functions? [message #378038 is a reply to message #378004] Sun, 28 December 2008 01:02 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
Kevin thank you very much for your comprehensive reply.
I prefer to start my work with Pipeline but I can not find any good example on web or oracle documentation library about how do that I mean how create a virtual table using a Web service data source. can you guide me how can I do that?

Thanks,
Re: UTL_HTTP,UTL.DBWS or Table Functions? [message #378084 is a reply to message #378038] Sun, 28 December 2008 10:19 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
To my knowledge, there is no such thing. This is what I mentioned before. I think you will have to use either UTL_HTTP or UTL_DBWS to go get the data first, and then as you parse it you can send results back through a table function. You will have to

1) write a get_service routine in plsql using UTL_HTTP to go get the data
2) assuming the data is in xml format, you will have to parse it out using Oracle's XML routines (see EXTRACT/EXTRACT_VALUE)
3) as you produce rows from the XML, you can use a table function to pipe them
4) you can wrap the table function in a view

I have done this before. The result is a rowsource in the database that looks like any other rowsource but in fact goes out to the internet for its data.

As you can see, you will have to combine several technologies to get your end result. This is generally a sign of a good solution, that it combines several technologies that co-operate with each other to produce a polished final product.

But in the end, to succeed, this means you will have to learn several different technologies. You will have to learn about web service construction and about WSDL(s) and how to interpret them so that you can understand how to access a specific web serivice and how to break down its return item. You will have to learn XML and how to break up an XML data stream into recognizable components. You will have to learn how to build, pass, and read table functions.

But, once you get it done, it is pretty cool to join some table to your view and see results from the internet add data to your system on the fly.

As an additional consideration, you should consider that web service access is generally slow compared to all other operations on a database. You might want to take your table function and fill a real table with it, then use the persisted data over time. Sort of like treating the web service as just another batch feed. Its way more efficient performance wise though like all batch feeds it implies aged data is in use.

Good luck, Kevin
Previous Topic: Single row result from multiple sub tables (Many tables join)
Next Topic: Removing multiple tables in one go [merged]
Goto Forum:
  


Current Time: Sun Dec 04 02:36:37 CST 2016

Total time taken to generate the page: 0.13925 seconds