It worked on my machine

Monday, 4 January 2016

Parsing parent child XML structure using PL/SQL

Doing some investigation about processing a XML stucture where we have a parent child type relationship where we want to get all the details for each child record and loop through them.
As part of looping through the child records we want the parent details to be available without the need to go back and read the XML again.

The following example displays each RaceLap tag and includes parent information. Including the case where there may be no child RaceLap entries.
The trick for me to understand was using the laptimes column extracted from the v_xml_example as an XMLTYPE as input into the second part of the FROM clause (laps) using the PASSING race.laptimes.

If there are any questions add a comment.

DECLARE
v_xml_example SYS.xmltype := xmltype(
'<Races>
<RaceResult>
<Id>743845</Id>
<PlateNo>420</PlateNo>
<Completed_ind>N</Completed_ind>
<Comments>DNF. No laps recorded</Comments>
</RaceResult>
<RaceResult>
<Id>123145</Id>
<PlateNo>233</PlateNo>
<Completed_ind>Y</Completed_ind>
<Comments>Finished after 3 laps</Comments>
<RaceLap>
<Lap>1</Lap>
<Time>34.34</Time>
</RaceLap>
<RaceLap>
<Lap>2</Lap>
<Time>35.66</Time>
</RaceLap>
<RaceLap>
<Lap>3</Lap>
<Time>34.00</Time>
</RaceLap>
</RaceResult>
</Races>');
CURSOR c_race_laps IS
SELECT race.id,
race.plate_num,
race.completed_ind,
race.comments,
laps.lap,
laps.lap_time
FROM XMLTABLE('/Races/RaceResult' -- XQuery string to get RaceResult tag
PASSING v_xml_example
COLUMNS
id VARCHAR2(100) PATH 'Id',
plate_num NUMBER(10) PATH 'PlateNo',
completed_ind VARCHAR2(1) PATH 'Completed_ind',
comments VARCHAR2(100) PATH 'Comments',
laptimes XMLTYPE PATH 'RaceLap') race
LEFT OUTER JOIN -- want parent with child nodes
XMLTABLE('/RaceLap' -- XQuery string to get RaceLap tag
PASSING race.laptimes -- the laptimes XMLTYPE output from the first xmltable containing the laptimes
COLUMNS
lap NUMBER PATH 'Lap',
lap_time NUMBER PATH 'Time') laps
ON (1 = 1); -- left outer join always join

BEGIN
FOR v_race_laps_rec IN c_race_laps LOOP
dbms_output.put_line('atr id:' || v_race_laps_rec.id ||
' plate_num:' || v_race_laps_rec.plate_num ||
' completed_ind:' || v_race_laps_rec.completed_ind ||
' Comments:' || v_race_laps_rec.comments ||
' Lap Number:' || v_race_laps_rec.lap ||
' Lap Time:' || v_race_laps_rec.lap_time);
END LOOP;
END;

Friday, 16 May 2014

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

Wednesday, 15 January 2014

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;

Tuesday, 13 August 2013

Suppressing leading zeros in date and time formats

I recently looked into a problem with a date format issue. The problem was a time component of a date needed to be formatted to match a certain format in a text file.

The format was DD/MM/YYYY HH24:MI:SS but if the hour was less than 10 it should suppres the leading zero but the normal format mask here doesn't do that.

SELECT to_char(to_date('07/04/2004 07:05:05',
  'DD/MM/YYYY HH24:MI:SS'),
  'DD/MM/YYYY HH24:MI:SS')
FROM dual
07/04/2004 07:05:05

With a bit of searching I found the Format Model Modifiers and specifically Fill Mode (FM). It can be used to strip leading zeros as well as leading spaces.
Basically it is used as toggle in the string. The first time it is used fill mode is turned on. The sceong time it is used fill mode is turned off.

Here are some examples.

To achieve the result for the problem at hand we surround the hour component with the fm tag like this.

SELECT to_char(to_date('07/04/2004 07:05:05',
  'DD/MM/YYYY HH24:MI:SS'),
'DD/MM/YYYY fmHH24fm:MI:SS')
FROM dual
07/04/2004 7:05:05

To strip off the zeros from the date we can also do this

SELECT to_char(to_date('07/04/2004 07:05:05',
  'DD/MM/YYYY HH24:MI:SS'),
'fmDDfm/fmMMfm/YYYY fmHH24fm:MI:SS')
FROM dual
7/4/2004 7:05:05

Or another way we use it across day and month

SELECT to_char(to_date('07/04/2004 07:05:05',
  'DD/MM/YYYY HH24:MI:SS'),
'fmDD/MMfm/YYYY fmHH24fm:MI:SS')
FROM dual
7/4/2004 7:05:05

Or then across day, month, year and hour in one go.

SELECT to_char(to_date('07/04/2004 07:05:05',
  'DD/MM/YYYY HH24:MI:SS'),
'fmDD/MM/YYYY HH24fm:MI:SS')
FROM dual
7/4/2004 7:05:05

But if we do the whole format mask we end up with something that is probably not what you want.

SELECT to_char(to_date('07/04/2004 07:05:05',
  'DD/MM/YYYY HH24:MI:SS'),
'fmDD/MM/YYYY HH24:MI:SSfm')
FROM dual
7/4/2004 7:5:5

This fill mode is also useful for the dates where you use the full word. If you format it without fill mode the month will have leading spaces. Like this

SELECT to_char(to_date('07/04/2004 07:05:05',
  'DD/MM/YYYY HH24:MI:SS'),
'DD/MONTH/YYYY HH24:MI:SS')
FROM dual
07/APRIL /2004 07:05:05

But if we use fill mode around the month then it is fomatted with the leading spaces stripped off.

SELECT to_char(to_date('07/04/2004 07:05:05',
  'DD/MM/YYYY HH24:MI:SS'),
'DD/fmMONTHfm/YYYY HH24:MI:SS')
FROM dual
07/APRIL/2004 07:05:05

Tuesday, 14 May 2013

A quick cheat sheet for clearing out oracle database queues with DBMS_AQADM

To find the name of the queue table you might need to deal with you can simply do

SELECT * FROM all_queue_tables

Which will give you the name and owner of the queue table. Then you can do a select to find out how many items are in the queue. In the example below the queue table is called test_msg_queue_table and the owner is app_owner. Note the aq$ must be added to the start of the queue table name.

SELECT count(*) FROM app_owner.aq$test_msg_queue_table

Then as the schema owner you can run a script as below to clear out all the items in the queue.

DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := TRUE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table => 'APP_OWNER.TEST_MSG_QUEUE_TABLE',
purge_condition => NULL,
purge_options => po);
END;

