Home arrow Oracle arrow Page 3 - Oracle Debugging Tips using Oracle SQL Developer

Basic PL/SQL Debugging Tips using Oracle SQL Developer - Oracle

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.

TABLE OF CONTENTS:
  1. Oracle Debugging Tips using Oracle SQL Developer
  2. Install Oracle SQL Developer in Ubuntu
  3. Basic PL/SQL Debugging Tips using Oracle SQL Developer
By: Codex-M
Rating: starstarstarstarstar / 5
April 21, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By Codex-M
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: