Functions

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.

PLS-00405

I was tasked with modifying some triggers to remove hard coding and use a lookup table. This seemed like a trivial task. However I ran into a number of interesting problems. One of the triggers would not compile. It kept giving me a PLS-00405 error. The text of this error is that a subquery is not allowed in this context.

Here is what I was trying to do. There was a test in a large IF statement which checked whether a certain column was in a list. Previously the list was hard coded. So I just replaced that list with a SELECT from my new lookup table. I figured if you could choose from a list, you could just as easily choose from the results of a SELECT statement.

It turns out I was wrong. You cannot do a subquery like that in an IF statement. You need to break the SELECT out into its own statement, storing the result in a temporary variable. Then you can use this temporary variable in you IF clause. Go figure.

A few weeks ago I went through a week long instructor led training course on PL/SQL. I know we went over triggers. However I don’t recall this detail being mentioned. Sometimes you can only learn a language’s nuances when you are deep in the trenches doing real work. I am glad that my current project gives me plenty of these opportunities.

Procedure Parameters

Procedures in Oracle can take optional parameters. Each parameter you specify can have a mode. The valid mode types are IN, OUT, and IN OUT. If you do not specify a mode, it defaults to IN. Note that while parameters also have a type, you do not specify the size of precision of types which normally have this information.

Here is some information which is counter intuitive. IN parameters are passed by reference. While OUT parameters are passed by value. Normally you would think it would be the other way around. At least that's how I think C, C++, and Java do it.

Some people think an OUT parameter is just like a return value. However this is not the best analogy. I heard it explained to me that it is more like the caller passing a bucket to the procedure. The procedure then fills the bucket with a certain OUT value.

Note that I have not spoken much about IN OUT parameters. I do not think I have used them much. My instructor for PL/SQL programming said that IN OUT parameters are just not that common. That makes sense. When do you need to receive a value in, and then turn around and want to overwrite it with something else?

Procedures

Today I want to share the wisdom I acquired during instructor led training on the topic of stored procedures. Most of the information about procedures applies to functions as well. PL/SQL code that needs to be reused is not normally contained in an anonymous block. Instead it is placed in a named block such as a procedure.

A benefit of using procedures has to do with how the code is cached in the database,. The SGA is the area of the database which holds your code,. It uses a least recently used aging process to keep frequent code in memory. If a procedure is used often, it will most likely stay in memory and be faster to call. You also have the option to manually pin objects down in the SGA for performance reasons.

Oracle applications such as forms also contain procedures. However unlike other procedures, the code for these procedures does not reside in the database. They reside with the application. In the example of the forms application, the forms stored procedures are housed in the forms application server.

You create a procedure using DDL. Starting in Oracle 10g, the procedure compilation process will give out warnings as well as errors if there are any. These warning are non critical recommendations to make changes that affect performance for example.

A stand-alone procedure’s name must be unique within the schema. Procedures can optionally take a number of parameters. There are rules governing the type and use of these parameters. I will cover that in a future post.

Exceptions

Oracle has a number of predefined server exceptions. Each of them has a name. However there are very few of them that are generic. You can catch and handle these exceptions in your PL/SQL code.

You can also generate your own exceptions. For this you use the RAISE_APPLICATION_ERROR function. You pass it an error number. The range of user defined error numbers is -20,000 to -20,999.

You can catch specific exceptions in your exception handler section. I already knew that you could also catch all types of exceptions using the WHEN OTHERS clause. However I also found out that this cause must be last in the list of exceptions that are handled.

SQLERRM is a function that returns the message of an exception that got raised. Similarly SQLCODE is a function which returns the error number of an exception. Both of these cannot be used in a SQL statement. You need to assign the return value of these functions to a variable before using the values.

Sor far I have only covered the information I learned in the first two days of instructor led training. In a future post I will go into writing your own procedures and functions in PL/SQL.

SQL Statements

I think I have finally recovered from studying and taking the two exams required to get Oracle certified. Now I am slowly putting away all my training materials. I still have not done anything with the sorry self study software I bought from Oracle. Here are some things I learned from instructor led training for the exams.

You would be surprised where you can stick a SQL statement in Oracle PL/SQL. When you do a FOR loop with a cursor, you have usually defined the cursor already. However you can put the SQL statement right in the FOR statement. This is creating an anonymous explicit cursor on the fly.

You can also put a SELECT statement right in a part of the WHERE clause. The result of that SELECT statement is an inline view that Oracle uses to limit the rows of your main query.

I could write a whole book chapter on the subject of locking, and how it works in Oracle. However I will just mention it briefly here. There are two ends of the spectrum with regards to how you handle locking. Optimistic locking is where you lock only 1 row at a time, and assume all your locks shall work. On the other hand there is pessimistic locking where you lock all the rows you need to update up front. Then if this lock succeeds, you know you will be able to update all the rows without contention.

Pseudo Columns

Having spent a week attending instructor led training, I still have a lot of wisdom to share. I first want to talk about pseudo columns. They are like columns. But they are not actual columns in the database. An example is ROWNUM, which is a number representing the order that the row was selected in.

Another pseudo column is ROWID. This is a base 64 value. It is the fastest way to access a row in a table. If you specify FOR UPDATE OF in your explicit cursor definition, a lock will be placed on the rows that you are selecting for update. This has a number of benefits.

The FOR UPDATE OF clause can be combined with a subsequent WHERE CURRENT OF clause in a DML statement. Normally the DML will have a WHERE clause that you define. It causes the database to find the row(s) you want to update. However if you use a WHERE CURRENT OF clause, the database will immediately use the row specified by the record fetched from the cursor to do the DML.

The beauty of WHERE CURRENT OF is that the database does not need to determine which row to act upon. It already know the row that was selected in the cursor. The subsequent DML using WHERE CURRENT OF acts as fast as using the ROWID. And that, as I explained above, is the fastest way to access a row in the database.