24 Using the Database Tools

This chapter provides an introduction to the various tools that JDeveloper uses to help you work with and manage databases.

This chapter includes the following sections:

• Using the Databases Window

• Using the Database Cart

• Using the Structure Window

• Using the Database Reports Window

• Using the Find Database Object Window

• Using the SQL Worksheet

• Using the SQL History Window

• Using the Snippets Window

• Using the Database Object Viewer

• Using the PL/SQL Source Editor

• Using SQL*Plus

• DBMS Output Window

• OWA Output Window

24.1 Using the Databases Window

The Databases window provides you with a complete editing environment for online databases. You can create, update and delete database objects using the Databases window.

The Databases window is integrated with:

• The SQL Worksheet. More

• The Database Object Viewer. More

• The Database Cart. More

You can drag database objects from a database connection onto a database diagram to either:

• Model the database objects on the diagram.

• Reverse engineer database objects to a project, and model the offline database objects on the diagram.

For more information about database modeling, see Modeling with Database Diagrams .

When you first open the Databases window, it appears in the default docked position, which is in the upper left-hand corner, flush with the main work area of JDeveloper. When more than one window is open in the same position, each appears with a tab displaying its name.

The top-level nodes in the Databases window are:

• IDE Connections. These are globally defined connections available for reuse, and the connections are also listed in the IDE Connections panel of the Resources window from where you can copy IDE connections to the Applications window to use them within an application. More

• Application Connections. These are connections defined for use in the applications named in the connection node.

• Cloud Connections. These are connections to Oracle Database Cloud Service instances.

Right-click on a node within the Databases window to bring up a context-sensitive menu of commands. The menu commands available depend on the node selected. You can open nodes in their default editors, as well as other editors common to that node type, using the context menu.

---

Table 24-1 Databases Window Toolbar Icons

Icon	Name	Function
	New Connection	Click to open the Create Database Connection wizard, where you enter the details to create a connection to a database.
	Refresh	Click to synchronize the display in the Databases window with the contents of the connection.
	Apply Filter	Click to filter which objects will be displayed for a given connection. To enable the icon, select a node within the connection in the Databases window and wait for the connection to be established.
	Collapse All	Click to collapse all expanded nodes.

---

You can perform various tasks from the context menus in the Databases window. Right-click the IDE Connections node (for globally defined connections) or an application name node (for connections that are locally-scoped, and just available within the application) and select the appropriate menu item to:

• Create a new database connection.

• Import an XML file with connection definitions.

• Export current connections.

You can perform the following operations from a database connection node:

• Connect to and disconnect from the database.

• Delete the database connection.

• Generate SQL from database objects.

• Reverse engineer database objects as offline database objects to a project.

• Run SQL*Plus.

• Filter the objects displayed in the connection.

• Edit the database connection properties.

• Open the SQL Worksheet.

• Generate DB doc

• Remote Debug

• Gather Schema Statistics

• Recompile Schema

• XML DB Protocol server configuration

• Perform remote debugging if you are using the Java Platform Debugger Architecture (JPDA) using a debugger to listen so that a debuggee can attach to the debugger.

There are additional options available from database object type nodes (for example, Tables, Indexes, or Procedures) or from database object nodes (such as a specific table, or a specific view). The options available depend on the node selected.

24.2 Using the Database Cart

The Database Cart allows you to deploy Oracle Database objects from one or more database connections to an Oracle Database Service instance, or to a ZIP file.

Note:

You cannot use the Database Cart to work with offline database objects.

You can choose to deploy:

• Just the DDL defining database objects

• DDL defining the database objects and the associated data

• Just data, for database objects that already exist in the Oracle Database Service instance

You add objects to the cart by dragging objects from the Databases window and dropping them into the Database Cart window. Alternatively, you can use the contents of a cart that you have previously saved.

From the cart, specify any desired options, and do one of:

• Click Deploy to Cloud button to display the Deploy Objects to Cloud dialog, where you choose the connection to deploy the objects to.

• Click the Deploy button to display the Deploy Objects dialog where you specify details of the deployment file to generate.

