We publish a number of XML web services from the database using the PL/SQL web toolkit, as described here. In more recent times we’ve had a number of requirements for JSON web services, so we did what most people probably do and Googled for “json pl/sql” and got a link to PL/JSON.
I know about the support for JSON in 12c, but we are not on 12c for these projects and that’s more about consuming JSON, rather than publishing it.
People seemed reasonably happy with PL/JSON, so I thought no more about it. At the weekend, kind-of by accident, I came across the APEX_JSON package that comes as part of APEX 5 and thought, how could I have missed that?
This is not a slight against PL/JSON, but given the choice of using something built and supported by Oracle, that is already in the database (we have APEX 5 in most databases already) or loading something else, I tend to pick the Oracle method. Since then I’ve been having a play with APEX_JSON and I quite like it. Here’s what I wrote while I was playing with it.
If you have done anything with XML in PL/SQL, you should find it pretty simple.
I’m guessing this post will result in a few people saying, “What about ORDS?” Yes I know. Because of history we are still mostly using mod_plsql and OHS, but ORDS is on the horizon. Even so, we will probably continue to use APEX_JSON to do the donkey-work, and just use ORDS to front it.
The problem has always been that every so often the web services referenced by these articles disappear, so I have to modify the articles to use a different web service. Well, I got sick of people pulling the plug on their web services, so I created one of my own that just adds two numbers together. The articles now reference that web service, so it should be a little more stable.
In the process of doing the alterations to the articles I had a lot of trouble with UTL_DBWS. I’m not at all convinced by this solution. On the surface it seems OK, and it certainly seemed to be OK in 10gR2, but it seems very flaky in 11gR2. The old 9i method seems a lot more stable, and you have complete control as you are doing everything yourself.
Of course the real problem with web services and PL/SQL is people look at their WSDL file and then mail me asking what the hell they do with it. I think I’m going to start suggesting they pass it to the Java guys and handle it in the middle tier. Problem solved. 🙂
One of the reasons it’s been on the back burner for some time is I’ve been having trouble with the anonymous access configuration. I’ve still not got this sorted, but I decided to open it up and leave that section as a work in progress. I’ll keep looking at it and update that section if I get a satisfactory solution.
Update: I received the following quote on Metalink from the “Global Technical Lead XML Database”:
“Anonymous access to web services is not possible. Direct PL/SQL access without authentication would be a massive security hole.”