Web Learning

When I first started studying for the Oracle SQL and PL/SQL certification exam, I had no study materials. So I just Googled the web for information. This helped me learn a lot. However it was a slow process as I had to cull through the query results.

This all changed when I got the OCP Oracle 8i DBA SQL and PL/SQL study guide book. I have gone through all 10 chapters. It was great because there were many tough example questions at the end of each chapter.

Now that I am done with the book, I am going over my notes from when I looked for material on the web. And I am finding a bunch of gems. For example, you can use the ALTER TABLE statement to do things with columns in a table. Most of the time, this statement does not include the word COLUMN in the command. That applies to adding and modifying columns. However to remove or rename a column, you have to use the word COLUMN. It is difficult to keep the syntax straight.

Let's talk a little bit about constraints. You do not have to name them when you create them. Oracle will give them a default name. Constraints are for columns in tables. Views cannot have constraints. Views can be created with deferred constraints. That means the constraint is not enforced until the transaction is committed.

You cannot drop a table unless you first drop some constraints which restrict it. For example, if you have a child table with a foreign key constraint pointing to the parent table, you cannot drop the parent table before this foreign key constraint on the child is dropped.

Records and Collections

Last night I finished up my study guide book on Oracle SQL and PL/SQL. There was a lot to take in. The second to last chapter was on records and collections.

Records are structures that are composed of fields. Those fields can be scalar types (like VARCHAR2), or they can be record types themselves.

Explicit records are defined when you list out each field and its type. An implicit record is one which takes its type from another object using %ROWTYPE.

I already kind of knew about implicit and explicit records. However I discovered that other things I knew were also implicit records. This includes the record in a FOR LOOP when you loop through a cursor. It also includes the :OLD and :NEW modifiers in a trigger.

You can assign one record variable to another if they are of the same exact type. You cannot do this if they are similar types, even if those two different types match field by field. Also you cannot use records as VALUES in an INSERT statement.

The second to last chapter went over the different collections available in PL/SQL. I will just mention the first one here. They are called Index-by Tables. Previously they were called PL/SQL tables. You can put any database types in these collections, as well as PL/SQL specific types. Maybe I will write another post on the other types of collections in the future.

DML and PL/SQL

Today I received my CD-ROM self study software from Oracle. I have not installed it yet. I am trying to finish my self study book first. This evening I read the chapter on PL/SQL in the book. Specifically they went over how you do DML within PL/SQL blocks.

The basic PL/SQL SELECT statement only allows you to retrieve one row into a variable. Any more rows coming back from your query will cause an exception in your code.

When you do issue a SELECT in a PL/SQL block, you are causing an implicit cursor to be defined. This cursor is controlled by PL/SQL. It is opened, your statement SQL is run, then it is closed. This all happens automatically. For this cursor, SQL%OPEN is always defined as FALSE.

Here is a gotcha that I have personally experienced. A function cannot issue DML. I guess they don't want you to have side effects from some code which is only supposed to return a value. This is similar to the fact that you cannot issue DML in a SELECT statement either.

There are only two more chapters in my self study book. Then I move on to the self study software from Oracle. I will keep you posted with all the goodies that I learn.

PLSQL Programming

I finally got to a point in my self study book where the topics seemed familiar. It was still good to review and learn a couple more facts.

PL/SQL is strongly typed. You have to specify a type for variables. And once that type is set, you cannot change the variable type.

Variables that are not initialized start out as NULL. Here are some important truth table facts for boolean logic with NULLs:

TRUE AND NULL = NULL
FALSE AND NULL = FALSE

TRUE OR NULL = TRUE
FALSE OR NULL = NULL

I learned a couple more facts and tricks. But perhaps I will save it for another post. This is the good stuff. I love PL/SQL programming.

Oracle A to Z

I just finished reading a chapter in my study guide that seemed to cover the whole Oracle database. It went over constraints, indexes, sequences, synonyms, procedures, functions, packages, triggers, and the Data Dictionary.

Primary key constraints incorporate NOT NULL constraints and unique constraints. Oracle implements a "Match None rule" for foreign key constraints. That is, if any column that a FK constraint is on turns out to be NULL, the database does not enforce the other columns to have to match the primary key values.