Before you use the Database Cart, ensure that you have correctly set the preferences you want to use.

From any cart tab, you can right-click and choose:

• Close: Closes the current cart tab.

• Close Others: Closes all cart tabs except the current one.

• Close All: Closes all cart tabs.

• Rename: Renames the current cart tab (for example, if you wanted to change Cart_1 to HR_objects).

Table 24-2 describes the operations you can perform from the Database Cart Window toolbar.

---

Table 24-2 Database Cart Window Toolbar

Element	Description
	Click to open a new empty cart.
	Click to open a saved cart by specifying the XML file that specifies the cart contents. How? If the cart currently contains any objects, you are asked if you want to remove the current objects from the cart before opening a saved cart. Yes Empties the current cart and fills the cart with objects from the cart you are opening. No Does not empty the current cart, but adds the objects from the cart you are opening to the current cart objects.
	Click to open the Save as Cart dialog, where you can save the contents of a cart as an XML file which you can later open and use again.
	Click to open the Save as Cart dialog, where you can save the contents of a cart as an XML file which you can later open and use again.
	Click to open a Save as Cart dialog for each currently open cart.
	Click to bring the cart to the left to the front.
	Click to bring the cart to the right to the front.
	Click to refresh the cart and validate the objects in the cart against those in the Databases window.
	Click to deploy the selected objects in the cart to an Oracle Database connection. The Deploy Objects to dialog opens where you can specify additional information.
	Click to create a deployment file. The Export Objects dialog opens where you can specify additional options, generate the deployment scripts, and optionally generate a .zip file that contains them.
	Click to open the Diff Objects dialog, which allows you to compare the selected objects with the objects in another currently open cart tab or a database connection that has access to the destination objects to be compared.
	Click to open the Copy Objects dialog, which allows you to copy the selected objects to a database connection.

---

Table 24-3 describes the operations you can perform from the Selected Objects toolbar.

---

Table 24-3 Selected Objects Toolbar

Element	Description
	Click to add an initial script to be run before the object is created. Alternatively, click the down arrow next to the icon and choose from: Add Initial Script Add Final Script, which is run after the object is created. Alternatively, you can open a scripts dialog by moving the cursor to the Scripts field on the row for the object, and clicking the button.
	Click to remove the selected row from the cart.

---

Initially database objects are shown in order of type, by owner, by name. You can reorder the rows using the shuttle buttons. Table 24-4 describes the content of the table.

---

Table 24-4 Selected Objects Table

Element	Description
Include	Either select or deselect objects row by row, or select or deselect in the column header for all objects. Selected objects will be included in the deployment action.
Type	The type of the database object.
Owner	The owning schema of the database object.
Name	The name of the database object.
DDL	Either select or deselect objects row by row, or select or deselect in the column header for all objects. Selected objects will be included in the DDL generated by the deployment action.
Data	Either select or deselect objects row by row, or select or deselect in the column header for all objects. Only objects that have associated data have an entry in this column, for example tables, views. Selected objects will be included in the DDL generated by the deployment action.
Where	You can add a WHERE clause. Click in the cell for the appropriate row, then click the edit button. The Data Where dialog opens where you can specify the WHERE conditions.
Connection	The connection from which the object was selected.
Scripts	Optionally specify a SQL script to be executed first in the generated master deployment script (before the other generated scripts).

---

24.3 Using the Structure Window

The database view of the Structure window displays details of a connection or database object selected in the Databases window, or an offline database object selected in the Applications window.

For an offline database, the Structure window displays details of offline database objects such as tables, views, synonyms. When you select the object in the Applications window, details of the object are shown in the Structures window, as shown in Figure 24-1.

Figure 24-1 Structure Window View of Offline Database Object

From the context menu of the offline database object, you can perform the following actions:

• Find Usages

• Use as Template

• Properties

• Go to Declaration

When you are working with database objects that have been reverse engineered from a database connection the Structure window can show you information about the original objects. For example, a table reverse engineered from an online database connection will display details of the source object and the connection used, as shown in Figure 24-2.

