Checking for hyphen and space with regexp_like

 If you need to check that a string only contains a certain set of characters then regexp_like can be very useful.

However you can have some trouble when trying to check for things like spaces and the hyphen symbol.

Through a bit of trial and error is seems like placement of the characters to check in the pattern section of the condition makes a difference.

For example if what we are trying to do is find a string that contains any character not in a set we define (using the ^ to indicate characters NOT matching the pattern) and that set contains a hyphen as an allowable character then the following falsely says that the string violates the regular expression. The hyphen is after the underscore in the pattern.

SELECT 'TRUE'
FROM dual
WHERE regexp_like('This is a string with a - hyphen', q'[[^a-zA-Z0-9._-~@/ ]]') 

This one results in ORA-12728: invalid range in regular expression. The hyphen is after the slash in the pattern.

SELECT 'TRUE'
FROM dual
WHERE regexp_like('This is a string with a - hyphen', q'[[^a-zA-Z0-9._~@/- ]]') 

And this one works as expected. The hyphen is at the start of the pattern. It passes as the string doesn't contain any characters other than those allowed in the pattern.

SELECT 'TRUE'
FROM dual
WHERE regexp_like('This is a string with a - hyphen', q'[[^-a-zA-Z0-9._~@/ ]]') 

I also found that space seems to work when it is defined at the end of the pattern. Originally I had space escaped with \ but it is not needed.

ORA-01446 when trying to delete in an Oracle Form that is based on a view with an instead of trigger

Issue.

The view that is used as the basis of a block contains a union or some other attribute that means the following error (ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc) is raised when a record is deleted in the Form.

Solution.

By default Oracle Forms uses the rowid to work out the records to be updated and deleted. As we can't use the rowid in this case we need to change the block Key Mode from the default value of Automatic to Updateable

Then in the block set the items for the which ones define the primary key of the rows to be deleted or updated. These will be used in place of the rowid to do deletes or updates.Set the columns that are Key columns for each item.

Here are the details of the block Key Mode attribute from Oracle Forms help.
Updateable 
Specifies that Oracle Forms should issue UPDATE statements that include primary key values. Use this setting if your database allows primary key columns to be updated and you intend for the application to update primary key values.

Replacing whole words using regular expressions in PL/SQL

Recently we had a need to replace a string with another string in PL/SQL but we only wanted to do that if it was a whole word string that matched. Also the string could contain an underscore and be part of a code example as we were using dynamic SQL. So we needed to preserve operators in the code.
So for example we might want to replace the string v_cat with v_dog in this code sample.

select emp_id, max(v_cat)
from employees
where v_cat>100
and v_cat_owner is not null;

We don't want to change v_cat_owner though as that is a different string. So we can't just do a normal replace as it will make it v_dog_owner.

After a lot of Google use and some trial and error, the following use of REGEXP_REPLACE was found to work

SELECT REGEXP_REPLACE ('select emp_id, max(v_cat)
from employees
where v_cat>100
and v_cat_owner is not null;', -- input string
'(\W|^)?v_cat(\W|$)' , -- \W is for a nonword character
'\1v_dog\2', -- replace with v_dog
1, -- position, first character
0, -- all occurrences
'i') -- i is case insensitive
FROM dual

Which changes the string to

select emp_id, max(v_dog)
from employees
where v_dog>100
and v_cat_owner is not null;

Further REGEXP_REPLACE info from Oracle at https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_regexp.htm#ADFNS235

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;

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;

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