Calling Webservices from pl/sql

For a client of us we had to call a webservice from a database, and the received information that we received from this call was needed in another procedure.

It was the first time for me that I had to do this and I believe that not so many people now of this functionality in the database and that’s why it is obviously a good idea to post this on our blog.

It is even possible to let the database be a webservice provider, but in this case the webservice already existed and should simply be called by the database.

What was the objective: we wanted to call the webservice from the database, get the resulting XML file and analyze some of the content of the returned XML file and then do some actions with the information that the webservice provided us.

Therefore I wrote 3 procedures in a package:
• fnc$_get_xml
• fnc$_handle_xml
• prc$_ws_call

As you will see when you look at the code it is possible to make this much more dynamically but for this case we only had to call only one specific webservice, as usual you can make it very complex but for the blog I made the procedures and functions as simple as possible.

The first function that I will explain is the prc$_ws_call
This procedure will contact the function fnc$_get_xml that will get the xml file. After this function the fnc$_handle_xml will be called to retrieve specific information out of the XML file.
When this is done the received values will be printed

PROCEDURE prc$_ws_call
v_xml VARCHAR2(32767);
v_type cab_base_adres.type%TYPE;
r_receive r_info;
v_xml := fnc$_get_xml(p_search => ‘search value’); –call to webservice
r_receive := fnc$_handle_xml(v_xml); –analization of xml content
dbms_output.put_line(‘value nr1=’||r_receive.value1) ;
dbms_output.put_line(‘value nr2=’||r_receive.value2) ;
dbms_output.put_line(sqlcode||sqlerrm) ;

This function will receive a parameter(search) this parameter will be included in the soap call(called v_soap_request).
Ones that I have build my soap envelope, I have to create a httpRequest, this is the variable v_httpRequest which is of the type utl_http.req.
For this variable I have to set some parameters like the webservice url that I will call, the content-type (plain text in this case), the content_length of the soap envelope and the specification that this is a soapaction. This is the preparation for the call to the webservice.

Next thing to do is to write this data to the body of the http request, this is done with the utl_http.write_text where we give in the http_request and the soap_request.
Now we want to receive the response from our soap call.
This will be created with the utl_http.get_response which is of the utl_http.resp type.

Next thing we want, is to have this file in a readable form. Therefore we use the utl_http.read_text, that will translate the response in a readable variable.

FUNCTION fnc$_get_xml(p_search VARCHAR2)
v_soapRequest VARCHAR2(32000);
v_soapResponse VARCHAR2(32767);
v_httpRequest utl_http.req;
v_httpResponse utl_http.resp;
v_soapRequest :=
‘<?xml version=”1.0″ encoding=”UTF-8″ standalone=”no”?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV=”; xmlns:apachesoap=”; xmlns:impl=”<the webservice>” xmlns:intf=”<the webservice>” xmlns:soapenc=”; xmlns:tns1=”” xmlns:wsdl=”; xmlns:wsdlsoap=”; xmlns:xsd=”; xmlns:xsi=”; >
<mns: <webservice name>xmlns:mns=”<services link>” SOAP-ENV:encodingStyle=””&gt;
<inputAddress xsi:type=”tns1: <input parameter name>”>
<input search xsi:type=”xsd:string”>’||p_search||'</search>
</mns: <webservice name> >

v_httpRequest:= utl_http.begin_request

utl_http.set_header(v_httpRequest, ‘Content-Type’, ‘text/xml’);
utl_http.set_header(v_httpRequest, ‘Content-Length’, length(v_soapRequest));
utl_http.set_header(v_httpRequest, ‘SOAPAction’, ”);

utl_http.write_text(v_httpRequest, v_soapRequest);
v_httpResponse:= utl_http.get_response(v_httpRequest);
utl_http.read_text(v_httpResponse, v_soapResponse);
RETURN v_soapResponse;
dbms_output.put_line(sqlcode||sqlerrm) ;
dbms_output.put_line(‘Error in fnc$_get_xml’) ;

Great, we have now received the xml file that contains the result of our request. Now we want to retrieve the data we need, out of this XML file.
Therefore I will call the fnc$_handle_xml function with the received XM file as parameter.