Figure 24-2 Reverse Engineered Object in Structure Window

When you select a database object such as a table in a database connection in the Databases window or an offline database object such as a table in an offline database in the Applications window, a node for that object is shown in the Structure window. You can expand the node to see details of the sub objects that make up the database object. In the case of a table, these include sub objects such as columns, constraints, and indexes.

You can perform the following operations from the database view of the Structure window:

• View properties or edit properties (offline database objects only) by choosing Properties from the context menu of an appropriate node. The Edit dialog for the object type opens. It is read only for database objects.

• Use a database object or offline database object such as a table as a template to create a new object by choosing Use as Template from the context menu. The Create dialog for the object type opens.

• Find usages of an offline database object such as a table by choosing Find Usages from the context menu.

24.4 Using the Database Reports Window

Use the Database Reports window to view reports about the database and its objects.

You can also create your own user defined reports.

To open a pre-defined report, expand Data Dictionary Reports and navigate to the report you want. Double-click the report name to run it. A number of dialogs may be displayed before the report is opened in the Reports Results window:

• Select Connection dialog (all reports), where you can choose an existing database connection or create a new database connection. Once you have chosen the connection, the same connection is used for subsequent reports you run.

• Enter Bind Values dialog (All Objects reports), where you can enter values for each bind variable. Bind variables enable you to restrict the output.

• Diagnostic Pack Required dialog (ASH and AWR reports). You must have a licensed copy of Oracle Diagnostic Pack running on the database to run these reports, and the dialog allows you to confirm that you have one.

You can create your own reports and store them in folders and sub-folders under the User Defined Reports node.

Some reports may take some time to run, and the time is affected by the number and complexity of objects involved, and by the speed of the network connection to the database.

From the Data Dictionary Reports node you can:

• Export a report into an XML file that can be imported later by right-clicking the report name and choosing Export.

• Create a shared report from an exported report.

User Defined reports are any reports that are created by JDeveloper users.

Information about user defined reports, including any folders for these reports, is stored in UserReports.xml in the directory for user-specific information.

You can perform the following operations from the User Defined Reports node:

• Create a user defined report by choosing Add Report from the User Defined Reports context menu.

• Organize user defined reports in folders, and create a hierarchy of folders and subfolders. Choose Add Folder from the User Defined Reports context menu.

• Import a report that had previously been exported. Select report folder in which to store the imported report, right-click, and select Import.

The Shared Reports node is displayed once you have defined the first shared report in the Preferences dialog.

For more information about creating and sharing database reports, see Working with Database Reports.

24.5 Using the Find Database Object Window

The Find Database Object Window allows you to search for and work on database objects within a live database.

The Find Database Object Window is fully integrated with the online database functionality, including the SQL Worksheet and the Database Object Viewer.

While you are using the Find Database Object Window these features are available:

• Open any currently closed window, or bring a currently open window to the foreground, using Window > window-name.

• Move, resize, float, minimize, maximize, restore or close the Find Database Object Window using the context menu available by right-clicking its tab or by pressing Alt+Minus.

---

Table 24-5 Find Database Object Toolbar

Name	Function
Connection	Choose the database connection to search in from the dropdown list. You must already have a connection to the database.
Name	Enter the search term. You can use the wildcard % to return a number of matching objects.
Type	Choose the type of database object to restrict the search to. The default is ALL OBJECTS.
Usage	Only for certain types of object. Choose the usage of the object, for example ALL.
Lookup	Click to display the results of the search. The results of the search are displayed in the panel. Double-click on an object to open it in the appropriate editor.

---

You can perform the following tasks from the Find Database Object window:

• Close or open the panel by clicking its bar.

• Change the area used by the panel by grabbing its bar and moving it up or down.

• Remove the panel from view by opening its dropdown menu (panel bar, far right) and choosing Minimize. Restore it by clicking the three dots at the very bottom of the Applications window and then clicking Recent Files.

• Open an object, or the parent object that contains the specified object, in its default editor, or bring the default editor into focus, by selecting the object in the list.