The default behavior with the help of indexes is a full table scan. A B-Tree index is the default type. It provides the best performance for columns with many distinct values (i.e. high cardinality). Bitmap indexes are better for columns with low cardinality. In addition, they only add value if Oracle can use a lot of them to limit the rows searched.

Tables and Views

I think I am starting to get the hang of locking in Oracle. Specifically I can tell which locks prevent other locks from going through. Now if only I could remember all these views in the data dictionary, I will be ready for the next topic.

My plan is to do a breadth first study for my first Oracle exam. So I have moved on to tables, columns, and views. Here are some hints I have learned. I always knew you could specify a default value for a column in a table. However I thought it could only be a literal. It turns out you can use things such as SYSDATE, which behaves as expected.

Here is some weird trivia. If you enclose a table name in double quotes, its case will be preserved in the Oracle data dictionary. Normally the name is stored as all caps.

The maximum number of columns in a view is 1000. The same holds true for a table in Oracle. When you CREATE OR REPLACE a view, the privileges are preserved. If you clone a table using the CREATE TABLE ... AS SELECT, then any NOT NULL constraints are copied in the clone as well. Finally you cannot specify the NOT NULL constraint when adding a column via ALTER TABLE if there are any rows in the table at all.

That's all for now. I think I will finish by saying I had a turn around and got some good customer service from Oracle University, the training arm of Oracle Corporation. Read all about it in my Micro ISV Blog.

Transactions and Priviledges

Today I learned a lot of little details about transactions and privileges. However I am still behind in my studies of these topics. I just about get half of the review questions wrong on these topics. But I will share those things I do know here.

You are free to omit the word "FROM" in a DELETE statement. Therefore "DELETE FROM mytable" and "DELETE mytable" are equivalent. How unusual.

I knew a little about the TRUNCATE command before. It removes records from a table quickly without generating undo. It also prevents delete triggers from firing. Here is the big deal. TRUNCATE, like all other DDL, causes the current transaction to COMMIT. Be careful.

Now let's move on to privileges. You can GRANT privileges at the column level. However you can only revoke them at the table level. Also you cannot rely on role based privileges to set security for stored SQL (such as packages and/or procedures). These privileges must be granted directly to the user.

Subqueries and Joins

I continue my studies to pass the SQL and PL/SQL exam for Oracle certification. And I am finding that I get only 70% of the review questions right at the end of each chapter. I need to step up my game if I want to be sure to pass the certification exam.

This past chapter I read in my study guide was about subqueries and joins. I have been writing queries for a while. However I still learned a lot from this chapter. For example, if you define an alias for tables in your FROM clause, you cannot qualify column names with the table name any more. Go figure.

In a similar vein, you cannot use columns that you outer join in either IN or OR clauses. You also cannot put an ORDER BY clause in the subquery of a WHERE clause. Of course such an ORDER BY clause would not make much sense. The WHERE does not control the order of anything. However sometime Oracle let's you do things that don't make sense. Not here though.

Finally I am starting to learn what correlated subqueries are. They are when the subquery reference the table from the parent query. The subquery in this scenario is evaluated once for each row of the parent query. I am still not confident with correlated subqueries. So I think it is time for some real world practice.

Single and Group Functions

I studied up on Oracle functions. Then I took a review test. I only got a 7 out of 10. In my book that is a grade of C. Nobody wants a C average student doing their crucial Oracle development. So I need a lot more practice. Here are some times that I have learned during my testing.

The TO_CHAR and TO_DATE functions take a value to format and a format string. I have a mental block where I cannot remember whether the value or the format string comes first. Here is a simple way to remember it. The format string always comes second in the list of arguments.

Previously I have written about the finer points of SQL. Here is an additional point to note. You cannot use an alias in a GROUP BY clause. Speaking of the GROUP BY clause, it is required if you have a group function in the SELECT clause and also have something other than a constant in the SELECT clause.

Finally I will share a little more trivia about Oracle functions. All of the trigonometry functions operate on units of radians (not degrees). And note that the COUNT function includes NULL values in its count. That's all for now. Maybe next time I will discuss joins and subqueries.

SQL*Plus and Scripting

I have written my fair share of database scripts. Most of the time I write them in PL/SQL However for some quick and dirty scripts, I write plain SQL. This year I plan on generating a lot of test data. Perhaps I should consider SQL scripts for them. I am just now learning some of the options available to them.

When I write a script, I include comments at the top. Normally I start a comment line with the double dash. I have read and seen where you can use the REMARKS or just REM keyword to also start a comment. Multi line comments are between the /* and */ characters. Here is something I have not heard before. The last style comments (/* */) are the ones that are displayed on the screen when they are contained in a script that is run.

I know about using an ampersand in front of a variable to get the script to prompt the user for a value. However just recently I found out that those variables are always of type CHAR. If you use the ACCEPT command, the script will prompt the user but allow the programmer to specify the type of the variable holding the user input. Things like this are good to know. Time to get out there and write some scripts with this newly found knowledge.

Finer Points of Oracle SQL

I continue to study hard to pass my first exam for Oracle certification. Right now I am reading a study guide on the SQL and PL/SQL exam. I just finished chapter 1. At first I thought I should skip the early chapters since I already know a little SQL. However I am glad I didn't. I have learned a bunch of tidbits that I want to share with you now.

Although I know you could define aliases in a SELECT statement, I hardly ever do it. I also didn't know much of the details of the aliases until now. You can have a space in the alias name if you enclose it in double quotes. You cannot use the alias name in the WHERE clause. But you can use the alias in the ORDER BY clause.

I have seen some seasoned Oracle developers use the EXISTS keyword in queries. And to tell the truth, I was always a little uncomfortable with what they were actually doing. Now I know that EXISTS returns TRUE if at least one record is returned from its expression.

Hands on Views

I am now studying for my Oracle Certification exam. The first one I plan to take is “Introduction to Oracle: SQL and PL/SQL”. I have experience in some of these topics such as basic SQL and cursors. However there are a number of topics that I have little to no background in. These include creating views.

My plan was to purchase a self study CD course from Oracle to learn the material well. However that requires approval from my company as they will be paying for it. In the mean time, I just started Googling Oracle views. This did not turn out to be an optimal method. But it was better than nothing.

I took the top 10 Google results web sites for Oracle view. After studying them, I had a lot of questions. The best way I found to answer these questions is to try things out. I have my Oracle Express Edition installed on the home PC. Here are some things I learned about views that I could not tell for sure from browsing the web.

When you create a view WITH CHECK OPTION, it is supposed to restrict your ability to insert or update rows that would not be visible through the view query. I was not entirely sure if that restriction applied only to actions done through the view, or on the base tables themselves. Sure enough you can do whatever you want with the base tables. Only inserts and updates using the view get affected by the WITH CHECK OPTION.

I also read about complex views. These are views which join more than one table with their query. Apparently you can only update one table at a time through the view. That did not seem right. So I created a complex view and tried it out myself. Sure enough, when I tried to update two tables through the view, I got an exception.

It has been slow going. Trying to learn by just using Google is a painful process. My immediate solution was to buy a book to help me. I got the “OCP Oracle 8i DBA SQL and PL/SQL Study Guide”. To my surprise, I did pretty good on the assessment test in that book. That made me feel good. However I am going to go through the entire book to get better.

In more good news, I just found out that my company approved the purchase of the Oracle self study CD course. Now I know I can pass this first test.

Oracle Express Edition Tools

Now that I want to achieve Oracle certification, I am using my installation of Oracle Express Edition to try things out with the database. Oracle XE comes with a web front end to perform database tasks. However I need to execute commands from the command line to learn. Oracle XE also has a menu choice called “Run SQL Command Line”. I started using it but found it limited. I am used to SQL*Plus and PL/SQL Developer.

It took a little while for me to realize that “Run SQL Command Line” is actually SQL*Plus command line version. It connects you to database XE. I prefer the Windows graphical version of SQL*Plus (sqlplusw.exe). I tried connecting my install of PL/SQL Developer to the XE database. It did not work. PL/SQL Developer requires components not installed with Oracle XE.

In lieu of PL/SQL Developer, I downloaded and installed Oracle SQL Developer. It worked with my Oracle XE installation. But I just did not like it as much as PL/SQL Developer. I really need a tool like this to do advanced querying. So it might be time to install the Oracle client to make PL/SQL Developer working.

