Oracle7 Server SQL Reference

SELECT (Embedded SQL)

Purpose

To retrieve data from one or more tables, views, or snapshots, assigning the selected values to host variables.

Prerequisites

For you to select data from a table or snapshot, the table or snapshot must be in your own schema or you must have SELECT privilege on the table or snapshot.

For you to select rows from the base tables of a view, the owner of the schema containing the view must have SELECT privilege on the base tables. Also, if the view is in a schema other than your own, you must have SELECT privilege on the view.

The SELECT ANY TABLE system privilege also allows you to select data from any table or any snapshot or any view's base table.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of each queried table, view, or snapshot or you must have READUP system privileges.

Syntax

Keywords and Parameters

identifies the database to which the SELECT statement is issued. The database can be identified by either:

db_name is a database identifier declared in a previous DECLARE DATABASE statement.

:host_variable is a host variable whose value is a previously declared db_name.

If you omit this clause, the SELECT statement is issued to your default database.

select_list

identical to the non-embedded SELECT command except that host variables can be used in place of literals.

INTO

specifies output host variables and optional indicator variables to receive the data returned by the SELECT statement. Note that these variables must be either all scalars or all arrays, but arrays need not have the same size.

WHERE

restricts the rows returned to those for which the condition is TRUE. See the syntax description of condition . The condition can contain host variables, but cannot contain indicator variables. These host variables can be either scalars or arrays.

All other keywords and parameters are identical to the non-embedded SQL SELECT command.

Usage Notes

If no rows meet the WHERE clause condition, no rows are retrieved and Oracle7 returns an error code through the SQLCODE component of the SQLCA.

You can use comments in a SELECT statement to pass instructions, or hints, to the Oracle7 optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information on hints, see Oracle7 Server Tuning.

Example I

This example illustrates the use of the embedded SQL SELECT command:

EXEC SQL SELECT ename, sal + 100, job

	INTO :ename, :sal, :job

	FROM emp