Lesson 4 | Logging into a database |
Objective | Log into a database with SQL*Plus. |
Logging into Oracle Database
The easiest way to start looking at data in your Oracle database is to use the built-in tool called SQL*Plus.
Uses of SQL*Plus
SQL*Plus gives you a convenient interface with a database in which you can:
- Create database objects such as tables, indexes, and constraints
- Create users and roles
- Grant or revoke privileges of users and roles
- Edit and execute queries
- Adjust output settings to produce simple reports
- Edit and execute PL/SQL blocks
- Insert, update, and delete data in tables
- Spool output to a file
- Read and write files containing SQL or PL/SQL scripts
How to reach SQL*Plus
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.
SQL*Plus modes
This tool is available to you in both
command line mode and 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.
Log into SQL*Plus
Here is a demonstration of how to log into SQL*Plus.
Log into SQL Plus
In the next lesson, we begin exploring the extensions to SQL that Oracle has added for enhancing the SQL standard language.