I will have to handle the content of the xml file. To make this easier I am going to put the content of the XML file into an XMLType by using the XMLType.createXML function. Now the parameter resp contains the XML file. But I am only interested in a certain part of the XML file. By using the ‘extract’ function I am able to get a certain part out of the hierarchical structure of the XML file.

Next thing that I want to receive is the information of certain parts of this resp variable. I will put his in the resp1 variable which make it possible to always use the resp file for the next value I want to retrieve.

In this example I get 2 values out of the XML file. And I will return this back to the calling procedure.

FUNCTION fnc$_handle_xml(p_xml VARCHAR2)
RETURN r_info
resp XMLType;
resp1 XMLType;
r_result r_info;
resp:= XMLType.createXML(p_xml);
resp:= resp.extract(‘/soap:Envelope/soap:Body/child::node()’
, ‘xmlns:soap=””&#8216;

resp1:= resp.extract(‘/multiRef[2]/<xml level>/text()’
, ‘xmlns:ns2=”<beans url>”‘
r_result.value1 := resp1.getStringVal();

resp1:= resp.extract(‘/multiRef[2]/<xml level>/text()’
, ‘xmlns:ns2=”<beans url>”‘
r_result.value2 := resp1.getStringVal();

RETURN r_result;
dbms_output.put_line(sqlcode||sqlerrm) ;
RETURN r_result;

How to reformat the highwatermark of a table after a delete?

For test purpose I had to delete and recreate a lot of records in a database.
So I just did a delete of all the records, every time I wanted to clean up my database.

Some of these tables have a few millions of records. So after a couple of times cleaning up and re-entering data into the database,
I had the impression that the database was getting slower and slower in showing me the results of my query.

I thought that this could have something to do with my indexes but I didn’t know how to fix this.

That’s why I contacted a few of my expert database colleagues and they learned me the following:
The table uses a highwatermark value and every time you add a record into the table the table will raise this value.
When you do a simple delete the value of this highwatermark will be kept, so I was wrong in suspecting the index to be the cause of this problem it was in fact the table itself who caused this.

So how do you fix this?

If you want to clean a lot of records in a database(in bulk) you have 2 possibilities to keep your Highwatermark clean.

First option, and the one that worked best in my case was: use TRUNCATE TABLE

This will remove all the content of the table and will put the ‘highwatermark’ back to 0.

The advantage of this option is that is will will work a bit faster then a normal deletion.
But there is also one disadvantage. If you want to use this, you will have to disable all the foreign keys for this table before starting the truncate.

So doing this in a live database is not really the best thing to do I believe :-)

The other option is just use the delete as you used to do, but after the deletion of the records use : ALTER TABLE

You can even use the cascade option for this shrink, this will shrink all the highwatermarks of the depending objects.
This option is only available from oracle 10g.

Thanks Erwin and Hans for helping me with this!

ODTUG Kaleidoscope 2008

Thursday night I got this fantastic news:

“Dear Karen:
Congratulations! Your abstract,
Use the Power of Apex Dictionary Views to Increase the Quality of Your Apex Applications,
has been accepted for presentation at ODTUG Kaleidoscope 2008, June 15-19, in New Orleans, Louisiana.”

Wauw!! A range of emotions ran through me. I go to New Orleans, the city from the gospel and the jazz!

It’s the first time I go to a foreign country to give a presentation, so you understand that I’m very excited to be one of the selected speakers at ODTUG! This is a wonderful opportunity!

The Apex-core team at iAdvise has developed their own QA-tool on top of the Apex Repository to guard the quality of our application guidelines and conventions. I will bring this case together with my colleague Jan Huyzentruyt. We already did a lot of presentations together in Belgium, but it is the first time we are doing it abroad …

So, we are looking forward going to New Orleans and we are honoured being on the list of speakers together with big names like Tom Kyte, Steven Feuerstein, Mark Rittman, Carl Backstrom, Patrick Wolf, …

Hope to see you there!

OWB Paris : What happened to “Generate intermediate results”???

Prior to OWB Paris, you could easily trap the SQL code that was being generated by OWB in a mapping : you simply had to rightclick on an outgoing group and select “Generate Intermediate Result”.

At first sight, this option disappeared in OWB 10G release 2, but this is not the case… Now, how can you get an intermediate result in OWB Paris? It’s simple … If you know it.

1. Select Generate from the top bar.

2. The Generation Results window will appear.

3. From the dropdown list, select “Intermediate” instead of “Full”.

4. Finally, select the outgoing group of which you want to see the intermediate result.

Like I said : simple, if you know it … ;o)

Using AJAX within Oracle Application Express (part 2)

Most of the AJAX calls will require some parameters. Instead of using an application item for every parameter, I wrote some javascript and PL/SQL functions that will store all AJAX parameters as XML in 1 application item!

The same javascript as in my first blog, but some extra parameters and functions are added.

This javascript function allows you to add an extra parameter to the AJAX call

This PL/SQL function allows you to read out the same parameter in the AJAX call’s PL/SQL code

We can simply add some parameters to our AJAX call. The first parameter of the AddParam function, is the name of the parameter (you must use the same name in your AJAX call’s PL/SQL code to read the parameter’s value), the second parameter is the parameter’s value.

In the PL/SQL code of the AJAX call, you can use the GetParameter function to read out these parameters

The result would be…

Using AJAX within Oracle Application Express

Everybody is already convinced about the strength and benefits of AJAX. There is only one thing that keeps bothering me when I’m using AJAX within Apex. For every AJAX call, you have to define a new application process.

I worked out a generic solution where you only need to create one application process for all your AJAX calls!
How to implement this generic solution
Step 1: Within Oracle Application Express
a) Create an application process and give it the name AJAX_DYNAMIC_PROCESS

