Roll Your Own Functions

David Clement

September 2004

Oracle versions 7-8

It is easy for a programmer to create his or her own SQL functions and use them anywhere another SQL function can legally be used. Typically such functions are defined in PL/SQL packages and the most common reason to create them is maintainability: logic nested in-line in SQL can be hard to read and understand.

To create your own SQL function, declare the function in a package specification as follows.

          create or replace package pkgExample
          as
              function fnExample
                  (pcParameter in varchar)
              return number;
              pragma restrict_references (fnExample, WNDS);
          end pkgExample;

The pragma, which is required in some versions of Oracle and optional in others, asks the compiler to verify that the function makes no changes in the database and therefore can safely be called in-line. "WNDS" stands for "writes no data state." The pragma should always be used, even in those versions of Oracle where it is not required, because it eliminates a cause of run-time errors.

The body of the function is compiled as part of a package body, as follows.

          create or replace package body pkgExample
          as 
              function fnExample
                  (pcParameter in varchar)
              return number 
              is 
              begin
                  return to_number (pcParameter);
              end fnExample;
          end pkgExample;