Oracle DBA FAQ - Introduction to Oracle SQL Developer

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6

(Continued from previous part...)

How To Assign Debug Privileges to a User?

In order to run SQL Developer in debug mode, the session must be connected with a user who has debug privileges. The following script shows you how to assign debug privileges:

Connect to the default database, XE, as SYSTEM.
Run "GRANT debug any procedure, debug connect session TO HR;".
Re-connect to the default database, XE, as HR.

You new session should have enough privileges to debug stored procedures now.

How To Set Up Breakpoints in Debug Mode?

To debug a stored procedure, you need to set breakpoints at different statements in the code block, so that execution will be stopped at the statement marked with a breakpoint. When execution is stopped, you can then investigate different variables to see their current values. The tutorial below shows you how to set up breakpoints:

Right-click the procedure name: HELLO.
Select Open. The source code of the stored procedure shows up in the code area.
Set your mouse cursor to the "PUT_LINE('Hello world!');" statement.
Click Debug menu.
Select Toggle Breakpoint. You should see a red dot shows up in left margin of the statement where the mouse cursor is. This red dot represents a breakpoint.
Set another breakpoint at the "PUT_LINE('Welcome to PL/SQL!');" statement.
Click the debug icon above the code area. The debug icon look like a bug. The Debug PL/SQL dialog shows up.
Click OK. The debug session starts and stopped at the first breakpoint. You should see an array in left margin indicating where the execution is stopped.
Press F8 (Step Over). The execution continues for one statement.
Press F9 (Resume). The execution continues to the end of the procedure.

You should see the following in the Debugging Log area:

Connecting to the database Local_XE.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '127.0.0.1', '3685' )
Debugger accepted connection from database on port 3685.
Processing 59 classes that have already been prepared...
Finished processing prepared classes.
Source breakpoint occurred at line 3 of HELLO.pls.
Source breakpoint occurred at line 4 of HELLO.pls.
Hello world!
Welcome to PL/SQL!
Process exited.
Disconnecting from the database Local_XE.
Debugger disconnected from database.

What Do You Think about Oracle SQL Developer?

To conclude this introductory FAQ collection, you should think about Oracle SQL Developer in comparison with other client tools like SQL*Plus and Oracle Web interface. SQL Developer is definitely better than the other tools, more functionality, and much easier to use. The script debugging mode is very useful.

Part: 1 2 3 4 5 6

More...