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.

Oracle Certified Associate

Today I passed the second of two exams required to become an Oracle Certified Associate. I called up Oracle University. They said my certificate should arrive by mail within 30 days. I am going to hang this bad boy up on the wall.

The exam I took today was "Program with PL/SQL". It was a very difficult exam. There were a total of 66 questions. You needed of 51 to pass (77%). I got 55 questions right (83% correct). That was enough to pass. But I did not feel confident.

My exam report showed I needed help in a whopping 10 areas. Some of my weaknesses were understandable. I took an instructor led course to prepare for the exam. We did not have time to really go over dependencies. So I figure I got all those questions wrong on the exam. And I also know I am weak on data dictionary views. At least I was good enough to pass. I can't wait until I get my certificate.

Day of Reckoning

Today was the big day. I was scheduled to take exam 1Z0-001. It is the first of two to get Oracle certified. I was a little concerned about this one because I tried to learn the material on my own. Even though I purchased some Oracle self study software, I found it useless. However I bought a good self study book which I devoured.

Luckily I set aside a good deal of time to get to the place. Somehow I took the wrote street and ended up past the testing center. I doubled back and got there with time to spare. There was a disturbing sign on the side of the building which read, "For elevator emergencies, call the police". This was extra troubling since I had to leave my cell phone behind to adhere to the test rules. As I entered the building elevator, I noticed there was no phone in there. I pressed the button for my floor a couple times. The elevator did not move. At that point I decided to take the stairs.

I was the only guy taking a test at this center today. This was the first time I sat for an Oracle exam. They gave me some "scratch paper" and two permanent markers. They planned to collect this scratch paper at the end of the exam. It took a while to read the terms and conditions of the test. However once I got started it seemed to go well. I am bound by the rules of the test to divulge no other information about it. I passed the test with ease, getting 50 out of 57 questions correct. That equates to an 87%. You only need to get 39 out of 57 correct to pass (which is 68%).

Tomorrow I take my second of two tests. Hopefully that one will also be easy to pass. I went to an Oracle instructor led training class for that one. So I assume I will do even better on that one.

The Cursor Loop

I continue to share some information I learned during my instructor led training at Oracle. A random tidbit is the CURRENT_DATE function. It operates like the SYSDATE that you know and love. However it automatically adjusts for your local time. How do you like that?

FOR LOOPs that iterate through a cursor as called Cursor Loops. The cursor active set is like the result set in Java. The query for your cursor takes place when you open the cursor. Note that the Cursor Loop does not perform any better than manually opening a cursor and fetching rows. A good thing about the Cursor Loop is that it automatically closes the cursor even if an exception is raised.

It is a good design to tie the cursor record type to the cursor by means of the %ROWTYPE. When you have fetched some records out of a cursor, the total number of records you have fetched so far is stored in the %ROWCOUNT variable. That's it for now. Next time I will probably talk a bit about FOR UPDATE OF and WHERE CURRENT OF. See you then.

Records and Collections

My instructor led training from Oracle is over. Tomorrow I take the first of two tests needed to get Oracle certified. I feel like I have learned a lot over the past week. There is still a little more review required.

I want to go back over some things I learned on the second day of my training. This covered PL/SQL records and collections. Both of these structures are stored in memory.

A record is like a database table. However the record holds one row of information to make the analogy accurate. And a collection is like the columns of a database table.

Records can be composed of a number of things, including other records. However they are usually composed with scalars (such as NUMBER, CHAR, etc). The reason for using collections is so you can treat the whole collection as a unit.

I will speak briefly about the collection type "index-by table" here. These are also called associative arrays. Previously I believe they were called PL/SQL tables. These are essentially hash tables. They contains pairs of keys plus values. The values can be NULL. Each pair is an association. The keys are unique, but can be sparse.

Control Structures

One of the topics we covered in my second day of instructor led training was control structures. Here a some tips and specifics I learned. The PL/SQL IF statement treats NULL and FALSE the same way. They fail the IF test and cause the code to go on. Also if you include an ELSE clause in a IF statement, then one of the blocks in the construct is guaranteed to execute.

The CASE expression is one that I was familiar with. Something I learned was that the CASE expression itself returns a NULL if none of the cases matched. I also was told that a CASE expression performs similar to the IF ... THEN construct.

Loops where you perform the test at the top are called "top tested loops". That makes sense. A WHILE loop is an example of that. However a LOOP where you have the WHEN EXIT clause at the top is also a top tested loop. FOR loops can have ranges that are themselves variables. They do not have to be literals. If the upper and lower bounds of a FOR loop are the same, the loop executes exactly once. Note that you cannot change the value of the FOR loop iterator manually inside the loop. In Oracle 11g, the CONTINUE statement can be used in any loop to make it move on to the next iteration immediately.

Precedence

Today was the last day of a 5 day course on PL/SQL programming. There was a lot packed into this week. I learned some much that my brain is bursting. Now I will go back to some things I learned on Day 1.

Nothing in PL/SQL is case sensitive. So you can issue a SELECT or a select, and the results will be the same.

In many scenarios, PL/SQL can figure out what the name you are referring to based on the context. However there are times when a name may be ambiguous. At that point, PL/SQL will look to the following object in this order to figure out which one you are referring to: column name --> variable name --> table name.

For example, suppose you have a column, variable, and table all named TEMP. If you refer to TEMP in the WHERE clause, PL/SQL knows you mean a table from the context. However if you reference TEMP elsewhere, then PL/SQL will first consider it to be a column name first.

The MERGE and Other Info

Day 1 of my Oracle PL/SQL programming course covered so much that I am still blogging about it. The instructor showed us an example relating to the Oracle MERGE functionality. I have heard of MERGE. But I did not know what it did. Our instructor then gave us the executive summary of what MERGE was.

You might have a Production database table with new information that you wish to promote to your data warehouse. You can accomplish this with a MERGE statement. It determines whether the data warehouse target already has a row for the Production one. If so, it is MATCHED and you do an UPDATE. Otherwise you perform an INSERT into your data warehouse.

Our instructor went over some other facts that were not in the class presentation materials. He explained that variables using %TYPE for definition, as well as the rest of the declaration section, are evaluated at compile time.

We also debunked the term predicates. These are parts of your WHERE clause. I guess that is just a fancy way of saying it. I have heard the data mining people talk about the complexity of their predicates. That just means that have a lot in their WHERE clauses.

Oracle 11g Specifics

There is one thing I like about attending technical training. They fill you in on the latest change. For example, in my PL/SQL programming course I am hearing about features in Oracle database version 11g. We are still using Oracle 10g at work.

Here is an example of an Oracle 11g specific change. Previously you had to SELECT sequence NEXTVAL values FROM DUAL. But in 11g, NEXTVAL is a function. So you can assign its value directly to a variable.

Prior to Oracle 11g, you were able to place labels in your PL/SQL code but enclosing them like this <>. However in 11g, you put a BEGIN keyword before this label, and end the label section with the END keyword. Oracle 10g did not support this BEGIN and END to demarcate the block with the label.

PL/SQL Trivia

I am attending an instructor led PL/SQL programming class. The environment is good. We get free drinks and snacks. Oracle has provided copious printed documentation for us to follow. The best part of the class is the instructor. He is first rate. I take lots of notes, as much of what he shares is not part of the official class.

Now for starters, SQL is a set language. PL/SQL, on the other hand, is a procedural language. When you write an anonymous block of PL/SQL, you can include the optional DECLARE keyword to start the DECLARE section. Procedures and functions also have this section, but they do not use the DECLARE keyword. This section starts after the IS or AS keyword.

In PL/SQL, the inclusion of NULL in your expression often turns the whole result NULL. However there are some exceptions. If you concatenate a character string with NULL, the NULL acts like an empty string and leaves the character string unchanged.

Normally I use a NUMBER variable type. I was enlightened when I found out that the optional precision for this type is the total number of significant digits in the value. It is not the total digits, as you can have huge numbers with more effective digits than are allowed for the precision. I also have discovered that the BINARY_INTEGER TYPE, which is equivalent to PLS_INTEGER, is much more efficient than NUMBER.

SQL Developer Trouble

Yesterday I started my instructor led course on PL/SQL programming. Our setup included Oracle SQL Developer running in a virtual machine. SQL Developer is a free tool for SQL and PL/SQL work. It is a scaled down tool compared to Oracle JDeveloper, which has all kinds of features for Java development.