24.6 Using the SQL Worksheet

Use to enter and execute SQL, PL/SQL, and SQL*Plus statements. You can specify any actions that can be processed by the database connection associated with the worksheet, such as creating a table, inserting data, creating and editing a trigger, selecting data from a table, and saving that data to a file.

You enter SQL statements in the SQL Statement area, and use the buttons on the toolbar to perform actions.

Table 24-6 describes the icons and fields in the toolbar above the SQL Worksheet statement area.

---

Table 24-6 Icons in the SQL Worksheet Toolbar

Icon	Name	Function
	Run Statement (Ctrl+Enter)	Click to execute the statement at the mouse pointer in the SQL statement area. The SQL statements can include bind variables and substitution variables of type VARCHAR2. If necessary, VARCHAR2 is automatically converted to NUMBER. If you use variable values, a window is displayed for you to enter them.
	Run Script (F5)	Click to execute all statements in the SQL statement area. The SQL statements can include bind variables and substitution variables of type VARCHAR2. If necessary, VARCHAR2 is automatically converted to NUMBER. If you use variable values, a window is displayed for you to enter them.
	Autotrace (F6)	Click to generate trace information for the statement. To see trace information, click the Autotrace tab.
	SQL Tuning Advisor (Ctrl + F12)	Opens a window that runs the SQL Tuning Advisor, which is SQL diagnostic software in the Oracle Database Tuning Pack. The Overview and Details tabs include advice or recommendations for how to tune the specified statement, along with a rationale and expected benefit.
	Explain Plan (F10)	Click to generate the execution plan for the statement, which internally executes the EXPLAIN PLAN statement. Trace information is shown in the Explain Plan Results window.
	Commit (F11)	Click to write any changes to the database. This ends the transaction and clears any output in the Results and Script Output tabs.
	Rollback (F12)	Click to discard any changes without writing them to the database. This ends the transaction and clears any output in the Results and Script Output tabs.
	Unshared SQL Worksheet (Ctrl+Shift+N)	Click to open a new unshared SQL Worksheet for a different connection.
	To Upper/Lower/Inicat (Ctrl+Quote)	Click to switch the selected text between upper case, lower case, and initial capitals.
	Clear (Ctrl+D)	Click to erase the statement or statements in the Enter SQL Statement area.
	Cancel	(Only displayed while a script is running) Click to stop execution of the script.
	SQL History	Click to open the SQL History window.
		(Only displayed once a statement or script has run) Displays the time it took to execute a statement or run a script. This can be used with Explain Plan to provide useful tuning information.
		Use to choose a different database connection.

---

The results area has a number of tabs:

• Results tab, Displays the results of clicking Run Statement.

• Script Output tab, which displays the results of clicking Run Script.

• Autotrace tab, which displays output as a result of clicking Autotrace.

• Explain tab, which displays output as a result of clicking Explain Plan.

The SQL Worksheet provides code insight for SQL code. When you type a word, a dropdown menu of valid code appears. For example:

• If you type select, SELECT is displayed.

• If you type select *, a list containing BULK, FROM, and INTO is displayed.

• If you are connected to the HR schema and type select * from em, a list containing the table employees and the view emp_details_view is displayed.

To configure Code Insight for the SQL Worksheet:

1. Select Tools > Preferences > Code Editor > Code Insight.

2. In the Code Insight page, adjust font size or font type, and completion insight and parameter insight timing.

3. Click OK. Your changes are active the next time you use the editor.

To open the SQL Worksheet:

1. Choose Window > Database > Databases window.

2. Expand IDE Connections or Application Connections.

3. Right-click the connection in the window, and choose Open SQL Worksheet.

   Alternatively, click the SQL Worksheet button on the JDeveloper toolbar.

   For more information at any time, press F1 or choose Help from within the SQL Worksheet.

Alternatively, from the main toolbar, click and choose the database connection from the Choose Connection dialog.

You can create a SELECT statement by dragging and dropping table and view names, and by graphically specifying columns and other elements of the query using Query Builder. You can run the statement within Query Builder to see the results, and when you close Query Builder, the resulting SELECT statement is inserted into the SQL Worksheet.

