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.
Teamwork Challenges - One of the newer guys on the team asked our project manager for some info. He needed to figure out how to get his scripts logging in to run unattended. Th...