Previously I had gone over what I learned about Oracle procedures. One thing I forgot to mention is that Oracle stores the procedure names in capital letters unless you surround it in quotes. Functions are very similar to procedures. I have been told that 90% of information on procedures applies to functions as well.

A function must be part of an expression. You just can’t have a function by itself to serve as a PL/SQL statement. You can pass a column name from a query into a function as a parameter. The function should then be in the SELECT clause. Note however that a function may not be in the FROM clause of a query.

Like procedures, functions can take parameters. Parameters may be optional. If you decide to not include a parameter, you just omit that position when making the actual call to the function. Note that a function parameter with a value of NULL is not the same as not passing a value. Passing a NULL will ensure the associated parameter gets set to NULL for the function call.

As an aside I want to mention some experiences I had using Oracle SQL Developer. We used this tool in our training class. It is a relatively new free product from Oracle. I did find a couple of problems which I consider bugs when dealing with functions and procedures in it. At times I typed in a parameter name, and then it disappeared from SQL Developer. I also found that I was not able to delete all the parameters from a function using its function wizard.