To use Query Builder:

1. Open the SQL Worksheet.

2. Select the Query Builder tab. For more information at any time, press F1 or click Help from within the SQL Worksheet.

3. Select the schema you want, and drag the table you want to base the query on onto the main pane of the dialog. There will be a delay of a few seconds while Query Builder connects to the database and loads information about the table.

4. The first time, you are prompted to choose the type of statement you want to use.

5. Use the SQL Worksheet buttons to perform the action you want, for example to run a query.

To execute a SQL statement:

1. Enter a SQL statement in the worksheet's upper pane.

2. Do any one of the following:

   • Press Ctrl+Enter.

   • Click the Execute the statement button on the toolbar.

   • Right-click, and select Execute SQL Statement from the context menu.

3. View the data returned by the statement in the lower pane.

For more information, see "Using the SQL Worksheet" in the Oracle® Database SQLJ Developer's Guide.

24.6.1 Using Execution Plan

An execution plan is the sequence of operations that will be performed to execute the statement, and you can use the SQL Worksheet to inspect the execution plans chosen by the Oracle optimizer for SQL SELECT, UPDATE, INSERT, and DELETE statements. You can also view explain plan for the SQL code for the query part of a view definition.

An execution plan shows a row source tree, which is the hierarchy of operations that comprise the statement. For each operation it shows the following information:

• An ordering of the tables referenced by the statement

• An access method for each table mentioned in the statement

• A join method for tables affected by join operations in the statement

• Data operations such as filter, sort, or aggregation

In addition to the row source tree, the plan table displays the following information about selected operations:

• Optimization, such as the cost and cardinality of each operation

• Partitioning, such as the set of accessed partitions

• Parallel execution, such as the distribution method of join inputs

For more information, see "Using EXPLAIN PLAN" in the Oracle® Database SQL Tuning Guide.

An additional source of information that can be used to tune SQL queries is the elapsed time that is displayed in the toolbar of the SQL Worksheet when statements are executed or scripts are run.

To view a SQL statement's execution plan:

1. If necessary, open the SQL Worksheet.

2. Enter a SQL statement in the worksheet's upper pane.

3. Do one of the following:

   • Click the Explain Plan button on the toolbar.

   • Right-click to open the context menu, and select Execute Explain Plan.

   The Explain Plan tab shows the explain plan information for the SQL statement.

24.6.2 How to Recall Statements from the SQL Worksheet History

The statements executed in a session with the SQL Worksheet are preserved in a history list. You can retrieve previous statements from the history, and re-execute them or view their execution plans.

To recall a statement from the SQL Worksheet history:

1. Do either of the following:

   • Click the SQL History button.

   • Right-click in the SQL Worksheet to open the context menu, and select History.

   A window showing the list of the statements previously entered is displayed.

2. Select the desired statement from the window.
3. Click OK.

The statement is displayed in the upper pane of the worksheet.

24.7 Using the SQL History Window

The SQL History Window allows you to reuse statements previously executed in a session with the SQL Worksheet.

SQL statements and scripts that you have executed are listed in the window, and you can select one or more statements to have them either replace the statements currently on the SQL Worksheet or be added to the statements currently on the SQL Worksheet.

While you are using the SQL History Window these features are available:

• Open any currently closed window, or bring a currently open window to the foreground, by choosing it from the Window menu.

• Move, size, float, minimize, maximize, restore or close the Find Database Object Window using the context menu available by right-clicking its tab or by pressing Alt+Minus.

---

Table 24-7 SQL History Toolbar Icons

Icon	Name	Function
	Append	Click to append the selected statement or statements to any statements currently on the SQL Worksheet. You can also append the selected statement or statements by dragging them from the SQL History window and dropping them at the desired location on the SQL Worksheet.
	Replace	Click to replace any statements currently on the SQL Worksheet with the selected statement or statements.
	Clear History	Click to remove all statements from the SQL history.
	Filter	Use to filter the SQL statements visible in the SQL History window. Type a string in the text box and click Filter. Only SQL statements containing that string are listed. To remove the filter, delete the string in the field and click Filter again.

