[PL/SQL] Oracle send mail through SMTP server with authentication

Sending mail through a PL/SQL script in Oracle is pretty easy thanks to the UTL_SMTP package.

Much like TELNET, sending a mail via our script will require us to use the HELO, MAIL FROM, RCPT TO and DATA commands. Thus, when sending a mail to multiple recipients - directly or in CC - we must issue the RCPT TO command once for each one and add the CC mail header to our message in order for them to appear as such.

[PL/SQL] Create and call Java procedure to read directory content

Sometimes even PL/SQL isn't enough; luckily Oracle gives us the ability to store Java functions and treat them as procedures.

Consider the following example: we know some files will be stored in a certain directory without knowing their names beforehand and we'd like to read and process those files from our PL/SQL code. By creating a simple Java function depending on the File class, we can solve our problem quickly and easily.

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

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

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.