Using INTERVAL for time operations in SQL

Sometimes when you are set in your ways you miss out on little things that can make a life a bit easier.

For a long time if I needed SQL to find changes that occurred in the last 5 minutes on a table that had some sort of time column I would do something like this

SELECT * 
FROM example_log 
WHERE updated_dt_time >= SYSDATE - 5/60/24
ORDER BY updated_dt_time desc

Using the calculation 5/60/24 to work out what fraction 5 minutes is as part of a day. (I guess if you knew you could say 0.003472..)

Recently someone was showing me a query and did the equivalent of this

SELECT * 
FROM example_log 
WHERE updated_dt_time >= SYSDATE - INTERVAL '5' MINUTE 
ORDER BY updated_dt_time desc

Which has the same result. I hadn't even come across the INTERVAL concept before. So much easier to read and work with. There is a lot more to it as usual (DAY, HOUR, options etc) and you can find reference at http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#SQLRF51067

Calling a web service from PL/SQL example

There are a few options that Oracle provide to interface with a web service directly from PL/SQL. The two most obvious are UTL_DBWS (a package built specifically for the purpose which calls through to a bunch of Java code) or the more generic UTL_HTTP package. After several attempt at using UTL_DBWS, with it's extra set up effort and lack of good documentation on using the package, and no success, it seemed like a good alternative to give UTL_HTTP a try, as it seems a simpler approach and there are more examples around. If anyone has good examples of using UTL_DBWS then let me know.

I still had a few issues as the examples around are quite specific. Below is an example that I used to test with some internal web services. I have left out the bulk of SOAP envelope details as they will depend on what you are calling but it should give you a good idea of the basic approach. I used SOAP UI to test the web service and get an example of the SOAP envelope that needs to be passed. I helps with building it in your PL/SQL.

Also our web service used basic http authentication for the web service container so the example below includes that.


One of the things it took me a while to understand was the need to trap the exception to handle the end of the response. so I hope that is shown clearly here too.

DECLARE
v_soap_request VARCHAR2(30000);
v_http_req UTL_HTTP.REQ;
v_http_resp UTL_HTTP.RESP;
v_name VARCHAR2(32767);
v_value VARCHAR2(32767);
v_txt VARCHAR2(32767);
BEGIN
-- Define the SOAP request according the the definition of the web service being called
v_soap_request:= '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
xmlns:res="http://com/example/ws/test/testLoad.wsdl" 
xmlns:typ="http://com/example/ws/test/testLoad.wsdl/types/">
<soapenv:Header/>
<soapenv:Body>
<res:testLoad>
<pTestLoad>
   <typ:iDNum>1</typ:iDNum>
  ..REST OF SOAP BODY.
</pTestLoad>
</res:testLoad>
</soapenv:Body>
</soapenv:Envelope>';
-- Initialise the request
v_http_req := UTL_HTTP.BEGIN_REQUEST
( 'http://example.server.com:8888/testLoad/testLoadPort', --UTL
'POST', -- method
'HTTP/1.1' -- http_version
);
-- this web service uses basic authentication so we include the username and password details
UTL_HTTP.SET_AUTHENTICATION(v_http_req, 'user', 'password');
-- set header details
UTL_HTTP.SET_HEADER(v_http_req, 'Content-Type', 'text/xml');
UTL_HTTP.SET_HEADER(v_http_req, 'Content-Length', LENGTH(v_soap_request));
-- add the soap part of the request
UTL_HTTP.WRITE_TEXT(v_http_req, v_soap_request);
-- call the web service and get the response
v_http_resp:= UTL_HTTP.GET_RESPONSE(v_http_req);
-- loop through the response headers and write them out
FOR i IN 1..UTL_HTTP.GET_HEADER_COUNT(v_http_resp) LOOP
UTL_HTTP.GET_HEADER(v_http_resp, i, v_name, v_value);
DBMS_OUTPUT.PUT_LINE('Response header ' || i || '-' ||v_name || ': ' || v_value);
END LOOP;
-- create a block to trap the end of body exception when no more lines exist.
BEGIN
-- loop through the response text which is the soap e details
LOOP
UTL_HTTP.READ_LINE(v_http_resp, v_txt);
DBMS_OUTPUT.PUT_LINE('response line ' || v_txt);
END LOOP;
UTL_HTTP.END_RESPONSE(v_http_resp);
DBMS_OUTPUT.PUT_LINE('End of response loop');
EXCEPTION
-- need to trap end of body exception as there is no other graceful way to do it.
WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(v_http_resp);
DBMS_OUTPUT.PUT_LINE('End of response loop');
END;
EXCEPTION
WHEN OTHERS THEN
-- make sure we free up any connections that might stay around
UTL_TCP.CLOSE_ALL_CONNECTIONS;
END;