Passing My Test

We interview a lot of candidates for developer positions at my company. I only look for three technical competencies: C++ programming, Windows framework programming, and SQL skills. Let me focus a little on the third trait.

Now I don't need an Oracle expert for my team. We already have that. But I do need somebody who knows how to construct basic SQL statements. I need to see evidence of hands-on experience in this area. So I asked about the clauses in a SELECT statement.

I want to hear that a candidate knows that GROUP BY treats the rows selected as one record or group. I also get impressed if the candidate can explain that HAVING restricts the results of GROUP BY.

With that I have given away one third of the keys to our kingdom. Study up your C++ and Microsoft Foundation Classes programming, and you have yourself a job.

Oracle Certified Professional

Today I started reading up to study to be an OCP. My plan is to be an Oracle Certified Profession in PL/SQL programming. I read the first and second chapters in the Oracle training for this exam. I will be sharing what I learned later.

For now I continue discussing some things I learned reading a book on Oracle 11g. Triggers are associated with a single table. The trigger gets dropped automatically if the table is dropped.

An Oracle user have some other names. It is called an account. It is also called a schema. This means the user has a password for authentication. The user potentially owns some database tables, as well as other database objects.

Next time I will discuss some standard power user accounts in Oracle, as well as the clauses in a SELECT statement.

11g Background Processes

The Oracle 11g database has familiar processes running in the background. Most of these processes were there in prior version of the database. However it seems like there are new names for these well known processes. Let's go over them.

There is the database writer called dbw0. Previously this was known as dbwr. There is also the log writer which writes to the redo logs. Pmon is the process monitor. Finally the archiver copies online redo logs to secondary storage (disk).

Obviously there are a lot of other background processes that make up the database instance. I am not as interested in them as I am a PL/SQL programmer, not a DBA. Next time I will go over simple ideas such as triggers and users.

Oracle Database 11g

I recently picked up a book from the library titled Oracle Database 11g A Beginner's Guide. I was excited because I thought I could learn the new PL/SQL features in 11g. Well this book did not specialize in PL/SQL. It also did not specialize in just 11g technologies. The book covered everything in Oracle 11g. That is just too broad. But I did learn a thing or two.

Oracle first ran on a Digital VAX system. That is strange. I assumed it first ran on some flavor of UNIX. An instance is a set of processes in memory. I frequently hear about the Redo Logs from DBAs. These are essentially transaction logs.

Another common term DBAs use is the tablespace. That is a fancy way of describes a disk file the databases uses. Another file of interest is the Spfile. It is the server parameter file. This file has startup parameters in it.

I will be blogging about this 11g book for a little bit. Next time I will cover the new names for the background processes in Oracle 11g.

Five Become One

I got a call from my team lead yesterday. He said he was swamped with the task that is due in a few days. He needed help writing a stored procedure to create a temporary table. The query was apparently all ready. It just needed to do it in PL/SQL. Although I was busy, I jumped at the opportunity.

Later I got the specs for the task. It was more complicated than I thought. there were a total of 5 queries. This was tricky because each of the 5 queries produced different columns of results. However they all needed to be combined into a big table.

I decided there was no way to CREATE a table AS SELECT FROM because there were multiple queries. So I determined the list of columns, and manually created the table with code. The create statement was so huge that I had to break the table creation down into one CREATE TABLE and multiple ALTER TABLE dynamic statements.

It was quite a chore to handle all the cases where a row in the resultant big table was sparse. Then I ran into more trouble when my dynamic statements to update pieces of the resultant table choked on some NULL inputs. That required some custom logic. By the time I was done, my script was around 1000 lines long. I did not leave work until midnight. What a chore.

Back To Basics

We have a new developer on our team. He is smart. He has a PhD in computer science. But there were some concepts he was not getting. So I had to break it down for him.

There is a text file that contains the source code for an Oracle PL/SQL package. You need to log in as the schema owner. Then you can compile the source code into the database. Once that is done, there is no relation between the source code file and the stored procedure in the database. The code gets stored within the database at compile time.

My peer thought that he could somehow step through the source code file on the hard drive. That's not how you do thing. I recommended he just add some logging to his new functions. He later came back saying he encountered problems. It did not take long to diagnose the problem.

I mentioned earlier that the source code upon compilation generates a package in the schema of the user logged in during compilation. We have users which access tables and packages in the main schema by use of synonyms. However you need to log in as the schema owner to modify that package. The code is not written to explicitly reference the schema owner. I have been doing these things for a long time and take them all for granted. Sometimes you need to step back and understand another person's perspective to help them see the light.

Oracle 11g Release 2

I have read some blurbs about the Oracle 11g Release 2 of the database. So I thought I would mention some things I have learned that are going to be in this release.

You will be able to run 11g on a cluster of non-Unix machines. For example you could string together a couple Intel machines and spend less money. Conversely you can run 11g RAC on a single machine, and still take advantage of RAC features.

11g R2 has new and improved data compression. Anything that saves disk space is a good thing in my book as long as it works correctly. Finally you will be able to do a hot application upgrade in 11g without shutting down the system. That's hot.