11/05/2013

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

05/04/2013

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

22/03/2013

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;
/

06/03/2013

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;

05/03/2013

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