It worked on my machine

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;

Tuesday, 18 September 2012

Nested tables example with multiset processing

A lot of people don't use array processing in PL/SQL. I don't know why. If they were programming in some other language they would probably use them all the time. The PL/SQL implementation uses different names like nested table but essentially these are just arrays and have some really useful features.
Like the ability to do complex comparisons of the entire contents of the array with a single line of code.

Here is an example where nested tables are used to work out if a role has any tables that have not been granted select access to a particular role.

What this does is select all the tables that exists in the example_owner schema and put them in one nested table.
Then get a list of all the the tables that have been granted to the ex_tabs_role and put those in another nested table.
Then see if there is any difference between the two nested tables.
If they are not exactly the same then using MULTISET EXCEPT DISTINCT find out which values are in the first nested table (hold the full list of tables) that are not in the second nested table (holds the list of tables that have already been granted to the role).
The resulting nested table (new_tables_nt) is the list of missing tables that we want to grant to the role.

DECLARE
cst_role CONSTANT varchar2(20) := 'EX_TABS_ROLE';
-- set up a type that will hold a list of table names
TYPE tables_nt IS TABLE OF VARCHAR2(100);
-- set up array to hold tables the role already has
role_tables_nt tables_nt;
-- set up array to hold tables that exist for schema
-- owner
all_tables_nt tables_nt;
-- set up array to hold the tables that need to be
-- added to the role
new_tables_nt tables_nt := tables_nt();

BEGIN
-- select the full list of tables we want to grant
SELECT alt.owner || '.' || alt.table_name
BULK COLLECT INTO all_tables_nt
FROM all_tables alt
WHERE alt.owner = 'EXAMPLE_OWNER'
AND alt.iot_type IS NULL;

-- select the list of tables we have already granted
SELECT dtp.table_schema || '.' || dtp.table_name
BULK COLLECT INTO role_tables_nt
FROM all_tab_privs dtp
WHERE grantee = cst_role;

-- We only need to keep processing if there is a
-- difference between the sets
-- As we are using nested tables we can just compare
-- if the whole table is the same
IF all_tables_nt != role_tables_nt THEN
-- with set logic find the list of tables that have
-- not been granted
-- This is full list of tables minus the granted list
-- then store this difference in the new_tables_nt
-- array
new_tables_nt := all_tables_nt
MULTISET EXCEPT DISTINCT role_tables_nt;

-- check if the difference is that we actually need
-- to add tables
-- if it is a postive number it means we need to
-- add tables
if new_tables_nt.COUNT > 0 THEN
-- Now actually do the grants of the new tables
-- to the role
FOR i IN new_tables_nt.FIRST..new_tables_nt.LAST LOOP
-- display what the differences are
dbms_output.put_line(new_tables_nt(i));
-- Now grant the differences to the role.
EXECUTE IMMEDIATE 'GRANT SELECT ON ' ||
new_tables_nt(i) || ' TO ' ||
cst_role;
END LOOP;
END IF; -- there are values to add
END IF; -- sets are different
END;

Note that if you don't check for new_tables_nt.COUNT > 0 then when you try to excute the for loop with no details in it you will get

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 40

Because it cannot loop from first to last as new_tables_nt.FIRST and new_tables_nt.LAST will both be NULL.