Oracle 11g regular expressions cheatsheet

Here's a useful cheatsheet for Oracle 11g's regular expressions from psoug.org:

General Information
Anchoring Characters	Character Class Description ^ Anchor the expression to the start of a line $ Anchor the expression to the end of a line

I amSterdam

Dragons you say? I couldn't see any with all those flying unicorns in the way!

Click on the image to go to the full album

Oracle update table while looping on it

You may find yourself needing to update a table while looping on it:

DECLARE
newValue someType;
BEGIN
FOR myCur IN (SELECT key FROM table)LOOP
    newValue:=myFunction(key);
    UPDATE table
    SET column=newValue
    WHERE key=myCur.key;
    COMMIT;
END LOOP;
END;
/

This however is not possible in Oracle, instead you should fetch the table keys into an array before and then cycle it:

DECLARE
newValue someType;
TYPE vArrayType IS VARRAY([size]) OF someType; --use VARRAY if you already know the size!
myArray vArrayType;
BEGIN
--fill the array
SELECT key
BULK COLLECT INTO myArray
FROM table;

--loop on it
FOR i IN myArray.FIRST..myArray.LAST LOOP
    newValue:=myFunction(myArray(i));
    UPDATE table
    SET column=newValue
    WHERE key=myArray(i);
    COMMIT;
END LOOP;
END;
/

Oracle error ORA-04091 table is mutating, trigger/function may not see it

When working with triggers in Oracle, you may encounter the "ORA-04091 table is mutating, trigger/function may not see it" error. This may happen when you have a trigger running on a table for an insert, update or delete operation and you're running a function which accesses a table being modified at the same time as the trigger.

The fact is, you may not perform further operations on a table which is currently being INSERTed into, UPDATEd or DELETEd FROM in a single statement. For example, you have a BEFORE trigger on myTable and try to run this statement:

UPDATE myTable
SET myColumn = myFunction(someValue);

It will result in our error if myFunction works on myTable to produce a result; this other statement however will not raise an error as the function and trigger executions will be kept separated:

myVariable := myFunction(someValue);

UPDATE myTable
SET myColumn = myVariable;

Windows delete file type association

Some extensions in Windows are automatically registered and assigned to be opened by a specific application, others can be manually set via the Open with... dialog or Control panel->Default programs. In either case however, you cannot delete an association.

You may do so by directly editing the registry (Start->Run->regedit):

Assuming you'd like to remove the association for the EXT extension, browse to HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\FileExts\

And delete the subtree named .EXT (with the dot before). Now browse to HKEY_CLASSES_ROOT\ and delete the subtree named .EXT (again, with the dot before) and EXT_auto_file