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

Oracle SELECT TOP

When working with Oracle, you may need to select the TOP row (or top N rows) from a query result, but you'll find there is no such statement.

While you can certainly use ROWNUM, you should note that it is executed BEFORE any ORDER BY statement, thus producing wrong results in that case. The solution in this case is as simple as:

SELECT *
FROM (SELECT * 
        FROM myTable
        ORDER BY myColumn)
WHERE ROWNUM = 1;

By encapsulating your query with the ORDER BY clause inside another query using ROWNUM, you now effectively order the dataset before extracting the TOP row. You may obviously use it as TOP N by setting another value for ROWNUM.