Home > SQL Developer Concepts and ... > 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 toolbar (under the Worksheet tab): Contains icons for the following operations:
Execute Statement executes the statement at the mouse pointer in the Enter SQL Statement box. The SQL statements can include bind variables and substitution variables of type VARCHAR2 (although in most cases, VARCHAR2 is automatically converted internally to NUMBER if necessary); a pop-up box is displayed for entering variable values.
Run Script executes all statements in the Enter SQL Statement box using the Script Runner. The SQL statements can include substitution variables (but not bind variables) of type VARCHAR2 (although in most cases, VARCHAR2 is automatically converted internally to NUMBER if necessary); a pop-up box is displayed for entering substitution variable values.
Commit writes any changes to the database, and ends the transaction; also clears any output in the Results and Script Output panes.
Rollback discards any changes without writing them to the database, and ends the transaction; also clears any output in the Results and Script Output panes.
Cancel stops the execution of any statements currently being executed.
Monitor SQL Status (Oracle Database Release 11.1 and later only) calls the real-time SQL monitoring feature of Oracle Database, enabling you to monitor the performance of SQL statements while they are executing.
Explain Plan generates the execution plan for the statement (internally executing the EXPLAIN PLAN statement). To see the execution plan, click the Explain tab. For more information, see Execution Plan.
SQL Tuning Advisor displays 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. For more information, see the material about the SQL Tuning Advisor in Oracle Database 2 Day DBA.
Autotrace generates trace information for the statement. To see the Autotrace Pane, click the Autotrace tab.
Clear erases the statement or statements in the Enter SQL Statement box.
To the right of these icons is a drop-down list for changing the database connection to use with the worksheet.
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):
Print File prints the contents of the Enter SQL Statement box.
Cut, Copy, Paste, and Select All have the same meanings as for normal text editing operations.
Refactoring enables you to do the following on selected text: switch character case (to upper/lower/initcap), extract the sequence of PL/SQL statements to a procedure, or rename the local variable.
Format formats the SQL statement (capitalizing the names of statements, clauses, keywords, and so on). The Database: SQL Formatter preferences are used for the formatting. (You can also the Command-Line Interface for SQL Formatting.)
Advanced Format displays a dialog box where you can specify the output destination and output type for the formal operation.
Quick Outline displays the Outline pane with a graphical outline of the object displayed in the worksheet (if an outline is relevant for this type of object). You can click a node in the outline to go to the associated place in the text in the worksheet.
Popup Describe, if the name of a database object is completely selected, displays a window with tabs and information appropriate for that type of object (see Describe Object Window).
Save Snippet opens the Save Snippet (User-Defined) dialog box with the selected text as the snippet text.
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:
If you drag and drop a table or view, by default a SELECT statement is constructed with all columns in the table or view. You can then edit the statement, for example, modifying the column list or adding a WHERE clause.
If you drag and drop a function or procedure, a snippet-like text block is constructed for you to edit when including that object in a statement.
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:
Results: Displays the results of the most recent Execute Statement operation.
Explain: Displays the output if you clicked the Explain Execution Plan icon (see Execution Plan).
Script Output: Displays the output if you clicked the Run Script icon (see Script Runner).
DBMS Output: Displays the output of DBMS_OUTPUT package statements (see DBMS Output Pane).
OWA Output: Displays Oracle Web Agent (MOD_PLSQL) output (see OWA Output Pane).
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
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
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
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:
You cannot use bind variables. (The Execute SQL Statement feature does let you use bind variables of type VARCHAR2, NUMBER, and DATE.)
For substitution variables, the syntax &&variable assigns a permanent variable value, and the syntax &variable assigns a temporary (not stored) variable value.
For EXIT and QUIT, commit is the default behavior, but you can specify rollback. In either case, the context is reset: for example, WHENEVER command information and substitution variable values are cleared.
DESCRIBE works for most, but not all, object types for which it is supported in SQL*Plus.
For SQL*Plus statements that are not supported, a warning message is displayed.
SQL*Plus comments are ignored.
For XMLType data, data in the column is displayed as "SYS.XMLDATA" if the database connection uses a JDBC Thin driver, but the expanded XML values are displayed if the connection uses an OCI (thick, Type 2) driver.
If you have SQL*Plus available on your system, you may want to use it instead of the script runner.
Related Topics
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
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
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:
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.)
Clear: Erases the contents of the pane.
Save: Saves the contents of the pane to a file that you specify.
Print: Prints the contents of the pane.
Buffer Size: For databases before Oracle Database 10.2, limits 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: The interval (in seconds) at which SQL Developer checks the DBMS_OUTPUT buffer to see if there is data to print. The poll rate can be between 1 and 15.
Related Topics
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:
Add New OWA Output Tab: Prompts you to specify a database connection, after which a tab is opened within the OWA Output pane for that connection, and entries written to the OWA output buffer are displayed in that tab. (To stop displaying output for that connection, close the tab.)
Clear: Erases the contents of the pane.
Save: Saves the contents of the pane to a file that you specify.
Print: Prints the contents of the pane.
Related Topics
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
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
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
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:
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:
SQL Query:
SELECT salary, 1000, 30000, 10000, 18000 FROM employees WHERE employee_id < :EMPLOYEE_ID;
Property:
Gauge Type: Status Meter (or Dial if you prefer semicircular dials, each with a label containing the salary amount and a "needle" pointing to an appropriate place on the dial).
Enable (check) Use Query to Specify the Minimum, Maximum, Low, and High Values.
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
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