---

24.8 Using the Snippets Window

Snippets are code fragments, such as SQL functions, Optimizer hints, and miscellaneous PL/SQL programming techniques. Some snippets are just syntax, and others are examples. The Snippets Window is integrated with the SQL Worksheet and when you are creating or editing a PL/SQL function or procedure.

In the Snippets Window, the snippets are organized in categories in the drop-down list, such as Aggregate Functions or Character Functions. You can create new snippets and add them to an existing category, or to a new category. To see a brief description of a snippet, hover the mouse pointer over the function name.

To insert a snippet into your code in a SQL Worksheet or in a PL/SQL function or procedure, drag the snippet from the snippets window and drop it into the desired place in your code; then edit the syntax so that the SQL function is valid in the current context.

For example, you could type SELECT and then drag CONCAT(char1, char2) from the Character Functions group. Then, edit the CONCAT function syntax and type the rest, as in this example:

SELECT CONCAT(title, ' is a book in the library.') FROM books; 

---

Table 24-8 Snippets Window Toolbar Icons

Icon	Name	Function
	Add Snippets	Click to open the Save Snippet dialog where you can create a new snippet and save it in an existing group or in a new group.
	Edit User Snippets	Click to open the Edit Snippets dialog which lists user snippets. You can create, edit and delete snippets in this dialog.

---

From the Snippets window, you can:

• Display snippets by choosing the category from the list.

• Add a snippet to the cursor position in a file such as a SQL file by double-clicking it.

24.9 Using the Database Object Viewer

The Database Object Viewer allows you to manage the structure and contents of objects in a database. The tabs available depend on the type of object being viewed.

You can edit the value in any of the cells by double-clicking the cell to select it, then clicking ... to open the Edit Value dialog.

Information about the object is contained in a number of tabs.

• Columns, which shows the columns comprising the object.

• Data, which shows the data in this object. You can edit the value in any of the cells by double-clicking the cell to select it, then clicking ... to open the Edit Value dialog.

• Constraints, which shows the details of any constraints.

• Grants, which shows privilege details.

• Statistics, which shows statistical information.

• Triggers, which shows information about triggers.

• Dependencies, which shows information about references.

• Indexes, which displays details of any indexes.

• Details, which displays details of the object.

• SQL, which displays the SQL that represents this object.

24.9.1 Database Object Viewer Tabs Toolbars

The specific buttons on the toolbar vary from tab to tab.

---

Table 24-9 Database Object Viewer Tabs Toolbar Icons

Icon	Name	Function
	Freeze and Unfreeze View	Use to toggle freezing the table viewer on the current view.
	Edit	Click to open the Edit Table dialog.
	Refresh	Click to refresh the data.
	Insert Row	Click to insert a new blank row below the row where the focus is.
	Delete Selected Row(s)	Click to delete the selected rows of data.
	Commit	Click to commit the changes to the database. The changes are logged in the Data Editor log window, and commit will fail if there is an error, such as a unique constraint violation.
	Rollback	Click to rollback database changes already made. The Data Editor log window reports on whether the rollback has succeeded.
		Click to open the Sort dialog where you specify the columns to sort by and the sort order.
		Enter a value to reduce the number of records displayed, for example DEPARTMENT_ID>20.
		Click to perform one of a range of common table actions.

---

24.10 Using the PL/SQL Source Editor

The PL/SQL Source Editor displays PL/SQL code for database objects such as procedures and functions. In addition to the PL/SQL-specific features of the PL/SQL Source Editor, you can also use many of the common set of features that JDeveloper provides to enhance coding across all domains. These features are available through the context menu or the Source menu. For more information, see Using the Source Editor.

In addition, there are some PL/SQL specific features which are described below.

Table 24-10 describes the operations available from the PL/SQL Source Editor toolbar.

---

Table 24-10 PL/SQL Source Editor Toolbar

