Log into Oracle Database with SQL*Plus

The easiest way to start looking at data in your Oracle database is the built-in tool called SQL*Plus. It gives you a direct, no-frills interface to the database, where you can:

1. Create database objects such as tables, indexes, and constraints
2. Create users and roles
3. Grant or revoke privileges of users and roles
4. Edit and execute queries
5. Adjust output settings to produce simple reports
6. Edit and execute PL/SQL blocks
7. Insert, update, and delete data in tables
8. Spool output to a file
9. Read and write files containing SQL or PL/SQL scripts

Everything else in this lesson is about the mechanics of getting from "not connected" to that SQL> prompt.

SQL*Plus is available in two forms, and it's worth being precise about what each one actually is today. The command-line version (sqlplus) ships with every Oracle client and database installation, on Windows, Linux, and Unix alike, and remains fully current. The old SQL*Plus for Windows GUI (sqlplusw.exe) is a different story: Oracle dropped it from the client and database install starting around Oracle 11g, roughly two decades ago now, and it never came back. Its functional replacement is SQL Developer — a full graphical worksheet environment that connects to your database, lets you write and run SQL and PL/SQL, browse schema objects, and see results in a grid, all in the same window. In modern cloud deployments you may also encounter a browser-based SQL worksheet (such as Database Actions) that serves the same purpose.

When this course refers to "window mode," it means a graphical SQL worksheet like SQL Developer — not the retired sqlplusw.exe. Both the command line and a GUI worksheet ultimately talk to the same SQL*Plus/SQL engine underneath; the difference is entirely in the interface, not in what SQL or PL/SQL you're able to run.

Wherever you connect from, connecting to Oracle creates a new session, and within that session the PL/SQL run-time engine is available as a built-in resource — you can run SQL or PL/SQL directly from the SQL*Plus environment, and PL/SQL program units can in turn run SQL statements or call external procedures. Applications can also call PL/SQL directly through the Oracle Call Interface (OCI) or JDBC, which lets you push logic — including transaction scope — down into stored PL/SQL program units instead of managing it entirely in an application's data-access layer.

One capability worth knowing about here: PL/SQL supports dynamic SQL — building and running SQL statements at run time rather than writing them out in advance. There are two approaches: Native Dynamic SQL (NDS), the more direct, higher-performance option for most cases, and the DBMS_SQL package, generally reserved for cases where you don't know the number of columns your dynamic query will return until run time. NDS is the right default; reach for DBMS_SQL specifically when that column-count uncertainty applies.

If you're using (or prefer) the command-line environment, here's the full flow. On Windows, open a command prompt; on Unix/Linux, open a terminal and get to your shell prompt. Then:

1. Type sqlplus and press Enter. SQL*Plus starts and prompts you for a username:

Enter user-name: PETSTORE

2. Type your username and press Enter. The sample username for this course is PETSTORE. SQL*Plus then prompts for a password:

Enter password:

3. Type the corresponding password and press Enter — the password for this course's sample user is GREATPETS. You won't see the characters as you type them. On success, SQL*Plus displays the SQL> prompt, telling you it's ready for commands:

SQL>

4. Type your query. When you're done entering it, press Enter until you're back at the SQL> prompt:

SQL> SELECT TABLE_NAME
FROM USER_TABLES
ORDER BY TABLE_NAME
SQL>

5. Type a forward slash (/) and press Enter to actually execute it:

SQL> /

TABLE_NAME
------------------------------
CUSTOMER
CUSTOMER_SALE
PET_CARE_LOG
PRODUCT
SALE_ITEM

SQL>

6. To leave SQL*Plus, type EXIT and press Enter:

SQL> EXIT

That's the whole cycle: connect, type a statement, terminate it with /, read the result, repeat.

You'll see SCOTT referenced constantly in Oracle's own documentation and in countless tutorials as the canonical example username, paired with the equally famous password TIGER. It's a naming convention that's stuck around for decades — but it's worth being precise about its current status: SCOTT is not installed by default in a modern Oracle database. If you want it, it has to be created explicitly via a demo script. And TIGER is about as publicly known a password as exists in the Oracle world, so it's an example to recognize in documentation, not one to actually use anywhere real. For this course, your working credentials are the sample schema already set up for you: username PETSTORE, password GREATPETS.

The command-line flow above is the most direct path and works identically everywhere. If you're using SQL Developer or a similar graphical worksheet instead, the underlying steps are conceptually the same, just presented differently: you'll create or select a database connection, supply your username, password, and connection details (host, port, and service name or database identifier), and connect. Once connected, you get a worksheet pane to type SQL or PL/SQL into, and a results grid below it instead of scrolling text — functionally equivalent to the command line, just easier to read at a glance. For example, entering:

SELECT LASTNAME FROM CUSTOMER;

and running it will return the same result set a command-line session would, just rendered as a table in the worksheet's results pane rather than printed inline.

Whichever interface you use day to day, the underlying mechanics are identical: connect with valid credentials, get a session, run SQL or PL/SQL, read the results. Getting comfortable with the command-line flow specifically is worth the effort even if you end up preferring a GUI worksheet for daily work — it's always available, it's identical across every platform Oracle runs on, and it's often the fastest path when you're troubleshooting a connection issue the GUI is hiding from you.