Using the SQL Worksheet

You can use the SQL Worksheet 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 can display a SQL Worksheet by right-clicking a connection in the Connections navigator and selecting Open SQL Worksheet, by selecting Tools and then SQL Worksheet, or by clicking the Use SQL Worksheet icon under the menu bar. In the Select Connection dialog box, select the database connection to use for your work with the worksheet. You can also use that dialog box to create and edit database connections. (You can have a SQL Worksheet window open automatically when you open a database connection by enabling the appropriate SQL Developer user preference under Database Connections.)

To create a separate unshared worksheet for a connection, click in the worksheet and use Ctrl+Shift+N.

The SQL Worksheet has Worksheet and Query Builder tabs, as shown in the following figure (where the Worksheet tab is selected):

SQL Worksheet interface

SQL Worksheet toolbar (under the Worksheet tab): Contains icons for the following operations:

The context menu (right-click, or Shift+F10) includes the preceding Worksheet toolbar operations, plus the following operations (some depending on the type of object displayed in the worksheet):

Enter SQL Statement: The statement or statements that you intend to execute. For multiple statements, each non-PL/SQL statement must be terminated with either a semicolon or (on a new line) a slash (/), and each PL/SQL statement must be terminated with a slash (/) on a new line. SQL keywords are automatically highlighted. To format the statement, right-click in the statement area and select Format SQL.

You can drag some kinds of objects from the Connections navigator and drop them into the Enter SQL Statement box:

To view details for any object, you can select its name in the Enter SQL Statement box and select Popup Describe from the context menu (or press Shift+F4). For example, if you select a table name and press Shift+F4, information about Columns, Constraints, Grants, and so on is displayed; or if you select a procedure name and press Shift+F4, information about Code, Grants, Dependencies, References, and Details is displayed.

Tabs display panes with the following information:

To toggle the relative heights of the Enter SQL Statement area and the area for tabs and display panes, press Ctrl+Alt+L. You can also manually adjust the heights.

Related Topics

SQL*Plus Statements Supported and Not Supported in SQL Worksheet

Script Runner

Execution Plan

Autotrace Pane

DBMS Output Pane

OWA Output Pane

SQL History

Query Builder

Command-Line Interface for SQL Formatting

Gauges: In the SQL Worksheet and User-Defined Reports

Entering OLAP DML Statements in the SQL Worksheet

Using Snippets to Insert Code Fragments

Use the SQL Worksheet for Queries (tutorial)

SQL Developer Concepts and Usage

SQL*Plus Statements Supported and Not Supported in SQL Worksheet

The SQL Worksheet supports some SQL*Plus statements. SQL*Plus statements must be interpreted by the SQL Worksheet before being passed to the database; any SQL*Plus that are not supported by the SQL Worksheet are ignored and not passed to the database.

The following SQL*Plus statements are supported by the SQL Worksheet:

@
@@
/
acc[ept]
autotrace
clear screen
conn[ect]
def[ine]
desc[ribe]
doc[ument]
echo
errors
esc[ape]
exec[ute]
exit
feed[back]
help
ho[st]
pagesize 
pau[se]
print
pro[mpt]
quit
rem[ark]
roll[back]
show (SHOW ALL and individual options shown in SHOW ALL output)
set (valid SET options corresponding to options in SHOW ALL output)
spo[ol] {filename[.ext] | OFF}
sta[rt]
term[out]
timi[ng]
undef[ine]
user
var[iable]
ver[ify]
whenever
xquery

The following SQL*Plus statements are not supported by the SQL Worksheet:

a[ppend]
archive
attr[ibute]
bre[ak]
bti[tle]
c[hange]
col[umn]
comp[ute]
copy
del
disc[onnect]
ed[it]
get
i[nput]
l[ist]
newpage
oradebug
passw[ord]
r[un]
recover
repf[ooter]
reph[eader]
sav[e]
startup
shu[tdown]
startup
store
tti[tle]

