The Oracle DECODE function provides similar functionality to an IF-THEN-ELSE statement. The general format for an Oracle DECODE function is:

DECODE(expression, value1, result1 [, valuen, resultn], [default])

In the above example, expression represents what you want to evaluate. The valueX fields represent what you would like expression evaluated against, while the resultX fields are the values you want substituted if a match is found.

Anything you can do with an Oracle DECODE function, you could just as easily achieve with an IF-THEN-ELSE block. You would normally use a DECODE function over the IF-THEN-ELSE when you have a simple comparison or you prefer the readability of a DECODE function.

As a practical example, consider the small block of data below which might represent a subset of data from a multi-user system.

If you issued the following SQL statement against that data, you should expect the output below:

SELECT userid, username, DECODE(power, 1, 'Registered', 2, 'Trusted', 3, 'Moderator', 4, 'Author', 5, 'Administrator', 'Pleb') As status FROM Users;

The caveat of using the Oracle DECODE function is that the expression must evaluate to a single value. As such, you can not use an evaluation which has multiple possible values. In such a circumstance, you would need to find a way (read: algorithm) to make each possible value evaluate to a single value. By doing so however, you are removing the simplicity of the DECODE function and an IF-THEN-ELSE block would probably suit better.

Next time you’re going to reach for the trusty IF-THEN-ELSE block by default, consider using a DECODE function – in the right circumstances, it’ll make your SQL simpler.

The Oracle RETURNING clause was implemented as part of the Oracle 10g release and is used to return information about the effected rows after issuing Data Manipulation Language (DML) statements. Prior to Oracle 10g, you would have needed to work around not having this feature, most likely by issuing additional statements to the database.

The RETURNING clause has a few restrictions:

• it is only available for use on tables, materialised views, normal views based on a single table or an expression based on the previous three items
• it is only valid on a single-set aggregate. A single set aggregate is DML which only effects a single row or using an aggregate function in the RETURNING statement (such as SUM).

The general syntax for the RETURNING clause is:

INSERT INTO <table> (c1, c2, .., cn) VALUES (v1, v2, .., vn) RETURNING <expression> INTO <variables>
UPDATE <table> SET (c1) = (v1), (c2) = (v2), (cn) = (vn) WHERE <condition> RETURNING <expression> INTO <variables>
DELETE FROM <table> WHERE <condition> RETURNING <expression> INTO <variables>


This feature is particularly useful when INSERTing into a table, where the Primary Key is sourced from a sequence and fetched via a TRIGGER. In the below example, the ID of the newly inserted row is assigned to pContactID using the RETURNING clause. This is an elegant solution as it means you don’t have to SELECT the NEXTVAL from the sequence and assign that value to the ContactID during INSERT simply so you can return the new primary key value.

PROCEDURE Ins
(pContactID     OUT Contacts.ContactID%TYPE,
 pFirstname     IN Contacts.Firstname%TYPE,
 pSurname       IN Contacts.Surname%TYPE)
IS
BEGIN
 INSERT INTO Contacts
 (fname, sname)
 VALUES
 (pFirstname, pSurname)
 RETURNING ContactID INTO pContactID;
END;

You could just as easily use it to return the information about a row deleted, such as:

PROCEDURE Del
(pContactID     IN Contacts.ContactID%TYPE,,
 pFirstname     OUT Contacts.Firstname%TYPE,
 pSurname       OUT Contacts.Surname%TYPE)
IS
BEGIN
 DELETE FROM Contacts
 WHERE ContactID = pContactID
 RETURNING fname, sname INTO pFirstname, pSurname;
END;

Since the RETURNING clause is for use with aggregates, an example illustrating its use is in order. The below example modifies pContactID salary by pPercentageChange and subsequently returns the updated total company salary expenditure.

PROCEDURE UpdateSalary
(pContactID        IN Contacts.ContactID%TYPE,
 pPercentageChange IN NUMBER,
 pGrossSalary      OUT NUMBER)
IS
BEGIN
 UPDATE Contacts
 SET salary = salary * pPercentageChange
 WHERE ContactID = pContactID
 RETURNING SUM(salary) INTO pGrossSalary;
END;

The Oracle RETURNING clause provides the PL/SQL developer with a lot of flexibility. The real benefits however, come from the simplified PL/SQL and clarity gained in the code. If you’ve got a lot of application code or PL/SQL which isn’t utilising the power available to you – it might be time to undertake a clean up in your project.

During a recent release of a new ASP.net website, the launch went horribly wrong and required a rollback.

Setup

Scenario

Whilst the code was in the development environment, we weren’t seeing any issues. Once the code was published to a staging environment and the load on the temporary under powered server increased, we started to see errors creeping in. At the time, it was written off as being a combination of Oracle 10g Release 1 Standard and the server simply not having the resources to handle the load. We had previously seen poorly written legacy code bring a Dual 3.2Ghz Xeon running Oracle 10g Standard Release 1 (dedicated server processes) to its knees with similar style random errors.

Searching online seemed to reveal a common trend; it was an Oracle Data Provider problem. Unfortunately, everything we found was related to the version 9 data provider – while we were already running version 10. After four developers spending a few hours trying to resolve the error, a support request was logged with Oracle via MetaLink. Over the following three hours and a continuous stream of phone and email correspondence with Oracle, one of their technicians informed us of a patch set available for our current Oracle Data Provider.

Patch Information

Out of list of bug fixes, two items should be highlighted:

1. 4020081: As we were running short of servers during the development phase, we had Oracle 10g Release 1 Standard installed on a standard desktop machine with a single hard drive. Once the application was released to testing, the Linux ‘load’ on this machine was regularly breaking 15-20. As such, this could have been contributing to the Data Provider Internal Error.
2. 3893458: The legacy version of this application had been running on the v10.1.0.301 of the Oracle Data Provider for nearly a year without any issues. That code base made very limited use of PL/SQL stored procedures, while all new code was being funneled through PL/SQL. Strangely, the legacy code would have been firing the ExecuteReader() method, while the new code was firing ExecuteNonQuery(). If this was the cause directly, you would have expected those to be around the other way. I think it is worth listing here, as the decision to funnel everything through PL/SQL is a fundamental shift in the application architecture.

It should be noted that without the help of the Oracle Technician, we would have never found this patch on MetaLink in a timely fashion; which I would consider a serious shortcoming of the site. However, after downloading and applying the patch manually, the random errors immediately vanished.

There was a lesson learned from this experience, the Oracle Data Provider was discounted as a likely problem early on because we were using a newer version of the provider than that of the error reports we were seeing. Looking back on it, we should have immediately upgraded the Oracle Data Provider to the latest public release, simply to rule out a bug in an older version.