Oracle7 Server Concepts Manual Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index

Go to previous file in sequence Go to next file in sequence


High thoughts must have high language.

Aristophanes: Frogs


For complete information on PL/SQL, see the PL/SQL User's Guide and Reference.

Structured Query Language (SQL)

SQL is a very simple, yet powerful, database access language. SQL is a non-procedural language; users describe in SQL what they want done, and the SQL language compiler automatically generates a procedure to navigate the database and perform the desired task.


Oracle SQL includes many extensions to the ANSI/ISO standard SQL language, and Oracle tools and applications provide additional commands. The Oracle tools SQL*Plus and Server Manager allow you to execute any ANSI/ISO standard SQL statement against an Oracle database, as well as additional commands or functions that are available for those tools.


See the Oracle7 Server SQL Reference for more information about SQL commands and other parts of SQL (for example, functions) and the Oracle Server Manager User's Guide for more information about Server Manager commands, including their distinction from SQL commands.

This section includes the following topics:

SQL Statements

All operations performed on the information in an Oracle database are executed using SQL statements. A SQL statement is a specific instance of a valid SQL command. A statement partially consists of SQL reserved words, which have special meaning in SQL and cannot be used for any other purpose. For example, SELECT and UPDATE are reserved words and cannot be used as table names.


SELECT ename, deptno FROM emp;


SELECT ename 


Oracle SQL statements are divided into the following categories:

Each category of SQL statement is briefly described below.

Note: Oracle also supports the use of SQL statements in PL/SQL program units; see Chapter 14, "Procedures and Packages," and Chapter 15, "Database Triggers," for more information about this feature.

Data Manipulation Statements (DML)

DML statements query or manipulate data in existing schema objects. They allow you to do the following:


SELECT ename, mgr, comm + sal FROM emp; 

Transaction Control Statements

Transaction control statements manage the changes made by DML statements and group DML statements into transactions. They allow you to do the following:

Data Definition Statements (DDL)

DDL statements define, alter the structure of, and drop schema objects. DDL statements allow you to do the following:

DDL statements implicitly commit the preceding and start a new transaction. Some examples of DDL statements follow:

DROP TABLE plants; 
GRANT SELECT ON emp TO scott; 

For specific information on DDL statements that correspond to database and data access, see Chapter 17, "Database Access", Chapter 18, "Privileges and Roles", and Chapter 19, "Auditing".

Session Control Statements

Session control commands manage the properties of a particular user's session. For example, they allow you to do the following:

System Control Statements

System control commands change the properties of the Oracle Server instance. The only system control command is ALTER SYSTEM. It allows you to change such settings as the minimum number of shared servers, to kill a session, and to perform other tasks.

Embedded SQL Statements

Embedded SQL statements incorporate DDL, DML, and transaction control statements within a procedural language program. They are used with the Oracle Precompilers. Embedded SQL statements allow you to do the following:

Identifying Non-Standard SQL

Oracle provides features beyond the standard SQL "Database Language with Integrity Enhancement". The Federal Information Processing Standard for SQL (FIPS 127-2) requires a method for identifying SQL statements that use vendor-supplied extensions. You can identify or "flag" Oracle extensions in interactive SQL, the Oracle Precompilers, or SQL*Module by using the FIPS flagger.

If you are concerned with the portability of your applications to other implementations of SQL, use the FIPS flagger. For information on how to use the FIPS flagger, see the Oracle7 Server SQL Reference, the Programmer's Guide to the Oracle Precompilers, or the SQL*Module User's Guide and Reference.

Recursive SQL



A cursor is a handle or name for an area in memory in which a parsed statement and other information for processing the statement are kept; such an area is also called a private SQL area. Although most Oracle users rely on the automatic cursor handling of the Oracle utilities, the programmatic interfaces offer application designers more control over cursors. In application development, a cursor is a named resource available to a program and can be used specifically for the parsing of SQL statements embedded within the application.

Shared SQL

Oracle automatically notices when applications send identical SQL statements to the database. If two identical statements are issued, the SQL area used to process the first instance of the statement is shared, or used for processing subsequent instances of that same statement.

