Using Modern Oracle SQL Tools

Oracle database administrators and developers frequently need to run ad-hoc SQL statements. An ad-hoc statement is a statement written for an immediate task rather than stored permanently inside an application, script, package, or scheduled job. A DBA may use an ad-hoc query to inspect users, roles, privileges, tablespaces, sessions, or profile settings. A developer may use one to test a join, validate a function, inspect sample rows, or confirm that a PL/SQL block behaves correctly before adding it to a larger application.

Older Oracle training material often described this activity through SQL*Plus Worksheet, a graphical worksheet associated with older Oracle Enterprise Manager environments. That tool is no longer the correct center of instruction for a modern Oracle database course. The modern lesson should teach the same skill - running SQL and PL/SQL interactively - but should map that skill to the tools Oracle users are more likely to use today.

In a modern Oracle environment, ad-hoc SQL work is commonly performed through three interfaces:

1. Oracle SQL Developer, a desktop graphical tool with a SQL Worksheet.
2. Database Actions, a browser-based SQL environment commonly used with Oracle Autonomous Database and Oracle Cloud Infrastructure.
3. SQLcl, a modern command-line interface for interactive SQL, PL/SQL, scripting, and automation.

The important modernization point is that SQL*Plus and SQL*Plus Worksheet should not be treated as the same thing. SQL*Plus is Oracle's long-standing command-line tool, and it still has a place in traditional administrative and scripting workflows. SQL*Plus Worksheet, however, was an older graphical worksheet interface. In current instructional material, the practical replacement for SQL*Plus Worksheet is the SQL Worksheet in SQL Developer or the browser-based SQL Worksheet available through Database Actions.

Why This Lesson Moves Beyond SQL*Plus Worksheet

The original purpose of SQL*Plus Worksheet was simple: give the user a place to enter SQL statements, execute them, and review the results. That idea is still central to database administration, but the interface has changed. Modern Oracle tools provide the same basic capability while adding features that older worksheet tools did not provide as effectively.

Modern SQL interfaces typically include syntax highlighting, code completion, statement history, result grids, export options, object navigation, formatting tools, and better support for working with SQL and PL/SQL in the same environment. These features matter because database administration is no longer limited to typing isolated commands. A DBA often moves between user accounts, roles, schema objects, sessions, storage configuration, auditing, and application troubleshooting. A good SQL tool must support that larger workflow.

For this reason, the best modern replacement for the older SQL*Plus Worksheet lesson is not a lesson about one obsolete screen. It is a lesson about how to choose and use the correct SQL interface for the task.

Oracle SQL Developer

Oracle SQL Developer is the primary desktop graphical tool to emphasize in this lesson. It gives developers and DBAs a visual environment for creating database connections, browsing schema objects, writing SQL statements, running PL/SQL, viewing result sets, and exporting query output. Its SQL Worksheet is the closest modern equivalent to the old SQL*Plus Worksheet.

The SQL Worksheet is connected to a specific database connection. After the user creates a connection, the worksheet can send SQL and PL/SQL statements to that database. The worksheet is useful for short queries, administrative checks, testing statements before placing them in scripts, and exploring database objects during development or troubleshooting.

Typical SQL Developer Workflow

A basic SQL Developer workflow usually follows this pattern:

1. Open Oracle SQL Developer.
2. Create or select a database connection.
3. Open a SQL Worksheet for that connection.
4. Type a SQL statement, SQL*Plus-style command, or PL/SQL block.
5. Execute the current statement or run the worksheet as a script.
6. Review the results, messages, or error output.
7. Save, copy, export, or revise the statement as needed.

This workflow is useful because it keeps the database connection, editor, results, and object browser in one environment. For example, a DBA who is managing users can inspect the current user, query data dictionary views, review privileges, and test GRANT or REVOKE statements from one tool window.

Creating a Database Connection

Before the SQL Worksheet can execute statements, SQL Developer needs a connection definition. A connection normally includes a username, password, hostname, port number, and service name. In many Oracle environments, the default listener port is 1521, but the actual value depends on the database configuration. The service name identifies the database service to which the session should connect.

A typical connection definition includes the following values:

• Connection Name: A local label used inside SQL Developer.
• Username: The database account used to connect.
• Password: The credential for the account, if password authentication is used.
• Hostname: The server or cloud endpoint where the listener is available.
• Port: The listener port.
• Service Name: The Oracle Net service used to identify the target database service.
• Role: The connection role, such as default, SYSDBA, or another administrative role where appropriate.

For normal application or schema work, the user typically connects with a regular database account. Privileged administrative connections should be used only when the task requires them. For example, creating users, changing profiles, or inspecting certain administrative views may require elevated privileges, but ordinary data exploration should not be performed as a highly privileged account.

Executing a Statement in the SQL Worksheet

Once the worksheet is open, the user can type a statement and execute it. A simple query might inspect rows from a sample table:

SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 90;

The result appears in a grid. The grid makes it easier to scan rows and columns, sort the output, copy selected values, or export the result to another format. This is one of the major practical differences between a graphical SQL worksheet and a plain command-line session.

The worksheet can also be used to inspect object structure:

DESCRIBE employees;

For database administration, the worksheet can query data dictionary views. For example, a DBA may inspect database users or granted roles:

SELECT username, account_status, default_tablespace, profile
FROM dba_users
ORDER BY username;

SELECT grantee, granted_role, admin_option
FROM dba_role_privs
WHERE grantee = 'HR'
ORDER BY granted_role;

These statements are ad-hoc because the DBA writes them to answer an immediate administrative question. They may later become part of a script or report, but during investigation they are simply interactive statements executed from the worksheet.

Statement Execution Versus Script Execution

SQL Developer supports two common execution styles. The first style executes the current statement. This is useful when the worksheet contains several statements but the user wants to run only the statement under the cursor. The second style runs the worksheet as a script. Script execution is useful when statements must run in sequence and the user wants output similar to a command-line script.

For example, a worksheet might contain several administrative checks:

SHOW USER

SELECT username, account_status
FROM dba_users
WHERE username IN ('HR', 'OE', 'SH');

SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = 'HR'
ORDER BY privilege;

Running one statement at a time is useful during exploration. Running the worksheet as a script is useful when the sequence matters or when the output should be reviewed as a continuous report.

Using SQL Developer for PL/SQL

The SQL Worksheet is not limited to SELECT statements. It can also execute PL/SQL blocks. This makes it useful for testing procedural logic before the logic is placed into a package, procedure, function, or trigger.

BEGIN
   DBMS_OUTPUT.PUT_LINE('Testing PL/SQL output from the SQL Worksheet');
END;
/

When working with PL/SQL, the user should make sure that server output is enabled when output from DBMS_OUTPUT is expected. SQL Developer also includes tools for working with stored procedures, functions, packages, and debugging workflows, making it more suitable than a legacy worksheet for modern development tasks.

Database Actions

Database Actions provides a browser-based SQL environment. It is especially useful in cloud-based Oracle environments where the user may not want to install a desktop client. In an Oracle Autonomous Database workflow, Database Actions gives the user access to a SQL Worksheet directly from the browser.

The browser-based SQL Worksheet is useful for quick access, cloud administration, classroom demonstrations, and situations where the database environment is managed through Oracle Cloud Infrastructure. A user can open the worksheet, enter SQL statements, execute scripts, inspect results, and perform many database tasks without configuring a local desktop tool.

When Database Actions Is a Good Choice

Database Actions is a strong choice when the database is already in a cloud-managed environment and the user wants a zero-install SQL interface. It is also useful when the user is working from a machine where installing SQL Developer is not practical. Because the worksheet runs through a browser, it can simplify access in training, testing, and cloud administration scenarios.

Typical uses include:

• Running quick SQL checks against an Autonomous Database.
• Testing SQL statements from a browser.
• Reviewing result sets without installing a desktop tool.
• Creating database objects during cloud-based development.
• Using SQL in environments where the browser is the primary administrative interface.

Database Actions does not eliminate the need for SQL Developer or command-line tools. Instead, it gives the DBA or developer another interface. The correct choice depends on the environment, the task, and the user's preferred workflow.

SQLcl

SQLcl is Oracle's modern command-line interface for SQL and PL/SQL work. It is especially useful for administrators and developers who prefer terminal-based workflows, batch execution, shell integration, source control, and automation.

SQLcl should be understood as a command-line companion to SQL Developer and Database Actions. It is not a graphical worksheet. It is useful when the user wants to run statements interactively from a terminal or execute SQL scripts as part of a repeatable process.

A simple SQLcl workflow may look like this:

sql /nolog

CONNECT username/password@hostname:port/service_name

SET SQLFORMAT ansiconsole

SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id = 90;

SQLcl is useful for automation because it can run scripts from the command line. A DBA may use it to run validation checks after deployment, generate reports, execute repeatable administrative scripts, or integrate SQL execution into operating system scripts.

When SQLcl Is a Good Choice

SQLcl is a good choice when the task benefits from repeatability. If the DBA expects to run the same SQL file many times, move it between environments, include it in a deployment process, or save it in source control, a command-line tool can be more practical than a graphical worksheet.

Common SQLcl use cases include:

• Running SQL scripts from a terminal.
• Executing administrative checks after a deployment.
• Formatting query output for reports or files.
• Testing SQL from a lightweight command-line session.
• Supporting automation workflows where a GUI would be inappropriate.

SQLcl also helps preserve continuity for users who already understand SQL*Plus-style workflows. Existing SQL*Plus scripts may often be reused or adapted, while SQLcl adds modern command-line features that make interactive work more productive.

Choosing the Right Tool

The modern Oracle user does not need to choose one SQL interface for every task. SQL Developer, Database Actions, and SQLcl overlap in important ways, but each one is strongest in a different situation.

For a beginning DBA or developer, SQL Developer is usually the easiest place to start. It provides a visible connection tree, a SQL Worksheet, an object browser, and a result grid. Database Actions is the natural choice when the database is managed through a cloud browser workflow. SQLcl is the strongest choice when the work needs to be automated, repeated, or run from a terminal.

Security and Administrative Caution

Because these tools can execute SQL and PL/SQL directly against the database, they must be used carefully. A SQL Worksheet is powerful because it can run statements immediately. That power also means a mistaken DELETE, DROP, ALTER, GRANT, or REVOKE statement can have serious consequences.

Good practice includes connecting with the least privilege required for the task, testing statements in a development or training environment before using them in production, reviewing the connected database before executing administrative commands, and using transactions carefully when modifying data.

For user management tasks, the DBA should also understand which account is connected and which privileges are active. A normal schema account is appropriate for many development tasks. A highly privileged administrative account should be reserved for work that truly requires administrative authority.

Lesson Summary

This lesson modernizes the older SQL*Plus Worksheet concept by focusing on current Oracle SQL interfaces. The core skill has not changed: the DBA or developer still needs a reliable way to enter SQL, execute it, review results, and test PL/SQL. What has changed is the toolset.

Oracle SQL Developer provides the primary desktop SQL Worksheet experience. Database Actions provides a browser-based worksheet for cloud and Autonomous Database workflows. SQLcl provides a modern command-line interface for scripting, automation, and terminal-based SQL work. Together, these tools replace the legacy SQL*Plus Worksheet experience while preserving the broader importance of SQL execution in Oracle database administration.

The key distinction is that SQL*Plus Worksheet is obsolete as a teaching target, while SQL*Plus-style command-line work remains part of Oracle's larger tooling ecosystem. A modern Oracle course should therefore teach SQL Developer, Database Actions, and SQLcl as complementary tools for running ad-hoc SQL and PL/SQL statements.

SQL Worksheet Exercise