Wednesday, 6 February 2013

Using DBMS_XMLGEN for XML from PL/SQL

I started looking into the DBMS_XMLGEN package when we had a need to do character escaping of some XML text. Basically we needed to convert all the < and > characters to < and > in PL/SQL.

Here is a quick script to demonstrate using DBMS_XMLGEN.CONVERT to do this.

To convert to a simple string ‘<>CAT<>’

SELECT DBMS_XMLGEN.CONVERT(xmldata=>'<>CAT<>', flag=>0) FROM dual

results in the string

<>CAT<>

Then to convert back the other way.

SELECT DBMS_XMLGEN.CONVERT(xmldata=>'<>CAT<>', flag=>1) FROM dual

results in the original string

<>CAT<>

With the flags instead of using 0 or 1 you should be able to use DBMS_XMLGEN.ENCODE and DBMS_XMLGEN.DECODE but I have not been able to get these to be recognised which might be an environmental issue in this database.

Looking further into the DBMS_XMLGEN package I found that is can do a great deal more with XML processing. Here is a basic example of running a query and having it formatted as XML then using dbms_output.put_line to display the CLOB.

DECLARE

v_ctxhandle DBMS_XMLGEN.ctxHandle;

v_clob CLOB;

v_offset NUMBER := 1;

BEGIN