Element	Operation
	Keeps that subprogram's tab and information in the window when you click another object in the Databases window; a separate tab and display are created for that other object. To unfreeze, click the pin again.
	Use to toggle between write mode and read-only mode.
	Enter a string and click Enter. To choose from a list of search options, click the down arrow.
	(Database only) Performs a PL/SQL compilation of the subprogram. Click the arrow to choose from: Compile for Debug, which performs a PL/SQL compilation of the subprogram so that it can be debugged. Compile, which performs a PL/SQL compilation of the subprogram. The results are displayed in the Log window.
	(Offline only) Performs a PL/SQL compilation of the subprogram. To enable, you must create or choose a database connection against which the subprogram will be compiled. When you click this button, the subprogram is saved to the offline database, and the program is submitted for compilation.
	(Offline only) Compile for Debug, which performs a PL/SQL compilation of the subprogram so that it can be debugged. To enable, you must create or choose a database connection against which the subprogram will be compiled. When you click this button, the subprogram is saved to the offline database, and the program along with debug information is submitted for compilation.
	Opens the Run PL/SQL window which allows you to specify arguments when running or debugging PL/SQL functions, procedures, and packages.
	Starts execution of the subprogram in debug mode, and displays the Debugging - Log tab, which includes the debugging toolbar for controlling the execution.
	Code highlight. For example, clicking on the declaration of, or any usage of p_column_name, and selecting this button will highlight all usages of p_column_name. Note that this is not a purely textual match, but is explicitly usages of the variable, obeying all scoping rules. If this button is not enabled when you have selected some code, expand the Search menu on the JDeveloper toolbar, and deselect Auto Code Highlight.
	Clear all code highlighting.
	The database connection is used for compilation. Use to choose a different database connection. Once you have chosen a connection, it is persisted and used in future instances of the PL/SQL Source Editor.

---

24.10.1 Using Test Query

For SELECT statements in PL/SQL, you can test a query against a live database to check that the correct rows are returned. Any PL/SQL variables are replaced in the test query with bind variables and clauses such as INTO are ignored.

To test the query, right-click in the SELECT statement of the PL/SQL and choose Test Query, as shown in Figure 24-3.

Figure 24-3 Context Menu of Select Statement

The query is run and the Test Query dialog is displayed, as shown in Figure 24-4.

If you do not have a connection to a database defined, you must first create one. You can do this from the Test Query dialog. To choose a different database connection, choose from the list in the Test Query dialog.

Figure 24-4 Test Query Dialog

24.10.2 Synchronizing Package Specifications and Bodies

You can use Synchronize Specification and Body to:

• Create the initial body for a package. Right click in the specification and choose Synchronize Specification and Body from the context menu. A new package body is created.

• Identify methods that are specified in the specification which are not in the package body. Right click in the specification and choose Synchronize Specification and Body from the context menu. The Synchronize Specification and Body dialog opens, as shown in Figure 24-5.

  Figure 24-5 Synchronize Specification and Body

  

  This lists all methods that can be synchronized, and all those in the specification that are not in the body are pre-selected. Select the methods you want synchronized and click OK.

24.11 Using SQL*Plus

SQL*Plus is an interactive and batch query tool that is installed with every Oracle Server or Client installation. It has a command-line user interface. You can launch SQL*Plus from within JDeveloper. For more information, see the SQL*Plus® User's Guide and Reference.

In most cases, using SQL Worksheet is preferable to using SQL*Plus as it is fully integrated with JDeveloper, and you can use SQL Worksheet to enter and execute SQL, PL/SQL, and some SQL*Plus statements.

SQL*Plus can use parameter substitution. The default escape character is '&', thus any comments that have '&' in them may cause an error. Additionally, the character used in the SQL*Plus session that runs the script can be changed from the default using SET DEFINE, so JDeveloper cannot look for the parameter substitution character in comments and warn you. If you encounter this error in a script, you can use SET DEFINE OFF to ignore the parameter substitution character or remove the character from the comment. For more information, see "'Using Scripts in SQL*Plus'" in the SQL*Plus® User's Guide and Reference.