For information about SQL*Plus statements, you can enter the help statement. For information about a specific statement or topic, include it as the parameter (for example, help @, help exit, or help reserved words). If the statement is not supported in SQL Developer, or if there are restrictions or usage notes, the help display includes this information (for example, "Not currently in SQL Developer").

Related Topics

Using the SQL Worksheet

Script Runner

The script runner emulates a limited set of SQL*Plus features. You can often enter SQL and SQL*Plus statements and execute them by clicking the Run Script icon. The Script Output pane displays the output.

The SQL*Plus features available in the script runner include @, @@, CONNECT, EXIT, QUIT, UNDEFINE, WHENEVER, and substitution variables. For example, to run a script named c:\myscripts\mytest.sql, type @c:\myscripts\mytest in the Enter SQL Statement box, and click the drop-down next to the Execute Statement icon and select Run Script.

The following considerations apply to using the SQL Developer script runner:

If you have SQL*Plus available on your system, you may want to use it instead of the script runner.

Related Topics

Using the SQL Worksheet

Execution Plan

The Execute Explain Plan icon generates the execution plan, which you can see by clicking the Explain Plan tab. The execution plan is the sequence of operations that will be performed to execute the statement. An execution plan shows a row source tree with the hierarchy of operations that make up the statement. For each operation, it shows the ordering of the tables referenced by the statement, access method for each table mentioned in the statement, join method for tables affected by join operations in the statement, and data operations such as filter, sort, or aggregation.

In addition to the row source tree, the plan table displays information about optimization (such as the cost and cardinality of each operation), partitioning (such as the set of accessed partitions), and parallel execution (such as the distribution method of join inputs). For more information, see the chapter about using EXPLAIN PLAN in Oracle Database SQL Tuning Guide.

Related Topics

Using the SQL Worksheet

Autotrace Pane

The Autotrace pane displays trace-related information when you execute the SQL statement by clicking the Autotrace icon. Most of the specific information displayed is determined by the SQL Developer Preferences for Database: Autotrace/Explain Plan. If you cancel a long-running statement, partial execution statistics are displayed.

This information can help you to identify SQL statements that will benefit from tuning. For example, you may be able to optimize predicate handling by transitively adding predicates, rewriting predicates using Boolean algebra principles, moving predicates around in the execution plan, and so on. For more information about tracing and autotrace, see the chapter about tuning in SQL*Plus User's Guide and Reference.

To use the autotrace feature, the database user for the connection must have the SELECT_CATALOG_ROLE and SELECT ANY DICTIONARY privileges.

Related Topics

Using the SQL Worksheet

DBMS Output Pane

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 pane is used to display the output of that buffer. This pane contains icons and other controls for the following operations:

Related Topics

Using the SQL Worksheet

OWA Output Pane

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 pane enables you to see the HTML output of MOD_PLSQL actions that have been executed in the SQL Worksheet. This pane contains icons for the following operations:

Related Topics

Using the SQL Worksheet

SQL History

You can click View, then History (or press F8 in the SQL Worksheet) to view a dockable window with SQL statements and scripts that you have executed, and optionally 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.

You can click on a column heading to sort the rows by the values in that column.

The SQL history list will not contain any statement that can include a password. Such statements include (but are not necessarily limited to) CONNECT, ALTER USER, and CREATE DATABASE LINK.

You can control the maximum number of statements in the history by setting the SQL History Limit preference (see Database: Worksheet preferences).

Append: Appends 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: Replaces any statements currently on the SQL Worksheet with the selected statement or statements.

Clear History: Removes the selected statement or statements (or all statements if no statements are selected) from the SQL history. (You will be asked to confirm this action.)

Filter: If you type a string in the text box and click Filter, only SQL statements containing that string are displayed.

Related Topics

Using the SQL Worksheet

Query Builder

The Query Builder pane enables you to display and build SQL queries graphically. 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. While you are building the query, you can click the Worksheet tab to see the SELECT statement reflecting current specifications, and then click the Query Builder tab to continue building the query if you want.

In the area below the graphical display of tables and views, you can specify one or more lines with the following information:

