Oracle Debugging Tips using Oracle SQL Developer

Oracle is one of the most important relational database implemented both in big and small corporations. This is a beginner tutorial that will walk you through common debugging techniques and tips in Oracle using SQL Developer.

This tutorial has been written using Oracle installed on an Ubuntu/Linux operating system (Ubuntu 10.04 Lucid Lynx). The version covered in this tutorial is Oracle Database 10g Express Edition Release 10.2.0.1.0.

Debugging Flow

To have a better understanding of the role debugging plays in the Oracle database implementation process, we must start with the design. For starters, database team members are provided with a project from their company to create a database. The first thing they will do is complete the database specifications. These includes what tables need to be created, as well as what fields and their corresponding data types.

Once the specification has been completed, it will then be forwarded to a group of programmers that will write down the database scripts or PL/SQL procedures needed to accomplish the project objectives and specifications.

Before handing it over to the database administrator for implementation, the scripts are tested and compiled for debugging with the test database server. The purpose of this is to identify any errors.  The Oracle debugging process is then started and once the script is clean and final it will be submitted to the database administrator.

The database administrator will then review the code for any additional suggestions or corrections and finally upload it to the company database server. Once uploaded, the database administrator will then run the script. And since it is now clean it will run without any errors.

{mospagebreak title=Install Oracle SQL Developer in Ubuntu}

Tip: There are a lot of debugging tools that are available for Oracle but one of the simplest is the one developed by Oracle Corporation itself – the Oracle SQL developer. The good thing is that this tool is free to use. Below are the steps to install Oracle SQL Developer in Ubuntu:

1.) Go to Applications – Ubuntu Software Center. In the search box, type Oracle SQL Developer.

2.) You should see a package named “Oracle SQL Developer Debian package builder.” Go ahead and install it. Type your Linux root password to proceed. Wait until it is completely installed on your system.

3.)Go to this page: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html . And then select “Accept License Agreement”.

4.) Scroll down until you see “Oracle SQL Developer for other platforms”. Click that link. You will then be required to login to your Oracle account.

5.) After logging in, you can start to download the zip file. Download it to your Ubuntu Downloads folder. Oracle SQL developer is not only helpful for troubleshooting PL/SQL procedures but it is an IDE, which means you can use it for developing Oracle applications.

6.) After downloading the zip file (which is around 147MB), the default download path will be: /home/your_ubuntu_username/Downloads

You are now ready to install Oracle SQL developer. Login as root in the terminal:

codex-m@codex-m-desktop:~$ sudo -s -H
[sudo] password for codex-m:

7.) Install tofrodos package:

root@codex-m-desktop:/home/codex-m# sudo apt-get install tofrodos

8.) Create symbolic links:

root@codex-m-desktop:/home/codex-m# sudo ln -s /usr/bin/fromdos /usr/bin/dos2unix

Then after that:

root@codex-m-desktop:/home/codex-m# sudo ln -s /usr/bin/todos /usr/bin/unix2dos

9.) The download file is a zip package (sqldeveloper-3.0.04.34-no-jre.zip) but the Ubuntu installer should be a Debian package. So convert the zip file to .deb:

root@codex-m-desktop:/home/codex-m# make-sqldeveloper-package -b /tmp/ORACLE/ /home/codex-m/Downloads/sqldeveloper-3.0.04.34-no-jre.zip

Note: It will create a temporary folder /tmp/ORACLE for the conversion process. Make sure the path to the downloaded zip file is correct. After pressing enter you will see:

make-sqldeveloper-package: Building sqldeveloper package in "/home/codex-m".

10.) Go to your home folder (e.g. /home/codex-m). Get the converted file name (e.g. sqldeveloper_3.0.04.34+0.2.3-1_all.deb). Finally execute the debian package and this will install the Oracle SQL developer:

root@codex-m-desktop:/home/codex-m# sudo dpkg -i sqldeveloper_3.0.04.34+0.2.3-1_all.deb

11.) In the command prompt as a root. Install Java OpenJDK (required):