Certification Practice

I plan to get Oracle certified. The first test I am going to take is SQL and PL/SQL. After going over the exam topics, I determined I was weak in a number of areas. Some of those include the ALTER TABLE command.

There were a couple good articles from a Google search on the ALTER TABLE command. I printed them out, then copied down the basics. Now it was time to put this study into practice.

At home I have an Oracle Express Edition installation. I logged in as SYSTEM. However I felt it would not be right to create tables as SYSTEM. So I created a user. In the back of my mind I knew to give that user CREATE SESSION so I could log in.

I logged in fine. Then I got an error on creating the table. This user had no permissions to create the table. So I granted the user the CREATE TABLE privilege. But I still could not create the table. I had no rights to the SYSTEM tablespace. Ooops. I changed the default tablespace for this user to the USERS tablespace. Then I granted a quote for the users on the USERS tablespace.

Now I was able to get some real practice in. I created a table. Then I renamed the table. I added some columns to the table. I proceeded to rename the columns. Then I changed the data type of some columns. Yes this is all basic stuff. However I usually let a DBA handle all this. Now I need to know what the DBA does to pass my first certification test.

Oracle Certification

My company has recently decided to encourage employees to get industry certifications. I want to become Oracle certified. My first goal is to become an Oracle PL/SQL certified associate. It seems I have to pass two tests to get this certification.

The good news is that my company will pay for whatever it takes to get ready for this certification. The bad news is that I am so busy on my project that it is hard to schedule training. Part of my plan is to get some training programs that run on my computer so I don't have to miss work.

I downloaded a sample test from uCertify for the first certification test I need to take. This test was the Introduction to Oracle SQL and PL/SQL. I will confess that there are some topics on this test that I am not strong on. The demo I downloaded and took said that I failed the test. I got a score of 692 out of 1000. You need at least 750 to pass. I did question one of the answers given. However that question would not have changed my result to a pass.

The uCertify software costs $120. That seemed like a good deal. I am considering a purchase of an Oracle training program that runs on the computer. The Oracle software costs $600. If I were using my own money, I would go with the uCertify software. However this is the company money I am spending. So only the best will do.

sqlerrd in the SQLCA

Another developer and I were tracing the Pro*C code for an error that was occurring in the production environment. The code was fetching the results of a cursor that was opened. It was expecting exactly one record back. Any other number caused an error to occur. Our users were getting the error. So we assumed that multiple records were coming back. Our mission was to figure out why.

This was a difficult problem to resolve. We could not make the problem happen in the development environment. We were not authorized to execute the code in production. And every time there was an error, the application cleaned up after itself, deleting the records we needed to inspect in order to understand the problem.

My coworker came up with a theory. The code was using sqlerrd[2] from the SQL common area (SQLCA) to determine how many records were retrieved from the last fetch. This technique is sometimes used to check the count of the total number of records retrieved from multiple fetches. Therefore my coworker concluded that perhaps there were multiple fetches performed, and that those old fetches were being counted in sqlerrd[2].

This did not feel right to me. I know we have a lot of code where we do a fetch, and look to sqlerrd[2] to see how many records we just got back. However I was willing to entertain and test out whether this theory held any water. It was through this testing that I got a better appreciation of what sqlerrd[2] represents.

First I tested two fetches from different cursors. In this scenario, sqlerrd[2] held the count of the records from the latest fetch. I even tried this when both queries were executed by opening and closing the same cursor. The results were the same. The count held the number of records from the latest fetch. The only time sqlerrd[2] would have more than the latest fetch is if you opened the cursor, fetched some records, and finally fetched some more records from the same cursor that remained open.
The sqlerrd[2] value is maintained for each cursor that you have open. It has the total count of records that you have fetched (possibly through multiple fetches) since you have opened that cursor. Sometimes you really need to know what is going on to solve tough problems

EXEC SQL AT

Recently we had some serious problems in our system. One of the chief operations was aborting with errors. Me and another developer decided to dig into the code. We found where the error was being generated. Right before the error, there was some SQL in a Pro*C file that we just did not understand. The code was doing an “EXEC SQL AT” and then declaring a cursor. Neither of us knew definitively what the AT meant.