In order to launch SQL*Plus from JDeveloper, you must have SQL*Plus installed on your machine. For information about installing a SQL*Plus client, see the information about Oracle Database Instant Client at http://www.oracle.com/technetwork/database/features/instant-client/index-100365.html.

You can launch SQL*Plus from:

• The Tools menu

• A database connection in the Databases window

• A SQL file in the Applications window

If you have not already specified the SQL*Plus executable in JDeveloper, you will able to do so when you launch SQL*Plus. Alternatively, you can specify the SQL*Plus executable in the Preferences dialog. You only need to perform this task once.

To specify the SQL*Plus executable:

1. Choose Tools > SQL*Plus, and select Database Connections.

2. Specify the path to the SQL*Plus executable.

3. Click OK to close the dialog. Now the SQL*Plus item is active in the Tools menu.

4. Select a database connection in the Databases window, then choose Tools > Database > SQL*Plus. If the path specified in step 2 is correct, a SQL *Plus command window will open.

Note:

On Unix, use xterm to create a terminal window to run the SQL*Plus command in.

To launch SQL*Plus from a connection:

1. Choose Window > Database > Databases window.

2. Right-click the connection, and choose SQL*Plus.

To launch SQL*Plus from a SQL file:

1. In the Applications window, navigate to a SQL file.

2. Right-click the SQL file, and choose Run in SQL*Plus.

3. In the submenu, select the connection you wish to use. If you have not already specified the location of the SQL *Plus executable, you will be prompted for that first.

24.12 DBMS Output Window

The PL/SQL DBMS_OUTPUT package enables you to send messages from stored procedures, packages, and triggers. The PUT and PUT_LINE procedures in this package enable you to place information in a buffer that can be read by another trigger, procedure, or package. In a separate PL/SQL procedure or anonymous block, you can display the buffered information by calling the GET_LINE procedure. The DBMS Output window is used to display the output of that buffer.

Add New DBMS Output Tab: Prompts you to specify a database connection, after which a tab is opened within the DBMS Output pane for that connection, and the SET SERVEROUTPUT setting is turned on so that any output is displayed in that tab. (To stop displaying output for that connection, close the tab.)

---

Table 24-11 DBMS Output Window Toolbar Icons

Icon	Name	Function
	Enable DMBS Output	Click to toggle the SET SERVEROUTPUT setting between ON and OFF. Setting server output ON checks for any output that is placed in the DBMS_OUTPUT buffer, and any output is displayed in this tab.
	Clear	Click to erase the content of this tab.
	Save File	Click to open the Save dialog where you can enter a filename to save the results in this tab.
	Print	Click to open the Print dialog, where you can choose the printer to print the content of this tab.
	Buffer Size	For databases before Oracle Database 10.2, click to limit the amount of data that can be stored in the DBMS_OUTPUT buffer. The buffer size can be between 1 and 1000000 (1 million).
	Poll	Move the slider to set the interval (in seconds) at which JDeveloper checks the DBMS_OUTPUT buffer to see if there is data to print. The poll rate can be between 1 and 15.
	Choose DB Connection	Change to a different database connection by choosing it from the list.

---

24.13 OWA Output Window

OWA (Oracle Web Agent) or MOD_PLSQL is an Apache (Web Server) extension module that enables you to create dynamic Web pages from PL/SQL packages and stored procedures. The OWA Output window enables you to see the HTML output of MOD_PLSQL actions that have been executed in the SQL Worksheet.

---

Table 24-12 OWA Output Window Toolbar Icons

Icon	Name	Function
	Enable OWA Output	Click to toggle the SET SERVEROUTPUT setting between ON and OFF. Setting server output ON checks for any output that is placed in the DBMS_OUTPUT buffer, and any output is displayed in this tab.
	Clear	Click to erase the content of this tab.
	Save File	Click to open the Save dialog where you can enter a filename to save the results in this tab.
	Print	Click to open the Print dialog, where you can choose the printer to print the content of this tab.
	Choose DB Connection	Change to a different database connection by choosing it from the list.

---