The purpose of this article is to cover techniques that are useful in debugging PL/SQL code, especially if you don’t have access to a debugger such as the one that comes packaged with SQL Developer (this has been the case for me a few times).
This article will cover the following topics:
This article will cover the following topics:
Note: I’m working on an IBM Thinkpad with Windows XP SP2 running at 1.6GHZ with 3.0GB of RAM. The VM is running on RedHat Linux.
Downloading, Installing & Running the Oracle 11G Virtual Machine.
Step 1: You will want to download Oracle VM VirtualBox from here (avaliable for Windows/Mac/Solaris/Linux): Oracle VM VirtualBox Download.
If you don’t have an Oracle account you will have to create one for free.
Once the download is complete continue with the installation process. I used the default installation options throughout the install. Once the instillation is complete run the application and you should see a screen similar to this:
Figure 1: Oracle VM VirtualBox Manager
Step 2: Next you will need to download the Virtual Image (Oracle Developer Day.ova) from here: Oracle Developer Day.ova
Step 3: Once the download is complete, we are ready to launch the VM.
Step 4: Now we can launch the Oracle Dev Virtual Machine. Double-click the OTN Developer Days VM. Click OK to close the Virtualbox Information dialogs. The VM will launch and boot into RedHat Enterprise Linux 5 (this will take few minutes), once booted in you can login using the following credentials:
User Name: oracle
Password: oracle
Figure 2: Oracle VM VirtualBox Manager Running
Figure 3: Oracle Virtual Machine logged-in
Starting up Oracle 11G and launching Enterprise Manager.
Now that the Oracle VM is up and running we can start Oracle and login to the Enterprise Manager. In order to stat Oracle simply type “emctl start dbconsole” into the console which appeared on your desktop when you logged into GNOME and hit enter. This will launch Oracle Enterprise Manager 11G. It took a few minutes to bootup for me, but once it is booted up you can log-in by going to the folling URL in your VM’s web browser:
https://localhost.localdomain:1158/em/console/aboutApplication
User name: system
Password: oracle
Connect As: SYSDBA
Figure 4: Oracle Enterprise Manager 11G
Logging in with SQL Developer 3.1 EA
The VM comes with oracle SQL Developer EA (the icon is on the desktop). I’ve been using SQL Developer for a while now and find it just as powerful as TOAD or any of the other mainstream Database IDE’s.
You can launch SQL Developer and login to the System account, you should see Auto-Generated Local Connections in the Connections pane on the left. Expand the Auto-Generated Local Connections branch, this will allow you to login to the system-oracle account (password: oracle).
Once connected to system-oracle, right click on system-oracle and select Scheme Browser, this will allow you to select the Demo schema which I will be referring to in other articles related to Oracle and PL/SQL.
Figure 5: Oracle SQL Developer EA
At this point you should have everything you need to start learning and experimenting with Oracle and PL/SQL in a consolidated Virtual Machine Environment!
Cursors
A Cursor in PL/SQL is used to return rows of data from an SQL query (a ResultSet). You can iterate over a Cursor in a PL/SQL using a LOOP or return a Cursor to a calling entity such as a Java application. There are two types of Cursors in PL/SQL. Implicit and Explicit, the differences are as follows:
Implicit Cursors
Implicit Cursors are indirectly created by Oracle when an INSERT, UPDATE, DELETE, or SELECT statement is executed. However, in the case of a SELECT statement implicit Cursors are only created when one row is returned, if more than one row is returned we must Explicitly declare a Cursor.
Explicit Cursors
Explicit Cursors are explicitly declared in PL/SQL code and they return multiple rows from a SELECT statement. Once these rows are returned, the can be itereated over one at time using a LOOP construct inside of a Stored Procedure, Package, Function, etc. Or they can be returned to a calling application or procedure.
Here is an example of an Explicit Cursor being who’s resulset is be iterated over with a loop construct:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- Declaring the a Cursor
CURSOR MY_CORSOR is
SELECT DISTINCT
userid, username, first_name, last_name
FROM T_USER WHERE
username like '%o%'
-- Looping through and executing when no records
-- are found
FOR MY_CURSOR_rec in MY_CORSOR
LOOP
EXIT WHEN MY_CORSOR%NOTFOUND;
-- Execute some logic here for example
IF MY_CURSOR_rec.username = 'mo' THEN
-- Do Something
END IF;
END LOOP; |
If the Cursor above were executed. A ResultSet similar to what is displayed below (Figure 1) could be returned, and with that ResultSet come a number of attributes that can be used to interact with the Cursor.
Figure 1: Oracle PL/SQL Cursor Attributes
Cursor State and Status
Oracle provides attributes for Cursors in order to evaluate their state. These attributes can be used in many situations, for example, if you look at Line 12 of the code above you will see we are using the %NOTFOUND attirbute. This is telling our loop to exit when no more rows are found in the Cursor.
Below are all of the attributes provided by ORACLE for identifying Cursor state:
%ISOPEN
%FOUND
%NOTFOUND
%ROWCOUNT
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DECLARE
CURSOR USERS_CR IS
SELECT userid,username, useraddress
FROM user
WHERE username like '%Mo%';
USERS_CR_row USERS_CR%ROWTYPE;
BEGIN
IF (NOT USERS_CR%ISOPEN) THEN
OPEN USERS_CR;
END IF;
FETCH USERS_CR INTO USERS_CR_row;
WHILE (USERS_CR_row%FOUND)LOOP
--Execute some logic here....
FETCH USERS_CR INTO USERS_CR_row;
END LOOP;
IF (USERS_CR%ISOPEN)THEN
CLOSE USERS_CR;
END IF;
END; |
Additional Reading:
ORACLE PL/SQL Documentation