An Introduction to Oracle SQL Developer – Part II

Welcome to part two of this several part series regarding Oracle’s SQL Developer application. SQL Developer is a wonderful GUI application that lets us non-Database Administrators (DBA’s) interact with Oracle databases. SQL Developer turns the intimidating command line DBA tasks into simple point and click actions.

For those of you that are familiar with MySQL and have recently started working with Oracle, you might be frustrated with the fact that Oracle doesn’t offer an auto-increment field attribute.

Most Oracle DBA’s would argue if you setup your table correctly, you don’t need auto-increment attributes. We’re not going to enter the debate of what is right or what is wrong. We are simply going to offer you a solution.

In this post, using SQL Developer, we are going to create a trigger and a sequence that are tied to insert statements on a specific table. When you insert a new row into the table, the trigger will grab the next number from the sequence and place it into the ID field. When we are finished, you will have an Oracle table with a column that has a custom made auto-increment attribute.

Preparation

As we stated in the previous post, you will need to download Oracle’s SQL Developer application. It’s free, but you will have to create an Oracle.com account to actually download the software.

At the time of this post, the current version is 2.1 and is available at

http://www.oracle.com/technology/software/products/sql/index.html

The software is available for several types of platforms. Also, since it’s based on Java, there’s a download version that includes the proper JDK. Once you uncompress the zip file, SQL Developer is ready to be used. Find the sqldeveloper.exe file and double click it to get started.

We are also going to need a new table. Our new table will be named FAV_BLOGS. FAV_BLOGS will consist of two fields. The first field will be an ID number (FAV_BLOGS_ID) and the second field will contain the name of your favorite blogs (FAV_BLOGS_NAME).

We will not populate this new table until the end of this post. We don’t want to add data to the table until we have setup our sequence and trigger. Feel free to setup our new table using SQL Developer and the methods discussed in part one of this series. For those of you that want to copy and paste code into the SQL Worksheet to create the new table, the code looks like this:

CREATE TABLE “FAV_BLOGS”

(“FAV_BLOG_ID” NUMBER(12,0) NOT NULL ENABLE,

“FAV_BLOG_NAME” VARCHAR2(255 BYTE) NOT NULL ENABLE,

CONSTRAINT “FAV_BLOGS_PK” PRIMARY KEY (“FAV_BLOG_ID”));

The Sequence

In Oracle databases, a sequence can be used to generate a series of numbers. Assuming you are already logged into an SQL Developer connection, you can create a sequence by clicking on the arrow next to the word Tables under your connection name in the Connection Explorer along the left hand side.

In the list that appears under Tables, right click on Sequence and choose New Sequence. Fill out the Create Database Sequence window that appears. Ours looks like this after we fill in the fields:

image_01

The Schema field should automatically populate with the correct information. You must supply a name for the sequence you are creating. As you can tell, we are using the name SEQ_FAV_BLOGS_ID. Under the Properties tab, you need to supply information regarding the particulars of this sequence. The definition of each field is as follows:

Increment – How much should the ID number increment with each insert statement?

Start With – What number should the sequence use for the very first insert statement?

Min Value – What is the minimum value the sequence should allow?

Max Value – What is the maximum value the sequence should allow?

The Cache, Cycle and Order settings for the sequence deserve a more in depth discussion. If you set the Cache option to CACHE, the sequence will be quicker and more efficient. For small tables this doesn’t matter, but if your table is going to have thousands of rows, you will want to use CACHE.

When the trigger invokes the sequence, the sequence looks for the next number to use. If you are using CACHE, Oracle won’t have to update the root file until it exhausts the cache size. For our example sequence, the root file won’t have to update until 50 calls to the sequence have successfully completed.

The larger the Cache size, the fewer times your database root file will update. If you use NO CACHE, every time the sequence is called, the root file for your database must update.

The Cycle option determines what happens if the sequence reaches the Max value. If you choose not to use Cycle, the next call to your sequence after the Max value has been used will cause an error to appear.

The error will state something to the effect of the next value for your sequence is not available. If you use the Cycle option, once the Max value you specify has been used, the next value returned by the sequence will start over with the Start with value you supply. Using Cycle will ruin your ID number uniqueness at some point in the future.

The Order option coordinates the next value used in your sequence across many sessions. This is achieved by forcing a single cache across all sessions for this sequence. Each call to your sequence will lock the sequence session while retrieving the next value. NOORDER uses a per session local cache. There is no lock synchronization with NOORDER. ORDER and NOORDER are useless if you do not use the CACHE option.

If you successfully create your sequence it should appear under the word Sequence in the Connection Explorer on the left hand side. Our Connection Explorer looks like this:

clip_image002

The Trigger

In Oracle databases, a trigger can be used to perform a list of “code” when a specific statement event occurs. For example, we are going to call a trigger every time an INSERT statement occurs involving our FAV_BLOGS table.

In your Connections Explorer panel along the left hand side of SQL Developer, right click on Triggers and choose New Trigger. Supply the specific information necessary for the Create New Trigger window. We have supplied the following information:

image_03

The information we are providing is pretty basic. We are supplying TRIG_FOR_AI_FAV_BLOGS as the name of our trigger. We have chosen our trigger type as TABLE (because we want this trigger to occur when a specific table is accessed). We have check marked the Enabled option.

We have chosen the appropriate table owner. We have chosen the appropriate Table Name. We want our trigger to work on the INSERT command (check box). We want our trigger to occur BEFORE the INSERT command (radio button). Next, we want our trigger to occur at the row level (radio button). Finally, click the OK button.

If everything goes well, you should be presented with the code that is to be used in creating the trigger. It should look like this:

CREATE OR REPLACE TRIGGER TRIG_FOR_AI_FAV_BLOGS

BEFORE INSERT ON FAV_BLOGS

FOR EACH ROW

BEGIN

NULL;

END;

In between BEGIN and END, you must remove the NULL statement and replace with SELECT SEQ_FAV_BLOGS_ID.nextval into :new.FAV_BLOG_ID from dual.

Our final trigger code looks like this:

CREATE OR REPLACE TRIGGER TRIG_FOR_AI_FAV_BLOGS

BEFORE INSERT ON FAV_BLOGS

FOR EACH ROW

BEGIN

SELECT SEQ_FAV_BLOGS_ID.nextval into :new.FAV_BLOG_ID from dual;

END;

Essentially, the trigger code is telling Oracle to CREATE the trigger and before any INSERT commands on the FAV_BLOGS table for every row and to SELECT the next value from our sequence and place it INTO any new FAV_BLOG_ID field being created.

That’s it. You have just created a custom auto-increment attribute for field in an Oracle table. Give it a test by inserting a couple of rows. Click on the table name in your Content Explorer and when the table tab opens next to the SQL Worksheet tab, click on the Data tab and fill in the FAV_BLOG_NAME field.

Leave the FAV_BLOG_ID field empty. Remember, you have to click on the Commit button in order to make the trigger run. The commit button looks like the flowchart symbol of a data base with a green check mark in front of it. After clicking on the Commit button, you should see your new row and the FAV_BLOG_ID field should automatically contain an incremental value.

Wrap Up

In this post, thanks to Oracle’s SQL Developer application, with a few short mouse clicks and a few key strokes we accomplished a somewhat advanced database task. As always, feel free to experiment with SQL Developer, triggers and sequences.

Also, stay tuned for the next post in this introduction series where we will cover creating stored functions with SQL Developer.

Comments [1]

Leave a Reply

Your email address will not be published. Required fields are marked *