b) Create an application item with the name AJAX_PROCEDURE

Instead of writing your PL/SQL code for the AJAX call in the application process, you have to write it on the database as a procedure (or a package procedure) Ex: pck$ajax_examples.prc$first_example

Step 2: Javascipt

Now we can put (by using javascript) the name of this procedure in the application item AJAX_PROCEDURE. The execute-immediate will execute the pck$ajax_examples.prc$first_example procedure.

I have written some javascript functions that simplify the use of AJAX:

Now implementing an AJAX call becomes as easy as this…

The ajaxResult variable will contain the result of your AJAX call!

This method is only recommended in an inTRAnet environment because your database is open to SQL Injections. You can also build in extra security, for example by checking your AJAX call againt a database table to make sure the call is permitted.

In a next blog I will explain how you also can generalise the passing of params to the Ajax call.

Solution for page refresh problems in ADF when no relationships are used.

I have a small program which is used for registering money transfers.

It has two tables, table A holds all the transfer information and table B keeps the total amount of money earned so far. There is no connection between the tables, there are only triggers on table A which keep table B up to date.

On the overview page it looks like this:

The problem that I had was that when I pressed “Create” and inserted a new record, the total amount field was not updated after the create page navigated back to the overview page. It didn’t refresh, but the trigger was called and executed. So, for example, after “2 very large bags of potatoes” was inserted the “Total Amount” field would still display 10, although if you peeked in table B it would say 30.

I don’t know if it’s the right solution, but I solved it this way:

In the page definition of the overview page I made a new action, an execute action based on the iterator of table B.

<action IterBinding=”TotalAmountIterator” id=”ExecuteTotalAmount”
DataControl=”SampleServiceDataControl” RequiresUpdateModel=”true”

To invoke it every time the page is loaded, you do this:

<invokeAction Binds=”ExecuteTotalAmount”

So far the Total Amount field will always be refreshed when the application navigates to the overview page.

There is only a small exception, the delete button. The effect of pressing the delete button – besides the deletion of the record – is a partial page refresh, so the main table is OK, but the total amount field is not.

I fixed it by calling the method I declared in the page definition in the deleteButton method in the backing bean of the overview page.

public String deleteButton_action() {
BindingContainer bindings = getBindings();
OperationBinding operationBinding =
Object result = operationBinding.execute();

operationBinding = bindings.getOperationBinding(“Commit”);
result = operationBinding.execute();

operationBinding = bindings.getOperationBinding(“ExecuteTotalAmount”);
result = operationBinding.execute();

return null; }


Get every new post delivered to your Inbox.

Join 53 other followers