SQL:  The Standard RDBMS Language

The Structured Query Language (SQL) is the lingua franca of all relational database management systems (RDBMS). The American National Standards Institute (ANSI) accepted SQL as the standard relational database language in October 1986 (ANSI X3.135-1986). SQL has also been accepted by the International Standards Organization (ISO standard 9075) and by the U.S. government in the Federal Information Processing Standard FIPS-127. A security enhancement has been added and is covered in ANSI SQL Addendum I and issued as X3.135-1989 and 9075-1989. SQL92 is the most recent standard, and Oracle's implementation is entry-level-compliant and has numerous extensions that make it one of the best SQL implementations available. Oracle9i SQL is documented in Oracle9i SQL Reference, Release 1(9.0.1), Part No. A90125-01, June 2001, Oracle Corporation. The latest SQL standard, simply titled SQL3, was supposed to be approved by 1999;however, not much is being said about it since its initial release in 1999, so it would appear to have stalled. Fortunately, much of the Oracle object features are supposedly SQL3- (SQL99-; described below) and ODMG-standard-compliant.

The latest SQL standard, published by ANSI and ISO, is often called SQL99. The formal names of the new standard are:

ANSI X3.135-1999, ?Database Language SQL,? Parts 1 ("Framework"), 2 ("Foundation"), 3 ("Bindings")

ISO/IEC 9075:1999, ?Database Language SQL,? Parts 1 ("Framework"), 2 ("Foundation"), 3 ("Bindings")

The Oracle9i server, Oracle precompilers for C/C++ release 8.1, Oracle Precompiler for  COBOL, release 8.1, and SQL*Module for ADA release 8.0.4 provide full or partial conformance with the ANSI and ISO standards. The Oracle SQL Reference Manual, Release 1 (9.0.1) provides charts in Appendix B that show which sections of the database are in compliance, and more important, which are not.

NIST testing of database compliance has been suspended, so we have to take the vendors? word (Oracle) about compliance for SQL3.

SQL is considered to be a nonprocedural language, because of the way it processes sets of records and provides automatic data access, or navigation. SQL also uses query optimization; that is, the RDBMS kernel determines the optimum method to reach the desired data so you don't have to. Under Oracle8, Oracle8i, and Oracle9i, a cost-based or a rules-based approach can be used. SQL is designed to be simple to learn and use. Despite this simplicity, SQL provides a rich command set under which virtually any combination of data in a database can be retrieved and reported.

In SQL the major statement is SELECT. SELECT allows the retrieval of data from the database. Multiple SELECTs can be nested into a single statement, and, using NET8, SELECTs can even span databases. SQL also allows insertion, update, and deletion of data, as well as creation, modification, and deletion of database objects such as tables, sequences, indexes, and tablespaces. SQL provides for database security and consistency. Unlike other systems where entirely different command sets govern the different areas of database activities, SQL combines these functions into an integrated command set. All of the SQL for Oracle is shown in the link to the Oracle SQL manual on the Wiley Web site; refer to the appropriate reference manual (Oracle9i SQL Reference, Release 1 (9.0.1), Part No. A90125-01, June 2001, Oracle Corp. as of this writing) for more details on command usage.

SQL is also portable. If two RDBMSs are ANSI-compliant, then an SQL statement that works in one will work in the other, assuming the data structures are the same. This only applies to standard SQL commands; naturally, system-specific extensions won't transport to other systems. A good example of this is the Oracle STORAGE clause.

If the DBA is not familiar with SQL, it is suggested that he or she look through the SQL Language Reference Manual and become familiar with the basic commands. The SQL language is utilized through SQL*Plus, Oracle Administrator Toolbar SQL Worksheet, or SVRMGR.

The link to the SQL manual on the Wiley Web page shows the formats for all SQL commands referenced in this book. For more detailed explanations, the DBA should refer to the SQL Language Reference Manual for the release of the Oracle database under which he or she is operating.

SQL*Plus: An Oracle Extension to Standard SQL

The SQL*Plus program allows users to access SQL and the Oracle SQL extensions. This combination of commands, SQL, and the Oracle SQL extensions enable users and the DBA to access the database via standard SQL and to format input and output using the SQL*Plus extensions to the language. In Oracle9i, the Server Manager (SVRMGR) program has been completely eliminated in favor of an extended SQL*Plus implementation. The most current documentation as of this writing is SQL*Plus User's Guide and Reference, Release 1 (9.0.1), PART# A88827-01, June, 2001, Oracle Corporation.

SQL*Plus has a command buffer that stores the current command and allows the user to edit the command using the native system editor or via command-line input. SQL*Plus also allows the user to store, retrieve, and execute SQL/SQL*Plus command scripts, as well as PL/SQL command scripts. It allows use of abbreviated commands and calls to the host system command language.  In SQL*Plus, local variables can be assigned as well as providing control of  system variables used for format and display control. However, though the new SQL*Worksheet handles standard SQL well, it will not handle the formatting commands from the SQL*Plus extensions.

SQL*Plus can also be used to dynamically create command scripts that can then be used to perform en masse changes to users, tables, or other Oracle objects. This is a very powerful feature that will be used in many of the example scripts. If DBAs are not familiar with SQL*Plus, it is suggested they review the SQL*Plus reference guide to acquaint themselves with the SQL*Plus commands.

SQL*Plus can be accessed once the user has set the appropriate system variables and path, which is done using  the ?.oraenv? command on UNIX and Linux; in some environments, however, the ORACLE_SID variable may have to be set to point to your database. On NT, the user must either set some environmental variables or use connect aliases (for everything but the prespecified password, I usually set up an SQLPLUS icon for each instance I need to connect to). The format for invoking SQL*Plus is as follows:

SQLPLUS  username/password@connect string  @command file
or
SQLPLUS /NOLOG

where:

username/password.  The user's Oracle username and password, which is usually different from the operating system username and password. If the user is assigned an autologin type of account, only the solidus (/) is required.

@connect string.  A string that connects the user to databases other than the default database. It can be used with SQL*NET or NET8 over networks to access other systems.

@command file.  Allows the user to specify an SQL command file that is run automatically.

/NOLOG. Enables connection without specifying username and password; however you must use the CONNECT username command before the database is available to you. Usually this form is used with a CONNECT SYS AS SYSDBA type connection to perform database maintenance, startup, or shutdown.

If the DBA account is what is known as an OPS$ account (not recommended), the format would be as follows:

SQLPLUS /

Since an OPS$ account allows the user to get into the Oracle system without specifying a password if he or she is logging in from his or her normal account, the use of OPS$ accounts should be restricted to ?captive?-type users, that is, users who can only access the system via an appropriate secure menu system. Under Oracle8, 8i, and 9i, the OPS$ format is the default, but the system manager can assign whatever prefix he or she desires by use of the OS_AUTHENT_PREFIX parameter in the INIT.ORA file.

SVRMGR: A Database Administrator's Toolset

As its name implies, SVRMGR (Server Manager) is designed for the DBA and only for the DBA. The SVRMGR program provides access to the database internals and to all database objects. To use SVRMGR, the user must belong to the DBA group under UNIX or Linux, or have the proper group specification in NT or W2K. The SVRMGR product has both a line command version and an X Windows implementation (SVRMGRL and SVRMGRM) on most operating systems where it is appropriate, such as UNIX or NT.

In Oracle7, the use of SVRMGR is covered in the Oracle7 Server Utilities User's Guide ; in later releases, it is covered in the Server Manager's User Guide.

Changes under Oracle9i