Obviously, a quick tutorial is not to make anyone an overnight expert, but as far as expanding upon the Scott schema or the newer sample schemas (HR, OE, SH, etc.) goes, the sample schema in SQL Developer provides a good reference structure or setup. I use the term reference structure because the code can be used as a reference for syntax and it illustrates a basic schema or structure complete with referential integrity.

Some preliminaries

Installation of SQL Developer is straightforward, but there are a few steps, which may cause some problems for a beginner and those unfamiliar with what was once known as Project Raptor. The first (relatively) minor item is configuring Oracle-related file type associations. Upon first use of SQL Developer, the window below appears, and it is a convenient time to make the associations for these very common Oracle files. Otherwise, the associations can be made via Tools>Preferences>File Types one file type at a time.

The next area has to do with enabling access via a user account. The Scott schema may be installed, but the account may be locked. You can unlock the account via SQL*Plus or SQL Developer. The SQL Developer approach to this is shown below. It is highly recommended you also have a mastery of basic commands and syntax outside of a GUI tool. In other words, using a GUI tool to unlock an account should just be a more convenient way for you to implement “alter user scott account unlock” in a SQL*Plus session.

For those with more experience, it is also useful to note what, exactly, you get for roles/grants/privileges now compared to what happened in older versions of Oracle. Selecting the SQL tab (expand users, right-click on Scott and select edit) shows what you get with Scott.

One thing I think should be implemented in Oracle once and for all is the prevention or restriction of being able to select key or special tablespaces as default tablespaces. For example, why would TEMP, UNDO and SYSTEM tablespaces be available in the drop down list shown below?

Getting to the tutorial

There are a couple of ways to get to the tutorial. One way is via the Oracle Technology Network Web site, and the other is from within the Help Center inside SQL Developer.

The last item in the tutorial is the script for creating and using the tutorial’s library schema.

As mentioned, the schema is rich with objects and structure. The three tables include all major constraints (primary key, foreign key, check, not null and unique; and the frequency of DBA candidates who cannot rattle these off in an interview is amazing). The DDL statements also show examples of inline and out-of-line constraint creation.

The sequences are pretty basic, but also illustrate variations in the starting number. I would have made the transactions_seq sequence simpler via “create sequence transactions_seq.” As many other defaults were left out in the example, why not go all the way and omit the starting and increment by values?

The view appears to be simple (in the context of simple versus difficult), but is, in fact, complex. The create view statement also includes an implementation of a key versus non-key preserved view (what does key preservation do for you?). A minor modification for the view’s DDL statement would be to have used “CREATE OR REPLACE” instead of “CREATE” by itself. Another enhancement would be the inclusion of a column alias instead of the column for column mapping between the base tables and the view. Further, use of “NATURAL JOIN” would have been another added/useful syntax example.

The table of contents omits mentioning of a trigger used in the schema. The trigger is statement level (as opposed to what other level?) based and uses a “NEW” reference. Without a lot of complexity in the schema, it’s hard to have a “real” trigger (or multiple triggers).

The procedure is where the first significant amount of PL/SQL is used. The procedure list_a_rating takes an IN parameter to demonstrate the use of those. The variable named matching_title is declared as a VARCHAR2 datatype with a maximum length of 50 characters. Is VARCHAR2(50) large enough to hold all book titles? Without knowing what the BOOKS table used, we could be at risk here for not having made the variable “big enough” to hold all titles in the table.

A code improvement here would be to use an anchored declaration such as “matching_title BOOKS.TITLE%TYPE.” No guesswork involved now as the anchored declaration takes care of not only the datatype, but the length as well.

Considering the cursor, what structure in other programming languages does “FETCH the_cursor INTO matching_title” represent? That structure would be an array, and the loop is essentially the same as looping through an array (or collection in Oracle-speak). What’s another way the matching titles could have been collected? One way would have been to use a BULK COLLECT where the matching titles are bulk collected, and then each one could be referenced by position (what if the schema setup had allowed multiple copies of a title?). You could then compare titles to determine if you had a new title or not. Of course, you could also select distinct titles and not worry about the comparisons.

The procedure illustrates the (good) use of OPEN-FETCH-CLOSE when working with cursors in this manner. It also incorporates an exit condition using NOTFOUND. The line at the end of the procedure code is also frequently used. If you get an error during compilation, you may as well see what it is right away instead of having to go through another manual step to type out “show errors” at a prompt.

After the procedure, the INSERT statements are of two formats: including specific columns and none at all. The inserts into PATRONS uses a sequence_name.nextval to generate what key part of the table? If it is not clear what is taking place with the sequence numbers and the table, then answer this: what is the nature of the primary key for the table? Is the key “real” or fake? Pseudo keys or surrogate keys can be used if there isn’t anything else to uniquely identify a row.

The very last statement in the sample script uses the word “CALL” to run or invoke the procedure. What happened to EXEC, as in “exec list_a_rating(10)?”

In the SQL Reference guide, the purpose of CALL is stated as follows:

Use the CALL statement to execute a routine (a standalone procedure or function, or a procedure or function defined within a type or package) from within SQL.

For all practical purposes, EXECUTE does the same thing as CALL, so they are interchangeable.

In Closing

For such a small schema, Oracle packed in quite a bit of detail and subtleties. If you can go through this code (or something similar) and not only describe what is being used and how, but also how else something could have been done, then you’ll have a pretty decent understanding of DDL, DML, objects, SQL and PL/SQL.

Back to DBAsupport.com