Run Statement vs Run Script in SQL Developer: When and How to Use Each Mode

Choosing the Right Execution Mode

In SQL Developer the Run Statement shortcut (Ctrl+Enter) and the Run Script shortcut (F5) are not interchangeable. They send different payloads to the database and render output in distinct UI panels, which affects how you can interact with the results, use SQL*Plus directives, and handle bind variables.

How Run Statement Works

Run Statement sends the current statement or the text you have highlighted as a single JDBC call. The server returns a result set that SQL Developer displays in the Query Result grid. This grid is fully editable – you can change values and commit them back with a single click. If the statement contains a bind variable (e.g. :deptno), SQL Developer will pop up a dialog asking you to supply a value.

Key points:

• Only one statement or a highlighted fragment is executed.
• SQL*Plus directives (SET, COLUMN, SPOOL, etc.) are ignored or cause syntax errors.
• Output is an editable grid; you can right‑click and commit changes.
• Bind variables are prompted with a dialog; substitution variables (&var) are not recognized.

How Run Script Works

Run Script emulates the SQL*Plus environment. It sends the entire contents of the worksheet (or the selected block) to the database as a single script. SQL*Plus directives are parsed and applied, and the output is rendered in the Script Output tab as formatted plain text. The result set is read‑only; you cannot edit it directly in the grid.

Key points:

• Supports multi‑statement scripts and SQL*Plus commands.
• Uses ampersand (&) substitution variables; bind variables (:x) are treated as literal text.
• Output is a fixed‑width table in a text pane; no direct editing or commit.
• Useful for generating reports, spooling to files, or applying formatting.

Worked Example: Formatting with SET and COLUMN

Suppose you want a nicely formatted list of employees. Create the following script in a worksheet:

# script.sql
SET PAGESIZE 50
SET LINESIZE 80
COLUMN ename FORMAT A15
SELECT empno, ename FROM emp ORDER BY ename;

Run it with F5. Result (Script Output):

----------------------------------------
 EMPNO ENAME
--------- -----------------
     7369 SMITH
     7499 ALLEN
     7521 WARD
     7566 JONES
     7654 MARTIN
     7698 BLAKE
     7782 CLARK
     7839 KING
     7844 TURNER
     7876 ADAMS
     7900 JAMES
     7902 FORD
     7934 MILLER

Run the same script with Ctrl+Enter. Result (Query Result grid):

Only the SELECT statement is executed; the preceding SET and COLUMN lines are ignored, and you get an editable grid with the same columns but no formatting. If you had used SET PAGESIZE 50 it would simply be sent as part of the SQL and likely cause an error.

Limitations and Common Pitfalls

Run Statement limitations:

• Cannot process scripts that contain SQL*Plus directives.
• Multi‑statement scripts require each statement to be executed separately.
• Bind variables are prompted; if you forget to supply a value the statement will fail.
• Results are not printed to a file – use F5 for spooling.

Run Script limitations:

• The result set is read‑only; you cannot edit or commit changes directly.
• Substitution variables (&var) are required; using :var will not work.
• Large result sets can be slow to render in the text pane.
• Interactive features like the bind‑variable dialog are not available.

Common mistakes:

• Assuming Ctrl+Enter will honour SQL*Plus formatting – it will not.
• Using F5 for a simple SELECT when you need to edit the data – use Ctrl+Enter instead.
• Mixing bind variables and substitution variables in the same script – choose one convention.
• Overlooking that the Script Output panel is read‑only; you cannot copy values back to the database from it.

To verify which mode you are using, check the tab that appears after execution: Query Result for Ctrl+Enter, Script Output for F5. You can also look at the status bar – it will display “RUN STATEMENT” or “RUN SCRIPT” accordingly.

When deciding which mode to use, ask:

• Do I need to edit the data in the grid? – use Ctrl+Enter.
• Do I need SQL*Plus directives or spooling? – use F5.
• Will I be submitting a multi‑statement script? – use F5.
• Do I need a substitution variable? – F5 with & syntax.

Practical Checklist

Scenario	Recommended Mode	Why
Single SELECT with bind variable	Ctrl+Enter	Prompted dialog, editable grid
Script with SET, COLUMN, SPOOL	F5	SQL*Plus support, formatted output
Multiple DML statements in one block	F5	Batch execution
Need to spool results to a file	F5	Uses SPOOL command
Quick data review without editing	Ctrl+Enter	Fast grid display

By matching your workflow to the appropriate execution mode, you avoid errors, improve productivity, and make the most of SQL Developer’s UI.