Therefore, instead of having multiple shared SQL areas for identical SQL statements, only one shared SQL area exists for a unique statement. Since shared SQL areas are shared memory areas, any Oracle process can use a shared SQL area. The sharing of SQL areas reduces memory usage on the database server, thereby increasing system throughput.


For more information on shared SQL, see the Oracle7 Server Application Developer's Guide.

What Is Parsing?

Parsing is one step in the processing of a SQL statement. When an application issues a SQL statement, the application makes a parse call to Oracle. During the parse call, Oracle performs these tasks:

Oracle also determines whether there is an existing shared SQL area containing the parsed representation of the statement in the library cache. If so, the user process uses this parsed representation and executes the statement immediately. If not, Oracle parses the statement, performing these tasks:


Since both parse calls and parsing can be expensive relative to execution, it is desirable to perform them as seldom as possible.

This discussion applies also to the parsing of PL/SQL blocks and the allocation of PL/SQL areas. (See the description of PL/SQL in the next section.) Stored procedures, functions, and packages and triggers are assigned PL/SQL areas. Oracle also assigns each SQL statement within a PL/SQL block a shared and a private SQL area.




A stored procedure is a PL/SQL block that Oracle stores in the database and can be called by name from an application. When you create a stored procedure, Oracle parses the procedure and stores its parsed representation in the database. Oracle also allows you to create and store functions, which are similar to procedures, and packages, which are groups of procedures and functions. For information on stored procedures, functions, packages, and database triggers, see Chapter 14, "Procedures and Packages", and Chapter 15, "Database Triggers".

How PL/SQL Executes

The PL/SQL engine is a special component of many Oracle products, including the Oracle Server, that processes PL/SQL. Figure 11 - 1 illustrates the PL/SQL engine contained in Oracle Server.

Figure 11 - 1. The PL/SQL Engine and the Oracle Server

The procedure (or package) is stored in a database. When an application calls a procedure stored in the database, Oracle loads the compiled procedure (or package) into the shared pool in the System Global Area (SGA), and the PL/SQL and SQL statement executors work together to process the statements within the procedure.

The following Oracle products contain a PL/SQL engine:



Language Constructs for PL/SQL


The following sections give a general description of each construct; see the PL/SQL User's Guide and Reference for more information.


Note: Some interactive tools, such as Server Manager, allow you to define variables in your current session. Variables so declared can be used similarly to variables declared within procedures or packages.


Exceptions PL/SQL allows you to explicitly handle internal and user-defined error conditions, called exceptions, that arise during processing of PL/SQL code. Internal exceptions are caused by illegal operations, such as divide-by-zero, or Oracle errors returned to the PL/SQL code. User-defined exceptions are explicitly defined and signaled within the PL/SQL block to control processing of errors specific to the application (for example, debiting an account and leaving a negative balance).


While many Oracle products have PL/SQL components, this chapter specifically covers the procedures and packages that can be stored in an Oracle database and processed using the PL/SQL engine of Oracle Server. The PL/SQL capabilities of each Oracle tool are described in the appropriate tool user guide.

Oracle also allows you to create and call stored procedures. If your application calls a stored procedure, the parsed representation of the procedure is retrieved from the database and processed by the PL/SQL engine in Oracle. You can call stored procedures from applications developed using these tools:

You can also call a stored procedure from another PL/SQL block, either an anonymous block or another stored procedure. For information on how to call stored procedures from each type of application, see the manual for the specific application tool, such as the Programmer's Guide to the Oracle Precompilers.

Dynamic SQL in PL/SQL

You can write stored procedures and anonymous PL/SQL blocks using dynamic SQL. Dynamic SQL statements are not embedded in your source program; rather, they are stored in character strings that are input to, or built by, the program at runtime.


Additionally, you can parse any data manipulation language (DML) or data definition language (DDL) statement using the DBMS_SQL package. This helps solve the problem of not being able to parse data definition language statements directly using PL/SQL. For example, you might now choose to issue a DROP TABLE statement from within a stored procedure by using the PARSE procedure supplied with the DBMS_SQL package.

Go to previous file in sequence Go to next file in sequence
Prev Next
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index