Right now the class has SQL Developer 1.2 installed. Now this is an older version of the software. And I am seeing a bunch of problems that are annoying. Each time I try to save an SQL file using the File Save command, the program tries to go to the A: drive. This is a virtual machine. So there is no A: drive. The program nonetheless keeps trying to access this non drive.

I can cancel out of the A: drive problem eventually. However I need to run a lot of PL/SQL scripts. The routine is to click the button to run the script. Any text sent out using DBMS_OUTPUT is supposed to be captured in a separate SQL Developer tab for DBMS_OUTPUT. That tab allows you to SET SERVEROUTPUT ON with the click of a button. The problem is that this usually does not work and none of the output is displayed.

Some other developers in the class have found a weird combination that works. You must (1) disconnect from the database, (2) reconnect to the database, (3) run your script, (4) toggle SERVEROUTPUT to OFF, and (5) toggle SERVEROUTPUT back ON. This usually cures the problem. But that is a lengthy work around to getting my output to be displayed.

The instructor tried upgrading to the latest version of SQL Developer. It did not help the DBMS_OUTPUT woes. I am not sure why this is happening. However it might be time to drop all the way back down to using SQL*Plus. I am confident that if I SET SERVEROUTPUT ON, I will see the DBMS_OUTPUT text in the SQL*Plus window. We have the Oracle client installed on the virtual machines. This is truly a pain. At least I did not have to pay money specifically for SQL Developer. I did pay a lot to attend the instructor led training though.

Oracle Basics

Here are some more facts I picked up from Chapter 1 of my SQL and PL/SQL self study guide. ROWID is a pseudocolumn. It provides a unique value for each row in your table. It is the fastest way to access any given row.

You can use set operations to combine the results of two queries. One such operator is UNION ALL, which gives you results from either of the queries, including duplicates. The UNION operator provides a similar capability. But it removes the duplicate records from the result.

You can send the output of the Oracle SQL*Plus tool to a file. This is done with the SPOOL command. You can also send the SQL*Plus output to the printer with the SPOOL OUT command.

I may come back and share additional details from the other 9 chapters of my self study guide. However I just started my instructor led training for my Program with PL/SQL class. I suspect I will be sharing tips from what I learn in person every day. Either way you benefit.

Database Theory

I have been reading a study guide to pass Oracle certification test SQL and PL/SQL. Having finished the book, I am going back over the things I have learned. Chapter 1 starts out with basic relational database theory. It then beings to show how this translates into the Oracle database.

There is logical database modeling which is is at a high level of abstraction. Real life items such as nouns are entities. These entities have characteristics called attributes. The entities are related by relationships.

When you get into the physical design of the database, entities become database tables. Attributes become columns in the tables. And relationships between the entities become table constraints. Instances of the entity are records in the table.

Next the chapter went on to some Oracle database basics. There are many native database types. One of them is the NUMBER, where you can specify the maximum total digits in the value, and the precision which is the number of digits after the decimal point.

I will do another post in the future with more Oracle basics. Some topics I might hit are ROWID, UNION, and SPOOL.

Views Revisited

I received and installed a couple Oracle self-study CR-ROMs. This was very exciting. I was hoping to be enlightened. Instead I spent a lot of time struggling with the software.

It was hard to get used to the navigation system. Then I tried to enter answers for questions it asked me. The darn thing kept marking me wrong because I did not put spaces in at the places it expected them. Now I can understand if Oracle itself required spaces to be a certain way. But they do not matter. This was just poorly written training software.

For now I have went back to my independent study and search for information on the web. I am going to not use the Oracle CD-ROMs even though they cost almost $500. Now let's talk about some details I have learned from the web. Views are based on tables which are called Base Tables in this context. Views can also be based on other views. You need explicit grants to the Base Tables. You cannot obtain the permissions through a role to use them in a view.

A view can be created even if the SQL is erroneous as long as you specify the FORCE option. There are a lot of scenarios where you cannot updates values through a view. They have to do with the nature of the query used to create the view. Any of these prevents the update through the view: set operators such as UNION, DISTINCT, aggregate functions like COUNT, GROUP BY, and ORDER BY.

There are some other restrictions like this. But these are ones I would use myself. You can also specify a WITH CHECK OPTION when creating a view. That means a user cannot update or insert through the view if the resulting rows would not be visible using the view query.