Log into Oracle Database with SQL*Plus.

The easiest way to start looking at data in your Oracle database is to use the built-in tool called SQL*Plus. SQL*Plus gives you a convenient interface with a database in which 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

You can reach SQL*Plus from the client or the server side, as shown in the previous lesson. Both client and server versions of SQL*Plus have the same look and feel. The PL/SQL run-time engine exists as a resource inside the SQL*Plus environment. The SQL*Plus environment is both interactive and callable. Every time you connect to the Oracle 11g Database, the database creates a new session. In that session, you can run SQL or PL/SQL statements from the SQL*Plus environment. PL/SQL program units can then run SQL statements or external procedures. Calls directly to PL/SQL can be made through the Oracle Call Interface (OCI) or Java Database Connectivity (JDBC). This lets you leverage PL/SQL directly in your database applications. This is important because it lets you manage transaction scope in your stored PL/SQL program units. This tremendously simplifies the myriad tasks often placed in the data abstraction layer of applications. PL/SQL also supports building SQL statements at run time. Run-time SQL statements are dynamic SQL. You can use two approaches for dynamic SQL: one is Native Dynamic SQL (NDS) and the other is the DBMS_SQL package. The Oracle 11g Database delivers new NDS features and improves execution speed. With this release, you only need to use the DBMS_SQL package when you don’t know the number of columns that your dynamic SQL call requires.
This tool is available to you in both

1. command line mode and
2. window mode.

During this course, you will use the window mode for SQL*Plus because it is quickly becoming the preferred mode among database developers.

If you prefer to use a command line environment, or if that is your only option, you can start up SQL*Plus in the command line mode. On Windows, start up a command line window (MS-DOS prompt). On UNIX, start up a terminal window or get to the dollar($) prompt. Then follow these steps:

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

Enter user-name: PETSTORE

1. Type a username and press Enter. The username for the sample user in this course is PETSTORE. If you do not know a username, use the default username, SCOTT. SQL*Plus prompts you for a password:

Enter password:

1. Type the password for the username that you typed and press Enter. The default username SCOTT has the password TIGER. The password for the sample user in this course is GREATPETS. You will not see the password when you type it on the screen. SQL*Plus logs you into the database and signals that it is ready to accept your commands by displaying the SQL prompt:

SQL>

1. Type in one or more lines of a query (or other SQL command). When done, press Enter until you see the SQL> prompt.

SQL> SELECT TABLE_NAME 2 
FROM USER_TABLES 3 ORDER BY TABLE_NAME 4 SQL> 

1. Type a forward slash (/) and press Enter to execute the query.

SQL> / SQL*Plus executes the query and displays the results on the screen. Then another prompt is displayed signaling that SQL*Plus is ready for more input. TABLE_NAME ------------------------------ CUSTOMER CUSTOMER_SALE PET_CARE_LOG PRODUCT SALE_ITEM SQL>

1. To exit, type EXIT and press Enter. SQL*Plus closes and returns you to your operating system.

SQL> EXIT

Here is a demonstration of how to log into SQL*Plus.
Log into SQL Plus

SQL*Plus is a tool used to enter SQL commands and display the output. It is provided with every Oracle installation, whether on Windows, Unix, or Linux. It is a command line interface and supports editing, user input, and report formatting. In 11g, SQL*Plus for Windows (sqlplusw.exe) is no longer part of the client or database install. The command line version (sqlplus.exe) is still available. You can use an older version of SQL*Plus for Windows to connect to an 11g database, but some functionality may not be supported. SQL Developer is a GUI interface that is shipped with 11g and should be considered the replacement for SQL*Plus for Windows.
To start SQL*Plus, simply type ‘sqlplus’ at the command prompt or after starting a DOS command session in Windows. Under normal circumstances, SQL*Plus prompts you for a username and corresponding password. If you are able to provide a valid username/password combination, the SQL> prompt appears on your screen to indicate that you have successfully established a session. You can also start SQL*Plus with the username and password at the command line, as shown in Figure 2-4. In this case, if the username/password are valid, the SQL> prompt will appear. If not, you will be asked to enter a valid username and password.

1. On Windows, click the Start button, then choose Programs.
2. Choose Application Development.
3. Choose SQL Plus.
4. Type a valid username, password, and service. For this simulation, we will use the sample username that contains the course project tables.
5. Click the OK button.
6. Type a query in the window, starting at the SQL> prompt. Use this query:
   

   SELECT LASTNAME FROM CUSTOMER;
   

   
   End your query with a forward slash on a single line. This tells SQL*Plus to execute the query.

8. SQL*Plus displays the results of the query. Now type EXIT and press Enter to close SQL*Plus.

In the next lesson, we begin exploring the extensions to SQL that Oracle has added for enhancing the SQL standard language.