It worked on my machine: 2013

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.