root@codex-m-desktop:/home/codex-m# sudo apt-get install openjdk-6-jdk

12.) Locate the path of your newly installed openjdk package. By default you can find it in this path: /usr/lib/jvm/java-6-openjdk

Confirm if it is correct.

{mospagebreak title=Basic PL/SQL Debugging Tips using Oracle SQL Developer}

Launch Oracle SQL developer by going to Applications – Programming – SQL Developer. If it asks for a JDK path, simply type the path you obtained in the previous section, then press enter (e.g.: /usr/lib/jvm/java-6-openjdk).

  1. The SQL Developer will then load.
  2. Go to Application – Oracle Database 10G Express Edition – click “Go to Database Homepage”.
  3. Login as database administrator.
  4. Click “Administration”.
  5. Click “Database users”. You should see a user named HR with a lock icon on it. This means you cannot use the HR user, you need to unlock it first.
  6. Click the “Home” link and in home, click “SQL”.
  7. Click SQL commands.
  8. Type this command in the textbox:

alter user hr identified by hr account unlock;

Click “Run”.
Assign “HR” the necessary debugging privileges (enter them one at a time at SQL commands text box and click run)

grant DEBUG ANY PROCEDURE to HR;
grant DEBUG CONNECT SESSION to HR;

In Oracle SQL Developer. Go to File – New – and select “Database Connection”. Click OK. Enter the following information:

Connection Name: debugging_example
Username: hr
Password: hr
Save password: checked
Hostname: localhost
Port: 1521
SID: xe

Click Test, it should not return any errors. If there are no errors, click “Connect”.

Expand “debugging_example”. And then expand “Procedures”. Click “ADD_JOB_HISTORY”. Let’s start with the most basic debugging example by simulating an error. Go to the next section.

Using the Oracle SQL Developer Debugger

In the ADD_JOB_HISTORY. Click the red bug icon (which is the debugger icon). You can locate this beside “Run”. See screenshot below:

Copy the code below and overwrite the existing procedure:

DECLARE
  P_EMP_ID NUMBER;
  P_START_DATE DATE;
  P_END_DATE DATE;
  P_JOB_ID VARCHAR2(10);
  P_DEPARTMENT_ID NUMBER;
BEGIN
  P_EMP_ID := NULL;
  P_START_DATE := NULL;
  P_END_DATE := NULL;
  P_JOB_ID := NULL;
  P_DEPARTMENT_ID := LL;

  ADD_JOB_HISTORY(
    P_EMP_ID => P_EMP_ID,
    P_START_DATE => P_START_DATE,
    P_END_DATE => P_END_DATE,
    P_JOB_ID => P_JOB_ID,
    P_DEPARTMENT_ID => P_DEPARTMENT_ID
  );
END;

This is how it looks:

Now click OK. An error will show, such as : “PLS-00201: Identifier ‘LL’ must be declared.” It is located in Line 12. Click OK.

Now to correct the error, change:

P_DEPARTMENT_ID := LL;

To simply:

P_DEPARTMENT_ID := NULL;

Click OK again. It will execute the debugger and then halt in this line:

INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id)

To continue; press F7 (for Step into). You will then see the result of the debugging process as follows:

Connecting to the database debugging_example.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘127.0.0.1’, ‘36477’ )
Debugger accepted connection from database on port 36477.
Exception breakpoint occurred at line 11 of ADD_JOB_HISTORY.pls.
$Oracle.EXCEPTION_ORA_1400:
ORA-01400: cannot insert NULL into ("HR"."JOB_HISTORY"."EMPLOYEE_ID")
ORA-06512: at "HR.ADD_JOB_HISTORY", line 10
ORA-06512: at line 14
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
Process exited.
Disconnecting from the database debugging_example.
Debugger disconnected from database.

In some instances you can test values to be used during the debugging process. To do this, click the “Data” tab (shown below). Change “null” to any data that can be assigned to the field as that field cannot be null. You can change the value by double clicking “Null”. See screenshot:

For example a fictitious value 3621 is assigned to employee ID.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan