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;