Output: Specifies whether to include the expression in the statement output.

Expression: Column name or expression.

Aggregate: Aggregation function to be used (Avg, Avg Distinct, Count, and so on).

Alias: Column alias to be used.

Sort Type: Ascending or Descending sorting of results.

Sort Order: Order to use in sorting results if multiple columns or expressions are to be used (for example, sorting first by department and then by salary within each department).

Grouping: Specifies whether to insert a GROUP BY clause.

Criteria: An expression with one or more criteria that must be satisfied for a result to be returned. You can specify any WHERE clause (without the WHERE keyword). For example, for employees.SALARY, specifying > 10000 limits the results to employees with salaries greater than $10,000.

Or: You can specify one or more OR clauses to be added to the query criteria. For example, if Expression = employees.LAST_NAME, you could specify Or as = 'Smith' to add OR (employees.LAST_NAME = 'Smith' to the query.

Related Topics

Using the SQL Worksheet

Command-Line Interface for SQL Formatting

As an alternative to using the SQL Developer graphical interface for formatting a .sql file or all .sql files in a directory or folder, you can use the command line, which is explained in Command-Line Interface for SQL Developer.

Before invoking the command-line interface for SQL formatting, start the SQL Developer graphical interface, so that the Database: SQL Formatter preferences (which are used for the formatting) are loaded and available.

The following example takes the SQL code in c:\temp\myfile.sql and creates c:\temp\myfile_out.sql containing the formatted code. (Enter the command on one command line.)

C:\sqldeveloper\sqldeveloper\bin>sdcli format input=c:\temp\myfile.sql output=c:\temp\myfile_out.sql

Related Topics

Command-Line Interface for SQL Developer

Gauges: In the SQL Worksheet and User-Defined Reports

You can use graphical gauges to display query results in the SQL Worksheet and in user-defined reports. In both cases, you need to specify the name of the value column for the gauge data, and minimum and maximum values on the gauge, and the values to be shown as low and high on the gauge (usually between the minimum and maximum values). In the SQL Worksheet, the required structure for the value to be selected is:

'SQLDEV:GAUGE:<min>:<max>:<low>:<high>:' || <value-column>

For example, to display the last name and the salary in gauge format, where the gauge shows from 1000 to 30000 with below 10000 as low and above 18000 as high, for employees with ID numbers less than a number to be specified, connect to the supplied HR schema and execute the following query:

SELECT last_name, 'SQLDEV:GAUGE:1000:30000:10000:18000:' || salary 
  FROM employees WHERE employee_id < :employee_id

If you specify 104 as the bind variable value, the output appears as shown in the following figure:

Gauge display of preceding query

For a user-defined gauge report, the query must specify only the value column, the minimum and maximum values, and the low and high values, and optionally a WHERE clause. The required structure for the query (before any optional WHERE clause) is:

SELECT <value-column>, <min>, <max>, <low>, <high> FROM <table-name>

For example, to create a report of salaries in gauge dial format, with the same values and WHERE clause as in the preceding query, right-click on User Defined Reports in the Reports navigator and select New Report. In the New Report dialog box, specify a report name; for Style, select Gauge; and enter the following on the subtabs:

Click Apply.

Use the Reports navigator to view the newly created user-defined report. For Connection, specify one that connects to the HR sample schema. For the bind variable value, specify 104.

Related Topics

Using the SQL Worksheet

User Defined reports

Entering OLAP DML Statements in the SQL Worksheet

If Oracle OLAP is available for the connection associated with a SQL Worksheet, you can enter OLAP DML statements by prefixing a tilde (~) to the statement and clicking the Run Statement (for a selected individual statement) or Run Script (for all statements on the worksheet) icon. For example, to enter the show tod statement to show the time of day:

~show tod

To see the output of OLAP DML statements, you must display the DBMS Output Pane (View > Dbms Output).

For information about OLAP DML statements, see the Oracle OLAP DML Reference major help topic.

Related Topics

Using the SQL Worksheet