Execute SQL and Review Results in Oracle SQL Developer Worksheet

Problem and outcome

You need to run ad-hoc SQL against an Oracle database, inspect the returned rows, capture server messages, and confirm how the database will execute the statement—without deploying application code or managing standalone script files. The SQL Worksheet in Oracle SQL Developer provides this scratchpad environment for any JDBC-connected database.

The useful outcome is a repeatable workflow: write a statement, execute it, review the result grid, check DBMS_OUTPUT messages, and inspect the execution plan before promoting the SQL to a controlled script or migration.

Prerequisites

• Oracle SQL Developer installed (recent 22.x or later releases are assumed; older versions may place menu items differently).
• A configured database connection with valid credentials.
• A JDBC driver compatible with the target database version. For example, pairing an older 11g driver with a 19c database can cause connection failures or unexpected syntax errors.
• Database privileges: SELECT on the tables you query, and EXECUTE on DBMS_XPLAN if you inspect execution plans through that package.

Open the worksheet and connect

1. Launch SQL Developer and open your database connection from the Connections panel.
2. Open the SQL Worksheet. Press Ctrl+N, select SQL File, or click the SQL Worksheet icon in the toolbar.
3. Confirm the connection selector at the top of the worksheet shows the intended database. If it does not, choose the connection from the dropdown before running any statement.

Run a query and inspect the result grid

Paste or type a statement in the editor pane. Replace your_schema and employees with objects you are authorized to read:

SELECT employee_id, last_name, salary
FROM your_schema.employees
WHERE department_id = 10
ORDER BY salary DESC;

Where to run: SQL Worksheet editor pane. Required permission: SELECT on your_schema.employees. Press F5 to run all statements in the worksheet, or Ctrl+Enter to run only the statement at the cursor. Expected check: the status bar reports success and the result grid below the editor displays columns and rows. If the grid is empty, verify the WHERE clause and confirm the table contains data in the connected schema. Relevant risk: large result sets can consume IDE memory. Limit rows during development with ROWNUM <= 100 or adjust the fetch size in the worksheet preferences before executing broad scans.

Capture server messages with DBMS_OUTPUT

PL/SQL blocks and some procedures send messages through DBMS_OUTPUT. To see them:

1. Choose View > DBMS_OUTPUT to open the panel.
2. Click the green plus icon in the DBMS_OUTPUT panel and select your active connection.
3. Run a block such as:

BEGIN
  DBMS_OUTPUT.PUT_LINE('Row count check started');
END;

Expected check: the panel displays the message text after execution. If the panel is blank, confirm the connection was added to the DBMS_OUTPUT monitor and that the block actually ran without exception.

Review the execution plan

Before assuming a query is efficient, inspect the plan:

1. With the statement in the editor, click the Explain Plan icon in the worksheet toolbar.
2. View the Explain Plan tab that opens beside the result grid.

Expected check: the plan shows the intended access path. For example, a query filtering by an indexed column should show an index range scan rather than a full table access. If it shows a full scan on a large table, consider adding a predicate, reviewing index availability, or gathering optimizer statistics. Required permission: EXECUTE on DBMS_XPLAN if SQL Developer generates the plan through that package.

Export or copy results

To move data out of the worksheet:

• Right-click inside the result grid and choose Export to save rows as CSV, SQL Insert, or another supported format.
• Select cells and copy to the clipboard for quick pasting into a spreadsheet or ticket.

Relevant risk: exported files contain live data. Handle them according to your organization’s data-handling policy, especially if the result set includes personal or financial information.

Debugging PL/SQL (optional)

For stored procedures or functions, use the Debug button in the worksheet toolbar instead of Execute. Debugging requires database debug privileges, such as DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE, granted by a DBA. If the debug session fails to attach, confirm these grants and that the target database listener permits debugging connections.

Expected checks

• Statement completes with a success status and no unhandled exceptions.
• Result grid row count matches your expectation for the filter criteria.
• DBMS_OUTPUT panel shows expected messages, if any were emitted.
• Explain Plan shows the intended access path and no unexpected full table scans on large objects.

Recovery and rollback

SELECT statements do not change data, so rollback is not applicable. If you run UPDATE, DELETE, INSERT, or DDL in the worksheet, changes are subject to SQL Developer’s transaction behavior and your session settings. To undo uncommitted DML in the same session, run:

ROLLBACK;

If the connection drops or the JDBC driver reports a protocol error, close the worksheet, reopen the database connection, and rerun the statement. Do not assume a failed DML statement automatically rolled back; verify row state before issuing further changes.

Limitations

• The worksheet is intended for development and investigation, not for version-controlled schema deployment. Promote tested SQL to migration scripts stored in source control.
• The result grid may truncate very long CLOB or XML values; use SQL*Plus-style output or a dedicated export for complete inspection.
• Execution plans captured in the worksheet reflect the current optimizer environment and statistics; production bind values or different sessions may produce different plans.

Verify the result

A practical verification is to run the same query with a count aggregate and compare it to the visible grid row count:

SELECT COUNT(*) FROM your_schema.employees WHERE department_id = 10;

If the aggregate count matches the number of rows shown in the grid and the status bar reports success, the worksheet execution is verified.