Both the SQL Worksheet and the Code Editor can make use of the Snippets tool. Snippets is a library of common built-in functions, optimizer hints, and PL/SQL constructs, like the Templates pane in SQL Developer. To use Snippets, simply select a code snippet and drag it into your editing window, or double-click to have it appear at the cursor.
The Reports tool is essentially a set of hierarchically organized queries that you can run under any of your connections. These queries are organized into two folders: Data Dictionary and User Defined.
Data Dictionary reports are a set of Oracle-supplied queries, neatly organized into folders, that provide basic information from the data dictionary (
DBA_ views) in categories such as Top SQL, Sessions, Jobs, Objects, and so on. To run a Data Dictionary report, double-click on the report name. SQL Developer prompts you for the connection and for any (optional) report parameters. The query's results open in a new window that's essentially an oversized results grid.
In addition to the Data Dictionary reports, the Reports tool has a folder for user-defined reports. You can supply any query you want (e.g., custom DBA queries, ASH queries, queries on your application's log and session tables, etc.). You can group the reports into folders and run a custom report just as you would run a Data Dictionary report.
SQL Developer's debugger covers all the basicsbreakpoints, watches, step over, step into, etc.and works well, but it is a little slow on each step. It's very functional and sure to be loved by developers who have struggled with the debuggers in other tools. Its nice features include the "Data" and "Smart Data" tabs, which show the current values of local variables without requiring you to set watches (see Figure 4
Like JDeveloper, SQL Developer remote debugging uses DBMS_DEBUG_JDWP, a server-side package based on Sun's Java Debug Wire Protocol. (It's a little-known fact that you can also use JDeveloper to debug PL/SQL that executes both locally and remotely, using the same mechanism.)
DBMS_DEBUG_JDWP runs in the remote database and establishes a direct connection back to your machine, which means it needs to be configured if you're using a firewall or VPN (see Sidebar 1. Using Debugger with a Firewall or VPN).
To get started with the Debugger, right-click on a package, function, or procedure in the Connection Manager and choose Debug. (Note: The Debugger provides more information if you choose a package, procedure, or function that's been compiled for debug. To compile an object for debug, right-click on the object in the Connection Manager and choose "Compile for Debug".)
SQL Developer pops up a dialog box that suggests an anonymous PL/SQL block you can use to debug your procedure. For example, to debug the procedure
hello_world(), SQL Developer would suggest the following block:
You can edit this block as you pleasefor example, to set parameters. Once you click OK, the debugger will establish the JDWP connection between the remote database and your machine (refer back to Sidebar 1. Using Debugger with a Firewall or VPN if you have trouble with this step). Once the connection has been established, you'll see the debugging layout shown in Figure 4.
Note: According to a forum post by Kris Rice, one of SQL Developer's creators, the debugger doesn't work against databases on VMS. This issue will be addressed in a future release.