|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.
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.