v_ctxhandle := DBMS_XMLGEN.NEWCONTEXT (

'SELECT employee_id ID,

last_name,

to_char(hire_date, ''DD/MM/YYYY'') Hire

FROM employees

WHERE employee_id < 105');

v_clob := DBMS_XMLGEN.getXML(v_ctxhandle);

loop exit when v_offset > dbms_lob.getlength(v_clob);

dbms_output.put_line(dbms_lob.substr( v_clob, 32767, v_offset));

v_offset := v_offset + 32767;

end loop;

DBMS_LOB.FREETEMPORARY(v_clob);

DBMS_XMLGEN.CLOSECONTEXT(v_ctxhandle);

END;

This results in this XML

<?xml version="1.0"?>

<ROW>

<LAST_NAME>King</LAST_NAME>

</ROW>

<ROW>

<LAST_NAME>Kochhar</LAST_NAME>

</ROW>

<ROW>

<LAST_NAME>De Haan</LAST_NAME>

</ROW>

<ROW>

<LAST_NAME>Hunold</LAST_NAME>

</ROW>

<ROW>

<LAST_NAME>Ernst</LAST_NAME>

</ROW>

</ROWSET>

Now to further expand the example we can include some options to change the way the XML is handled.

DECLARE

v_ctxhandle DBMS_XMLGEN.ctxHandle;

v_clob CLOB;

v_offset NUMBER := 1;

BEGIN

v_ctxhandle := DBMS_XMLGEN.NEWCONTEXT (

'SELECT employee_id ID,

last_name,

to_char(hire_date, ''DD/MM/YYYY'') Hire

FROM employees

WHERE employee_id < 105');

-- Pretty print will do the nice indentation.

DBMS_XMLGEN.setPrettyPrinting(v_ctxhandle, TRUE);

-- The indentation width can be set.

DBMS_XMLGEN.setIndentationWidth(v_ctxhandle, 2);

-- Set the tag for the rowset

DBMS_XMLGEN.setrowsettag(v_ctxhandle, 'PEOPLE');

-- Set the tag for the row.

DBMS_XMLGEN.setrowtag(v_ctxhandle, 'PERSON');

-- when we retrieve row from this query only get two at a time, this can let

-- you do things like control the amount of data you show on one page.

DBMS_XMLGEN.setmaxrows(v_ctxhandle, 2);

-- Now do the query as before

v_clob := DBMS_XMLGEN.getXML(v_ctxhandle);

loop exit when v_offset > dbms_lob.getlength(v_clob);

dbms_output.put_line(dbms_lob.substr( v_clob, 32767, v_offset));

v_offset := v_offset + 32767;

end loop;

-- so this has displayed two rows and we can do some other processing then

-- process two more rows.

dbms_output.put_line('Can do some new page processing here');

DBMS_LOB.FREETEMPORARY(v_clob);

v_offset := 1;

v_clob := DBMS_XMLGEN.getXML(v_ctxhandle);

loop exit when v_offset > dbms_lob.getlength(v_clob);

dbms_output.put_line(dbms_lob.substr( v_clob, 32767, v_offset));

v_offset := v_offset + 32767;

end loop;

DBMS_LOB.FREETEMPORARY(v_clob);

DBMS_XMLGEN.CLOSECONTEXT(v_ctxhandle);

END;

Gives us XML like this. Notice the text 'Can do some new page processing here' is just to show where you could do further processing between section of XML output.

<?xml version="1.0"?>

<LAST_NAME>King</LAST_NAME>

</PERSON>

<LAST_NAME>Kochhar</LAST_NAME>

</PERSON>

</PEOPLE>

Can do some new page processing here

<?xml version="1.0"?>

<LAST_NAME>De Haan</LAST_NAME>

</PERSON>

<LAST_NAME>Hunold</LAST_NAME>

</PERSON>

</PEOPLE>

There are lots of ways of doing XML processing from PL/SQL and DBMS_XMLGEN seems to offer many options that can be explored further.

Thursday, 4 October 2012

Casting a Nested Table in a Cursor

Recently I did some work where we had a big selection of data that was returned by a function into a collection. The data needed to be manipulated in a few different ways. One of the great things in PL/SQL is you can cast the collection or nested table as normal database table.

This means you can use the processing power on SQL to do all sorts of things that would require a lot of work if you were just using standard array logic.

Below is an example of creating a simple nested table and then using SQL to sort it. This is a very simple example to show you how to use it. You can treat this table like any other table. Join it to normal database tables or even join it to another version of itself.

CREATE OR REPLACE
TYPE
pet_details AS OBJECT
(
pet_id NUMBER(3),
pet_name VARCHAR2(200),
pet_age NUMBER(3)
)
/

CREATE OR REPLACE
TYPE
list_of_pets AS TABLE OF pet_details
/

DECLARE
v_list_of_pets list_of_pets := list_of_pets();
v_pet_details pet_details;
-- pass the nested table in as a parameter to the cursor
CURSOR c_test (cp_input_list list_of_pets) IS
SELECT *
FROM TABLE(cp_input_list)
ORDER BY pet_age;

BEGIN
-- put some sample data into a nested table
v_pet_details := pet_details(1, 'Tom', 14);
v_list_of_pets.EXTEND;
v_list_of_pets(1) := v_pet_details;
v_pet_details := pet_details(2, 'Jenny', 2);
v_list_of_pets.EXTEND;
v_list_of_pets(2) := v_pet_details;
v_pet_details := pet_details(3, 'Rambo', 5);
v_list_of_pets.EXTEND;
v_list_of_pets(3) := v_pet_details;
-- Now pass the nested table into the cursor and loop
FOR v_test IN c_test(v_list_of_pets) LOOP
dbms_output.put_line('Show me some pets ' ||
to_char(v_test.pet_id) ||','||
v_test.pet_name ||','||
to_char(v_test.pet_age));
END LOOP;
END;