Oracle PL/SQL Best Practices

Name

SQL-05: Hide reliance on the dual table.

Synopsis

This is a special case of [SQL-04: Put single-row fetches inside functions; never hard-code a query in your block.] but is worth mentioning. The dual table is a “dummy” table that is used by Oracle itself and by many developers to access functionality in the SQL engine that is otherwise not available in PL/SQL.

Use of the dual table is, therefore (and by definition) a workaround or “kludge.” We all know we have to do these things, but we also know or hope that over time, we will no longer have to do them. So hide your kludges behind a function or procedure and then, when they are no longer needed, you can change the implementation without affecting those usages.

Example

Instead of:

DECLARE
   my_id INTEGER;
BEGIN
   SELECT patient_seq.NEXTVAL INTO my_id
     FROM dual;

you should build yourself a function:

CREATE OR REPLACE FUNCTION next_patient_id
   RETURN patient.patient_id%TYPE
IS
   retval patient.patient_id%TYPE;
BEGIN
   SELECT patient_seq.nextval
     INTO retval
     FROM dual;
   RETURN retval;
END;

And then you only need to write this to get your next primary key value:

DECLARE
   my_id INTEGER;
BEGIN
   my_id := next_patient_id;

Benefits

You gain the ability to remove workarounds and kludges from code more easily as underlying software improves.

Resources

nextseq.sf : A function that uses dynamic SQL to offer a single function that retrieves the n th NEXTVAL from any sequence you specify.