The developer I was working with had a lot of guesses as to what the AT meant. He called up some people with Oracle experience. However this did not help us. We needed somebody with Pro*C experience. We both did some Google searches. I finally found some articles online that helped me get the point.

You use an EXEC SQL AT statement when you are using multiple connections to the database. Each connection would have been started with an EXE SQL CONNECT. The connections themselves were names when you connected to the database. Here is an example:

EXEC SQL CONNECT user
IDENTIFIED BY password
AT connection1
USING dbname;

Later, if you want to use this particular connection to execute a SQL statement, you reference the connection name. Here is an example:

EXEC SQL AT connection1
SELECT COUNT(*) FROM bigtable;

Our software was creating a separate transaction to perform the main operation. This was separate from the normal database connection used for the rest of the database access. Therefore the Pro*C SQL code for this specific connection required the AT clause. You learn something new every day. I think the moral of this story was that I need some more Pro*C training to be prepared for my current project.

Trouble with Test Data

Some new functionality went to our internal test team. They were overwhelmed. A developer was called in to assist. He found that they were spending 30 minutes to analyze each scenario. And there were an abundance of scenarios. The developer asked the development team for help. We responded by writing a script which grabs a lot of data from a number of database tables. It does some processing and interpreting of the data to make it easier to see what is going on. The initial developer asked if this script could be driven by a temporary table containing the records a tester want to inspect. That was easy enough to code.

The temp table had exactly one column. This column had keys from another data table which drove the whole process. It all seemed very easy. However like real life, nothing is ever that easy. The testers had problems putting data into the temp table. They wanted to do one insert statement to add about 100 records to the temp table. They user an INSERT INTO table VALUES (value1, value2, value3, etc). The problem with this is that it is the wrong way to do things. That attempts to insert one record with many columns. The temp data table had only one column,

At first I thought maybe could try to rewrite their insert as INSERT INTO table SELECT value1, value2, value3 FROM DUAL. However this too resulted in an insertion of just one row. I was stumped. Perhaps I was just tired. Luckily I had previously written a utility that took a file with lot of values, and translated it into a script which inserted many records into a table. This was perfect. My program even prompted the user for the name of the table and column that you wanted to populate. This banged out a script that got the tests going.

Later I found there were more problems in testing. The requirements were very complex. Wouldn’t you know it? They put a junior tester on this task. I guess you have to learn somewhere. At least they were provided with the best custom test tool that development could product. You can’t ask for anything more than that. I am still a bit perplexed on how to write a single insert statement that will add a number of rows to a temp test table. Any ideas?

Oracle Express Edition

I was starting up a new project at home. And I thought it would be a good candidate to store some info in a database. So I decided to look into installing an Oracle database on my home PC. The problem was in choosing which version to install. I knew I wanted to stick with Oracle 10g. However there are multiple levels of databases that Oracle provides.

The top of the line install seems to be Oracle Enterprise Edition. This offers a lot of high end options that just don’t make sense for me. These include things like recovery, security, management, large database support, and Real Application Clusters. To tell you the truth, I don’t yet really know what RAC is. And I don’t want to know. I am not a DBA. I am a developer.

There are some other database versions which are one level down from the Enterprise Edition. These are Oracle Standard Edition and Oracle Standard Edition One. Again I must confess that I did not immediately see the difference between these two options. I do know that they include things that developers might want.

Oracle also offers Oracle Personal Edition. The name itself sounded like something for me. Then I found that it essentially has a lot of the features that are part of the Enterprise Edition. The main feature that is missing from Personal Edition is RAC. I was looking for something small that could meet my minimal needs.

I decided on installing Oracle Express Edition. This version is also called Oracle Database XE. It has a smaller download. Therefore I figure a lot of features are stripped out. You can upgrade from this version to the others. This one is called an entry level database. That sounded right for me. It is also provided free of charge which I love.

Now I just need to find the time to use this version of Oracle. The default mode of interacting with the database is through the web browser. Call me old fashioned. But I like driving with the command line. I am used to the SQL*Plus tool that comes with the Oracle 10g client. Luckily Oracle Database XE has a command line option as well that seems to work just like SQL*Plus.