Help topic not found.  Check the online help index for related topics.


About this version of PL/SQL

These help topics refer to PL/SQL V2.3 only, for use on the client.  If you require documentation for Release 8.0 of PL/SQL, refer to your Oracle8 server documentation.


Alphabetic list of language elements

Assignment statement

Blocks

Close statement

Commit statement

Comments

Constants and Variables

Cursors

Cursor attributes

Cursor variables

Delete statement

Exceptions

EXCEPTION_INIT pragma

Exit statement

Expressions

Fetch statement

Functions

Goto statement

If statement

Insert statement

Literals

Lock statement

Loop statement

Null statement

Open statement

Open For statement

Packages

PL/SQL tables

PL/SQL table attributes

Procedures

Raise statement

Records

Return statement

Rollback statement

%ROWTYPE

Savepoint

Select Into

Set Transaction

SQL cursor

SQLCODE function

SQLERRM function

%TYPE

Update statement


Attributes

Cursor

PL/SQL table

%ROWTYPE

%TYPE


Base elements

Blocks

Comments

Constants and Variables

Cursors

Cursor Variables

Exceptions

Expressions

Functions

Literals

Packages

PL/SQL tables

Procedures

Records

SQL cursor


Exceptions and error constructs

Exceptions

EXCEPTION_INIT pragma

SQLCODE function

SQLERRM function


Statements

Assignment

Close

Commit

Delete

Exit

Fetch

Goto

If

Insert

Lock

Loop

Null

Open

Open For

Raise

Return

Rollback

Savepoint

Select Into

Set Transaction

Update


No language elements begin with this letter.


No base constructs begin with this letter.


No statements begin with this letter.


Blocks

  Examples	   Related Topics

Description	The basic program unit in PL/SQL is the block. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords partition the PL/SQL block into a declarative part, an executable part, and an exception-handling part. Only the executable part is required.

You can nest a block within another block wherever you can place an executable statement.

Syntax

plsql_block ::=
[<<label_name>>]
[DECLARE
   object_declaration [object_declaration] ...
   [subprogram_declaration [subprogram_declaration] ...]]
BEGIN
   seq_of_statements
[EXCEPTION
   exception_handler [exception_handler] ...]
END [label_name];

object_declaration ::=
{  constant_declaration
 | cursor_declaration
 | cursor_variable_declaration
 | exception_declaration
 | plsql_table_declaration
 | record_declaration
 | variable_declaration}

subprogram_declaration ::=
{function_declaration | procedure_declaration}

Parameters

label_name	This is an undeclared identifier that optionally labels a PL/SQL block. If used, label_name must be enclosed by double angle brackets and must appear at the beginning of the block. Optionally, label_name can also appear at the end of the block.  A global identifier declared in an enclosing block can be redeclared in a sub-block, in which case the local declaration prevails and the sub-block cannot reference the global identifier. To reference the global identifier, you must use a block label to qualify the reference, as the following example shows: <<outer>> DECLARE    x INTEGER; BEGIN    ...    DECLARE       x INTEGER;    BEGIN       ...       IF x = outer.x THEN  -- refers to global x          ...       END IF;    END; END outer;
DECLARE	This keyword signals the start of the declarative part of a PL/SQL block, which contains local declarations. Objects declared locally exist only within the current block and all its sub-blocks and are not visible to enclosing blocks. The declarative part of a PL/SQL block is optional. It is terminated implicitly by the keyword BEGIN, which introduces the executable part of the block.  PL/SQL does not allow forward references. So, you must declare an object before referencing it in other statements, including other declarative statements. Also, you must declare subprograms at the end of a declarative section after all other program objects.
constant_declaration	This construct declares a constant.
cursor_declaration	This construct declares an explicit cursor.
cursor_variable_ declaration	This construct declares a cursor variable.
exception_declaration	This construct declares an exception.
plsql_table_declaration	This construct declares a PL/SQL table.
record_declaration	This construct declares a user-defined record.
variable_declaration	This construct declares a variable.
function_declaration	This construct declares a function.
procedure_declaration	This construct declares a procedure.
BEGIN	This keyword signals the start of the executable part of a PL/SQL block, which contains executable statements. The executable part of a PL/SQL block is required. That is, a block must contain at least one executable statement. The NULL statement meets this requirement.
seq_of_statements	This represents a sequence of executable (not declarative) statements, which can include SQL statements and PL/SQL blocks (sometimes called block statements). The syntax of seq_of_statements follows:  seq_of_statements ::=  statement [statement] ... Statements are used to create algorithms. Besides SQL statements, PL/SQL has flow-of-control and error-handling statements. PL/SQL statements are free format. That is, they can continue from line to line, providing you do not split keywords, delimiters, or literals across lines. A semicolon (;) must terminate every PL/SQL statement. The syntax of statement follows:  statement ::=  [<<label_name>>]  {  assignment_statement  | exit_statement  | goto_statement  | if_statement  | loop_statement  | null_statement  | plsql_block  | raise_statement  | return_statement  | sql_statement}  PL/SQL supports a subset of SQL statements that includes data manipulation, cursor control, and transaction control statements but excludes data definition and data control statements such as ALTER, CREATE, GRANT, and REVOKE. The syntax of sql_statement follows:  sql_statement ::= {  close_statement  | commit_statement  | delete_statement  | fetch_statement  | insert_statement  | lock_table_statement  | open_statement  | open-for_statement  | rollback_statement  | savepoint_statement  | select_statement  | set_transaction_statement  | update_statement}
EXCEPTION	This keyword signals the start of the exception-handling part of a PL/SQL block. When an exception is raised, normal execution of the block stops and control transfers to the appropriate exception handler. After the exception handler completes, execution proceeds with the statement following the block.  If there is no exception handler for the raised exception in the current block, control passes to the enclosing block. This process repeats until an exception handler is found or there are no more enclosing blocks. If PL/SQL can find no exception handler for the exception, execution stops and an unhandled exception error is returned to the host environment.
exception_handler	This construct associates an exception with a sequence of statements, which is executed when that exception is raised.
END	This keyword signals the end of a PL/SQL block. It must be the last keyword in a block. Neither the END IF in an IF statement nor the END LOOP in a LOOP statement can substitute for the keyword END.  END does not signal the end of a transaction. Just as a block can span multiple transactions, a transaction can span multiple blocks.


Blocks examples

The following PL/SQL block declares several variables and constants, then calculates a ratio using values selected from a database table:

DECLARE
  numerator    NUMBER;
  denominator  NUMBER;
  the_ratio    NUMBER;
  lower_limit  CONSTANT NUMBER := 0.72;
  samp_num     CONSTANT NUMBER := 132;
BEGIN
  SELECT x, y INTO numerator, denominator FROM result_table
     WHERE sample_id = samp_num;
  the_ratio := numerator/denominator;
  IF the_ratio > lower_limit THEN
     INSERT INTO ratio VALUES (samp_num, the_ratio);
  ELSE
     INSERT INTO ratio VALUES (samp_num, -1);
  END IF;
  COMMIT;
EXCEPTION
  WHEN ZERO_DIVIDE THEN
     INSERT INTO ratio VALUES (samp_num, 0);
     COMMIT;
  WHEN OTHER THEN
     ROLLBACK;
END;


Comments

  Examples	  Related Topics

Description	Comments describe the purpose and use of code segments and so promote readability. PL/SQL supports two comment styles: single-line and multi-line. Single-line comments begin with a double hyphen (- -) anywhere on a line and extend to the end of the line. Multi-line comments begin with a slash-asterisk (/*), end with an asterisk-slash (*/), and can span multiple lines.

Syntax

comment ::=
{-- text | /* text */}

Comments	Comments can appear within a statement at the end of a line. However, you cannot nest comments.

You cannot use single-line comments in a PL/SQL block that will be processed dynamically by an Oracle Precompiler program because end-of-line characters are ignored. As a result, single-line comments extend to the end of the block, not just to the end of a line. Instead, use multi-line comments.

While testing or debugging a program, you might want to disable a line of code. The following example shows how you can "comment-out" the line:

-- UPDATE dept SET loc = my_loc WHERE deptno = my_deptno;

You can use multi-line comment delimiters to comment-out whole sections of code.


Comments examples

The following examples show various comment styles:

-- compute the area of a circle
area := pi * radius**2;  -- pi equals 3.14159

/* Compute the area of a circle. */
area := pi * radius**2;  /* pi equals 3.14159 */

/*
  The following line computes the area of a circle using pi,
  which is the ratio between the circumference and diameter.
  Pi is an irrational number, meaning that it cannot be
  expressed as the ratio between two integers.
*/
area := pi * radius**2;


Constants and variables

  Examples 	   Related Topics

Description	You can declare constants and variables in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage space for a value, specify its datatype, and name the storage location so that you can reference it. Declarations can also assign an initial value and impose the NOT NULL constraint.

Syntax

constant_declaration ::=
constant_name CONSTANT
   {  record_name.field_name%TYPE
    | scalar_type_name
    | table_name.column_name%TYPE
    | variable_name%TYPE} [NOT NULL] {:= | DEFAULT} expression;

variable_declaration ::=
variable_name
   {  cursor_name%ROWTYPE
    | cursor_variable_name%TYPE
    | plsql_table_name%TYPE
    | record_name%TYPE
    | record_name.field_name%TYPE
    | scalar_type_name
    | table_name%ROWTYPE
    | table_name.column_name%TYPE
    | variable_name%TYPE} [[NOT NULL] {:= | DEFAULT} expression];

Parameters

constant_name	This identifies a program constant.
CONSTANT	This keyword denotes the declaration of a constant. You must initialize a constant in its declaration. Once initialized, the value of a constant cannot be changed.
record_name.field_name	This identifies a field in a user-defined or %ROWTYPE record previously declared within the current scope.
scalar_type_name	This identifies a predefined scalar datatype such as BOOLEAN, NUMBER, or VARCHAR2.
table_name.column_name	This identifies a database table and column that must be accessible when the declaration is elaborated.
variable_name	This identifies a program variable.
cursor_name	This identifies an explicit cursor previously declared within the current scope.
cursor_variable_name	This identifies a PL/SQL cursor variable previously declared within the current scope.
plsql_table_name	This identifies a PL/SQL table previously declared within the current scope.
record_name	This identifies a user-defined record previously declared within the current scope.
table_name	This identifies a database table (or view) that must be accessible when the declaration is elaborated.
%ROWTYPE	This attribute provides a record type that represents a row in a database table or a row fetched from a previously declared cursor. Fields in the record and corresponding columns in the row have the same names and datatypes.
%TYPE	This attribute provides the datatype of a previously declared field, record, PL/SQL table, database column, or variable.
NOT NULL	This constraint prevents the assigning of nulls to a variable or constant. At run time, trying to assign a null to a variable defined as NOT NULL raises the predefined exception VALUE_ERROR. The constraint NOT NULL must be followed by an initialization clause.
expression	This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the declaration is elaborated, the value of expression is assigned to the constant or variable. The value and the constant or variable must have compatible datatypes.
Comments	Constants and variables are initialized every time a block or subprogram is entered. By default, variables are initialized to NULL. So, unless you expressly initialize a variable, its value is undefined.

Whether public or private, constants and variables declared in a package specification are initialized only once per session.

An initialization clause is required when declaring NOT NULL variables and when declaring constants.

You cannot use the attribute %ROWTYPE to declare a constant. If you use %ROWTYPE to declare a variable, initialization is not allowed.


Constants and variables examples

Several examples of variable and constant declarations follow:

credit_limit CONSTANT NUMBER := 5000;
invalid      BOOLEAN := FALSE;
acct_id      INTEGER(4) NOT NULL DEFAULT 9999;
pi           CONSTANT REAL := 3.14159;
last_name    VARCHAR2(20);
my_ename     emp.ename%TYPE;


Cursors

  Examples	  Related Topics

Description	To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. A cursor lets you name the work area, access the information, and process the rows individually.

Syntax

cursor_declaration ::=
CURSOR cursor_name [(cursor_parameter_declaration[,
  cursor_parameter_declaration]...)] IS select_statement;

cursor_specification ::=
CURSOR cursor_name [(cursor_parameter_declaration[,
  cursor_parameter_declaration]...)]
    RETURN {  cursor_name%ROWTYPE
            | record_name%TYPE
            | record_type_name
            | table_name%ROWTYPE};

cursor_body ::=
CURSOR cursor_name [(cursor_parameter_declaration[,
  cursor_parameter_declaration]...)]
    RETURN {  cursor_name%ROWTYPE
            | record_name%TYPE
            | record_type_name
            | table_name%ROWTYPE} IS select_statement;

cursor_parameter_declaration ::=
cursor_parameter_name [IN]
   {  cursor_name%ROWTYPE
    | cursor_variable_name%TYPE
    | plsql_table_name%TYPE
    | record_name%TYPE
    | scalar_type_name
    | table_name%ROWTYPE
    | table_name.column_name%TYPE
    | variable_name%TYPE} [{:= | DEFAULT} expression]

Parameters

cursor_parameter_name	This identifies a cursor parameter; that is, a variable declared as the formal parameter of a cursor. A cursor parameter can appear in a query wherever a constant can appear. The formal parameters of a cursor must be IN parameters. The query can also reference other PL/SQL variables within its scope.
select_statement	This is a query that returns a result set of rows. If the cursor declaration declares parameters, each parameter must be used in the query. The syntax of select_statement is like that of select_into_statement except that select_statement cannot have an INTO clause.
RETURN	This keyword introduces the RETURN clause, which specifies the datatype of a cursor result value. You can use the %ROWTYPE attribute in the RETURN clause to provide a record type that represents a row in a database table or a row returned by a previously declared cursor. Also, you can use the %TYPE attribute to provide the datatype of a previously declared record. A cursor body must have a SELECT statement and the same RETURN clause as its corresponding cursor specification. Also, the number, order, and datatypes of select items in the SELECT clause must match the RETURN clause.
cursor_name	This identifies an explicit cursor previously declared within the current scope.
record_name	This identifies a user-defined record previously declared within the current scope.
record_type_name	This identifies a RECORD type previously defined within the current scope.
table_name	This identifies a database table (or view) that must be accessible when the declaration is elaborated.
%ROWTYPE	This attribute provides a record type that represents a row in a database table or a row fetched from a previously declared cursor. Fields in the record and corresponding columns in the row have the same names and datatypes.
%TYPE	This attribute provides the datatype of a previously declared field, record, PL/SQL table, database column, or variable.
cursor_variable_name	This identifies a PL/SQL cursor variable previously declared within the current scope.
scalar_type_name	This identifies a predefined scalar datatype such as BOOLEAN, NUMBER, or VARCHAR2, which must be specified without constraints.
plsql_table_name	This identifies a PL/SQL table previously declared within the current scope.
variable_name	This identifies a PL/SQL variable previously declared within the current scope.
expression	This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the declaration is elaborated, the value of expression is assigned to the parameter. The value and the parameter must have compatible datatypes.
Comments	You must declare a cursor before referencing it in an OPEN, FETCH, or CLOSE statement. And, you must declare a variable before referencing it in a cursor declaration. The word SQL is reserved by PL/SQL for use as the default name for implicit cursors and cannot be used in a cursor declaration.

You cannot assign values to a cursor name or use it in an expression. However, cursors and variables follow the same scoping rules.

You retrieve data from a cursor by opening it, then fetching from it. Because the FETCH statement specifies the target variables, using an INTO clause in the SELECT statement of a cursor_declaration is redundant and invalid.

The scope of cursor parameters is local to the cursor, meaning that they can be referenced only within the query used in the cursor declaration. The values of cursor parameters are used by the associated query when the cursor is opened. The query can also reference other PL/SQL variables within its scope.

The datatype of a cursor parameter must be specified without constraints. For example, the following parameter declarations are illegal:

CURSOR c1 (emp_id NUMBER NOT NULL, dept_no NUMBER(2))  -- illegal


Cursor examples

Two examples of cursor declarations follow:

CURSOR c1 IS
   SELECT ename, job, sal FROM emp WHERE deptno = 20;
CURSOR c2 (start_date DATE) IS
   SELECT empno, sal FROM emp WHERE hiredate > start_date;


Cursor variables

  Examples	  Related Topics

Description	To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. To access the information, you can use an explicit cursor, which names the work area. Or, you can use a cursor variable, which points to the work area. Whereas a cursor always refers to the same query work area, a cursor variable can refer to different work areas. Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some object instead of the object itself. So, declaring a cursor variable creates a pointer, not an object.

To create cursor variables, you take two steps. First, you define a REF CURSOR type, then declare cursor variables of that type.

Syntax

ref_type_definition ::=
TYPE ref_type_name IS REF CURSOR
   RETURN {  cursor_name%ROWTYPE
           | cursor_variable_name%ROWTYPE
           | record_name%TYPE
           | record_type_name
           | table_name%ROWTYPE};

cursor_variable_declaration ::=
cursor_variable_name ref_type_name;

Parameters

ref_type_name	This is a user-defined type specifier, which is used in subsequent declarations of PL/SQL cursor variables.
REF CURSOR	In PL/SQL, pointers have datatype REF X, where REF is short for REFERENCE and X stands for a class of objects. Therefore, cursor variables have datatype REF CURSOR. Currently, cursor variables are the only REF variables that you can declare.
RETURN	This keyword introduces the RETURN clause, which specifies the datatype of a cursor variable result value. You can use the %ROWTYPE attribute in the RETURN clause to provide a record type that represents a row in a database table or a row returned by a previously declared cursor or cursor variable. Also, you can use the %TYPE attribute to provide the datatype of a previously declared record.
cursor_name	This identifies an explicit cursor previously declared within the current scope.
cursor_variable_name	This identifies a PL/SQL cursor variable previously declared within the current scope.
Record_name	This identifies a user-defined record previously declared within the current scope.
Record_type_name	This identifies a RECORD type previously defined within the current scope.
table_name	This identifies a database table (or view) that must be accessible when the declaration is elaborated.
%ROWTYPE	This attribute provides a record type that represents a row in a database table or a row fetched from a previously declared cursor or cursor variable. Fields in the record and corresponding columns in the row have the same names and datatypes.
%TYPE	This attribute provides the datatype of a previously declared user-defined record.
Comments	Cursor variables are available to every PL/SQL client. For example, you can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program, then pass it as a bind variable to PL/SQL. Moreover, application development tools such as Oracle Forms and Oracle Reports, which have a PL/SQL engine, can use cursor variables entirely on the client side.

The Oracle Server also has a PL/SQL engine. So, you can pass cursor variables back and forth between an application and server via remote procedure calls (RPCs). And, if you have a PL/SQL engine on the client side, calls from client to server impose no restrictions. For example, you can declare a cursor variable on the client side, open and fetch from it on the server side, then continue to fetch from it back on the client side.

Mainly, you use cursor variables to pass query result sets between PL/SQL stored subprograms and various clients. Neither PL/SQL nor any of its clients owns a result set; they simply share a pointer to the query work area in which the result set is stored. For example, an OCI client, Oracle Forms application, and Oracle Server can all refer to the same work area.

REF CURSOR types can be strong (restrictive) or weak (nonrestrictive). A strong REF CURSOR type definition specifies a return type, but a weak definition does not. Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with type-compatible queries. However, weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query.

Once you define a REF CURSOR type, you can declare cursor variables of that type. They follow the usual scoping and instantiation rules. Local PL/SQL cursor variables are instantiated when you enter a block or subprogram and cease to exist when you exit.

You use three statements to control a cursor variable: OPEN-FOR, FETCH, and CLOSE. First, you OPEN a cursor variable FOR a multi-row query. Then, you FETCH rows from the result set one at a time. When all the rows are processed, you CLOSE the cursor variable.

Other OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. When you reopen a cursor variable for a different query, the previous query is lost.

PL/SQL makes sure the return type of the cursor variable is compatible with the INTO clause of the FETCH statement. For each column value returned by the query associated with the cursor variable, there must be a corresponding, type-compatible field or variable in the INTO clause. Also, the number of fields or variables must equal the number of column values. Otherwise, you get an error.

If both cursor variables involved in an assignment are strongly typed, they must have the same datatype. However, if one or both cursor variables are weakly typed, they need not have the same datatype.

When declaring a cursor variable as the formal parameter of a subprogram that fetches from or closes the cursor variable, you must specify the IN (or IN OUT) mode. If the subprogram opens the cursor variable, you must specify the IN OUT mode.

Be careful when passing cursor variables as parameters. At run time, PL/SQL raises ROWTYPE_MISMATCH if the return types of the actual and formal parameters are incompatible.

You can apply the cursor attributes %FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT to a cursor variable.

If you try to fetch from, close, or apply cursor attributes to a cursor variable that does not point to a query work area, PL/SQL raises the predefined exception INVALID_CURSOR. You can make a cursor variable (or parameter) point to a query work area in two ways:

•	the cursor variable FOR the query.

•	to the cursor variable the value of an already OPENed host cursor variable or PL/SQL cursor variable.

A query work area remains accessible as long as any cursor variable points to it. Therefore, you can pass the value of a cursor variable freely from one scope to another. For example, if you pass a host cursor variable to a PL/SQL block embedded in a Pro*C program, the work area to which the cursor variable points remains accessible after the block completes.

Currently, cursor variables are subject to the following restrictions, some of which future releases of PL/SQL will remove:

•	cannot declare cursor variables in a package because they do not have persistent state.

•	subprograms on another server cannot accept the values of cursor variables. Therefore, you cannot use RPCs to pass cursor variables from one server to another

•	subprograms on another server cannot accept the values of cursor variables. Therefore, you cannot use RPCs to pass cursor variables from one server to another

•	you pass a host cursor variable (bind variable) to PL/SQL, you cannot fetch from it on the server side unless you also open it there on the same server call

•	query associated with a cursor variable in an OPEN-FOR statement cannot be FOR UPDATE

•	cannot use comparison operators to test cursor variables for equality, inequality, or nullity

•	cannot assign nulls to a cursor variable

•	cannot use REF CURSOR types to specify column types in a CREATE TABLE or CREATE VIEW statement. So, database columns cannot store the values of cursor variables and cursor variables are not interoperable; that is, you cannot use one where the other is expected.

•	cannot use a REF CURSOR type to specify the element type of a PL/SQL table, which means that elements in a PL/SQL table cannot store the values of cursor variables

•	cannot use cursor variables with dynamic SQL


Cursor variable examples

You can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program. To use the host cursor variable, you must pass it as a bind variable to PL/SQL. In the following Pro*C example, you pass a host cursor variable and selector to a PL/SQL block, which opens the cursor variable for the chosen query:

EXEC SQL BEGIN DECLARE SECTION;
   ...
   /* Declare host cursor variable. */
   SQL_CURSOR generic_cv;
   int        choice;
EXEC SQL END DECLARE SECTION;
...
/* Initialize host cursor variable. */
EXEC SQL ALLOCATE :generic_cv;
...

/* Pass host cursor variable and selector to PL/SQL block. */
EXEC SQL EXECUTE
BEGIN
   IF :choice = 1 THEN
      OPEN :generic_cv FOR SELECT * FROM emp;
   ELSIF :choice = 2 THEN
      OPEN :generic_cv FOR SELECT * FROM dept;
   ELSIF :choice = 3 THEN
      OPEN :generic_cv FOR SELECT * FROM salgrade;
   END IF;
END;
END-EXEC;

Host cursor variables are compatible with any query return type. They behave just like weakly typed PL/SQL cursor variables.

When passing host cursor variables to PL/SQL, you can reduce network traffic by grouping OPEN-FOR statements. For example, the following PL/SQL block opens three cursor variables in a single round-trip:

/* anonymous PL/SQL block in host environment */
BEGIN
   OPEN :emp_cv FOR SELECT * FROM emp;
   OPEN :dept_cv FOR SELECT * FROM dept;
   OPEN :grade_cv FOR SELECT * FROM salgrade;
END;

You can also pass a cursor variable to PL/SQL by calling a stored procedure that declares a cursor variable as one of its formal parameters. To centralize data retrieval, you can group type-compatible queries in a packaged procedure, as the following example shows:

CREATE PACKAGE emp_data AS
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
   PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp,
                          choice IN NUMBER);
END emp_data;

CREATE PACKAGE BODY emp_data AS
   PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp,
                          choice IN NUMBER) IS
   BEGIN
      IF choice = 1 THEN
         OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL;
      ELSIF choice = 2 THEN
         OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500;
      ELSIF choice = 3 THEN
         OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20;
      END IF;
   END open_emp_cv;
END emp_data;

Alternatively, you can use a standalone procedure to open the cursor variable. Simply define the REF CURSOR type in a separate package, then reference that type in the standalone procedure. For instance, if you create the following (bodiless) package, you can create standalone procedures that reference the types it defines:

CREATE PACKAGE cv_types AS
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
   TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
   TYPE BonusCurTyp IS REF CURSOR RETURN bonus%ROWTYPE;
   ...
END cv_types;


Exceptions

  Examples	   Related Topics

Description	An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements. To handle raised exceptions, you write separate routines called exception handlers.

Syntax

exception_declaration ::=
exception_name EXCEPTION;

exception_handler ::=
WHEN {exception_name [OR exception_name] ... | OTHERS}
   THEN seq_of_statements

Parameters

WHEN	This keyword introduces an exception handler. You can have multiple exceptions execute the same sequence of statements by following the keyword WHEN with a list of the exceptions, separating them by the keyword OR. If any exception in the list is raised, the associated statements are executed.  Each WHEN clause can associate a different sequence of statements with a list of exceptions. However, an exception name can appear only once in the exception-handling part of a PL/SQL block or subprogram.
exception_name	This identifies a predefined exception such as ZERO_DIVIDE, or a user-defined exception previously declared within the current scope.
OTHERS	This keyword stands for all the exceptions not explicitly named in the exception-handling part of the block. The use of OTHERS is optional and is allowed only as the last exception handler. You cannot include OTHERS in a list of exceptions following the keyword WHEN.
seq_of_statements	This construct represents a sequence of statements.
Comments	An exception declaration can appear only in the declarative part of a block, subprogram, or package. The scope rules for exceptions and variables are the same. But, unlike variables, exceptions cannot be passed as parameters to subprograms.

Some exceptions are predefined by PL/SQL. PL/SQL declares predefined exceptions globally in package STANDARD, so you need not declare them yourself.

Redeclaring predefined exceptions is error prone because your local declaration overrides the global declaration. In such cases, you must use dot notation to specify the predefined exception, as follows:

EXCEPTION
   WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN ...

The exception-handling part of a PL/SQL block is optional. Exception handlers must come at the end of the block. They are introduced by the keyword EXCEPTION. The exception-handling part of the block is terminated by the same keyword END that terminates the entire block.

An exception should be raised only when an error occurs that makes it impossible or undesirable to continue processing. If there is no exception handler in the current block for a raised exception, the exception propagates according to the following rules:

•	there is an enclosing block for the current block, the exception is passed on to that block. The enclosing block then becomes the current block. If a handler for the raised exception is not found, the process repeats.

•	there is no enclosing block for the current block, an unhandled exception error is passed back to the host environment.

However, exceptions cannot propagate across remote procedure calls (RPCs). Therefore, a PL/SQL block cannot catch an exception raised by a remote subprogram. For a workaround, see "Using raise_application_ error".

Only one exception at a time can be active in the exception-handling part of a block. Therefore, if an exception is raised inside a handler, the block that encloses the current block is the first block searched to find a handler for the newly raised exception. From there on, the exception propagates normally.

An exception handler can reference only those variables that the current block can reference.


Exception examples

The following PL/SQL block has two exception handlers:

DELARE
   bad_emp_id  EXCEPTION;
   bad_acct_no EXCEPTION;
   ...
BEGIN
   ...
EXCEPTION
   WHEN bad_emp_id OR bad_acct_no THEN  -- user-defined
      ROLLBACK;
   WHEN ZERO_DIVIDE THEN  -- predefined
      INSERT INTO inventory VALUES (part_number, quantity);
      COMMIT;
END;


Expressions

  Examples	  Related Topics

Description	An expression is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable.

The PL/SQL compiler determines the datatype of an expression from the types of the variables, constants, literals, and operators that comprise the expression. Every time the expression is evaluated, a single value of that type results.

Syntax

expression ::=
[(]{  boolean_expression
    | character_expression
    | date_expression
    | numeric_expression}[)]

boolean_expression ::=
[NOT] {  boolean_constant_name
       | boolean_function_call
       | boolean_literal
       | boolean_variable_name
       | other_boolean_form}
[{AND | OR} [NOT] {  boolean_constant_name
                   | boolean_function_call
                   | boolean_literal
                   | boolean_variable_name
                   | other_boolean_form}] ...

other_boolean_form ::=
expression
   {  relational_operator expression
    | IS [NOT] NULL
    | [NOT] LIKE pattern
    | [NOT] BETWEEN expression AND expression
    | [NOT] IN (expression[, expression]...)
    | {  cursor_name
       | cursor_variable_name
       | :host_cursor_variable_name
       | SQL}{%FOUND | %ISOPEN | %NOTFOUND}
    | plsql_table_name.EXISTS(index)}

numeric_expression ::=
{  {  cursor_name
    | cursor_variable_name
    | :host_cursor_variable_name
    | SQL}%ROWCOUNT
 | :host_variable_name[:indicator_name]
 | numeric_constant_name
 | numeric_function_call
 | numeric_literal
 | numeric_variable_name
 | plsql_table_name{  .COUNT
                    | .FIRST
                    | .LAST
                    | .NEXT(index)
                    | .PRIOR(index)}}[**exponent]
[ {+ | - | * | /}
 {  {  cursor_name
     | cursor_variable_name
     | :host_cursor_variable_name
     | SQL}%ROWCOUNT
  | :host_variable_name[:indicator_name]
  | numeric_constant_name
  | numeric_function_call
  | numeric_literal
  | numeric_variable_name
  | plsql_table_name{  .COUNT
                     | .FIRST
                     | .LAST
                     | .NEXT(index)
                     | .PRIOR(index)}}[**exponent]]...

character_expression ::=
{  character_constant_name
 | character_function_call
 | character_literal
 | character_variable_name
 | :host_variable_name[:indicator_name]}
[ || {  character_constant_name
     | character_function_call
     | character_literal
     | character_variable_name
     | :host_variable_name[:indicator_name]}]...

date_expression ::=
{  date_constant_name
 | date_function_call
 | date_literal
 | date_variable_name
 | :host_variable_name[:indicator_name]}
[{+ | -} numeric_expression]...

Parameters

boolean_expression	This is an expression that yields the Boolean value TRUE, FALSE, or NULL.
Character_expression	This is an expression that yields a character or character string.
Date_expression	This is an expression that yields a date/time value.
Numeric_expression	This is an expression that yields an integer or real value.
NOT, AND, OR	These are logical operators. AND returns the value TRUE only if both its operands are true. OR returns the value TRUE if either of its operands is true. NOT returns the opposite value (logical negation) of its operand. NOT NULL returns NULL because nulls are indeterminate.
Boolean_constant_name	This identifies a constant of type BOOLEAN, which must be initialized to the value TRUE or FALSE or the non-value NULL. Arithmetic operations on Boolean constants are illegal.
Boolean_function_call	This is any function call that returns a Boolean value.
Boolean_literal	This is the predefined value TRUE or FALSE or the non-value NULL, which stands for a missing, unknown, or inapplicable value. You cannot insert the value TRUE or FALSE into a database column.
Boolean_variable_name	This identifies a variable of type BOOLEAN. Only the values TRUE and FALSE and the non-value NULL can be assigned to a BOOLEAN variable. You cannot select or fetch column values into a BOOLEAN variable. Also, arithmetic operations on Boolean variables are illegal.
Relational_operator	This operator allows you to compare expressions.
IS [NOT] NULL	This comparison operator returns the Boolean value TRUE if its operand is null, or FALSE if its operand is not null.
[NOT] LIKE	This comparison operator compares a character value to a pattern. Case is significant. LIKE returns the Boolean value TRUE if the character patterns match, or FALSE if they do not match.
Pattern	This is a character string compared by the LIKE operator to a specified string value. It can include two special-purpose characters called wildcards. An underscore (_) matches exactly one character; a percent sign (%) matches zero or more characters.
[NOT] BETWEEN	This comparison operator tests whether a value lies in a specified range. It means "greater than or equal to low value and less than or equal to high value."
[NOT] IN	This comparison operator tests set membership. It means "equal to any member of." The set can contain nulls, but they are ignored. Also, expressions of the form  dvalue NOT IN set yield FALSE if the set contains a null.
Cursor_name	This identifies an explicit cursor previously declared within the current scope.
Cursor_variable_name	This identifies a PL/SQL cursor variable previously declared within the current scope.
Host_cursor_variable_ name	This identifies a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. Host cursor variables must be prefixed with a colon.
SQL	This identifies a cursor opened implicitly by Oracle to process a SQL data manipulation statement. The implicit SQL cursor always refers to the most recently executed SQL statement.
%FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNT	These are cursor attributes. When appended to the name of a cursor or cursor variable, these attributes return useful information about the execution of a multi-row query. You can also append them to the implicit SQL cursor.
Plsql_table_name	This identifies a PL/SQL table previously declared within the current scope.
EXISTS, COUNT, FIRST, LAST, NEXT, PRIOR	These are PL/SQL table attributes. When appended to the name of a PL/SQL table, these attributes return useful information. For example, EXISTS(n) returns TRUE if the nth element of a PL/SQL table exists. Otherwise, EXISTS(n) returns FALSE.
Index	This is a numeric expression that must yield a value of type BINARY_INTEGER or a value implicitly convertible to that datatype.
Host_variable_name	This identifies a variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host variable must be implicitly convertible to the appropriate PL/SQL datatype. Also, host variables must be prefixed with a colon.
Indicator_name	This identifies an indicator variable declared in a PL/SQL host environment and passed to PL/SQL. Indicator variables must be prefixed with a colon. An indicator variable "indicates" the value or condition of its associated host variable. For example, in the Oracle Precompiler environment, indicator variables can detect nulls or truncated values in output host variables.
Numeric_constant_name	This identifies a previously declared constant that stores a numeric value. It must be initialized to a numeric value or a value implicitly convertible to a numeric value.
Numeric_function_call	This is a function call that returns a numeric value or a value implicitly convertible to a numeric value.
Numeric_literal	This is a literal that represents a numeric value or a value implicitly convertible to a numeric value.
Numeric_variable_name	This identifies a previously declared variable that stores a numeric value.
NULL	This keyword represents a null; it stands for a missing, unknown, or inapplicable value. When NULL is used in a numeric or date expression, the result is always a null.
Exponent	This is an expression that must yield a numeric value.
+, -, /, *, **	These symbols are the addition, subtraction, division, multiplication, and exponentiation operators, respectively.
Character_constant_name	This identifies a previously declared constant that stores a character value. It must be initialized to a character value or a value implicitly convertible to a character value.
Character_function_call	This is a function call that returns a character value or a value implicitly convertible to a character value.
Character_literal	This is a literal that represents a character value or a value implicitly convertible to a character value.
Character_variable_name	This identifies a previously declared variable that stores a character value.
||	This is the concatenation operator. As the following example shows, the result of concatenating string1 with string2 is a character string that contains string1 followed by string2: d'Good' || ' morning!'  yields  'Good morning!' The next example shows that nulls have no effect on the result of a concatenation: 'suit' || NULL || 'case'    'suitcase' A string zero characters in length ('') is called a null string and is treated like a null.
Date_constant_name	This identifies a previously declared constant that stores a date value. It must be initialized to a date value or a value implicitly convertible to a date value.
Date_function_call	This is a function call that returns a date value or a value implicitly convertible to a date value.
Date_literal	This is a literal that represents a date value or a value implicitly convertible to a date value.
Date_variable_name	This identifies a previously declared variable that stores a date value.
Comments	In a Boolean expression, you can only compare values that have compatible datatypes.

In conditional control statements, if a Boolean expression yields TRUE, its associated sequence of statements is executed. But, if the expression yields FALSE or NULL, its associated sequence of statements is not executed.

When PL/SQL evaluates a boolean expression, NOT has the highest precedence, AND has the next-highest precedence, and OR has the lowest precedence. However, you can use parentheses to override the default operator precedence.

The relational operators can be applied to operands of type BOOLEAN. By definition, TRUE is greater than FALSE. Comparisons involving nulls always yield a null.

The value of a Boolean expression can be assigned only to Boolean variables, not to host variables or database columns. Also, datatype conversion to or from type BOOLEAN is not supported.

You can use the addition and subtraction operators to increment or decrement a date value, as the following examples show:

hire_date := '10-MAY-95';
hire_date := hire_date + 1;  -- makes hire_date '11-MAY-95'
hire_date := hire_date - 5;  -- makes hire_date '06-MAY-95'

Within an expression, operations occur in their predefined order of precedence. From first to last (top to bottom), the default order of operations is

parenthesesexponentsunary operatorsmultiplication and divisionaddition, subtraction, and concatenation

PL/SQL evaluates operators of equal precedence in no particular order. When parentheses enclose an expression that is part of a larger expression, PL/SQL evaluates the parenthesized expression first, then uses the result value in the larger expression. When parenthesized expressions are nested, PL/SQL evaluates the innermost expression first and the outermost expression last.


Expression examples

Several examples of expressions follow:

(a + b) > c             -- Boolean expression
NOT finished            -- Boolean expression
TO_CHAR(acct_no)        -- character expression
'Fat ' || 'cats'        -- character expression
'15-NOV-95'             -- date expression
MONTHS_BETWEEN(d1, d2)  -- date expression
pi * r**2               -- numeric expression
emp_cv%ROWCOUNT         -- numeric expression


Functions

  Examples	   Related Topics

Description	A function is a named program unit that takes parameters and returns a computed value.

A function has two parts: the specification and the body. The function specification begins with the keyword FUNCTION and ends with the RETURN clause, which specifies the datatype of the result value. Parameter declarations are optional. Functions that take no parameters are written without parentheses.

The function body begins with the keyword IS and ends with the keyword END followed by an optional function name. The function body has three parts: an optional declarative part, an executable part, and an optional exception-handling part.

The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These objects are local and cease to exist when you exit the function. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains exception handlers, which deal with exceptions raised during execution.

Syntax

function_specification ::=
FUNCTION function_name [(parameter_declaration[,
  parameter_declaration]...)]
RETURN return_type;

function_body ::=
FUNCTION function_name [(parameter_declaration[,
  parameter_declaration]...)]
RETURN return_type IS
  [[object_declaration [object_declaration] ...]
   [subprogram_declaration [subprogram_declaration] ...]]
BEGIN
    seq_of_statements
[EXCEPTION
    exception_handler [exception_handler] ...]
END [function_name];

parameter_declaration ::=
parameter_name [IN | OUT | IN OUT]
   {  cursor_name%ROWTYPE
    | cursor_variable_name%TYPE
    | plsql_table_name%TYPE
    | record_name%TYPE
    | scalar_type_name
    | table_name%ROWTYPE
    | table_name.column_name%TYPE
    | variable_name%TYPE} [{:= | DEFAULT} expression]

return_type ::=
{  cursor_name%ROWTYPE
 | cursor_variable_name%ROWTYPE
 | plsql_table_name%TYPE
 | record_name%TYPE
 | scalar_type_name
 | table_name%ROWTYPE
 | table_name.column_name%TYPE
 | variable_name%TYPE}

object_declaration ::=
{  constant_declaration
 | cursor_declaration
 | cursor_variable_declaration
 | exception_declaration
 | plsql_table_declaration
 | record_declaration
 | variable_declaration}

subprogram_declaration ::=
{function_declaration | procedure_declaration}

Parameters

function_name	This identifies a user-defined function.
parameter_name	This identifies a formal parameter, which is a variable declared in a function specification and referenced in the function body.
IN, OUT, IN OUT	These parameter modes define the behavior of formal parameters. An IN parameter lets you pass values to the subprogram being called. An OUT parameter lets you return values to the caller of the subprogram. An IN OUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller.
:= | DEFAULT	This operator or keyword allows you to initialize IN parameters to default values.
expression	This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the declaration is elaborated, the value of expression is assigned to the parameter. The value and the parameter must have compatible datatypes.
RETURN	This keyword introduces the RETURN clause, which specifies the datatype of the result value.
cursor_name	This identifies an explicit cursor previously declared within the current scope.
cursor_variable_name	This identifies a PL/SQL cursor variable previously declared within the current scope.
plsql_table_name	This identifies a PL/SQL table previously declared within the current scope.
record_name	This identifies a user-defined record previously declared within the current scope.
scalar_type_name	This identifies a predefined scalar datatype such as BOOLEAN, NUMBER, or VARCHAR2, which must be specified without constraints.
table_name	This identifies a database table (or view) that must be accessible when the declaration is elaborated.
table_name.column_name	This identifies a database table and column that must be accessible when the declaration is elaborated.
variable_name	This identifies a PL/SQL variable previously declared within the current scope.
%ROWTYPE	This attribute provides a record type that represents a row in a database table or a row fetched from a previously declared cursor or cursor variable. Fields in the record and corresponding columns in the row have the same names and datatypes.
%TYPE	This attribute provides the datatype of a previously declared field, record, PL/SQL table, database column, or variable.
constant_declaration	This construct declares a constant.
cursor_declaration	This construct declares an explicit cursor.
cursor_variable_ declaration	This construct declares a cursor variable.
exception_declaration	This construct declares an exception.
plsql_table_declaration	This construct declares a PL/SQL table.
record_declaration	This construct declares a user-defined record.
variable_declaration	This construct declares a variable.
function_declaration	This construct declares a nested function.
procedure_declaration	This construct declares a procedure.
exception_handler	This construct associates an exception with a sequence of statements, which is executed when that exception is raised.
Comments	Every function must contain at least one RETURN statement. Otherwise, PL/SQL raises the predefined exception PROGRAM_ERROR at run time.

A function is called as part of an expression. For example, the function sal_ok might be called as follows:

promotable := sal_ok(new_sal, new_title) AND (rating > 3);

To be callable from SQL expressions, a stored function must obey certain rules meant to control side effects. For standalone functions, Oracle can enforce these rules by checking the function body. However, the body of a packaged function is hidden. So, for packaged functions, you must use the pragma RESTRICT_REFERENCES to enforce the rules. For more information, see "Calling Stored Functions from SQL Expressions" in Oracle7 Server Application Developer's Guide.

You can write the function specification and body as a unit. Or, you can separate the function specification from its body. That way, you can hide implementation details by placing the function in a package. You can define functions in a package body without declaring their specifications in the package specification. However, such functions can be called only from inside the package.

Inside a function, an IN parameter acts like a constant. Therefore, it cannot be assigned a value. An OUT parameter acts like an uninitialized variable. So, its value cannot be assigned to another variable or reassigned to itself. An IN OUT parameter acts like an initialized variable. Therefore, it can be assigned a value, and its value can be assigned to another variable.

Avoid using the OUT and IN OUT modes with functions. The purpose of a function is to take zero or more parameters and return a single value. It is poor programming practice to have a function return multiple values. Also, functions should be free from side effects, which change the values of variables not local to the subprogram. Thus, a function should not change the values of its actual parameters.

Functions can be defined using any Oracle tool that supports PL/SQL. However, to become available for general use, functions must be CREATEd and stored in an Oracle database. You can issue the CREATE FUNCTION statement interactively from SQL*Plus or Server Manager. For the full syntax of the CREATE FUNCTION statement, see Oracle7 Server SQL Reference.


Function examples (PL/SQL)

The following function returns the balance of a specified bank account:

FUNCTION balance (acct_id INTEGER) RETURN REAL IS
   acct_bal  REAL;
BEGIN
   SELECT bal INTO acct_bal FROM accts WHERE acctno = acct_id;
   RETURN acct_bal;
END balance;


Literals

  Examples	  Related Topics

Description	A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. The numeric literal 135 and the string literal 'hello world' are examples.

Syntax

numeric_literal ::=
[+ | -]{integer | real_number}

integer ::=
digit[digit]...

real_number ::=
{  integer[.integer]
 | integer.
 | .integer}[{E | e}[+ | -]integer]

character_literal ::=
{'character' | ''''}

string_literal ::=
'{character[character]... | ''['']...}'

boolean_literal ::=
{TRUE | FALSE | NULL}

Parameters

integer	This is an optionally signed whole number without a decimal point.
real_number	This is an optionally signed whole or fractional number with a decimal point.
digit	This is one of the numerals 0 .. 9.
char	This is a member of the PL/SQL character set.
TRUE, FALSE	This is a predefined Boolean value.
NULL	This is a predefined non-value, which stands for a missing, unknown, or inapplicable value.
Comments	Two kinds of numeric literals can be used in arithmetic expressions: integers and reals. Numeric literals must be separated by punctuation. Space characters can be used in addition to the punctuation.

A character literal is an individual character enclosed by single quotes (apostrophes). Character literals include all the printable characters in the PL/SQL character set: letters, numerals, spaces, and special symbols. PL/SQL is case sensitive within character literals. So, for example, PL/SQL considers the literals 'Q' and 'q' to be different.

A string literal is a sequence of zero or more characters enclosed by single quotes. The null string ('') contains zero characters. To represent an apostrophe within a string, write two single quotes. PL/SQL is case sensitive within string literals. So, for example, PL/SQL considers the literals 'white' and 'White' to be different.

Also, trailing blanks are significant within string literals, so 'White' and 'White  ' are different. How a string literal compares to a variable does not depend on the variable; trailing blanks in a literal are never trimmed.

Unlike the non-value NULL, the Boolean values TRUE and FALSE cannot be inserted into a database column.


Literal examples

Several examples of numeric literals follow:

25   6.34   7E2   25e-03   .1   1.   +17  -4.4

Several examples of character literals follow:

'H'   '&'   ' '   '9'   ']'   'g'

A few examples of string literals follow:

'$5,000'
'02-AUG-87'
'Don''t leave without saving your work.'


Packages

  Related Topics

Description	A package is a database object that groups logically related PL/SQL types, objects, and subprograms. Packages have two parts: a specification and a body.

Syntax

package_specification ::=
PACKAGE package_name IS
   {object_declaration | spec_declaration}
   [{object_declaration | spec_declaration}]...
END [package_name];

package_body ::=
PACKAGE BODY package_name IS
   [[object_declaration [object_declaration] ...]
    [body_declaration [body_declaration] ...]]
[BEGIN
   seq_of_statements]
END [package_name];

object_declaration ::=
{  constant_declaration
 | cursor_declaration
 | exception_declaration
 | plsql_table_declaration
 | record_declaration
 | variable_declaration}

spec_declaration ::=
{  cursor_specification
 | function_specification
 | procedure_specification}

body_declaration ::=
{  cursor_body
 | function_body
 | procedure_body}

Parameters

package_name	This identifies a package.
constant_declaration	This construct declares a constant.
cursor_declaration	This construct, which cannot contain a RETURN clause, declares an explicit cursor.
exception_declaration	This construct declares an exception.
plsql_table_declaration	This construct declares a PL/SQL table.
record_declaration	This construct declares a user-defined record.
variable_declaration	This construct declares a variable.
cursor_specification	This construct declares the interface to an explicit cursor.
function_specification	This construct declares the interface to a function.
procedure_specification	This construct declares the interface to a procedure.
cursor_body	This construct defines the underlying implementation of an explicit cursor.
procedure_body	This construct defines the underlying implementation of a procedure.
function_body	This construct defines the underlying implementation of a function.
Comments	You cannot define packages in a PL/SQL block or subprogram. However, you can use any Oracle tool that supports PL/SQL to create and store packages in an Oracle database. You can issue the CREATE PACKAGE and CREATE PACKAGE BODY statements interactively from SQL*Plus or Server Manager and from an Oracle Precompiler or OCI host program. For the full syntax of the CREATE PACKAGE statement, see Oracle7 Server SQL Reference.

Most packages have a specification and a body. The specification is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the specification.

Only subprograms and cursors have an underlying implementation (definition). So, if a specification declares only types, constants, variables, and exceptions, the package body is unnecessary. However, the body can still be used to initialize objects declared in the specification, as the following example shows:

CREATE PACKAGE emp_actions AS
   ...
   number_hired INTEGER;
END emp_actions;

CREATE PACKAGE BODY emp_actions AS
BEGIN
   number_hired := 0;
END emp_actions;

You can code and compile a specification without its body. Once the specification has been compiled, stored subprograms that reference the package can be compiled as well. You need not define the package bodies fully until you are ready to complete the application.

Furthermore, you can debug, enhance, or replace a package body without changing the interface (package specification) to the package body. That means you need not recompile calling programs.

Cursors and subprograms declared in a package specification must be defined in the package body. Other program objects declared in the package specification cannot be redeclared in the package body.

To match subprogram specifications and bodies, PL/SQL does a token-by-token comparison of their headers. So, except for white space, the headers must match word for word. Otherwise, PL/SQL raises an exception.


PL/SQL tables

  Examples	  Related Topics

Description	PL/SQL tables are objects of type TABLE, which are modelled as (but not the same as) database tables. PL/SQL tables use a primary key to give you array-like access to rows. Like an array, a PL/SQL table is an ordered collection of elements of the same type. Each element has a unique index number that determines its position in the ordered collection.

However, PL/SQL tables differ from arrays in two important ways. First, arrays have fixed lower and upper bounds, but PL/SQL tables are unbounded. So, the size of a PL/SQL table can increase dynamically. Second, arrays require consecutive index numbers, but PL/SQL tables do not. So, a PL/SQL table can be indexed by any series of integers.

To create PL/SQL tables, you must take two steps. First, you define a TABLE type, then declare PL/SQL tables of that type.

Syntax

table_type_definition ::=
TYPE table_type_name IS TABLE OF
   {  cursor_name%ROWTYPE
    | record_type_name
    | record_name%TYPE
    | scalar_type_name
    | table_name%ROWTYPE
    | table_name.column_name%TYPE
    | variable_name%TYPE} [NOT NULL] INDEX BY BINARY_INTEGER;

plsql_table_declaration ::=
plsql_table_name table_type_name;

Parameters

table_type_name	This identifies a user-defined type specifier, which is used in subsequent declarations of PL/SQL tables.
Cursor_name	This identifies an explicit cursor previously declared within the current scope.
Record_type_name	This identifies a RECORD type previously defined within the current scope.
Record_name	This identifies a user-defined record previously declared within the current scope.
Scalar_type_name	This identifies a predefined scalar datatype such as BOOLEAN, NUMBER, or VARCHAR2, which must be specified without constraints.
Table_name	This identifies a database table (or view) that must be accessible when the declaration is elaborated.
Table_name.column_name	This identifies a database table and column that must be accessible when the declaration is elaborated.
Variable_name	This identifies a PL/SQL variable previously declared within the current scope.
%ROWTYPE	This attribute provides a record type that represents a row in a database table or a row fetched from a previously declared cursor. Fields in the record and corresponding columns in the row have the same names and datatypes.
%TYPE	This attribute provides the datatype of a previously declared record, database column, or variable.
INDEX BY BINARY INTEGER	The index of a PL/SQL table must have datatype BINARY_INTEGER, which can represent signed integers. The magnitude range of a BINARY_INTEGER value is -2147483647 .. 2147483647.
Plsql_table_name	This identifies an entire PL/SQL table.
Comments	You can define TABLE types in the declarative part of any block, subprogram, or package. To specify the element type, you can use %TYPE or %ROWTYPE.

A PL/SQL table is unbounded; its index can include any BINARY_ INTEGER value. So, you cannot initialize a PL/SQL table in its declaration. For example, the following declaration is illegal:

sal_tab SalTabTyp := (1500, 2750, 2000, 950, 1800);  -- illegal

The INDEX BY clause must specify datatype BINARY_INTEGER, which has a magnitude range of -2147483647 .. 2147483647. If the element type is a record type, every field in the record must have a scalar datatype such as CHAR, DATE, or NUMBER.

You can declare PL/SQL tables as the formal parameters of functions and procedures. That way, you can pass PL/SQL tables to stored subprograms and from one subprogram to another.

PL/SQL tables follow the usual scoping and instantiation rules. In a package, PL/SQL tables are instantiated when you first reference the package and cease to exist when you end the database session. In a block or subprogram, local PL/SQL tables are instantiated when you enter the block or subprogram and cease to exist when you exit.

Every PL/SQL table has the attributes EXISTS, COUNT, FIRST, LAST, PRIOR, NEXT, and DELETE. They make PL/SQL tables easier to use and your applications easier to maintain.

The first reference to an element in a PL/SQL table must be an assignment. Until an element is assigned a value, it does not exist. If you reference a nonexistent element, PL/SQL raises the predefined exception NO_DATA_FOUND.

To reference elements in a PL/SQL table, you specify an index number using the following syntax:

plsql_table_name(index)

When calling a function that returns a PL/SQL table, you use the following syntax to reference elements in the table:

function_name(parameters)(index)

If the function result is a PL/SQL table of records, you use the following syntax to reference fields in a record:

function_name(parameters)(index).field_name

Currently, you cannot use the syntax above to call a parameterless function because PL/SQL does not allow empty parameter lists. That is, the following syntax is illegal:

function_name()(index)  -- illegal; empty parameter list

Instead, declare a local PL/SQL table to which you can assign the function result, then reference the PL/SQL table directly.

You can retrieve Oracle data into a PL/SQL table in three ways: the SELECT INTO statement lets you select a single row of data; the FETCH statement or a cursor FOR loop lets you fetch multiple rows.

Using the SELECT INTO statement, you can select a column entry into a scalar element. Or, you can select an entire row into a record element. Using the FETCH statement or a cursor FOR loop, you can fetch an entire column of Oracle data into a PL/SQL table of scalars. Or, you can fetch an entire table of Oracle data into a PL/SQL table of records.

You cannot reference record variables in the VALUES clause. So, you cannot insert entire records from a PL/SQL table of records into rows in a database table.

With the Oracle Call Interface (OCI) or the Oracle Precompilers, you can bind host arrays to PL/SQL tables declared as the formal parameters of a subprogram. That allows you to pass host arrays to stored functions and procedures.

You can use a BINARY_INTEGER variable or compatible host variable to index the host arrays. Given the array subscript range m .. n, the corresponding PL/SQL table index range is always 1 .. n - m + 1. For example, if the array subscript range is 5 .. 10, the corresponding PL/SQL table index range is 1 .. (10 - 5 + 1) or 1 .. 6.


PL/SQL table examples

In the following example, you define a TABLE type named SalTabTyp:

DECLARE
   TYPE SalTabTyp IS TABLE OF emp.sal%TYPE
      INDEX BY BINARY_INTEGER;

Once you define type SalTabTyp, you can declare PL/SQL tables of that type, as follows:

sal_tab SalTabTyp;

The identifier sal_tab represents an entire PL/SQL table.

In the next example, you assign the sum of variables salary and increase to the tenth row in PL/SQL table sal_tab:

sal_tab(10) := salary * increase;

In the following example, you select a row from the database table dept into a record stored by the first element of the PL/SQL table dept_tab:

DECLARE
   TYPE DeptTabTyp IS TABLE OF dept%ROWTYPE
      INDEX BY BINARY_INTEGER;
   dept_tab DeptTabTyp;
BEGIN
   /* Select entire row into record stored by first element. */
   SELECT * INTO dept_tab(1) FROM dept WHERE deptno = 10;
   IF dept_tab(1).dname = 'ACCOUNTING' THEN
      ...
   END IF;
   ...
END;

In the final example, you fetch rows from a cursor into the PL/SQL table of records emp_tab:

DECLARE
   TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
      INDEX BY BINARY_INTEGER;
   emp_tab EmpTabTyp;
   i BINARY_INTEGER := 0;
   CURSOR c1 IS SELECT * FROM emp;
BEGIN
   OPEN c1;
   LOOP
      i := i + 1;
      /* Fetch entire row into record stored by ith element. */
      FETCH c1 INTO emp_tab(i);
      EXIT WHEN c1%NOTFOUND;
      -- process data record
   END LOOP;
   CLOSE c1;
END;


Procedures

  Examples	   Related Topics

Description	A procedure is a named PL/SQL block, which can take parameters and be invoked. Generally, you use a procedure to perform an action.

A procedure has two parts: the specification and the body. The procedure specification begins with the keyword PROCEDURE and ends with the procedure name or a parameter list. Parameter declarations are optional. Procedures that take no parameters are written without parentheses.

The procedure body begins with the keyword IS and ends with the keyword END followed by an optional procedure name. The procedure body has three parts: an optional declarative part, an executable part, and an optional exception-handling part.

The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These objects are local and cease to exist when you exit the procedure. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains exception handlers, which deal with exceptions raised during execution.

Syntax

procedure_specification ::=
PROCEDURE procedure_name (parameter_declaration[,
  parameter_declaration]...)];

procedure_body ::=
PROCEDURE procedure_name [(parameter_declaration[,
  parameter_declaration]...)] IS
   [[object_declaration [object_declaration] ...]
    [subprogram_declaration [subprogram_declaration] ...]]
BEGIN
    seq_of_statements
[EXCEPTION
    exception_handler [exception_handler] ...]
END [procedure_name];

parameter_declaration ::=
parameter_name [IN | OUT | IN OUT]
   {  cursor_name%ROWTYPE
    | cursor_variable_name%TYPE
    | plsql_table_name%TYPE
    | record_name%TYPE
    | scalar_type_name
    | table_name%ROWTYPE
    | table_name.column_name%TYPE
    | variable_name%TYPE} [{:= | DEFAULT} expression]

object_declaration ::=
{  constant_declaration
 | cursor_declaration
 | cursor_variable_declaration
 | exception_declaration
 | plsql_table_declaration
 | record_declaration
 | variable_declaration}

subprogram_declaration ::=
{function_declaration | procedure_declaration}

Parameters

procedure_name	This identifies a user-defined procedure.
Parameter_name	This identifies a formal parameter, which is a variable declared in a procedure specification and referenced in the procedure body.
IN, OUT, IN OUT	These parameter modes define the behavior of formal parameters. An IN parameter lets you pass values to the subprogram being called. An OUT parameter lets you return values to the caller of the subprogram. An IN OUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller.
:= | DEFAULT	This operator or keyword allows you to initialize IN parameters to default values.
Expression	This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the declaration is elaborated, the value of expression is assigned to the parameter. The value and the parameter must have compatible datatypes.
Cursor_name	This identifies an explicit cursor previously declared within the current scope.
Cursor_variable_name	This identifies a PL/SQL cursor variable previously declared within the current scope.
Plsql_table_name	This identifies a PL/SQL table previously declared within the current scope.
Record_name	This identifies a user-defined record previously declared within the current scope.
Scalar_type_name	This identifies a predefined scalar datatype such as BOOLEAN, NUMBER, or VARCHAR2, which must be specified without constraints.
Table_name	This identifies a database table (or view) that must be accessible when the declaration is elaborated.
Table_name.column_name	This identifies a database table and column that must be accessible when the declaration is elaborated.
Variable_name	This identifies a PL/SQL variable previously declared within the current scope.
%ROWTYPE	This attribute provides a record type that represents a row in a database table or a row fetched from a previously declared cursor. Fields in the record and corresponding columns in the row have the same names and datatypes.
%TYPE	This attribute provides the datatype of a field, record, PL/SQL table, database column, or variable.
Constant_declaration	This construct declares a constant.
Cursor_declaration	This construct declares an explicit cursor.
Cursor_variable_ declaration	This construct declares a cursor variable.
Exception_declaration	This construct declares an exception.
Plsql_table_declaration	This construct declares a PL/SQL table.
Record_declaration	This construct declares a user-defined record.
Variable_declaration	This construct declares a variable.
Function_declaration	This construct declares a nested function.
Procedure_declaration	This construct declares a procedure.
Exception_handler	This construct associates an exception with a sequence of statements, which is executed when that exception is raised.
Comments	At least one statement must appear in the executable part of a procedure. The NULL statement meets this requirement.

A procedure is called as a PL/SQL statement. For example, the procedure raise_salary might be called as follows:

raise_salary(emp_num, amount);

Inside a procedure, an IN parameter acts like a constant. Therefore, it cannot be assigned a value. An OUT parameter acts like an uninitialized variable. So, its value cannot be assigned to another variable or reassigned to itself. An IN OUT parameter acts like an initialized variable. Therefore, it can be assigned a value, and its value can be assigned to another variable.

Before exiting a procedure, explicitly assign values to all OUT formal parameters. Otherwise, the values of corresponding actual parameters are indeterminate. If you exit successfully, PL/SQL assigns values to the actual parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.

Unlike OUT and IN OUT parameters, IN parameters can be initialized to default values.

You can write the procedure specification and body as a unit. Or, you can separate the procedure specification from its body. That way, you can hide implementation details by placing the procedure in a package. You can define procedures in a package body without declaring their specifications in the package specification. However, such procedures can be called only from inside the package.

Procedures can be defined using any Oracle tool that supports PL/SQL. To become available for general use, however, procedures must be CREATEd and stored in an Oracle database. You can issue the CREATE PROCEDURE statement interactively from SQL*Plus or Server Manager. For the full syntax of the CREATE PROCEDURE statement, see Oracle7 Server SQL Reference.


Procedure examples

The following procedure debits a bank account:

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
   old_balance REAL;
   new_balance REAL;
   overdrawn   EXCEPTION;
BEGIN
   SELECT bal INTO old_balance FROM accts WHERE acctno = acct_id;
   new_balance := old_balance - amount;
   IF new_balance < 0 THEN
      RAISE overdrawn;
   ELSE
      UPDATE accts SET bal = new_balance WHERE acctno = acct_id;
   END IF;
EXCEPTION
   WHEN overdrawn THEN
      ...
END debit_account;

In the following example, you call the procedure using named notation:

debit_account(amount => 500, acct_id => 10261);


Records

  Examples	  Related Topics

Description	Records are objects of type RECORD. Records have uniquely named fields that can store data values of different types.

To create records, you must take two steps. First, you define a RECORD type, then declare user-defined records of that type.

Syntax

record_type_definition ::=
TYPE record_type_name IS RECORD (field_declaration[,
  field_declaration]...);

record_declaration ::=
record_name record_type_name;

field_declaration ::=
field_name
   {  cursor_name%ROWTYPE
    | local_field_name%TYPE
    | plsql_table_name%TYPE
    | record_name%TYPE
    | scalar_type_name
    | table_name%ROWTYPE
    | table_name.column_name%TYPE
    | variable_name%TYPE} [[NOT NULL] {:= | DEFAULT} expression]

Parameters

record_type_name	This identifies a user-defined type specifier, which is used in subsequent declarations of records.
cursor_name	This identifies an explicit cursor previously declared within the current scope.
local_field_name	This identifies a field previously declared in the same user-defined record definition.
plsql_table_name	This identifies a PL/SQL table previously declared within the current scope.
record_name	This identifies a user-defined record previously declared within the current scope.
scalar_type_name	This identifies a predefined scalar datatype such as BOOLEAN, NUMBER, or VARCHAR2, which must be specified without constraints.
table_name	This identifies a database table (or view) that must be accessible when the declaration is elaborated.
table_name.column_name	This identifies a database table and column that must be accessible when the declaration is elaborated.
variable_name	This identifies a PL/SQL variable previously declared within the current scope.
%ROWTYPE	This attribute provides a record type that represents a row in a database table or a row fetched from a previously declared cursor. Fields in the record and corresponding columns in the row have the same names and datatypes.
%TYPE	This attribute provides the datatype of a field, record, PL/SQL table, database column, or variable.
NOT NULL	This constraint prevents the assigning of nulls to a field. At run time, trying to assign a null to a field defined as NOT NULL raises the predefined exception VALUE_ERROR. The constraint NOT NULL must be followed by an initialization clause.
:= | DEFAULT	This operator or keyword allows you to initialize fields to default values.
expression	This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the declaration is elaborated, the value of expression is assigned to the field. The value and the field must have compatible datatypes.
Comments	You can define RECORD types and declare user-defined records in the declarative part of any block, subprogram, or package. Also, a record can be initialized in its declaration, as the following example shows:

DECLARE
   TYPE TimeTyp IS RECORD(
      second SMALLINT := 0,
      minute SMALLINT := 0,
      hour   SMALLINT := 0);

The next example shows that you can use the %TYPE attribute to specify a field datatype. It also shows that you can add the NOT NULL constraint to any field declaration and so prevent the assigning of nulls to that field.

DECLARE
   TYPE DeptRecTyp IS RECORD(
      deptno NUMBER(2) NOT NULL,
      dname  dept.dname%TYPE,
      loc    dept.loc%TYPE);
   dept_rec DeptRecTyp;

To reference individual fields in a record, you use dot notation. For example, you might assign a value to the dname field in the dept_rec record as follows:

dept_rec.dname := 'PURCHASING';

Instead of assigning values separately to each field in a record, you can assign values to all fields at once. This can be done in two ways. First, PL/SQL lets you assign one record to another if they have the same datatype. Note, however, that even if their fields match exactly, a user-defined record and a %ROWTYPE record have different types. Second, you can assign a list of column values to a record by using the SELECT or FETCH statement. Just make sure the column names appear in the same order as the fields in your record.

You can declare and reference nested records. That is, a record can be the component of another record, as the following example shows:

DECLARE
   TYPE TimeTyp IS RECORD(
      minute SMALLINT,
      hour   SMALLINT);
   TYPE MeetingTyp IS RECORD(
      day     DATE,
      time    TimeTyp,     -- nested record
      place   CHAR(20),
      purpose CHAR(50));

   TYPE PartyTyp IS RECORD(
      day  DATE,
      time TimeTyp,        -- nested record
      loc  CHAR(15));
   meeting MeetingTyp;
   seminar MeetingTyp;
   party   PartyTyp;

The next example shows that you can assign one nested record to another if they have the same datatype:

seminar.time := meeting.time;

Such assignments are allowed even if the containing records have different datatypes.

User-defined records follow the usual scoping and instantiation rules. In a package, they are instantiated when you first reference the package and cease to exist when you exit the application or end the database session. In a block or subprogram, they are instantiated when you enter the block or subprogram and cease to exist when you exit the block or subprogram.

Like scalar variables, user-defined records can be declared as the formal parameters of procedures and functions. The restrictions that apply to scalar parameters also apply to user-defined records.

You can specify a RECORD type in the RETURN clause of a function specification. That allows the function to return a user-defined record of the same type. When calling a function that returns a user-defined record, you use the following syntax to reference fields in the record:

function_name(parameters).field_name

To reference nested fields in a record returned by a function, you use the following syntax:

function_name(parameters).field_name.nested_field_name

Currently, you cannot use the syntax above to call a parameterless function because PL/SQL does not allow empty parameter lists. That is, the following syntax is illegal:

function_name().field_name  -- illegal; empty parameter list

You cannot just drop the empty parameter list because the following syntax is also illegal:

function_name.field_name  -- illegal; no parameter list

Instead, declare a local user-defined record to which you can assign the function result, then reference its fields directly.


Record examples

In the following example, you define a RECORD type named DeptRecTyp, declare a record named dept_rec, then select a row of values into the record:

DECLARE
   TYPE DeptRecTyp IS RECORD(
      deptno NUMBER(2),
      dname  CHAR(14),
      loc    CHAR(13));
   dept_rec DeptRecTyp;
   ...
BEGIN
   SELECT deptno, dname, loc INTO dept_rec FROM dept
      WHERE deptno = 20;
   ...
END;


SQL cursor

  Examples	   Related Topics

Description	Oracle implicitly opens a cursor to process each SQL statement not associated with an explicit cursor. PL/SQL lets you refer to the most recent implicit cursor as the "SQL" cursor. The SQL cursor has four attributes: %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. They give you useful information about the execution of INSERT, UPDATE, DELETE, and SELECT INTO statements.

Syntax

sql_cursor ::=
SQL{%FOUND | %ISOPEN | %NOTFOUND | %ROWCOUNT}

Parameters

SQL	This is the name of the implicit SQL cursor.
%FOUND	This attribute yields TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it yields FALSE.
%ISOPEN	This attribute always yields FALSE because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
%NOTFOUND	This attribute is the logical opposite of %FOUND. It yields TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it yields FALSE.
%ROWCOUNT	This attribute yields the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.
Comments	You can use cursor attributes in procedural statements but not in SQL statements. Before Oracle opens the SQL cursor automatically, the implicit cursor attributes yield NULL.

The values of cursor attributes always refer to the most recently executed SQL statement, wherever that statement appears. It might be in a different scope. So, if you want to save an attribute value for later use, assign it to a Boolean variable immediately.

If a SELECT INTO statement fails to return a row, PL/SQL raises the predefined exception NO_DATA_FOUND whether you check SQL%NOTFOUND on the next line or not.

However, a SELECT INTO statement that calls a SQL group function never raises NO_DATA_FOUND. That is because group functions such as AVG and SUM always return a value or a null. In such cases, SQL%NOTFOUND yields FALSE.


SQL cursor examples

In the following example, %NOTFOUND is used to insert a row if an update affects no rows:

UPDATE emp SET sal = sal * 1.05 WHERE empno = my_empno;
IF SQL%NOTFOUND THEN
   INSERT INTO emp VALUES (my_empno, my_ename, ...);
END IF;

In the next example, you use %ROWCOUNT to raise an exception if more than 100 rows are deleted:

DELETE FROM parts WHERE status = 'OBSOLETE';
IF SQL%ROWCOUNT > 100 THEN  -- more than 100 rows were deleted
   RAISE large_deletion;
END IF;


• 9


Exceptions

  Related Topics

An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements. To handle raised exceptions, you write separate routines called exception handlers.


EXCEPTION_INIT pragma

  Examples	   Related Topics

Description	The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. That allows you to refer to any internal exception by name and to write a specific handler for it instead of using the OTHERS handler.

Syntax

exception_init_pragma ::= PRAGMA EXCEPTION_INIT (exception_name, error_number);

Parameters

PRAGMA	This keyword signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler.
exception_name	This identifies a user-defined exception previously declared within the current scope.
error_number	This is any valid Oracle error number. These are the same error numbers returned by the function SQLCODE.
Comments	You can use EXCEPTION_INIT in the declarative part of any PL/SQL block, subprogram, or package. The pragma must appear in the same declarative part as its associated exception, somewhere after the exception declaration.

Be sure to assign only one exception name to an error number.


EXCEPTION_INIT pragma examples

The following pragma associates the exception insufficient_privileges with Oracle error -1031:

DECLARE
   insufficient_privileges EXCEPTION;
   PRAGMA EXCEPTION_INIT(insufficient_privileges, -1031);
BEGIN
   ...
EXCEPTION
   WHEN insufficient_privileges THEN
      -- handle the error
END;


 SQLCODE function

  Related Topics

Description	The function SQLCODE returns the number code associated with the most recently raised exception. SQLCODE is meaningful only in an exception handler. Outside a handler, SQLCODE always returns zero.

For internal exceptions, SQLCODE returns the number of the associated Oracle error. The number that SQLCODE returns is negative unless the Oracle error is no data found, in which case SQLCODE returns +100.

For user-defined exceptions, SQLCODE returns +1 unless you used the pragma EXCEPTION_INIT to associate the exception with an Oracle error number, in which case SQLCODE returns that error number.

Syntax

sqlcode_function ::=
SQLCODE

Comments	You cannot use SQLCODE directly in a SQL statement. For example, the following statement is illegal:

INSERT INTO errors VALUES (SQLCODE, ...);

Instead, you must assign the value of SQLCODE to a local variable, then use the variable in the SQL statement, as follows:

DECLARE
   my_sqlcode  NUMBER;
BEGIN
   ...
EXCEPTION
   ...
   WHEN OTHERS THEN
      my_sqlcode := SQLCODE;
      INSERT INTO errors VALUES (my_sqlcode, ...);
END;

SQLCODE is especially useful in the OTHERS exception handler because it lets you identify which internal exception was raised.


 SQLERRM function

  Related Topics

Description	The function SQLERRM returns the error message associated with its error-number argument or, if the argument is omitted, with the current value of SQLCODE. SQLERRM with no argument is meaningful only in an exception handler. Outside a handler, SQLERRM with no argument always returns the message normal, successful completion.

For internal exceptions, SQLERRM returns the message associated with the Oracle error that occurred. The message begins with the Oracle error code.

For user-defined exceptions, SQLERRM returns the message user-defined exception unless you used the pragma EXCEPTION_INIT to associate the exception with an Oracle error number, in which case SQLERRM returns the corresponding error message.

Syntax

sqlerrm_function ::=
SQLERRM [(error_number)]

Parameters

error_number	This must be a valid Oracle error number. For a list of Oracle errors, see Oracle7 Server Messages.
Comments	You can pass an error number to SQLERRM, in which case SQLERRM returns the message associated with that error number. The error number passed to SQLERRM should be negative. Passing a zero to SQLERRM always returns the following message:

ORA-0000: normal, successful completion

Passing a positive number to SQLERRM always returns the message User-Defined Exception unless you pass +100, in which case SQLERRM returns the following message:

ORA-01403: no data found

You cannot use SQLERRM directly in a SQL statement. For example, the following statement is illegal:

INSERT INTO errors VALUES (SQLERRM, ...);

Instead, you must assign the value of SQLERRM to a local variable, then use the variable in the SQL statement, as follows:

DECLARE
   my_sqlerrm  CHAR(150);
   ...
BEGIN
   ...
EXCEPTION
   ...
   WHEN OTHERS THEN
   my_sqlerrm := SUBSTR(SQLERRM, 1, 150);
   INSERT INTO errors VALUES (my_sqlerrm, ...);
END;

The string function SUBSTR ensures that a VALUE_ERROR exception (for truncation) is not raised when you assign the value of SQLERRM to my_sqlerrm. SQLERRM is especially useful in the OTHERS exception handler because it lets you identify which internal exception was raised.


Cursor attributes

  Examples	   Related Topics

Description	Cursors and cursor variables have four attributes that give you useful information about the execution of a data manipulation statement.

There are two kinds of cursors: implicit and explicit. PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including single-row queries. For multi-row queries, you can explicitly declare a cursor or cursor variable to process the rows.

Syntax

cursor_attribute ::=
{  cursor_name
 | cursor_variable_name
 | :host_cursor_variable_name
 | SQL}{%FOUND | %ISOPEN | %NOTFOUND | %ROWCOUNT}

Parameters

cursor_name	This identifies an explicit cursor previously declared within the current scope.
cursor_variable_name	This identifies a PL/SQL cursor variable (or parameter) previously declared within the current scope.
host_cursor_variable_ name	This identifies a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.
SQL	This is the name of the implicit SQL cursor.
%FOUND	This is a cursor attribute, which can be appended to the name of a cursor or cursor variable. After a cursor is opened but before the first fetch, cursor_name%FOUND yields NULL. Thereafter, it yields TRUE if the last fetch returned a row, or FALSE if the last fetch failed to return a row.  Until a SQL statement is executed, SQL%FOUND yields NULL. Thereafter, it yields TRUE if the statement affected any rows, or FALSE if it affected no rows.
%ISOPEN	This is a cursor attribute, which can be appended to the name of a cursor or cursor variable. If a cursor is open, cursor_name%ISOPEN yields TRUE; otherwise, it yields FALSE. Oracle automatically closes the implicit SQL cursor after executing its associated SQL statement, so SQL%ISOPEN always yields FALSE.
%NOTFOUND	This is a cursor attribute, which can be appended to the name of a cursor or cursor variable. After a cursor is opened but before the first fetch, cursor_name%NOTFOUND yields NULL. Thereafter, it yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row.  Until a SQL statement is executed, SQL%NOTFOUND yields NULL. Thereafter, it yields FALSE if the statement affected any rows, or TRUE if it affected no rows.
%ROWCOUNT	This is a cursor attribute, which can be appended to the name of a cursor or cursor variable. When a cursor is opened, %ROWCOUNT is zeroed. Before the first fetch, cursor_name%ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the latest fetch returned a row.
Until a SQL statement is executed, SQL%ROWCOUNT yields NULL. Thereafter, it yields the number of rows affected by the statement. SQL%ROWCOUNT yields 0 if the statement affected no rows.

Comments	You can use the cursor attributes in procedural statements but not in SQL statements.

The cursor attributes apply to every cursor or cursor variable. So, for example, you can open multiple cursors, then use %FOUND or %NOTFOUND to tell which cursors have rows left to fetch. Likewise, you can use %ROWCOUNT to tell how many rows have been fetched so far.

If a cursor or cursor variable is not open, referencing it with %FOUND, %NOTFOUND, or %ROWCOUNT raises the predefined exception INVALID_CURSOR.

When a cursor or cursor variable is opened, the rows that satisfy the associated query are identified and form the result set. Rows are fetched from the result set one at a time.

If a SELECT INTO statement returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS and sets %ROWCOUNT to 1, not the actual number of rows that satisfy the query.


Cursor attribute examples

The PL/SQL block below uses %FOUND to select an action. The IF statement either inserts a row or exits the loop unconditionally.

The next example uses the same block. However, instead of using %FOUND in an IF statement, it uses %NOTFOUND in an EXIT WHEN statement.

In the following example, you use %ISOPEN to make a decision:

IF NOT (emp_cur%ISOPEN) THEN
   OPEN emp_cur;
END IF;
FETCH emp_cur INTO emp_rec;

In the final example, you use %ROWCOUNT to raise an exception if an unexpectedly high number of rows is deleted:

DELETE FROM accts WHERE status = 'BAD DEBT';
IF SQL%ROWCOUNT > 10 THEN
    RAISE out_of_bounds;
END IF;


PL/SQL table attributes

  Examples	  Related Topics

Description	Every PL/SQL table has the attributes EXISTS, COUNT, FIRST, LAST, PRIOR, NEXT, and DELETE. They make PL/SQL tables easier to use and your applications easier to maintain.

The attributes EXISTS, PRIOR, NEXT, and DELETE take parameters. Each parameter must be an expression that yields a BINARY_INTEGER value or a value implicitly convertible to that datatype.

DELETE acts like a procedure, which is called as a statement. The other PL/SQL table attributes act like a function, which is called as part of an expression.

Syntax

plsql_table_attribute ::=
plsql_table_name{  .COUNT
                 | .DELETE[(index[, index])]
                 | .EXISTS(index)
                 | .FIRST
                 | .LAST
                 | .NEXT(index)
                 | .PRIOR(index)}

Parameters

plsql_table_name	This identifies a PL/SQL table previously declared within the current scope.
COUNT	This is a PL/SQL table attribute, which can be appended to the name of a PL/SQL table. COUNT returns the number of elements that a PL/SQL table contains.
DELETE	This is a PL/SQL table attribute, which can be appended to the name of a PL/SQL table. This attribute has three forms. DELETE removes all elements from a PL/SQL table. DELETE(n) removes the nth element. If n is null, DELETE(n) does nothing. DELETE(m, n) removes all elements in the range m .. n. If m is larger than n or if m or n is null, DELETE(m, n) does nothing.
index	This is a numeric expression that must yield a value of type BINARY_INTEGER or a value implicitly convertible to that datatype.
EXISTS	This is a PL/SQL table attribute, which can be appended to the name of a PL/SQL table. EXISTS(n) returns TRUE if the nth element in a PL/SQL table exists. Otherwise, EXISTS(n) returns FALSE.
FIRST, LAST	These are PL/SQL table attributes, which can be appended to the name of a PL/SQL table. FIRST and LAST return the first and last (smallest and largest) index numbers in a PL/SQL table. If the PL/SQL table is empty, FIRST and LAST return nulls. If the PL/SQL table contains only one element, FIRST and LAST return the same index number.
NEXT, PRIOR	These are PL/SQL table attributes, which can be appended to the name of a PL/SQL table. NEXT(n) returns the index number that succeeds index n in a PL/SQL table. PRIOR(n) returns the index number that precedes index n. If n has no successor, NEXT(n) returns a null. Likewise, if n has no predecessor, PRIOR(n) returns a null.
Comments	Currently, you cannot use PL/SQL table attributes in a SQL statement. If you try, you get a compilation error.

DELETE lets you free the resources held by a PL/SQL table. DELETE(n) and DELETE(m, n) let you prune a PL/SQL table. If an element to be deleted does not exist, DELETE simply skips it; no exception is raised.

The amount of memory allocated to a PL/SQL table can increase or decrease dynamically. As you delete elements, memory is freed page by page. If you delete the entire PL/SQL table, all the memory is freed.

You can use EXISTS to avoid the exception NO_DATA_FOUND, which is raised when you reference a nonexistent element.

You can use PRIOR or NEXT to traverse PL/SQL tables from which some elements have been deleted.


PL/SQL table attribute examples

In the following example, you delete elements 20 through 30 from a PL/SQL table:

ename_tab.DELETE(20, 30);

The next example shows that you can use FIRST and LAST to specify the lower and upper bounds of a loop range provided each element in that range exists:

FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP
   ...
END LOOP;

In the following example, PL/SQL executes the assignment statement only if the element sal_tab(i) exists:

IF sal_tab.EXISTS(i) THEN
   sal_tab(i) := sal_tab(i) + 500;
ELSE
   RAISE salary_missing;
END IF;

You can use PRIOR or NEXT to traverse PL/SQL tables from which some elements have been deleted, as the following generic example shows:

DECLARE
   ...
   i BINARY_INTEGER;
BEGIN
   ..
   i := any_tab.FIRST;  -- get index of first element
   WHILE i IS NOT NULL LOOP
      ...  -- process any_tab(i)
      i := any_tab.NEXT(i);  -- get index of next element
   END LOOP;
END;


%ROWTYPE attribute

  Examples	  Related Topics

Description	The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. Fields in a record and corresponding columns in a row have the same names and datatypes.

You can use the %ROWTYPE attribute in variable declarations as a datatype specifier. Variables declared using %ROWTYPE are treated like those declared using a datatype name.

Syntax

rowtype_attribute ::=
{cursor_name | cursor_variable_name | table_name}%ROWTYPE

Parameters

cursor_name	This identifies an explicit cursor previously declared within the current scope.
cursor_variable_name	This identifies a PL/SQL cursor variable previously declared within the current scope.
table_name	This identifies a database table (or view) that must be accessible when the declaration is elaborated.
Comments	The %ROWTYPE attribute lets you declare records structured like a row of data in a database table. In the following example, you declare a record that can store an entire row from the emp table:

emp_rec emp%ROWTYPE;

The column values returned by the SELECT statement are stored in fields. To reference a field, you use dot notation. For example, you might reference the deptno field as follows:

IF emp_rec.deptno = 20 THEN ...

You can assign the value of an expression to a specific field, as the following example shows:

emp_rec.sal := average * 1.15;

There are two ways to assign values to all fields in a record at once. First, PL/SQL allows aggregate assignment between entire records if their declarations refer to the same table or cursor.

Second, you can assign a list of column values to a record by using the SELECT or FETCH statement. The column names must appear in the order in which they were defined by the CREATE TABLE or CREATE VIEW statement. Select-items fetched from a cursor associated with %ROWTYPE must have simple names or, if they are expressions, must have aliases.


%ROWTYPE attribute examples

In the example below, you use %ROWTYPE to declare two records. The first record stores a row selected from the emp table. The second record stores a row fetched from the c1 cursor.

DECLARE
   emp_rec   emp%ROWTYPE;
   CURSOR c1 IS SELECT deptno, dname, loc FROM dept;
   dept_rec  c1%ROWTYPE;

In the next example, you select a row from the emp table into a %ROWTYPE record:

DECLARE
   emp_rec  emp%ROWTYPE;
   ...
BEGIN
   SELECT * INTO emp_rec FROM emp WHERE empno = my_empno;
   IF (emp_rec.deptno = 20) AND (emp_rec.sal > 2000) THEN
      ...
   END IF;
END;


%TYPE attribute

  Related Topics

Description	The %TYPE attribute provides the datatype of a field, record, PL/SQL table, database column, or variable. You can use the %TYPE attribute as a datatype specifier when declaring constants, variables, fields, and parameters.

Syntax

type_attribute ::=
{  cursor_variable_name
 | plsql_table_name
 | record_name
 | record_name.field_name
 | table_name.column_name
 | variable_name}%TYPE

Parameters

cursor_variable_name	This identifies a PL/SQL cursor variable previously declared within the current scope.
plsql_table_name	This identifies a PL/SQL table previously declared within the current scope.
record_name	This identifies a user-defined or %ROWTYPE record previously declared within the current scope.
record_name.field_name	This identifies a field in a user-defined or %ROWTYPE record previously declared within the current scope.
table_name.column_name	This refers to a table and column that must be accessible when the declaration is elaborated.
variable_name	This is the name of a variable previously declared in the same scope.
Comments	The %TYPE attribute is particularly useful when declaring variables, fields, and parameters that refer to database columns. However, the NOT NULL column constraint does not apply to objects declared using %TYPE.


Reading syntax definitions

The syntax of PL/SQL is described using a simple variant of Backus-Naur Form (BNF). BNF is a metalanguage used mainly to define the syntax of computer languages. If you are unfamiliar with BNF, do not worry. This section tells you all you need to know.

These syntax descriptions are not meant to provide a formal language definition. So, they define syntax using a BNF-style grammar less concise but more readable than BNF.

When you are unsure of the syntax to use in a PL/SQL statement, trace through its syntax definition, reading from left to right and top to bottom. You can verify or construct any PL/SQL statement that way.

Syntax definitions use the following symbols and lexical conventions:

::=	This symbol means "is defined as."
[ ]	Brackets enclose optional items.
{ }	Braces enclose items only one of which is required.
|	A vertical bar separates alternatives within brackets or braces.
...	An ellipsis shows that the preceding syntactic element can be repeated.
lower case	Lower case denotes a syntactic element for which you must substitute a literal, identifier, or construct, whichever is appropriate.
UPPER CASE	Upper case denotes PL/SQL keywords, which must be spelled as shown but can be entered in lower or mixed case.
punctuation	Punctuation other than brackets, braces, vertical bars, and ellipses must be entered as shown.


Assignment statement

  Examples	   Related Topics

Description	An assignment statement sets the current value of a variable, field, parameter, or element. The statement consists of an assignment target followed by the assignment operator and an expression. When the statement is executed, the expression is evaluated and the resulting value is stored in the target.

Syntax

assignment_statement ::=
{  cursor_variable_name
 | :host_cursor_variable_name
 | :host_variable_name[:indicator_name]
 | parameter_name
 | plsql_table_name(index)
 | record_name.field_name
 | variable_name} := expression;

Parameters

cursor_variable_name	This identifies a PL/SQL cursor variable previously declared within the current scope. Only the value of another cursor variable can be assigned to a cursor variable.
host_cursor_variable_ name	This identifies a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.
host_variable_name	This identifies a variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. Host variables must be prefixed with a colon.
indicator_name	This identifies an indicator variable declared in a PL/SQL host environment and passed to PL/SQL. Indicator variables must be prefixed with a colon. An indicator variable "indicates" the value or condition of its associated host variable. For example, in the Oracle Precompiler environment, indicator variables let you detect nulls or truncated values in output host variables.
parameter_name	This identifies a formal OUT or IN OUT parameter of the subprogram in which the assignment statement appears.
plsql_table_name	This identifies a PL/SQL table previously declared within the current scope.
index	This is a numeric expression that must yield a value of type BINARY_INTEGER or a value implicitly convertible to that datatype.
record_name.field_name	This identifies a field in a user-defined or %ROWTYPE record previously declared within the current scope.
variable_name	This identifies a PL/SQL variable previously declared within the current scope.
expression	This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the assignment statement is executed, the expression is evaluated and the resulting value is stored in the assignment target. The value and target must have compatible datatypes.
Comments	By default, unless a variable is initialized in its declaration, it is initialized to NULL every time a block or subprogram is entered. So, never reference a variable before you assign it a value.

You cannot assign nulls to a variable defined as NOT NULL. If you try, PL/SQL raises the predefined exception VALUE_ERROR.

Only the values TRUE and FALSE and the non-value NULL can be assigned to a Boolean variable. When applied to an expression, the relational operators return a Boolean value. So, the following assignment is legal:

DECLARE
   out_of_range BOOLEAN;
   ...
BEGIN
   ...
   out_of_range := (salary < minimum) OR (salary > maximum);

As the next example shows, you can assign the value of an expression to a specific field in a record:

DECLARE
   emp_rec emp%ROWTYPE;
BEGIN
   ...
   emp_rec.sal := current_salary + increase;

Moreover, you can assign values to all fields in a record at once. PL/SQL allows aggregate assignment between entire records if their declarations refer to the same cursor or table. For example, the following assignment is legal:

DECLARE
   emp_rec1 emp%ROWTYPE;
   emp_rec2 emp%ROWTYPE;
   dept_rec dept%ROWTYPE;
BEGIN
   ...
   emp_rec1 := emp_rec2;

The next assignment is illegal because you cannot use the assignment operator to assign a list of values to a record:

dept_rec := (60, 'PUBLICITY', 'LOS ANGELES');

Using the following syntax, you can assign the value of an expression to a specific element in a PL/SQL table:

plsql_table_name(index) := expression;

In the following example, you assign the uppercase value of last_name to the third row in PL/SQL table ename_tab:

ename_tab(3) := UPPER(last_name);


Assignment statement examples

Several examples of assignment statements follow:

wages := hours_worked * hourly_salary;
country := 'France';
costs := labor + supplies;
done := (count > 100);
dept_rec.loc := 'BOSTON';
comm_tab(5) := sales * 0.15;


CLOSE statement

  Examples	   Related Topics

Description	The CLOSE statement allows resources held by an open cursor or cursor variable to be reused. No more rows can be fetched from a closed cursor or cursor variable.

Syntax

close_statement ::=
CLOSE {  cursor_name
       | cursor_variable_name
       | :host_cursor_variable_name};

Parameters

cursor_name	This identifies an explicit cursor previously declared within the current scope and currently open.
cursor_variable_name	This identifies a PL/SQL cursor variable (or parameter) previously declared within the current scope and currently open.
host_cursor_variable_ name	This identifies a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.
Comments	Once a cursor or cursor variable is closed, you can reopen it using the OPEN or OPEN-FOR statement, respectively. If you reopen a cursor without closing it first, PL/SQL raises the predefined exception CURSOR_ALREADY_OPEN. However, you need not close a cursor variable before reopening it.

If you try to close an already-closed or never-opened cursor or cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.


CLOSE statement examples

In the following example, after the last row is fetched and processed, you close the cursor variable emp_cv:

LOOP
   FETCH emp_cv INTO emp_rec;
   EXIT WHEN emp_cv%NOTFOUND;
   ...  -- process data record
END LOOP;
/* Close cursor variable. */
CLOSE emp_cv;


COMMIT statement

  Related Topics

Description	The COMMIT statement explicitly makes permanent any changes made to the database during the current transaction. Changes made to the database are not considered permanent until they are committed. A commit also makes the changes visible to other users.

Syntax

commit_statement ::=
COMMIT [WORK] [COMMENT 'text'];

Parameters

WORK	This keyword is optional and has no effect except to improve readability.
COMMENT	This keyword specifies a comment to be associated with the current transaction and is typically used with distributed transactions. The text must be a quoted literal no more than 50 characters long.
Comments	The COMMIT statement releases all row and table locks. It also erases any savepoints you marked since the last commit or rollback. Until your changes are committed, the following conditions hold:

•	can see the changes when you query the tables you modified, but other users cannot see the changes.

•	you change your mind or need to correct a mistake, you can use the ROLLBACK statement to roll back (undo) the changes.

If you commit while a cursor that was declared using FOR UPDATE is open, a subsequent fetch on that cursor raises an exception. The cursor remains open, however, so you should close it.

When a distributed transaction fails, the text specified by COMMENT helps you diagnose the problem. If a distributed transaction is ever in doubt, Oracle stores the text in the data dictionary along with the transaction ID. For more information about distributed transactions, see Oracle7 Server Concepts.

PL/SQL does not support the FORCE clause, which, in SQL, manually commits an in-doubt distributed transaction. For example, the following COMMIT statement is illegal:

COMMIT FORCE '23.51.54';  -- illegal

With embedded SQL, the optional RELEASE parameter is allowed after COMMIT WORK. The keyword RELEASE acts like a "disconnect" statement, which logs you off the database once your transaction is committed. PL/SQL does not support data control statements such as CONNECT, GRANT, or REVOKE. Therefore, it does not support the RELEASE parameter.


DELETE statement

  Examples	   Related Topics

Description	The DELETE statement removes entire rows of data from a specified table or view. For a full description of the DELETE statement, see Oracle7 Server SQL Reference.

Syntax

delete_statement ::=
DELETE [FROM] {table_reference | (subquery)} [alias]
   [WHERE {search_condition | CURRENT OF cursor_name}];

table_reference ::=
[schema_name.]{table_name | view_name}[@dblink_name]

Parameters

table_reference	This specifies a table or view, which must be accessible when you execute the DELETE statement, and for which you must have DELETE privileges.
subquery	This is a select statement that provides a value or set of values to the DELETE statement. The syntax of subquery is like the syntax of select_into_statement, except that subquery cannot have an INTO clause.
alias	This is another (usually short) name for the referenced table or view and is typically used in the WHERE clause.
WHERE search_condition	This clause conditionally chooses rows to be deleted from the referenced table or view. Only rows that meet the search condition are deleted. If you omit the WHERE clause, all rows in the table or view are deleted.
WHERE CURRENT OF cursor_name	This clause refers to the latest row processed by the FETCH statement associated with the cursor identified by cursor_name. The cursor must be FOR UPDATE and must be open and positioned on a row. If the cursor is not open, the CURRENT OF clause causes an error.
If the cursor is open, but no rows have been fetched or the last fetch returned no rows, PL/SQL raises the predefined exception NO_DATA_FOUND.

Comments	You can use the DELETE WHERE CURRENT OF statement after a fetch from an open cursor (this includes implicit fetches executed in a cursor FOR loop), provided the associated query is FOR UPDATE. This statement deletes the current row; that is, the one just fetched.

The implicit SQL cursor and the cursor attributes %NOTFOUND, %FOUND, and %ROWCOUNT let you access useful information about the execution of a DELETE statement.

A DELETE statement might delete one or more rows or no rows. If one or more rows are deleted, you get the following results:

•	%NOTFOUND yields FALSE

•	%FOUND yields TRUE

•	%ROWCOUNT yields the number of rows deleted

If no rows are deleted, you get these results:

•	%NOTFOUND yields TRUE

•	%FOUND yields FALSE

•	%ROWCOUNT yields 0


DELETE statement examples

The following statement deletes from the bonus table all employees whose sales were below quota:

DELETE FROM bonus WHERE sales_amt < quota;


EXIT statement

  Examples	  Related Topics

Description	You use the EXIT statement to exit a loop. The EXIT statement has two forms: the unconditional EXIT and the conditional EXIT WHEN. With either form, you can name the loop to be exited.

Syntax

exit_statement ::=
EXIT [label_name] [WHEN boolean_expression];

Parameters

EXIT	An unconditional EXIT statement (that is, one without a WHEN clause) exits the current loop immediately. Execution resumes with the statement following the loop.
label_name	This identifies the loop to be exited. You can exit not only the current loop but any enclosing labeled loop.
boolean_expression	This is an expression that yields the Boolean value TRUE, FALSE, or NULL. It is evaluated with each iteration of the loop in which the EXIT WHEN statement appears. If the expression yields TRUE, the current loop (or the loop labeled by label_name) is exited immediately.
Comments	The EXIT statement can be used only inside a loop.

PL/SQL allows you to code an infinite loop. For example, the following loop will never terminate in the normal way:

WHILE TRUE LOOP
   ...
END LOOP;

In such cases, you must use an EXIT statement to exit the loop.

If you use an EXIT statement to exit a cursor FOR loop prematurely, the cursor is closed automatically. The cursor is also closed automatically if an exception is raised inside the loop.


EXIT statement examples

The EXIT statement in the following example is illegal because you cannot exit from a block directly; you can exit only from a loop:

DECLARE
   amount  NUMBER;
   maximum NUMBER;
BEGIN
   ...
   BEGIN
      ...
      IF amount >= maximum THEN
         EXIT;  -- illegal
      END IF;
   END;
   ...
END;

The following loop normally executes ten times, but it will exit prematurely if there are less than ten rows to fetch:

FOR i IN 1..10
   FETCH c1 INTO emp_rec;
   EXIT WHEN c1%NOTFOUND;
   total_comm := total_comm + emp_rec.comm;
END LOOP;

The following example illustrates the use of loop labels:

<<outer>>
FOR i IN 1..10 LOOP
   ...
   <<inner>>
   FOR j IN 1..100 LOOP
      ...
      EXIT outer WHEN ...  -- exits both loops
   END LOOP inner;
END LOOP outer;


FETCH statement

  Examples 	  Related Topics

Description	The FETCH statement retrieves rows of data one at a time from the result set of a multi-row query. The data is stored in variables or fields that correspond to the columns selected by the query.

Syntax

fetch_statement ::=
FETCH {  cursor_name
       | cursor_variable_name
       | :host_cursor_variable_name}
   INTO {variable_name[, variable_name]... | record_name};

Parameters

cursor_name	This identifies an explicit cursor previously declared within the current scope.
cursor_variable_name	This identifies a PL/SQL cursor variable (or parameter) previously declared within the current scope.
host_cursor_variable_ name	This identifies a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.
variable_name[, variable_name]...	This identifies a list of previously declared scalar variables into which column values are fetched. For each column value returned by the query associated with the cursor or cursor variable, there must be a corresponding, type-compatible variable in the list.
record_name	This identifies a user-defined or %ROWTYPE record into which rows of values are fetched. For each column value returned by the query associated with the cursor or cursor variable, there must be a corresponding, type-compatible field in the record.
Comments	You must use either a cursor FOR loop or the FETCH statement to process a multi-row query.

Any variables in the WHERE clause of the query are evaluated only when the cursor or cursor variable is opened. To change the result set or the values of variables in the query, you must reopen the cursor or cursor variable with the variables set to their new values.

To reopen a cursor, you must close it first. However, you need not close a cursor variable before reopening it.

You can use different INTO lists on separate fetches with the same cursor or cursor variable. Each fetch retrieves another row and assigns values to the target variables.

If you FETCH past the last row in the result set, the values of the target fields or variables are indeterminate and the %NOTFOUND attribute yields TRUE.

PL/SQL makes sure the return type of a cursor variable is compatible with the INTO clause of the FETCH statement. For each column value returned by the query associated with the cursor variable, there must be a corresponding, type-compatible field or variable in the INTO clause. Also, the number of fields or variables must equal the number of column values.

When you declare a cursor variable as the formal parameter of a subprogram that fetches from the cursor variable, you must specify the IN (or IN OUT) mode. However, if the subprogram also opens the cursor variable, you must specify the IN OUT mode.

Eventually, the FETCH statement must fail to return a row; so when that happens, no exception is raised. To detect the failure, you must use the cursor attribute %FOUND or %NOTFOUND.

PL/SQL raises the predefined exception INVALID_CURSOR if you try to fetch from a closed or never-opened cursor or cursor variable.


FETCH statement examples

The following example shows that any variables in the query associated with a cursor are evaluated only when the cursor is opened:

DECLARE
   my_sal NUMBER(7,2);
   num    INTEGER(2) := 2;
   CURSOR emp_cur IS SELECT num*sal FROM emp;
BEGIN
   OPEN emp_cur;  -- num equals 2 here
   LOOP
      FETCH emp_cur INTO my_sal;
      EXIT WHEN emp_cur%NOTFOUND;
         -- process the data
      num := num + 1;  -- does not affect next FETCH; sal will
                       -- be multiplied by 2
   END LOOP;
   CLOSE emp_cur;
END;

In this example, each retrieved value equals 2 * sal, even though num is incremented after each fetch. To change the result set or the values of variables in the query, you must close and reopen the cursor with the variables set to their new values.

In the following Pro*C example, you fetch rows from a host cursor variable into a host record (struct) named emp_rec:

/* Exit loop when done fetching. */
EXEC SQL WHENEVER NOTFOUND DO break;
for (;;)
{
   /* Fetch row into record. */
   EXEC SQL FETCH :emp_cur INTO :emp_rec;
   /* process the data. */
}

The next example shows that you can use a different INTO clause on separate fetches with the same cursor variable. Each fetch retrieves another row from the same result set.

/* Exit loop when done fetching. */
EXEC SQL WHENEVER NOTFOUND DO break;
for (;;)
{
   /* Fetch row from result set. */
   EXEC SQL FETCH :emp_cur INTO :emp_rec1;
   /* Fetch next row from same result set. */
   EXEC SQL FETCH :emp_cur INTO :emp_rec2;
   /* process the data. */
}


GOTO statement

  Examples 	   Related Topics

Description	The GOTO statement branches unconditionally to a statement label or block label. The label must be unique within its scope and must precede an executable statement or a PL/SQL block. The GOTO statement transfers control to the labelled statement or block.

Syntax

label_declaration ::=
<<label_name>>

goto_statement ::=
GOTO label_name;

Parameters

label_name	This is an undeclared identifier that labels an executable statement or a PL/SQL block. You use a GOTO statement to transfer control to the statement or block following <<label_name>>.
Comments	Some possible destinations of a GOTO statement are illegal. In particular, a GOTO statement cannot branch into an IF statement, LOOP statement, or sub-block. For example, the following GOTO statement is illegal:

BEGIN
   ...
   GOTO update_row;  -- illegal branch into IF statement
   ...
   IF valid THEN
      ...
      <<update_row>>
      UPDATE emp SET ...
   END IF;
END;

From the current block, a GOTO statement can branch to another place in the block or into an enclosing block, but not into an exception handler. From an exception handler, a GOTO statement can branch into an enclosing block, but not into the current block.

If you use the GOTO statement to exit a cursor FOR loop prematurely, the cursor is closed automatically. The cursor is also closed automatically if an exception is raised inside the loop.

A given label can appear only once in a block. However, the label can appear in other blocks including enclosing blocks and sub-blocks. If a GOTO statement cannot find its target label in the current block, it branches to the first enclosing block in which the label appears.


GOTO statement examples

A GOTO label cannot precede just any keyword. It must precede an executable statement or a PL/SQL block. For example, the following GOTO statement is illegal:

BEGIN
   ...
   FOR ctr IN 1..50 LOOP
      DELETE FROM emp WHERE ...
      IF SQL%FOUND THEN
         GOTO end_loop;  -- illegal
      END IF;
      ...
   <<end_loop>>
   END LOOP;   -- not an executable statement
END;

To debug the last example, simply add the NULL statement, as follows:

BEGIN
   ...
   FOR ctr IN 1..50 LOOP
      DELETE FROM emp WHERE ...
      IF SQL%FOUND THEN
         GOTO end_loop;
      END IF;
      ...
   <<end_loop>>
   NULL;   -- an executable statement that specifies inaction
   END LOOP;
END;


IF statement

  Examples 	   Related Topics

Description	The IF statement lets you execute a sequence of statements conditionally. Whether the sequence is executed or not depends on the value of a Boolean expression.

Syntax

if_statement ::=
IF boolean_expression THEN
   seq_of_statements
[ELSIF boolean_expression THEN
   seq_of_statements
[ELSIF boolean_expression THEN
   seq_of_statements] ...]
[ELSE
   seq_of_statements]
END IF;

Parameters

boolean_expression	This is an expression that yields the Boolean value TRUE, FALSE, or NULL. It is associated with a sequence of statements, which is executed only if the expression yields TRUE.
THEN	This keyword associates the Boolean expression that precedes it with the sequence of statements that follows it. If the expression yields TRUE, the associated sequence of statements is executed.
ELSIF	This keyword introduces a Boolean expression to be evaluated if the expression following IF and all the expressions following any preceding ELSIFs yield FALSE or NULL.
ELSE	If control reaches this keyword, the sequence of statements that follows it is executed.
Comments	There are three forms of IF statements: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF. The simplest form of IF statement associates a Boolean expression with a sequence of statements enclosed by the keywords THEN and END IF. The sequence of statements is executed only if the expression yields TRUE. If the expression yields FALSE or NULL, the IF statement does nothing. In either case, control passes to the next statement.

The second form of IF statement adds the keyword ELSE followed by an alternative sequence of statements. The sequence of statements in the ELSE clause is executed only if the Boolean expression yields FALSE or NULL. Thus, the ELSE clause ensures that a sequence of statements is executed.

The third form of IF statement uses the keyword ELSIF to introduce additional Boolean expressions. If the first expression yields FALSE or NULL, the ELSIF clause evaluates another expression. An IF statement can have any number of ELSIF clauses; the final ELSE clause is optional. Boolean expressions are evaluated one by one from top to bottom. If any expression yields TRUE, its associated sequence of statements is executed and control passes to the next statement. If all expressions yield FALSE or NULL, the sequence in the ELSE clause is executed.

An IF statement never executes more than one sequence of statements because processing is complete after any sequence of statements is executed. However, the THEN and ELSE clauses can include more IF statements. That is, IF statements can be nested.


IF statement examples

In the example below, if shoe_count has a value of 10, both the first and second Boolean expressions yield TRUE. Nevertheless, order_quantity is assigned the proper value of 50 because processing of an IF statement stops after an expression yields TRUE and its associated sequence of statements is executed. The expression associated with ELSIF is never evaluated and control passes to the INSERT statement.

IF shoe_count < 20 THEN
   order_quantity := 50;
ELSIF shoe_count < 30 THEN
   order_quantity := 20;
ELSE
   order_quantity := 10;
END IF;
INSERT INTO purchase_order VALUES (shoe_type, order_quantity);

In the following example, depending on the value of score, one of two status messages is inserted into the grades table:

IF score < 70 THEN
   fail := fail + 1;
   INSERT INTO grades VALUES (student_id, 'Failed');
ELSE
   pass := pass + 1;
   INSERT INTO grades VALUES (student_id, 'Passed');
END IF;


INSERT statement

  Examples 	   Related Topics

Description	The INSERT statement adds new rows of data to a specified database table or view. For a full description of the INSERT statement, see Oracle7 Server SQL Reference.

Syntax

insert_statement ::=
INSERT INTO {table_reference | (subquery)}
   [(column_name[, column_name]...)]
   {VALUES (sql_expression[, sql_expression]...) | subquery};

table_reference ::=
[schema_name.]{table_name | view_name}[@dblink_name]

Parameters

table_reference	This identifies a table or view that must be accessible when you execute the INSERT statement, and for which you must have INSERT privileges.
column_name[, column_name]...	This identifies a list of columns in a database table or view. Column names need not appear in the order in which they were defined by the CREATE TABLE or CREATE VIEW statement. However, no column name can appear more than once in the list. If the list does not include all the columns in a table, the missing columns are set to NULL or to a default value specified in the CREATE TABLE statement.
sql_expression	This is any expression valid in SQL. For more information, see Oracle7 Server SQL Reference.
VALUES (...)	This clause assigns the values of expressions to corresponding columns in the column list. If there is no column list, the first value is inserted into the first column defined by the CREATE TABLE statement, the second value is inserted into the second column, and so on. There must be only one value for each column in the column list. The first value is associated with the first column, the second value is associated with the second column, and so on. If there is no column list, you must supply a value for each column in the table. The datatypes of the values being inserted must be compatible with the datatypes of corresponding columns in the column list.
subquery	This is a select statement that provides a value or set of values to the INSERT statement. The syntax of subquery is like the syntax of select_into_statement, except that subquery cannot have an INTO clause.
As many rows are added to the table as are returned by the subquery in the VALUES clause. The subquery must return a value for every column in the column list or for every column in the table if there is no column list.

Comments	All character and date literals in the VALUES list must be enclosed by single quotes ('). Numeric literals are not enclosed by quotes.

The implicit SQL cursor and cursor attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN let you access useful information about the execution of an INSERT statement.

An INSERT statement might insert one or more rows or no rows. If one or more rows are inserted, you get the following results:

•	%NOTFOUND yields FALSE

•	%FOUND yields TRUE

•	%ROWCOUNT yields the number of rows inserted

If no rows are inserted, you get these results:

•	%NOTFOUND yields TRUE

•	%FOUND yields FALSE

•	%ROWCOUNT yields 0


INSERT statement examples

The following examples show various forms of INSERT statement:

INSERT INTO bonus SELECT ename, job, sal, comm FROM emp
   WHERE comm > sal * 0.25;
...
INSERT INTO emp (empno, ename, job, sal, comm, deptno)
   VALUES (4160, 'STURDEVIN', 'SECURITY GUARD', 2045, NULL, 30);
...
INSERT INTO dept
   VALUES (my_deptno, UPPER(my_dname), 'CHICAGO');


LOCK TABLE statement

  Examples 	   Related Topics

Description	The LOCK TABLE statement lets you lock entire database tables in a specified lock mode so that you can share or deny access to tables while maintaining their integrity.

Syntax

lock_table_statement ::=
LOCK TABLE table_reference[, table_reference]...
   IN lock_mode MODE [NOWAIT];

table_reference ::=
[schema_name.]{table_name | view_name}[@dblink_name]

Parameters

table_reference	This identifies a table or view that must be accessible when you execute the LOCK TABLE statement.
lock_mode	This parameter specifies the lock mode. It must be one of the following: ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE, SHARE, SHARE ROW EXCLUSIVE, or EXCLUSIVE.
NOWAIT	This optional keyword tells Oracle not to wait if the table has been locked by another user. Control is immediately returned to your program, so it can do other work before trying again to acquire the lock.
Comments	If you omit the keyword NOWAIT, Oracle waits until the table is available; the wait has no set limit. Table locks are released when your transaction issues a commit or rollback.

A table lock never keeps other users from querying a table, and a query never acquires a table lock.

If your program includes SQL locking statements, make sure the Oracle users requesting locks have the privileges needed to obtain the locks. Your DBA can lock any table. Other users can lock tables they own or tables for which they have a privilege, such as SELECT, INSERT, UPDATE, or DELETE.


LOCK statement examples

The following statement locks the accts table in shared mode:

LOCK TABLE accts IN SHARE MODE;


LOOP statements

  Examples 	   Related Topics

Description	LOOP statements execute a sequence of statements multiple times. The loop encloses the sequence of statements that is to be repeated. PL/SQL provides the following types of loop statements:

•	loop

•	FOR loop

Syntax

basic_loop_statement ::=
[<<label_name>>]
LOOP
   seq_of_statements
END LOOP [label_name];

while_loop_statement ::=
[<<label_name>>]
WHILE boolean_expression
LOOP
   seq_of_statements
END LOOP [label_name];

for_loop_statement ::=
[<<label_name>>]
FOR index_name IN [REVERSE] lower_bound..upper_bound
LOOP
   seq_of_statements
END LOOP [label_name];

cursor_for_loop_statement ::=
[<<label_name>>]
FOR record_name IN
 {  cursor_name [(cursor_parameter_name[,
      cursor_parameter_name]...)]
  | (select_statement)}
LOOP
   seq_of_statements
END LOOP [label_name];

Parameters

label_name	This is an undeclared identifier that optionally labels a loop. If used, label_name must be enclosed by double angle brackets and must appear at the beginning of the loop. Optionally, label_name can also appear at the end of the loop.  You can use label_name in an EXIT statement to exit the loop labelled by label_name.  You cannot reference the index of a FOR loop from a nested FOR loop if both indexes have the same name unless the outer loop is labeled by label_name and you use dot notation, as follows: label_name.index_name In the following example, you compare two loop indexes that have the same name, one used by an enclosing loop, the other by a nested loop: <<outer>> FOR ctr IN 1..20 LOOP    ...    <<inner>>    FOR ctr IN 1..10 LOOP       IF outer.ctr > ctr THEN ...          ...    END LOOP inner; END LOOP outer;
basic_loop_statement	The simplest form of LOOP statement is the basic (or infinite) loop, which encloses a sequence of statements between the keywords LOOP and END LOOP. With each iteration of the loop, the sequence of statements is executed, then control resumes at the top of the loop. If further processing is undesirable or impossible, you can use the EXIT, GOTO, or RAISE statement to complete the loop. A raised exception will also complete the loop.
while_loop_statement	The WHILE-LOOP statement associates a Boolean expression with a sequence of statements enclosed by the keywords LOOP and END LOOP. Before each iteration of the loop, the expression is evaluated. If the expression yields TRUE, the sequence of statements is executed, then control resumes at the top of the loop. If the expression yields FALSE or NULL, the loop is bypassed and control passes to the next statement.
boolean_expression	This is an expression that yields the Boolean value TRUE, FALSE, or NULL. It is associated with a sequence of statements, which is executed only if the expression yields TRUE.
for_loop_statement	Whereas the number of iterations through a WHILE loop is unknown until the loop completes, the number of iterations through a FOR loop is known before the loop is entered. Numeric FOR loops iterate over a specified range of integers. (Cursor FOR loops, which iterate over the result set of a cursor, are discussed later.) The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP.  The range is evaluated when the FOR loop is first entered and is never re-evaluated. The sequence of statements in the loop is executed once for each integer in the range defined by lower_bound..upper_bound. After each iteration, the loop index is incremented.
index_name	This is an undeclared identifier that names the loop index (sometimes called a loop counter). Its scope is the loop itself. Therefore, you cannot reference the index outside the loop. The implicit declaration of index_name overrides any other declaration outside the loop. So, another variable with the same name cannot be referenced inside the loop unless a label is used, as follows: <<main>> DECLARE    num NUMBER; BEGIN    ...    FOR num IN 1..10 LOOP       ...       IF main.num > 5 THEN  -- refers to the variable num,          ...                -- not to the loop index       END IF;    END LOOP; END main; Inside a loop, its index is treated like a constant. The index can appear in expressions, but cannot be assigned a value.
lower_bound, upper_bound	These are expressions that must yield integer values. The expressions are evaluated only when the loop is first entered.  By default, the loop index is assigned the value of lower_bound. If that value is not greater than the value of upper_bound, the sequence of statements in the loop is executed, then the index is incremented. If the value of the index is still not greater than the value of upper_bound, the sequence of statements is executed again. This process repeats until the value of the index is greater than the value of upper_bound. At that point, the loop completes.
REVERSE	By default, iteration proceeds upward from the lower bound to the upper bound. However, if you use the keyword REVERSE, iteration proceeds downward from the upper bound to the lower bound. After each iteration, the loop index is decremented.  In this case, the loop index is assigned the value of upper_bound. If that value is not less than the value of lower_bound, the sequence of statements in the loop is executed, then the index is decremented. If the value of the index is still not less than the value of lower_bound, the sequence of statements is executed again. This process repeats until the value of the index is less than the value of lower_bound. At that point, the loop completes. An example follows: FOR i IN REVERSE 1..10 LOOP  -- i starts at 10, ends at 1    -- statements here execute 10 times END LOOP;
cursor_for_loop_ statement	A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed. Thus, the sequence of statements in the loop is executed once for each row that satisfies the query associated with cursor_name.
cursor_name	This identifies an explicit cursor previously declared within the current scope. When the cursor FOR loop is entered, cursor_name cannot refer to a cursor already opened by an OPEN statement or an enclosing cursor FOR loop.
record_name	This identifies an implicitly declared record. The record has the same structure as a row retrieved by cursor_name and is equivalent to a record declared as follows: drecord_name cursor_name%ROWTYPE; The record is defined only inside the loop. You cannot refer to its fields outside the loop. The implicit declaration of record_name overrides any other declaration outside the loop. So, another record with the same name cannot be referenced inside the loop unless a label is used. Fields in the record store column values from the implicitly fetched row. The fields have the same names and datatypes as their corresponding columns. To access field values, you use dot notation, as follows: record_name.field_name Select-items fetched from the FOR loop cursor must have simple names or, if they are expressions, must have aliases. In the following example, wages is an alias for the select item sal+NVL(comm,0): CURSOR c1 IS SELECT empno, sal+NVL(comm,0) wages, job ...
cursor_parameter_name	This identifies a cursor parameter; that is, a variable declared as the formal parameter of a cursor. A cursor parameter can appear in a query wherever a constant can appear. The formal parameters of a cursor must be IN parameters.
select_statement	This is a query associated with an internal cursor unavailable to you. PL/SQL automatically declares, opens, fetches from, and closes the internal cursor. Because select_statement is not an independent statement, the implicit SQL cursor does not apply to it.
The syntax of select_statement is like the syntax of select_into_ statement, except that select_statement cannot have an INTO clause.

Comments	You can use the EXIT WHEN statement to exit any loop prematurely. If the Boolean expression in the WHEN clause yields TRUE, the loop is exited immediately.

When you exit a cursor FOR loop, the cursor is closed automatically even if you use an EXIT or GOTO statement to exit the loop prematurely. The cursor is also closed automatically if an exception is raised inside the loop.


LOOP statement examples

The following cursor FOR loop calculates a bonus, then inserts the result into a database table:

DECLARE
   bonus  REAL;
   CURSOR c1 IS SELECT empno, sal, comm FROM emp;
BEGIN
   FOR c1rec IN c1 LOOP
      bonus := (c1rec.sal * 0.05) + (c1rec.comm * 0.25);
      INSERT INTO bonuses VALUES (c1rec.empno, bonus);
   END LOOP;
   COMMIT;
END;


NULL statement

  Examples 	   Related Topics

Description	The NULL statement explicitly specifies inaction; it does nothing other than pass control to the next statement. In a construct allowing alternative actions, the NULL statement serves as a placeholder.

Syntax

null_statement ::=
NULL;

Comments	The NULL statement improves readability by making the meaning and action of conditional statements clear. It tells readers that the associated alternative has not been overlooked, but that indeed no action is necessary.

Each clause in an IF statement must contain at least one executable statement. The NULL statement meets this requirement. So, you can use the NULL statement in clauses that correspond to circumstances in which no action is taken.

Do not confuse the NULL statement with the Boolean non-value NULL; they are unrelated.


NULL statement examples

In the following example, the NULL statement emphasizes that only salespeople receive commissions:

IF job_title = 'SALESPERSON' THEN
   compute_commission(emp_id);
ELSE
   NULL;
END IF;

In the next example, the NULL statement shows that no action is taken for unnamed exceptions:

EXCEPTION
   WHEN ZERO_DIVIDE THEN
      ROLLBACK;
   ...
   WHEN OTHERS THEN
      NULL;
END;


OPEN statement

  Examples 	  Related Topics

Description	The OPEN statement executes the multi-row query associated with an explicit cursor. It also allocates resources used by Oracle to process the query and identifies the result set, which consists of all rows that meet the query search criteria. The cursor is positioned before the first row in the result set.

Syntax

open_statement ::=
OPEN cursor_name [(cursor_parameter_name[,
  cursor_parameter_name]...)];

Parameters

cursor_name	This identifies an explicit cursor previously declared within the current scope and not currently open.
cursor_parameter_name	This identifies a cursor parameter; that is, a variable declared as the formal parameter of a cursor. A cursor parameter can appear in a query wherever a constant can appear.
Comments	Generally, PL/SQL parses an explicit cursor only the first time it is opened and parses a SQL statement (thereby creating an implicit cursor) only the first time the statement is executed. All the parsed SQL statements are cached. A SQL statement must be reparsed only if it is bumped out of the cache by a new SQL statement.

So, although you must close a cursor before you can reopen it, PL/SQL need not reparse the associated SELECT statement. If you close, then immediately reopen the cursor, a reparse is definitely not needed.

Rows in the result set are not retrieved when the OPEN statement is executed. The FETCH statement retrieves the rows. With a FOR UPDATE cursor, the rows are locked when the cursor is opened.

If a cursor is currently open, you cannot use its name in a cursor FOR loop.

If formal parameters are declared, actual parameters must be passed to the cursor. The values of actual parameters are used when the cursor is opened. The datatypes of the formal and actual parameters must be compatible. The query can also reference PL/SQL variables declared within its scope.

Unless you want to accept default values, each formal parameter in the cursor declaration must have a corresponding actual parameter in the OPEN statement. Formal parameters declared with a default value need not have a corresponding actual parameter. They can simply assume their default values when the OPEN statement is executed.

The formal parameters of a cursor must be IN parameters. Therefore, they cannot return values to actual parameters.

You can associate the actual parameters in an OPEN statement with the formal parameters in a cursor declaration using positional or named notation.


OPEN statement examples

Given the cursor declaration CURSOR parts_cur IS SELECT part_num, part_price FROM parts;

the following statement opens the cursor:

OPEN parts_cur;

Given the cursor declarationCURSOR emp_cur(my_ename CHAR, my_comm NUMBER DEFAULT 0)
   IS SELECT * FROM emp WHERE ...

any of the following statements opens the cursor:

OPEN emp_cur('LEE');
OPEN emp_cur('BLAKE', 300);
OPEN emp_cur(employee_name, 150);
OPEN emp_cur('TRUSDALE', my_comm);

In the last example, an actual parameter in the OPEN statement has the same name as its corresponding formal parameter in the cursor declaration. To avoid confusion, use unique identifiers.


OPEN-FOR statement

  Examples 	  Related Topics

Description	The OPEN-FOR statement executes the multi-row query associated with a cursor variable. It also allocates resources used by Oracle to process the query and identifies the result set, which consists of all rows that meet the query search criteria. The cursor variable is positioned before the first row in the result set.

Syntax

open-for_statement ::=
OPEN {cursor_variable_name | :host_cursor_variable_name}
   FOR select_statement;

Parameters

cursor_variable_name	This identifies a cursor variable (or parameter) previously declared within the current scope.
host_cursor_variable_ name	This identifies a cursor variable previously declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.
select_statement	This is a query associated with cursor_variable, which returns a set of values. The query can reference bind variables and PL/SQL variables, parameters, and functions but cannot be FOR UPDATE. The syntax of select_statement is similar to the syntax for select_into_statement, except that select_statement cannot have an INTO clause.
Comments	You can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program. To open the host cursor variable, you can pass it as a bind variable to an anonymous PL/SQL block. You can reduce network traffic by grouping OPEN-FOR statements. For example, the following PL/SQL block opens five cursor variables in a single round-trip:

/* anonymous PL/SQL block in host environment */
BEGIN
   OPEN :emp_cv FOR SELECT * FROM emp;
   OPEN :dept_cv FOR SELECT * FROM dept;
   OPEN :grade_cv FOR SELECT * FROM salgrade;
   OPEN :pay_cv FOR SELECT * FROM payroll;
   OPEN :ins_cv FOR SELECT * FROM insurance;
END;

Other OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. When you reopen a cursor variable for a different query, the previous query is lost.

Unlike cursors, cursor variables do not take parameters. No flexibility is lost, however, because you can pass whole queries (not just parameters) to a cursor variable.

You can pass a cursor variable to PL/SQL by calling a stored procedure that declares a cursor variable as one of its formal parameters. However, remote subprograms on another server cannot accept the values of cursor variables. Therefore, you cannot use a remote procedure call (RPC) to open a cursor variable.

When you declare a cursor variable as the formal parameter of a subprogram that opens the cursor variable, you must specify the IN OUT mode. That way, the subprogram can pass an open cursor back to the caller.


OPEN-FOR statement examples

In the following Pro*C example, you pass a host cursor variable and selector to a PL/SQL block, which opens the cursor variable for the chosen query:

EXEC SQL BEGIN DECLARE SECTION;
   ...
   /* Declare host cursor variable. */
   SQL_CURSOR generic_cv;
   int        choice;
EXEC SQL END DECLARE SECTION;
...
/* Initialize host cursor variable. */
EXEC SQL ALLOCATE :generic_cv;
...
/* Pass host cursor variable and selector to PL/SQL block. */
EXEC SQL EXECUTE
BEGIN
   IF :choice = 1 THEN
      OPEN :generic_cv FOR SELECT * FROM emp;
   ELSIF :choice = 2 THEN
      OPEN :generic_cv FOR SELECT * FROM dept;
   ELSIF :choice = 3 THEN
      OPEN :generic_cv FOR SELECT * FROM salgrade;
   END IF;
END;
END-EXEC;

To centralize data retrieval, you can group type-compatible queries in a stored procedure. When called, the following packaged procedure opens the cursor variable emp_cv for the chosen query:

CREATE PACKAGE emp_data AS
   TYPE GenericCurTyp IS REF CURSOR;
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
   PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp,
                          choice IN NUMBER);
END emp_data;

CREATE PACKAGE BODY emp_data AS
   PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp,
                          choice IN NUMBER) IS
   BEGIN
      IF choice = 1 THEN
         OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL;
      ELSIF choice = 2 THEN
         OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500;
      ELSIF choice = 3 THEN
         OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20;
      END IF;
   END open_emp_cv;
END emp_data;

For more flexibility, you can pass a cursor variable to a stored procedure that executes queries with different return types, as follows:

CREATE PACKAGE BODY emp_data AS
   PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,
                      choice     IN NUMBER) IS
   BEGIN
      IF choice = 1 THEN
         OPEN generic_cv FOR SELECT * FROM emp;
      ELSIF choice = 2 THEN
         OPEN generic_cv FOR SELECT * FROM dept;
      ELSIF choice = 3 THEN
         OPEN generic_cv FOR SELECT * FROM salgrade;
      END IF;
   END open_cv;
END emp_data;


RAISE statement

  Examples 	   Related Topics

Description	The RAISE statement stops normal execution of a PL/SQL block or subprogram and transfers control to the appropriate exception handler.

Normally, predefined exceptions are raised implicitly by the runtime system. However, RAISE statements can also raise predefined exceptions. User-defined exceptions must be raised explicitly by RAISE statements.

Syntax

raise_statement ::=
RAISE [exception_name];

Parameters

exception_name	This identifies a predefined or user-defined exception.
Comments	PL/SQL blocks and subprograms should RAISE an exception only when an error makes it impractical or impossible to continue processing. You can code a RAISE statement for a given exception anywhere within the scope of that exception.

When an exception is raised, if PL/SQL cannot find a handler for it in the current block, the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search. In the latter case, PL/SQL returns an unhandled exception error to the host environment.

Omitting the exception name in a RAISE statement, which is allowed only in an exception handler, reraises the current exception. When a parameterless RAISE statement executes in an exception handler, the first block searched is the enclosing block, not the current block.


RAISE statement examples

In the following example, you raise an exception when an inventoried part is out of stock:

IF quantity_on_hand = 0 THEN
   RAISE out_of_stock;
END IF;


RETURN statement

  Examples 	  Related Topics

Description	The RETURN statement immediately completes the execution of a subprogram and returns control to the caller. Execution then resumes with the statement following the subprogram call. In a function, the RETURN statement also sets the function identifier to the result value.

Syntax

return_statement ::=
RETURN [expression];

Parameters

expression	This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the RETURN statement is executed, the value of expression is assigned to the function identifier.
Comments	Do not confuse the RETURN statement with the RETURN clause, which specifies the datatype of the result value in a function specification.

A subprogram can contain several RETURN statements, none of which need be the last lexical statement. Executing any of them completes the subprogram immediately. However, it is poor programming practice to have multiple exit points in a subprogram.

In procedures, a RETURN statement cannot contain an expression. The statement simply returns control to the caller before the normal end of the procedure is reached.

However, in functions, a RETURN statement must contain an expression, which is evaluated when the RETURN statement is executed. The resulting value is assigned to the function identifier. Therefore, a function must contain at least one RETURN statement. Otherwise, PL/SQL raises the predefined exception PROGRAM_ERROR at run time.

The RETURN statement can also be used in an anonymous block to exit the block (and all enclosing blocks) immediately, but the RETURN statement cannot contain an expression.


RETURN statement examples

In the following example, the function balance RETURNs the balance of a specified bank account:

FUNCTION balance (acct_id INTEGER) RETURN REAL IS
    acct_bal  REAL;
BEGIN
    SELECT bal INTO acct_bal FROM accts WHERE acctno = acct_id;
    RETURN acct_bal;
END balance;


ROLLBACK statement

  Related Topics

Description	The ROLLBACK statement is the inverse of the COMMIT statement. It undoes some or all database changes made during the current transaction.

Syntax

rollback_statement ::=
ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name];

Parameters

ROLLBACK	When a parameterless ROLLBACK statement is executed, all database changes made during the current transaction are undone.
WORK	This keyword is optional and has no effect except to improve readability.
ROLLBACK TO	This statement undoes all database changes (and releases all locks acquired) since the savepoint identified by savepoint_name was marked.
SAVEPOINT	This keyword is optional and has no effect except to improve readability.
savepoint_name	This is an undeclared identifier, which marks the current point in the processing of a transaction.
Comments	All savepoints marked after the savepoint to which you roll back are erased. However, the savepoint to which you roll back is not erased. For example, if you mark savepoints A, B, C, and D in that order, then roll back to savepoint B, only savepoints C and D are erased.

An implicit savepoint is marked before executing an INSERT, UPDATE, or DELETE statement. If the statement fails, a rollback to the implicit savepoint is done. Normally, just the failed SQL statement is rolled back, not the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what is rolled back.


SAVEPOINT statement

  Related Topics

Description	The SAVEPOINT statement names and marks the current point in the processing of a transaction. With the ROLLBACK TO statement, savepoints let you undo parts of a transaction instead of the whole transaction.

Syntax

savepoint_statement ::=
SAVEPOINT savepoint_name;

Parameters

savepoint_name	This is an undeclared identifier, which marks the current point in the processing of a transaction.
Comments	When you roll back to a savepoint, any savepoints marked after that savepoint are erased. However, the savepoint to which you roll back is not erased. A simple rollback or commit erases all savepoints.

If you mark a savepoint within a recursive subprogram, new instances of the SAVEPOINT statement are executed at each level in the recursive descent. However, you can only roll back to the most recently marked savepoint.

Savepoint names can be reused within a transaction. This moves the savepoint from its old position to the current point in the transaction.

An implicit savepoint is marked before executing an INSERT, UPDATE, or DELETE statement. If the statement fails, a rollback to the implicit savepoint is done. Normally, just the failed SQL statement is rolled back, not the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what is rolled back.


SELECT INTO statement

  Examples 	   Related Topics

Description	The SELECT INTO statement retrieves data from one or more database tables, then assigns the selected values to variables or fields. For a full description of the SELECT statement, see Oracle7 Server SQL Reference.

Syntax

select_into_statement ::=
SELECT [DISTINCT | ALL] {* | select_item[, select_item]...}
   INTO {variable_name[, variable_name]... | record_name}
   FROM {table_reference | (subquery)} [alias]
          [, {table_reference | (subquery)} [alias]]...
   rest_of_select_statement;

select_item ::=
{  function_name[(parameter_name[, parameter_name]...)]
 | NULL
 | numeric_literal
 | [schema_name.]{table_name | view_name}.*
 | [[schema_name.]{table_name. | view_name.}]column_name
 | sequence_name.{CURRVAL | NEXTVAL}
 | 'text'} [[AS] alias]

table_reference ::=
[schema_name.]{table_name | view_name}[@dblink_name]

Parameters

select_item	This is a value returned by the SELECT statement, then assigned to the corresponding variable or field in the INTO clause.
variable_name[, variable_name]...	This identifies a list of previously declared scalar variables into which select_item values are fetched. For each select_item value returned by the query, there must be a corresponding, type-compatible variable in the list.
record_name	This identifies a user-defined or %ROWTYPE record into which rows of values are fetched. For each select_item value returned by the query, there must be a corresponding, type-compatible field in the record.
subquery	This is query that provides a value or set of values to the SELECT statement. The syntax of subquery is like the syntax of select_into_ statement, except that subquery cannot have an INTO clause.
alias	This is another (usually short) name for the referenced column, table, or view.
rest_of_select_ statement	This is anything that can legally follow the FROM clause in a SELECT statement.
Comments	The implicit SQL cursor and the cursor attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN let you access useful information about the execution of a SELECT INTO statement.

When you use a SELECT INTO statement to assign values to variables, it should return only one row. If it returns more than one row, you get the following results:

•	/SQL raises the predefined exception TOO_MANY_ROWS

•	returns -1422 (Oracle error code ORA-01422)

•	returns the Oracle error message single-row query returns more than one row

•	%NOTFOUND yields FALSE

•	%FOUND yields TRUE

•	%ROWCOUNT yields 1

If no rows are returned, you get these results:

•	/SQL raises the predefined exception NO_DATA_FOUND unless the SELECT statement called a SQL group function such as AVG or SUM. (SQL group functions always return a value or a null. So, a SELECT INTO statement that calls a group function never raises NO_DATA_FOUND.)

•	returns +100 (Oracle error code ORA-01403)

•	returns the Oracle error message no data found

•	%NOTFOUND yields TRUE

•	%FOUND yields FALSE

•	%ROWCOUNT yields 0


SELECT INTO statement examples

The following SELECT statement returns an employee's name, job title, and salary from the emp database table:

SELECT ename, job, sal INTO my_ename, my_job, my_sal FROM emp
   WHERE empno = my_empno;


SET TRANSACTION statement

  Examples 	   Related Topics

Description	The SET TRANSACTION statement begins a read-only or read-write transaction, establishes an isolation level, or assigns the current transaction to a specified rollback segment. Read-only transactions are useful for running multiple queries against one or more tables while other users update the same tables.

Syntax

set_transaction_statement ::=
SET TRANSACTION
   {  READ ONLY
    | READ WRITE
    | ISOLATION LEVEL {SERIALIZABLE | READ COMMITTED}
    | USE ROLLBACK SEGMENT rollback_segment_name};

Parameters

READ ONLY	This clause establishes the current transaction as read-only. If a transaction is set to READ ONLY, subsequent queries see only changes committed before the transaction began. The use of READ ONLY does not affect other users or transactions.
READ WRITE	This clause establishes the current transaction as read-write. The use of READ WRITE does not affect other users or transactions. If the transaction executes a data manipulation statement, Oracle assigns the transaction to a rollback segment.
ISOLATION LEVEL	This clause specifies how transactions that modify the database are handled. When you specify SERIALIZABLE, if a serializable transaction trys to execute a SQL data manipulation statement that modifies any table already modified by an uncommitted transaction, the statement fails. To enable SERIALIZABLE mode, your DBA must set the Oracle initialization parameter COMPATIBLE to 7.3.0 or higher. When you specify READ COMMITTED, if a transaction includes SQL data manipulation statements that require row locks held by another transaction, the statement waits until the row locks are released.
USE ROLLBACK SEGMENT	This clause assigns the current transaction to the specified rollback segment and establishes the transaction as read-write. You cannot use this parameter with the READ ONLY parameter in the same transaction because read-only transactions do not generate rollback information.
Comments	The SET TRANSACTION statement must be the first SQL statement in your transaction and can appear only once in the transaction.

Only the SELECT INTO, OPEN, FETCH, CLOSE, LOCK TABLE, COMMIT, and ROLLBACK statements are allowed in a read-only transaction. For example, including an INSERT statement raises an exception. Also, queries cannot be FOR UPDATE.


SET TRANSACTION  statement examples

In the following example, you establish a read-only transaction:

COMMIT;  -- end previous transaction
SET TRANSACTION READ ONLY;
SELECT ... FROM emp WHERE ...
SELECT ... FROM dept WHERE ...
SELECT ... FROM emp WHERE ...
COMMIT;  -- end read-only transaction


UPDATE statement

  Examples 	   Related Topics

Description	The UPDATE statement changes the values of specified columns in one or more rows in a table or view. For a full description of the UPDATE statement, see Oracle7 Server SQL Reference.

Syntax

update_statement ::=
UPDATE {table_reference | (subquery)} [alias]
   SET {  column_name = {sql_expression | (subquery)}
        | (column_name[, column_name]...) = (subquery)}
       [, {  column_name = {sql_expression | (subquery)}
           | (column_name[, column_name]...) = (subquery)}]...
   [WHERE {search_condition | CURRENT OF cursor_name}];

table_reference ::=
[schema_name.]{table_name | view_name}[@dblink_name]

Parameters

table_reference	This specifies a table or view, which must be accessible when you execute the UPDATE statement, and for which you must have UPDATE privileges.
subquery	This is a select statement that provides a value or set of values to the UPDATE statement. The syntax of subquery is like the syntax of select_into_statement, except that subquery cannot have an INTO clause.
alias	This is another (usually short) name for the referenced table or view and is typically used in the WHERE clause.
column_name	This is the name of the column (or one of the columns) to be updated. It must be the name of a column in the referenced table or view. A column name cannot be repeated in the column_name list. Column names need not appear in the UPDATE statement in the same order that they appear in the table or view.
sql_expression	This is any expression valid in SQL. For more information, see Oracle7 Server SQL Reference.
SET column_name = sql_expression	This clause assigns the value of sql_expression to the column identified by column_name. If sql_expression contains references to columns in the table being updated, the references are resolved in the context of the current row. The old column values are used on the right side of the equal sign. In the following example, you increase every employee's salary by 10%. The original value of the sal column is multiplied by 1.1, then the result is assigned to the sal column. UPDATE emp SET sal = sal * 1.1;
SET column_name = subquery	This clause assigns the value retrieved from the database by subquery to the column identified by column_name. The subquery must return exactly one row and one column.
SET (column_name[, column_name]...) = subquery	This clause assigns the values retrieved from the database by subquery to the columns in the column_name list. The subquery must return exactly one row, which includes all the columns listed in parentheses on the left side of the equal sign. The column values returned by subquery are assigned to the columns in the column_name list in order. Thus, the first value is assigned to the first column in the column_name list, the second value is assigned to the second column in the column_name list, and so on. In the following correlated query, the column item_id is assigned the value stored in item_num, and the column price is assigned the value stored in item_price: UPDATE inventory inv  -- alias    SET (item_id, price) = (SELECT item_num, item_price        FROM item_table       WHERE item_name = inv.item_name);
WHERE search_condition	This clause chooses which rows to update in the database table. Only rows that meet the search condition are updated. If you omit the search condition, all rows in the table are updated.
WHERE CURRENT OF cursor_name	This clause refers to the latest row processed by the FETCH statement associated with the cursor identified by cursor_name. The cursor must be FOR UPDATE and must be open and positioned on a row. If the cursor is not open, the CURRENT OF clause causes an error.
If the cursor is open, but no rows have been fetched or the last fetch returned no rows, PL/SQL raises the predefined exception NO_DATA_FOUND.

Comments	You can use the UPDATE WHERE CURRENT OF statement after a fetch from an open cursor (this includes implicit fetches executed in a cursor FOR loop), provided the associated query is FOR UPDATE. This statement updates the current row; that is, the one just fetched.

The implicit SQL cursor and the cursor attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN let you access useful information about the execution of an UPDATE statement.

An UPDATE statement might update one or more rows or no rows. If one or more rows are updated, you get the following results:

•	%NOTFOUND yields FALSE

•	%FOUND yields TRUE

•	%ROWCOUNT yields the number of rows updated

If no rows are updated, you get these results:

•	%NOTFOUND yields TRUE

•	%FOUND yields FALSE

•	%ROWCOUNT yields 0


UPDATE statement examples

In the following example, a 10% raise is given to all analysts and clerks in department 20:

UPDATE emp SET sal = sal * 1.10
   WHERE (job = 'ANALYST' OR job = 'CLERK') AND DEPTNO = 20;

In the next example, an employee named Ford is promoted to the position of Analyst and her salary is raised by 15%:

UPDATE emp SET job = 'ANALYST', sal = sal * 1.15
   WHERE ename = 'FORD';


Glossary

A

    action
    active window/view
    Ada
    aggregation
    alert
    alignment
    alias
    All tool
    ancestor
    ancestor group
    anchor
    anchor point
    anonymous block
    antecedent
    applet
    application
    argument
    ASCII
    attached library
    attribute
    attribute columns
    attribute mask
    Attribute tool
    attribute value
    axis chart

 B

    background element
    background process
    baseline value
    Batch
    batch compile
    bind reference
    bind variable
    block
    body
    boilerplate
    border
    bounding box
    break chart
    break column
    break group
    break report
    breakpoint
    builder
    built-in datatype
    built-ins
    built-in macro
    buffer
    button
    button procedure

C

    cache (memory)
    call interface
    call stack
    canvas
    canvas view
    cartridge
    cascading style sheet
    category
    category axis
    cell
    CGI
    CGM
    CHAR datatype
    chart element
    chart item
    chart template
    check box
    checksum
    child
    child query
    classification
    clause
    client
    client/server architecture
    Clipboard
    CMDFILE
    collection type
    color palette
    column
    column object
    command line
    comment
    commit
    comparison operators
    compile
    component
    concurrency
    condition
    confine mode
    connect
    connect string
    constraint
    construct
    context area
    continuous axis
    control point
    coordinate plane
    coordinate system
    coordinates (x, y)
    copy
    correlated subquery
    cross product
    crosstab
    cursor
    custom measure
    cut

D

    damage
    data dictionary
    data link
    data model
    data structure node
    database
    datasource
    datatype
    DATE
    date axis
    debug
    debug action
    debug trigger
    Debugger
    deconstructed object
    default
    deliver
    dependency tree
    dependency view
    dependent data
    descendant
    Designer
    Designer/2000
    detail query
    dialog box
    DIANA
    dimension
    dimension value
    disabled
    discrete axis
    display
    display item
    downward-growing
    drag
    drill
    duplicate
    dynamic saved selection

E

    EBCDIC
    edge
    Edge/Fill/Text Display
    editor
    element
    element type
    Ellipsis button
    enabled
    enclosing object
    encryption
    environment
    exception
    Exception tool
    executable argument
    execute
    export
    export file
    expression
    external PL/SQL library
    external query

F

    family
    Family tool
    field
    field template
    file node
    fill area
    filter
    firewall
    flattened object
    flex mode
    focus
    foreground element
    foreign key
    form layout
    form letter layout
    format mask
    format trigger
    formula
    formula column
    frame
    function

G

    generate
    Generate
    Global Registry
    global variable
    grant
    gridlines
    group
    group filter
    group tree
    GUI

H

    handle
    header
    heading node
    hidden
    hierarchy
    HTML
    HTTP
    hyperlink
    hypertext

I

    ICD
    icon
    image
    implied item
    import
    independent data
    index
    inherit
    inner bounding box
    input item
    insert mode
    internet
    Interpreter script
    intranet
    IP (Internet Protocol) Address
    item
    item node

J

    JAR
    Java
    join
    justification

K

    keyword

L

    label
    Launcher
    level
    Level tool
    layout
    layout unit
    lexical reference
    library
    link
    link file
    list box
    list item
    list of values
    List tool
    local database
    local registry
    local variable
    logical operators
    logical page
    loop

M

    macro
    mailing label layout
    margin
    master/detail layout
    Match tool
    matrix cell
    matrix layout
    matrix report
    measure
    megabyte (Mb)
    message box
    method
    modal window
    modeless window
    module
    multi-line field

N

    NA value
    NCA
    nested matrix
    NOCACHE
    node
    non-caching column
    NULL value
    numeric operators

O

    object
    object column
    object group
    object handle
    Object Navigator
    object table
    object type
    ODBC
    OLE
    OLE container
    OLE Server
    Oracle datatype
    Oracle Designer
    ORACLE_HOME
    organization node
    origin
    outer bounding box
    overflow

P

    packaged function
    package
    package body
    package specification
    page
    page break
    palette
    pane
    panel
    parameter
    parameter list
    parent
    paste
    pattern
    Pcode
    PDF
    physical page
    plot type
    PL/SQL
    pld file
    pll file
    pls file
    plx file
    pop-up list
    port
    pre-defined action
    pre-defined type
    preference
    preview
    Previewer
    primary key
    primitive
    printer control code
    prt file
    procedure
    program unit
    project
    project file
    project item
    Project Navigator
    project node
    project registry
    project view
    Project Wizard
    property
    property class
    protocol

Q

    qualifying dimension
    query
    query filter function
    quit

R

    radio button
    radio group
    Range tool
    RC4
    RDBMS
    rdf file
    rep file
    read consistency
    record
    record group
    REF column
    reference line
    refresh
    register
    registry
    relational operator
    remote database
    repeating frame
    replace mode
    report-level objects
    Reports Background Engine (RWRBE60)
    Reports Queue Manager (RWRQM60)
    Reports Server (RWMTS60)
    Reports Web Cartridge (RWCGI60)
    rex file
    RGB
    root
    root node
    row
    row object
    RTF
    run
    runfile
    Runtime
    Runtime Parameter Form

S

    saved selection
    Saved Selection tool
    schema
    scope
    script
    section
    SELECT statement
    selection
    selection script
    Selector
    Selector tool
    session
    sibling
    socket
    Sort tool
    source
    specification
    split bar
    SQL
    sql file
    SQL script
    SQL statement
    stack
    statement
    static saved selection
    stored program unit
    stored subprogram
    style sheet
    subprogram
    subproject
    subproject node
    summary column
    SYLK
    syntax
    system variable

T

    table
    table type
    tabular
    target
    target node
    TCP
    tear-off menu
    text item
    title bar
    title page
    toggle
    tool
    toolbar
    tool palette
    Top/Bottom tool
    trailer
    transaction
    trigger
    type

U

    update
    URL
    user exit
    user-defined action
    user-defined type
    user-defined macro
    user registry

V

    value axis
    value construct
    variable
    varying array type
    VBX
    VBX control
    view
    virtual directory
    virtual file system
    visual attribute

W

    Web
    Web browser
    Web cartridge
    Web link
    Web server
    wildcard
    window
    WKS
    WRB
    WWW

X

Y

Z

    zoom


In Project Builder, a command string supplied either by Project Builder or by the user that applies to files of a given type.  An action is not restricted to a single file type; for example, if the action "Compile" is defined for both forms and C source files, selecting "Compile Project" will compile all .fmb and .c files using the appropriate tools.  See also:  pre-defined action, user-defined action.


A high-level programming language developed by the U.S. Department of Defense for use in embedded computer systems.


A window or view that can receive user input.


(Oracle Express) The consolidation of data for several dimension values into a single value, such as the total units sold for all the cities, or into a smaller set of values, such as the average units sold for cities in each region. Data is often collected at the lowest level of detail and is aggregated into higher level totals for analysis.


A modal window that notifies you of a condition that occurred because of your last action.  You must respond to an alert.


The way in which data is positioned in a field.  It may be positioned to the left, right, center, flush/left, flush/right, or flush/center of the defined width of a field.


A secondary name used in a SQL statement to reference a table, view, or column.


(Oracle Express) A Selector dialog box tool that allows you to select all of the values in a dimension, or all of the values in a dimension for a specific hierarchy.


(Oracle Express) A dimension value at any level above a particular value in a hierarchy. The ancestor value is the aggregated total of the values of its descendants.  The following example shows the dimension value "Europe" as the ancestor of "France," "Lyon," and "Paris" in the GEOGRAPHY dimension.


A group that owns another group, either directly or indirectly.


A layout object used to fix a spot on one object to a spot on another object, ensuring the position of the first object in relation to the second object.


(Oracle Express) A specific dimension value that is used when selecting dimension values based on their family relationship or when navigating through a hierarchy.


A PL/SQL program unit that has no name and does not require the explicit presence of the BEGIN and END keywords to enclose the executable statements.  Since they are unnamed, anonymous blocks cannot be referenced by other program units.


An object that appears in another object's group tree at any position above that other object.  Every object is an antecedent of its descendants.


A Java term for small programs that can be dynamically imported into Web pages or applications as needed.


One or more program modules used to achieve a specific result.  Applications can be nested within other applications.  For example, an application to control a company's inventory could consist of various Form Builder applications for input of data, and various Graphics Builder applications to display the data visually.


1. An optional expression passed to a program unit for the program unit to operate on.
2. Clauses containing keywords and their specified values.


Acronym for American National Standard Code for Information Interchange.


A read only collection of PL/SQL subprograms, usually provided with an application to supply necessary PL/SQL source.


1. See property.
2.  A part or component of an Oracle8 object type definition.  Each attribute has a name and an Oracle8 datatype.  When an object table is created based on the object type, the columns in the table correspond to the attributes in the object type definition.  When a column object in a relational table is created based on the object type, the column object contains the equivalent of sub-columns, each of which corresponds to an attribute in the object type definition.  In Oracle Developer, each Oracle8 object attribute in a table is treated as a separate data item, as if it were a separate column.  This is true for both object tables and column objects in relational tables.
3. (Oracle Express) A descriptive characteristic that is shared by dimension values. Attributes represent logical groupings that allow users to select data based on like characteristics. For example, in a database representing footwear, you can use a shoe color attribute to select all boots, sneakers, and slippers that share the same color.


The columns that compose a column object (Oracle8).


In Graphics Builder, the first element in an attribute descriptor.  It is a 32-bit mask that informs the toolkit of the attributes that will be set or retrieved by a subsequent function call.  Only those elements that have their corresponding bits set in the attribute mask are set or retrieved.


(Oracle Express) A Selector dialog box tool that allows you to select dimension values based on attributes and attribute values. For example, in a database representing soft drinks, you might select all products whose size is 1 liter. In this example, Size is an attribute and 1 liter is an attribute value.


(Oracle Express) The values in the database that make up an attribute. For example, in a database representing ice cream products, the following attribute values might belong to a flavor attribute in a Product dimension: vanilla, chocolate, and strawberry. Dimension values at various levels in the Product dimension -- brand, form, and size, for instance -- might share the same value for flavor.


A type of chart in which the data is plotted according to where it lies along the chart's edges, called axes.  An axis chart may contain two or three axes, each of which may be one of the following types:  discrete, continuous, or date.


The white part of a pattern.  You apply color to the background element using the Fill Color palette.


A non-interactive process that runs in an operating system environment and performs some service or action.  Certain Oracle products use background processes for different tasks, such as performing and coordinating tasks on behalf of concurrent users of the database, processing and delivering electronic messages, and managing printing services.


The value used as the starting point for plotting fields on the continuous axis of a chart.


The executable that executes an application in batch mode, without using a windowing system.


To translate, at once, several PL/SQL program units into a machine executable form.


A reference to a parameter used to replace a single literal value (e.g., a character string, number, or date) appearing anywhere in a PL/SQL construct or a SQL SELECT statement.  For a bind reference, you must precede the parameter name with a colon (:).


A global variable you can create and use outside of a PL/SQL package.


1. The basic program unit in PL/SQL, defined by the keywords DECLARE, BEGIN, EXCEPTION, and END.
2. In Form Builder, a logical container that relates to a database table or view.


1. Contains the same information as the program unit specification, and also includes the actual implementation of the subprogram (i.e., its sequence of actions).  In most cases, only a body must be defined for a subprogram.
2. In Report Builder, a region that contains the bulk of the report layout (text, graphics, data, and computations).  The report body appears between the report header and trailer pages.


Text and/or graphics that appear in a report every time it is run.  In some products this is called "constant" text or graphics.


A user-defined outline around a layout object, usually used to highlight its contents.  Borders are drawn such that half the width of the border lies outside the edge of the object, and half inside the edge.


See inner bounding box, outer bounding box.


An axis chart that displays two categories of data along one axis.


A query column assigned to a break group.


A group used to break or categorize report data.  All data in query columns assigned to a break group appear above the data in columns belonging to other groups.  Each record fetched for a break group appears only once, while multiple related records in other groups can be displayed.


A report that contains categories that break the report data into sets.


A debug action you can place at any source in a PL/SQL subprogram that interrupts execution of the program unit and passes control to the PL/SQL Interpreter.  Breakpoints allow you to incrementally execute a subprogram and search for runtime errors.


Refers to a component of Oracle Developer used to create a specific type of document; for example, Form Builder is used to create forms, while Report Builder is used to create reports.


A datatype defined and supported by the Oracle Server.


A collection of extensions to the PL/SQL language that are designed to be used with Oracle Developer.  Built-ins include datatypes, procedures, functions, exceptions, and constants.


In Project Builder, macro shipped with Project Builder.  See also:  macro.


A temporary storage area for data during the transfer of that data between the computer and a peripheral, or between parts of a computer.  A buffer prevents loss of information due to differences in the speed or timing of the transfer and speeds up certain functions.


1. An interface element used to select an action item, display a dialog box, or acknowledge the current condition.
2. An object in an application that has a PL/SQL procedure associated with it.  The procedure is generally executed upon a user mouse event.


A PL/SQL procedure associated with an application object, that is executed when a specific mouse event occurs on the item at runtime (e.g. clicking the button).  It does this by establishing a hierarchy for the fetched data containing a break group.


A temporary storage place for database data that is currently being accessed or changed by users, or for data that Oracle Server requires to support users. The terms are often used interchangeably.


An interface that enables you to invoke Oracle Developer component executables from other Oracle products, or a 3GL program.


A chain of subprogram calls, from the initial entry point down to the currently executing subprogram.  Each subprogram call is represented by a frame on the downward-growing call stack, in which newly entered subprograms are added to the bottom of the stack.


     See editor.


In Form Builder, the background object on which you place the interface objects that users will interact with in the runtime application.  A canvas view is usually associated with a window.


Manageable objects that plug in to the client, application server, or database through an open API.


See style sheet.


A label component that allows differentiation between information on the basis of non-hierarchical delineation or compartments rather than sensitivity levels.  With classification, they are used as a means of restricting access to information.


See discrete axis.


An instance of data in a spreadsheet (e.g., Microsoft Excel).


Acronym for Common Gateway Interface. The industry-standard technique for running applications on a Web server.  Whereas standard HTML documents retrieved from a Web server are static (the exact same text is retrieved every time) CGI enables a program running on the Web server to communicate with another computer to generate "dynamic" HTML documents in response to user-entered information.


Acronym for Computer Graphics Metafile.  A file format recognized internationally and used to store and transport object-oriented graphics from one system or program to another.


An Oracle datatype provided for ANSI/ISO compatibility.  A CHAR column is a fixed-length column and can contain any printable characters, such as A, 3, &, or blanks, and can have from 1 to 255 characters or can be null.


A bar, line, symbol, or other graphical object that represents a single value for a field.


A bordered rectangle of any size used to embed Graphics Builder charts in a form or report.


A collection of attributes and properties that defines the format of a chart.


A interface element, appearing as a small square, that a user can toggle on or off.


An error-checking algorithm.


1. An object that is a member of a group, and is immediately below that group object in its group tree.  The objects that compose a group object are children of that group.  Every object is a child of its parent.
2. (Oracle Express) A dimension value at the level immediately below a particular value in a hierarchy. Values of children are included in the calculation that produces the aggregated total for a parent.  A dimension value may be a child for more than one parent if the dimension has more than one hierarchy.  The following example shows the dimension values "Lyon" and "Paris" as the children of "France" in the GEOGRAPHY dimension.


See detail query.


A hierarchical level that denotes the sensitivity of the information that is labeled.  Classifications are used as a means of restricting access to information.


A part of a SQL statement that does not constitute the full statement; for example, a "WHERE clause."


A user, software application, or computer that requests the services, data, or processing of another application or computer (the "server").  In a two-task environment, the client is the user process.  In a network environment, the client is the local user process and the server may be local or remote.


Separation of processing between two CPUs, one acting as the server that provides services in a transaction and the other as the client in the transaction, requesting and receiving services.  All responsibilities of shared data management can be processed by the computer running the database management system while the workstations running the database application concentrate on the interpretation and display of data.


A memory buffer.  An object remains on the Clipboard until you cut or copy another object, or until you quit the application.


A user-defined datatype that describes a data unit made up of an indefinite number of elements, all of the same datatype.  Varying array types and table types are collection types.  (Oracle8)


An argument that allows you to specify a file that contains a set of command line arguments for Oracle Developer executables.


Contains all the colors available to the windowing system, the drawing surface, or a window and its views.


1. A vertical space in a database table that represents a particular domain of data.  A column has a column name (e.g., ENAME) and a specific datatype (e.g., CHAR).  For example, in a table of employee information, all of the employees' names would constitute one column.  A record group column represents a database column.
2. In Report Builder, a data model object created automatically for each column expression in a query's SELECT list, or created manually to perform summaries, formulas, or act as a placeholder.


In an Oracle8 relational table or object table, a column whose definition is based on an object type.  The column is a composite, the parts of which correspond to the attributes of the object type.


An operating-system command line.  Most Oracle products can be invoked from a command line using a number of executable arguments.


A language construct for the inclusion of explanatory text in a program, the execution of which remains unaffected.


To make changes to data in the database permanent.  Before changes are stored, both the old and new data exist so that changes can be stored or the data can be restored to its prior state.


(Oracle Express)

>	Greater than

<	Less than

=	Equal to

<=	Less than or equal to

>=	Greater than or equal to

<>	Not equal to

For numbers, the operands are widened to the least common type (Integer is preferred over Long, which is preferred over Single, which is preferred over Double). For Strings, the comparison is case-sensitive and is based on the collating sequence used by the language specified in the Windows Control Panel. The result is 0 for FALSE and -1 for TRUE.


To translate a source program into a binary, executable format.  PL/SQL program units must be compiled before they can be executed.


A specific target object that is a part of a larger application; e.g. a form in an order entry system, or a library that is shared among many other elements.  See also:  target.


The simultaneous access of the same data by multiple users executing different database applications or the same application.  In database software concurrency requires additional logic to ensure that all users see consistent data and that all changes are made in the proper order.


An expression whose value is either true or false, such as X> 100.  For example, you can set a condition in a query when you want the query to display only those rows that evaluate as true in your expression.


Layout editor mode that prevents child objects from being moved or resized on or outside their parent objects.


To log on to a database.  You must connect if you want to create or modify queries or access an application stored in a database.


The set of parameters, including a protocol, that SQL*Net uses to connect to a specific Oracle instance on the network.


A rule or restriction concerning a piece of data (such as a NOT NULL restriction on a column) that is enforced at the database level, rather than the object or application level.


A PL/SQL code structure.  There are two types of constructs: non-value constructs do not return a value; value constructs return a value.


A work area in which Oracle Server stores the current SQL statement, and if the statement is a query, the result's column headings and one row of the result.  Also referred to as cursor.


An axis that plots values that begin with one value and continue until they reach another value.  Thus, these values are mathematically related.  Dependent data is usually plotted along this axis, also called the value axis.


The four corner points of the virtual rectangle that constitutes an object's ideal shape.  When you select an object on the layout, its control points appear.  Depending on the object selected, you may be able to modify it by dragging a control point.


The two-dimensional grid on which drawing takes place.


The type of x, y units used to place and size objects in the Layout Editor view.  The coordinate systems available are 1) character, which uses the platform's native character cell size, and 2) real, which can be in units of pixels, centimeters, inches, or points.


The location points for objects appearing in the layout.  x and y coordinates determine the horizontal (x) and vertical (y) placement of objects.


To store a replica of a selected object on the Clipboard, so that it may be pasted elsewhere in an editor if desired.


A subquery based on the same tables as the parent query (i.e., references columns in the tables included in its parent query).


Group that owns two or more other groups and performs a mathematical cross product (i.e., correlates values between them).


See matrix cell.


1. A small icon representing the position of the mouse.  The shape of the cursor varies, depending on the selected tool.
2. An internal pointer to data retrieved by a query.  A cursor points only to one row of data at a time; however, you can use built-in subprograms to move the cursor to any row in the data set.


(Oracle Express) A formula created by a user, which allows fine-tuning of data analysis. You create custom measures using base measures in the database. For example, if the database contains the existing measure Volume Share, you can use this measure to create a custom measure named Volume Share Year Ago. This custom measure would allow you to look at a product's absolute share from the previous year.


To delete one or more objects and store them in the Clipboard, so that they may be pasted elsewhere in an editor, if desired.


A view is damaged when it is totally or partially occluded by another view.


A set of tables and views owned by the database administrator.  It is a central source of information for Oracle Server and other relational databases.


A data model object used to define a master/detail (parent-child) relationship between a group and a query.


A relational model that defines what data should be fetched from the database, what values should be computed, and how data should be ordered in a report.  Report Builder objects that define the data model are queries, groups, columns, parameters, and links.


A source for data returned by a query, including database objects such as tables, views, synonyms, snapshots, and queries stored as views.


In Project Builder, a node in the Project Navigator denoting a project item that governs the structure of an open project.  For example, the Global Registry node denotes the Global Registry, which contains a list of local registries and a list of global type definitions.


A set of dictionary tables and user tables that are treated as a unit.


A standard form of data.  Some common Oracle datatypes are CHAR, VARCHAR2, DATE, NUMBER, LONG, RAW, and LONG RAW.


An Oracle Server datatype.  A date column may contain a date and time between January 1, 4712 BC and December 31, 4712 AD.


An axis that plots date values that begin at one date and continue until they reach another date.


To detect, diagnose, and eliminate runtime errors in programs.


A user-specified event placed in a PL/SQL subprogram, usually for the purpose of troubleshooting runtime errors.


A conditional debug action you can place at any source line in a PL/SQL subprogram.  If the specified condition is met, the specified action is taken before execution of the subprogram resumes.


The Procedure Builder functionality within an Oracle Developer component used for testing and debugging.


In Oracle8, object types and objects are composites, built of parts or attributes.  Oracle Developer treats each part/attribute as an independent, individually-selectable data item.  This treatment results in a deconstructed object (sometimes also known as a flattened object).  The object is not an homogeneous entity within the Oracle Developer environment.


A piece of data that depends on other data for its value.  For example, an employee's  years of service may have the value 10, which is dependent upon the value of the employee's name.  Also called value data.


A value supplied by the system when a user does not specify a required command parameter or attribute.


To prepare and provide a completed application for distribution and deployment.


In Project Builder, the mechanism used by Project Builder to determine how to compile files that are referenced in projects.  Items toward the top left of the tree depend on items toward the lower right; so, for example, if a header file is either out of date or more recent than the executable file it is used to build, then the Project Builder will rebuild the executable file, given a Build command.  Dependencies also extend to subprojects.  For example, if a subproject consisting of a function library has been rebuilt, the Project Builder "knows" the dependent project will need to be rebuilt in order to use the updated library functions.  See also:  dependency view.


In Project Builder, shows files in the Project Navigator in the order in which they depend on each other, with project nodes at the highest point in the hierarchy, followed by target nodes, which are followed by buildable components of those targets.  For example, an executable form depends on and will be followed by an .fmb file, which may depend on and be followed by a library used for a USEREXIT procedure, and so on.  See also: project view, target.


1. An object that appears in another object's group tree at any position below that other object.  Every object is a descendant of its antecedents.
2. (Oracle Express) A dimension value at any level below a particular value in a hierarchy. Values of descendants are included in the calculation that produces the aggregated total for an ancestor.  The following example shows the dimension values "France," "Lyon," and "Paris" as descendants of "Europe" in the GEOGRAPHY dimension.


The interface used to design different components of an Oracle Developer application.


See Oracle Designer.


An application modelling, design and generation toolset.  Using Oracle Designer, you can generate forms and menus from module definitions recorded in the Oracle Repository.


When defining a master/detail report, the detail query retrieves all related records for each record retrieved by the master, or parent, query.


A partial screen or window that prompts you to enter information necessary to complete an operation.


An intermediate representation of a program unit, generated by the compiler.  The DIANA includes all of the syntactic and semantic information for a compiled program unit.  In order to compile a reference to a program unit, the compiler must have access to the referenced program unit's DIANA.  For example, if program unit A calls program unit B, the DIANA for B must be available before A can be compiled.


(Oracle Express) A type of database object that is a list of values that provide categories for data. A dimension acts as an index for identifying values of a variable. For example, if you have sales data with a separate sales figure for each month, then the data has a MONTH dimension; that is, the data is organized by month. A dimension is similar to a key in a relational database.

Any item of data within a multidimensional variable can be uniquely and completely selected by specifying one member from each of the variable's dimensions. For example, if a sales variable is dimensioned by MONTH, PRODUCT, and MARKET, specifying "January" for the MONTH dimension, "Stereos" for the PRODUCT dimension, and "Eastern Region" for the MARKET dimension uniquely specifies a single cell in the variable. Thus, dimensions offer a concise and intuitive way of organizing and selecting data for retrieval, updating, and performing calculations.

A dimension can be simple, with values that are single text or integer values, or it can be conjoint, with values that are combinations of values in other dimensions. A composite is not a dimension, but it is a conjoint-like internal object that Express treats like a dimension for the purpose of handling sparse data.


(Oracle Express) One element in the list that makes up a dimension. For example, a computer company might have dimension values in the PRODUCT dimension called "LAPPC" and "DESKPC." Values in the GEOGRAPHY dimension might include "Boston" and "Paris." Values in the TIME dimension might include "MAY96" and "JAN97."


An interface element state that means a menu item, button, etc., cannot be used in the current context; i.e., it does not respond to keyboard or mouse input.


An axis that plots distinct values at fixed intervals.  These values are not mathematically related.  Independent data is usually plotted along this axis, also called the category axis.


A Graphics Builder document containing any combination of charts, images, text objects and shapes.


In Form Builder, an item that stores or displays information that must be fetched or assigned programmatically with PL/SQL.


As it pertains to the call stack, in which newly entered subprograms are added to the bottom of the stack.  The earliest frame, corresponding to the initial program entry point, is at the top of the stack, while the latest frame, associated with the most deeply nested subprogram call, is at the bottom of the stack.


Press and hold down a mouse button while you slide the mouse pointer to a particular location in a window.


(Oracle Express) To navigate up and down through the levels of aggregation in a dimension that has a hierarchy. When selecting dimension values or viewing data, you can expand or collapse a dimension hierarchy by drilling down or up in it. Drilling down expands the view to include child values that are associated with parent values in the dimension hierarchy. Drilling up collapses the list of descendant values associated with a parent value in the dimension hierarchy.

You can click on a plus icon ( [image]) to drill down on (expand) the hierarchy or on a minus icon ( [image]) to drill up on (collapse) the hierarchy.

In the Selector, you can click on the drill icon ( [image]) to display the Drill dialog box so you can select a level of detail for the entire dimension.


An option that allows you to copy objects directly on the layout without affecting the contents of the Clipboard.


(Oracle Express) A saved selection that can have different dimension values each time it is evaluated. The selected dimension values might differ from use to use. In contrast, a static saved selection is an unchanging list of dimension values. A dynamic saved selection script might include "Select the last three Months", while the script for a static saved selection might have "Select February 1996, March 1996, and April 1996".


Acronym for Extended Binary-Coded Decimal Interchange Code.


The boundary of an object.


A display box on the Layout Editor tool palette that shows the current settings for the edge, fill, and text patterns and colors.


A work area in which you perform a specific set of tasks, such as creating a program unit or designing an application.


A thing of significance about which you may need to record information.  Elements are described by attributes and associations to other elements.


Any element held in the dictionary is classified as being of a particular type.  Examples of element type are entity, attribute, program module, table, diagram, text, softbox.  Occurrences or instances of these are called elements.


(Oracle Express) Displays a dialog box related to the task you are performing, usually so you can select one or more values that you need.


An interface element state that means that a menu item, button, etc., can be used in the current context, that is, it responds to keyboard or cursor/mouse input.


An object that contains another object.  An object is considered to be enclosed by another object only if all of the following are true:  both objects belong to the same region (Body, Margin, Header, or Trailer);  the outermost of the two objects is a frame or repeating frame;  the outermost of the two objects is behind the other object;  the innermost of the two objects lies entirely within the borders of the other object.


The practice of scrambling (encrypting) data in such a way that only an intended recipient can unscramble (decrypt) and read the data.


The user's computer-based workplace, including the tools typically used and their configurations. For Project Builder, this refers to the subset of tools that are used to complete various tasks related to developing Oracle Developer applications.


A warming or error condition generated from a PL/SQL subprogram.


(Oracle Express) A Selector dialog box tool that allows the user to select dimension values associated with data that meets cutoff terms that the user specifies. For example, the user can select all products whose dollar sales were greater than $10,000 for the week ending December 23, 1995 in the Boston market.


An argument you can pass to a product executable, such as `OPENFILE=my_disp' and `PRINT=YES'.  These arguments usually are specified when the executable is invoked from a command line.


See run.


To store a copy of an object, module, selected text or image to a file or database.  In Project Builder, the process of writing out a file containing project, type, action, and/or macro definitions in a portable format for distribution to others who may work on heterogenous platforms.  See also:  export file, import.


In Project Builder, the shareable, portable file created by exporting a project.


1. A PL/SQL construct combining variables, constants, literals, and operations on their values.
2. (Oracle Express) One or more data values that are specified in an Express language program. For example, you can specify expressions as arguments to programs or functions. An expression can be any of the following:

n	A single, literal value (for example, 10)

n	A variable or formula that contains one or more values

n	A function that returns one or more values

n	A calculation that combines values, such as variables, formulas, or functions, with arithmetic or Boolean operators


See library.


An ANSI-standard SQL SELECT statement that can be referenced by other Oracle products.


(Oracle Express) A group of related dimension values that correspond to the levels of the dimension hierarchy. Dimension values within a family can relate to each other as ancestor, parent, child, and descendant.  The following example shows family relationships for the dimension value "France" in the GEOGRAPHY dimension.


(Oracle Express) A Selector dialog box tool that allows the user to select a family of dimension values for a particular hierarchy. A family includes a parent and all of its descendant dimension values. For example, the user can select all of the family members below the Chicago market level in the Geography dimension.


1. An interface element in which you enter, edit, or delete data.  2. In Graphics Builder or Report Builder, a layout object that define how the data for a specific query column appears.


In Graphics Builder, a collection of attributes and properties that determines how dependent data (values) are represented as chart fields, such as bars or lines.


In Project Builder, a node in the Project Navigator denoting a file stored in the file system.  For example, the global and local registry nodes correspond to global and local registry files.


The area within an object to which a fill (color or pattern) should be applied.


A PL/SQL function or anonymous function that restricts the data displayed by a group.


A computer that regulates access to computers on a local area network from outside, and regulates access to outside computers from within the local area network.


See deconstructed object.


Layout Editor mode that automatically resizes or shrinks parent objects when child objects are moved or resized, and adjusts the positions of other layout objects that might otherwise be affected by the repositioning or resizing of the objects.


The state of an entity that is able to respond to input from the user or the client.  If an entity has the keyboard focus, it can receive events when the user presses a key.  If a drawn view has the drawing focus, it can respond to client routines that affect drawing.


The black part of a pattern.  You apply color to the foreground element using the Edge Color palette.


A value or column in one table that refers to a primary key in another table.


In Report Builder, a default layout style in which labels appear to the left of fields, and each record prints on a separate logical page.


In Report Builder, a default layout style that intermixes boilerplate and fields, wrapping lines at "word" breaks.


(Field Properties)  A setting that defines the appearance of the value of a field.  For example, a format mask is used to specify the display of currency amounts and dates.


A PL/SQL function that allows you to dynamically change the formatting attributes of an object.


(Oracle Express) A type of database object that represents a stored calculation, expression, or procedure that produces a value. A formula provides a way to define and save complex or frequently used relationships within the data without resaving the data itself. Each time you use a formula, Express performs the calculation or procedure that is required to produce the value.


A user-created column that gets its data from a PL/SQL function or expression, a user exit, a SQL statement, or any combination of these.


In Graphics Builder, the part of a chart template that defines the basic structure of a chart, but does not determine the way the data is plotted.
2. In Report Builder, a layout object used to enclose other layout objects and control the formatting, frequency, and positioning of several objects simultaneously.


A PL/SQL subprogram that performs a specified sequence of actions, and then returns a value.


To save an application to a file or database in a binary format that can be executed by Runtime and Batch executables.


The Form Builder executable used to save an application to a file or database in a binary format.


A Project Builder registry that stores information common to an entire Oracle Developer installation.  This information is restricted to type definitions, and their associated actions and pre-defined or user-defined properties.  The use of the Global Registry is optional; its functions can be performed by individual user registries.  See also: registry, user registry.


A logical container that exists across an application.  When an application uses a global variable, the application maintains the variable until the application is exited, or until another object explicitly removes it.


To give a user access to a module.  Only a module's creator can grant its access to other users.


In Graphics Builder, the lines that separate rows and columns in the Layout Editor.


1. An object that is composed of several other objects.
2. In Report Builder, a data model object that is created automatically to contain all the columns selected by a query, or created by the user to modify the hierarchy of the data appearing in a report;  it is used primarily for creating breaks in a report, as well as for resetting computations.
3. In Project Builder, collections of related items available via submenus off the Launcher.  Groups allow users to set up the Launcher much like the Windows 95 Start menu, with arbitrary "groups" as entries that pop up to reveal other items and/or groups.  For example, a user could define a group named "Project Docs", containing a project schedule, to-do list, component specifications, etc.; some files may be .txt files, other .doc files.


A PL/SQL function that restricts the data fetched by a group.


A hierarchical model-similar to a "family tree"-that describes the relationships between group objects and the objects that compose them.


Acronym for graphical user interface. The use of pictures rather than just words to represent the input and output of a program.  Programs with GUIs run under a windowing system (such as  X Windows, Microsoft Windows, Apple Macintosh, and so on).  GUI programs display icons, buttons, and so on, in windows on the screen;  users control the GUI programs mainly by moving a pointer on the screen (typically controlled by a mouse).  Also known as a bitmapped interface.


An internal pointer to an application object.  In order to reference an object in a PL/SQL program unit, you must pass its handle as an argument to the appropriate subprogram.


An optional report region that can contain introductory material for the report, including text, graphics, data, and computations.  The report header appears first, before the body and trailer.


A node in the Object Navigator denoting a type of object which can be contained within or associated with a report.  Selecting a heading node does not select an object in the report.


(Field Properties)  A property that specifies whether Report Builder will suppress the indicated field at its location in the layout.  If Hidden is specified, the field's value will appear only where referenced in text objects (using &fieldname).


(Oracle Express) A means of organizing and structuring data. A hierarchy exists when values within a dimension are arranged in levels, with each level representing the aggregated total of the data from the level below. Some dimensions have multiple hierarchies based on them.

The following example shows a hierarchy based on the GEOGRAPHY dimension, in which dimension values are arranged in five levels. Data at the Customers level is aggregated into the Cities level, which, in turn, is aggregated into the Countries/Areas, Continents/Regions, and Global levels.


Acronym for Hypertext Markup Language.  A tag-based ASCII language used to specify the content and links to other documents on Web servers on the Internet.  End users with Web browsers view HTML documents and follow links to display other documents.


Acronym for Hypertext Transfer Protocol. The protocol used to carry WWW traffic between a WWW browser computer and the WWW server being accessed.


A reference (link) from some point in one document to (some point in) another document or another place in the same document.  A Web browser usually displays a hyperlink in some distinguishing way (in a different color, font or style).  When users activate hyperlinks (by clicking on them with a mouse) the browser displays the target of the link.


A collection of documents containing cross-references which, with the aid of a Web browser, allow readers to move easily from one document to another.


Cross-references which, with the aid of a Web browser, allow readers to move easily from one document to another.


Acronym for interface C definition-a PL/SQL application programming interface to functionality implemented in C.


A graphic representation of a window or tool.


A bitmapped object that can be stored and loaded into an application.  The client cannot modify an imported image.


In Project Builder, a project item, usually the result of automatic generation, that Project Builder recognizes and for which it automatically creates entries in the Project Navigator.  For example, Project Builder can recognize .obj files, generated as an intermediate step during the compilation of C source files, and create entries for them in the Project Navigator.  Although resetting the properties of an implied item is of limited use (the next compilation will destroy changes) such items can be useful, as they can be examined via actions such as Edit, View, and Print.  See also: item, action.


1. To read a module from the file system or database, and incorporate it into an application.
2. To read in a file containing project information.


A piece of data that does not depend on other data for its value.  For example, an employee's name may have the value Jones, which is independent of the values of other employee's names or associated data.  Also called category data.


An optional structure associated with a table that is used by Oracle Server to locate rows of the table quickly, and (optionally) to guarantee that every row is unique.


In Project Builder, to obtain information for an action, type, macro, or property definition from an ancestor node in the dependency tree.  If related attributes exist in an ancestor node, they may be inherited.  Thus, filesystem items like forms and documents may inherit action definitions from subprojects, projects, the user registry, or the Global Registry; projects may inherit type definitions from the local or the Global Registry; and so on.


The virtual rectangle that constitutes an object's ideal shape and connects its four control points, regardless of edge thickness or other attribute settings.


The file used to build a target: for instance, an .fmb is the input for an .fmx.  Also called source.


A mode in which each character you enter is inserted at the cursor, pushing the following characters to the right.  The opposite of replace mode.


A worldwide TCP/IP-based network of computers.


A file containing any mixture of PL/SQL source, Procedure Builder commands, and SQL statements.


An internal TCP/IP network, access to which is restricted (via a firewall) to individuals inside the company or organization.  An intranet provides similar services within an organization to those provided by the Internet, but is not necessarily connected to the Internet.  A common example of an intranet is when a company sets up one or more Web servers on an internal network for distribution of information or applications within the company.


A four-part number with no more than three digits in each part that uniquely identifies a computer on the Internet.


In Project Builder, an object in the file system associated with the project, such as a form or a document, pointed to or represented by a node in the Project Navigator.


In Project Builder, an entry in the Project Navigator window, consisting of an icon and a descriptive title.  Nodes point to actual items in the filesystem, such as Form Builder files and documents.  See also: node, item.


Acronym for Java ARchive.  A file used for aggregating many files (Java class files, images, and so on)  into one file.


A computer language that supports programming for the Internet in the form of platform-independent "applets".


Combining data from two (or more) tables in a single SELECT statement.


    See alignment.


1. Part of a command line syntax that must be supplied with a corresponding argument.  2. A required part of a PL/SQL construct.


Text identifying the values or meaning of an application's visual objects.


(Oracle Express) A position in a dimension hierarchy. Each level above the base level represents the aggregated total of the data from the level below. For example, the TIME dimension might have ascending levels such as Month, Quarter, and Year. Within a dimension hierarchy, a dimension value at one level has a family relationship with the dimension values at the levels above and below that level.


(Oracle Express) A Selector dialog box tool that allows the user to select dimension values based on their level in a particular hierarchy. For example, the user can select all of the dimension values at the Key Account level in the Geography dimension.


The area of an editor in which you can create, modify, position, or delete objects.


A unit of measurement for ensuring a consistent measurement system across different display devices.  Used to specify a position on the layout, or the dimensions of an object.


A reference to a parameter used to represent a string of text in a SQL SELECT statement.  For a lexical reference, you must precede the parameter name with an ampersand (&).


1. A collection of one or more PL/SQL program units that are stored together in a file or database, and that can be referenced by several applications at once.
2. (Oracle Express) A collection of saved selections. Use the Library button in the Selector dialog box to add a new selection. Use the Saved Selection tool in the Selector dialog box to view, retrieve, modify, or delete a selection that has been saved in the library.


A data model object used to define a master/detail (parent-child) relationship between a group and a query.


The name of a file to which a boilerplate object is associated.  At runtime, Report Builder imports the file's contents into your report layout.


A list of items within a dialog box.  Usually, the list box offers choices for selection, and it is scrollable.


In Form Builder, an item that displays a list of choices.


Also called LOV. A scrollable popup window that provides the user with a single or multi-column selection list.


(Oracle Express) A Selector dialog box tool that allows you to select dimension values from a list of the currently selected values in the dimension.


1. The database on the computer running the application.  2. The database to which an application is connected.  This database parses and executes all SQL statements generated by the application.


See user registry.


A PL/SQL variable declared only within the scope of the current program unit.


(Oracle Express)

Not	Unary Not -- Operand can be integer or long. The operation is performed bitwise (one's complement).

And	And -- Operands can be integer or long. The operation is performed bitwise.

Or	Inclusive Or -- Operands can be integer or long. The operation is performed bitwise.

Xor	Exclusive Or -- Operands can be integer or long. The operation is performed bitwise.

Eqv	Equivalence -- Operands can be integer or long. The operation is performed bitwise. (A Eqv B) is the same as (Not (A Xor B)).

Imp	Implication -- Operands can be integer or long. The operation is performed bitwise. (A Imp B) is the same as ((Not A) OR B).


One page of your actual report (it may be displayed on one or more physical pages).


In a PL/SQL subprogram, a conditional statement that is repeated until the condition is no longer met.


In Project Builder, a type-specific variable that may be used to customize and extend actions.  A macro may be either a constant or a simple expression (which, in turn, may contain other constants and/or expressions).  The use of macros adds great flexibility in issuing command options, and in allowing the user to modify sets of commands by changing one property definition.


A default layout that displays records in one or more columns on the page.  Fields appear directly beneath each other in a single repeating frame.


An optional report region that appears at the top and bottom of each logical page in the body region.  The margin may include any layout object, but typically contains boilerplate and fields (for page numbers, page totals, grand totals, and current date and time).


A default layout in which the detail records appear below the master records.  Master records appear in form layout style, while detail records appear in tabular layout style.


(Oracle Express) A Selector dialog box tool that allows the user to select dimension values by matching characters in dimension value names to characters that the user specifies. For example, the user can select all dimension values in the Product dimension at the Brand level that contain the characters "new."


A repeat direction that displays each record of a repeating frame as a cell of a matrix.


A default layout, resembling a grid, that formats the four sets of data necessary for a matrix report; one set of data displays as a row of labels, one as a column of labels, one set creates the grid, and one set fills the cells of the grid.


A cross-tabulation of four sets of data:  one set of data displays across the page; one set of data displays down the page; one set of data is the cross-product, determining all possible locations in which the across and down data relate and placing a cell in those locations; and one set of data displays as the "filler" of the cells.


(Oracle Express) Data that can be examined and analyzed, such as sales or cost data. You can select and display the data in a measure. Measures can be stored as variables or relations, or measures can be calculated by means of formulas.


A unit of memory equal to 1,048,576 bytes (1024 x 1024). Often rounded to one million bytes.


A modal window that notifies you of a condition that occurred because of your last action.  You must respond to a message box.


Executable code attached to an Oracle8 object through its object type definition.


A window that elicits a response from the operator before the application can continue.


An unrestricted window that can have as much functionality as the runtime window manager can support.


1. An object that can be shared by multiple applications.  External queries and PL/SQL libraries are examples of modules.  2. In Form Builder, a major component of an application (i.e., a menu module).


A field in a window or dialog that extends for several lines, allowing you to scroll vertically through the information.


(Oracle Express) A special data value that indicates that data is "not available" (NA). NA is the value of any cell to which a specific data value has not been assigned or for which data cannot be calculated.


Acronym for Network Computing Architecture.  Oracle's comprehensive, open, standards-based software platform for building and deploying network-computing applications, allowing all PCs, network computers, and other client devices to work with all Web servers, database servers and application servers over any network.


Matrix report in which either the across and/or down dimension contains one or more master/detail relationships.  There is no limit to the amount of nesting allowed.


A datatype indicating that the data will be fetched when the page on which it appears is formatted (instead of fetched and cached until formatted).


1.  A specific location in the Object Navigator object hierarchy.  Nodes can be expanded to reveal information about an object, such as cross references to other objects.  2.  In Project Builder, an entry in the Project Navigator.  See also:  item, item node, organization node.


A report column that references a database column of datatype NOCACHE.


The absence of a value.


(Oracle Express)

^	Exponentiation,

-,+	Unary minus and plus,

*, /	Numeric multiplication or division. For division, the result is a double.

\	Integer division. The operands can be integer or long.

Mod	Modulus or Remainder. The operands can be integer or long,

-, +	Numeric addition and subtraction. The + operator can also be used for string concatenation.


1.  An item that can be placed on the layout.  The following are examples of objects:  rectangle, line, ellipse, arc, polygon, polyline, rounded rectangle, freehand, chart, text, symbol, and text field.  2.  In Oracle8, an instance of an object type.  An object can be a row in an object table, or the portion of a row contained in a column object in a relational table.


See column object.


A container for a group of objects, used to provide the ability to copy or reference related objects.


See handle.


The window containing a hierarchical list of objects for the current session.  The list appears in outline form, and enables the user to accomplish several tasks such as creating, editing, renaming, and deleting objects.


In Oracle8, a table whose definition is based on an object type.  Each attribute in the object type becomes a column in the object table.  Each row in the table is an individual Oracle8 object.


In Oracle8, a user-supplied definition or template, upon which objects can be built.  An object type is a composite, whose parts are attributes.  Each attribute has a name and a datatype, and the object type as a whole has a name.  An object type may also include one or more methods.


Acronym for Open Database Connectivity.  A standard for accessing different database systems.  An application can submit statements to ODBC using the ODBC flavor of SQL.  ODBC then translates these to whatever flavor the database understands.  Using the Oracle Open Client Adapter (OCA), an application can access different database management systems in one consistent manner.  This allows an application developer to develop, compile, and ship an application without targeting a specific DBMS.


Object Linking and Embedding.


An application that can store and display OLE objects.


An application that creates OLE objects.


See built-in datatype.


An environment variable that indicates the root directory of Oracle products.


An entry in the Project Navigator, such as "Types", "Projects", "Form Files", etc., used to help organize the Navigator and indicate the filetypes of child items.  It may also correspond to a data structure within certain types of filesystem objects.


Represented in x and y coordinates.  The origin of a view indicates the location of the view on its parent's canvas.  The origin of a canvas indicates the position of drawing on a drawn view.


The smallest virtual rectangle that completely surrounds an object.


When an object's contents cannot fit on the current logical page.  In the event of overflow, some of the contents are moved to the next logical page.


A PL/SQL function provided with Oracle Developer that can be referenced anywhere within a program unit.


A method of encapsulating and storing related procedures, functions, variables, and other package constructs together as a unit in the database.  While packages provide the database administrator or application developer organizational benefits, they also offer increased functionality and database performance.


Includes the actual implementation of the package, which may include private subprograms and datatypes.  The body is optional if the package consists only of declarations.


Declares the public interface to the package-that is, the datatypes and subprograms that can be referenced by other program units.


(Oracle Express) 1. A slice of data included in a report. Additional pages display additional slices of data.

The following figure shows three pages of data.

2. In Express, a page can also refer to a unit of storage in an Express database.


(Oracle Express) A representation of how data selected for a query will appear in your spreadsheet. The page layout shows three edges -- Across, Down, and Page. The edges indicate how the multi-dimensional datacube defined by the query will appear in the two-dimensional spreadsheet. Dimension tiles display the names and positions of the dimensions. A dimension tile on the Across edge indicates that this dimension will appear as a column. A dimension tile on the Down edge indicates that this dimension will appear as a row. A dimension on the Page edge indicates that this dimension will appear in a page box at the top of the results.


A setting that controls whether to move the current object and its children to the next logical page.  You can set page breaks to occur before or after the current object appears in the report output.


An interface element that displays tools, colors, or patterns available in an editor.


A distinct portion of an editor where work is performed, usually resizeable.


The number of physical pages needed to print one logical page.


1. A PL/SQL construct used to pass information to a subprogram.  For example, in the subprogram call MYPROC (x),x is a parameter.  2. In Oracle Developer, parameters are used to pass information between components such as Form Builder and Graphics Builder.  3. A variable you can change at runtime.


A group of parameter names and associated values used to pass information between Oracle Developer components.


1. An object that is immediately above another object in its group tree.  A group object is the parent to each of the objects that compose it.  Every object is the parent of its children.
2. (Oracle Express) A dimension value at the level immediately above a particular value in a hierarchy. The parent value is the aggregated total of the values of its children.  The following example shows the dimension value "France" as the parent of "Lyon" and "Paris" in the GEOGRAPHY dimension.


To place the contents of the Clipboard (cut or copied objects) at the current cursor location.


A graphical property you can apply to the edge or fill of most objects.


The machine code or instruction list for a compiled program unit.


Acronym for Portable Document Format.  A file format (native for Adobe Acrobat) for representing documents in a manner that is independent of the original application software, hardware, and operating system used to create the documents.  A PDF file can describe documents containing any combination of text, graphics, and images in a device-independent and resolution independent format.


The size of a page that is output by your printer.


The type of element used to plot a field on a chart, such as bar, line, or symbol.


Oracle's proprietary extension to the SQL language.  Adds procedural and other constructs to SQL that make it suitable for writing applications.


A Procedure Builder Interpreter script file.


A PL/SQL library file that contains source code and compiled code. The file can be opened in Procedure Builder for editing.


A text file that contains the PL/SQL source of a single program unit.


A runtime PL/SQL library file that contains executable code only.  The file cannot be opened in Procedure Builder for editing.


A list that pops up when the user performs a particular action.


A number that TCP uses to route transmitted data to and from a particular program.


An action shipped with Project Builder and automatically available to the user via a menu item and/or a toolbar button.  Pre-defined actions include Build, Deliver, and several source control options.  When a pre-defined action is defined for a supported file type, the action is invoked for any selected item of that type when the user calls the action from Project Builder.  See also:  action, user-defined action.


See built-in datatype.


A setting that affects the behavior of the application's interface.


(Oracle Express) View the script that would be generated if you accept the current criteria. In the dialog box for any Selector tool you can preview your selection by choosing the Preview button.


The interface with which you view and scroll through report output online.


A column in a database table whose members consist of unique values that can be used to identify a row in a table.


See built-in datatype.


In a printer definition file, an escape sequence to which you assign a printer code that can be referenced in a report.


A file that contains a printer definition in text (e.g., ASCII or EBCDIC) format.


A PL/SQL subprogram that performs a specified sequence of actions.


A code structure you can create using PL/SQL.  Anonymous blocks, subprogram specifications and bodies, and package specifications and bodies are examples of program units.


The basic data structure created by Project Builder.  Projects are collections of pointers to all the files you consider part of your application, or otherwise want to group together conceptually.  You can define the editors, compilers, and other tools you use to work with the items contained within a project.  All project information is stored within a project registry.  See also:  project item, project file, export.


In Project Builder, project files are all the files associated with an application.  Project files can be of any type, including forms, reports, displays, 3GL modules, documentation, and test scripts. See also:  project, project item.


In Project Builder, anything for which properties may be defined, including all entities stored under the Projects node, types, actions, and macros. See also: project, project file.


The window containing a hierarchical list of project items for the current session.  The list appears in outline form, and enables the user to accomplish several tasks such as creating, editing, renaming, and deleting objects.  Although only one is visible at any time, the user can choose from two different schema by which to organize the objects.


The top-level node for a project.  It represents or points to the project file.  See also: project, project file.


In Project Builder, the file that stores all the information associated with a project:  files belonging to the project, types, actions, and macros defined for the project, and so on.


The project view shows objects organized by their type, and by their project/subproject relationships.  The projects are organized alphabetically by project file, then alphabetically by category.  See also:  dependency view, project, project file, view.


A dialog that assists the user in accomplishing the steps necessary to create a new project or sub project.


A characteristic of an object that determines the behavior or appearance of that object.


A named object that contains a list of properties and their settings used to globally control the appearance and functionality of objects based on that class.


A string you can enter when connecting to a database or server that allows you to connect to a remote database or server.


(Oracle Express) When you select values for one of the dimensions in a measure, the other dimensions that make up that measure are known as qualifying dimensions.

Suppose your Sales measure is dimensioned by Product, Month, and Geography. If you select the top ten cities based on Sales, you must also specify which product and month you want to consider when you compare Sales values. In this case, Product and Month are the qualifying dimensions.


A SQL SELECT statement that specifies the data you wish to retrieve from one or more tables or views of a database.


A PL/SQL function that, when executed, filters data retrieved by a query.  Only the data that meet the conditions in the function are charted.


An option that terminates the current session and returns the user to the operating system.  On some systems, Quit is Exit.


A control (similar to a check box), appearing in sets of two or more, only one of which may be either "on" or "off" at any given time.


A set of two or more radio buttons, only one of which may be either "on" or "off" at any given time.


(Oracle Express) A Selector dialog box tool that allows the user to select consecutive time periods as dimension values. These time periods can be based on any of the following:

n	A specific number of the most recent time periods

n	A beginning time period and a most recent time period

n	A beginning and ending time period

For example, the user can select dimension values for the range from the week ending November 25, 1996 through the week ending December 30, 1996.


A cipher designed by RSA Data Security, Inc. which can accept keys of arbitrary length, and is essentially a pseudo random number generator with the output of the generator being XORed with the data stream to produce the encrypted data.  For this reason, it is very important that the same RC4 key never be used to encrypt two different data streams.


Acronym for Relational Databasse Management System.  A database that allows the definition of data structures, storage and retrieval operations, and integrity constraints.  In such a database, data and relations between them are organized in tables.


A file that contains a single report definition in binary format.  .rdf files are used to both run and edit reports.


A file that contains a single report definition in binary format.  .rep files are used solely to run reports; you cannot edit a .rep file.


The ability to query the data throughout a database as of a single point in time.


One row fetched by a SQL SELECT statement.


An internal data structure object with a column/row framework, that belong to the form module in which they are defined.


A type of column that points to data in other column objects or object tables (Oracle8).


A line that appears at a specified value on a chart.


(Oracle Express) Fetch the most recent data from an Express database after query results are displayed in your report. When you open a saved Express query, the results are refreshed automatically.


To add the definition of a type or a new project to either the global or local registry.  Upon being registered, a new type can have actions defined for it, while a new project can have its referenced files identified as specific types, so that actions can be invoked against them.  See also:  registry.


A global and/or user-specific configuration file used to store project definitions and environment information.  See also:  Global Registry, user registry.


A symbol used in search criteria to indicate a comparison between two values, such as the equal sign in "WHERE DEPTNO = 10." Rows in which the comparison results in "true" are returned in the result (fetched), while rows in which the comparison returns "false" are rejected from the result.


A database on a computer other than the local database.  Usually a computer on the same network, but at a different node (i.e., a database that you use through a database link).


A layout object used to display rows of data that are fetched for a group.


A mode in which each character you type replaces the current character at the cursor.  The opposite of insert mode.


The objects that are used to define a report's data model and layout.  Report-level objects are assigned to a single report, and cannot be shared by other reports or other Oracle products. Objects include reports, queries, external queries, external PL/SQL libraries, groups, columns, parameters, links, frames, repeating frames, fields, boilerplate, and anchors.


Available on Windows NT and Windows 95 only, waits for any command lines to run a report, then places that report in its queue to run in the background on the local server.  Typically command lines are received from Form Builder or Graphics Builder via RUN_PRODUCT, or from another report via SRW.RUN_REPORT.  You can also use RWISV60 to submit a report to the Reports Background Engine.


Maintains timestamp and status information about reports managed by the Reports Server.


Available on Windows NT, Windows 95, and UNIX, enables running reports on a remote server.  Queues job requests and allows you to schedule when to run reports.  Use RWCLI60 to send a report to the Reports Server.


Provides an interface between your Web Server and Reports Runtime, enabling you to run reports dynamically from your Web browser.


A file that contains a report definition stored in text (e.g., ASCII or EBCDIC) format.  .rex files are used to port reports from one platform to another.


Refers to the intensities of the red, green, and blue components of a color.


The group object to which all other objects in an application belong.


Top-level node for a project representing user's environment.  May correspond to the user registry or the Global Registry, depending on which registry has been chosen to set defaults.


1. One set of field values in a table; for example, the fields representing one employee in the example table EMP.  2.  To generate a runtime version of an application.


An object in an Oracle8 object table.  Each row in the table is an object.  However, in Oracle Developer an object table is deconstructed into individual columns, so that the row object no longer exists as a single entity (only as its separate attributes).


To execute a runtime version of an application or program unit.


Acronym for Rich Text Format.  A non-platform-specific file format based on control words used to represent unformatted text and images in various applications and operating systems.  RTF uses the ANSI, PC-8, Macintosh, or IBM PC character set to control the representation and formatting of a document, both on the screen and in print.


See rep file.


The interface in which users view and interact with a runtime application.


A screen or window appearing optionally at runtime in which a user can modify print options and parameters prior to report execution.


(Oracle Express) A set of dimension values that has been saved either as a list of values or as a script. You can save selections that you build, or your database administrator can create saved selections and make them available to you.

Saved selections are a time-saving feature: each time that you need to re-use a selection, you can simply retrieve a saved selection without having to recreate it.


(Oracle Express) A Selector tool that allows you to retrieve a selection of dimension values that has been saved.


A collection of related database objects, usually grouped by database userid.  Schema objects includes tables, views, sequences, stored program units, synonyms, indexes, clusters, and database links.


The level at, or range in which, an object operates.  For Project Builder:  1.  Describes the range of files affected by Compile and Compile All commands invoked against compilable project items.  2:  Describes the range of files influenced by inheritable definitions.


See SQL script, Interpreter script.


In Report Builder, one of multiple layouts in the same report, each with a different target audience, output format, page layout, page size, or orientation.  For example, a single report can include an executive summary for senior management in one section and also a detailed breakdown for  individual managers in another section.


A SQL statement that specifies which rows and columns to fetch from one or more tables or views.


(Oracle Express) The set of dimension values currently chosen for a dimension, or the script that contains conditions or criteria to specify those values.


(Oracle Express) As shown on various dialog boxes in the Selector, a description of the choices that a user has made in selecting dimension values. It is a translation, in ordinary language, of the Express Language script.


(Oracle Express) A set of tools that you use to choose the dimension values to include, for instance, in a report, table, graph, or dimension list box. For example, you can use the Selector to choose the top 10 cities based on Sales or those cities whose Sales exceeded Quota by 15 percent. The Selector also allows you to sort dimension values and to create and retrieve saved selections.


(Oracle Express) A dialog box or group of dialog boxes that provides a particular way of selecting or sorting dimension values. Each Selector tool is part of the Selector. You can use Selector tools to select dimension values based on their level in the hierarchy, their attributes, the characters in the value name, and so on. You can also use Selector tools to sort the dimension values.


The period between invoking and quitting the executable.


One of two or more objects that have the same parent object in its group tree.  All objects that belong to one group object are siblings.


The combination of an IP address and a port number.


(Oracle Express) A Selector dialog box tool that allows you to rearrange the order of dimension values in a displayed selection. You can sort a selection in ascending or descending order by the following:

n	Alphabet

n	Order in the database

n	Data value

n	Order or level in the hierarchy

n	Time period

You can also sort a selection manually, by dragging and dropping dimension values.


In PL/SQL:  Statements provided in a subprogram or package.  You must have the source code for a program unit before you can compile it into executable form.  In Project Builder:  The input types required to build target types.


Defines only the names, parameters, and return type (applies to functions only) of the subprogram.


A small rectangle at the bottom of the vertical scrollbar and to the left of the horizontal scrollbar in an editor.  Dragging and releasing the mouse button creates a new pane, and divides the scroll bar into separate scroll bars for each pane.


A standard interface for storing and retrieving information in a relational database.  SQL is an acronym for Structured Query Language.


A that contains a query stored in text (e.g., ASCII or EBCDIC) format.


A file containing SQL statements that you can run to perform database administration quickly and easily.  Several SQL scripts are shipped with Oracle products.


A SQL instruction to Oracle.  A SELECT statement is one type of SQL statement.


See call stack.


A PL/SQL construct used for conditional, iterative, and sequential control, and for error handling.  A semi-colon (;) must terminate every PL/SQL statement.


(Oracle Express) A saved selection that contains the same dimension values every time it is evaluated. Static saved selections list the dimension values that are to be selected, rather than the selection criteria. For example, a static saved selection script might include "Select February 1996, March 1996, and April 1996", while the script for a dynamic saved selection might have "Select the last three Months".


A procedure, function, or package that resides and executes in the Oracle7 Server.


A procedure or function that resides and executes in the Oracle7 Server.


HTML extensions that provide powerful formatting flexibility in HTML documents.  To view an HTML document that takes advantage of style sheets, display it in a browser that supports style sheets.


A PL/SQL procedure or function.


In Project Builder, a project contained within another project file, rather than being stored in its own file.  See also:  project.


In Project Builder, a node denoting a subproject.  See also:  project, project node, subproject node.


A column, with a type of summary, which computes subtotals, grand totals, running totals, and other summaries of the data in a report, using one of the packaged summary functions provided by Report Builder.


The file format used by the Microsoft Excel program.


The orderly system by which commands, qualifiers, and parameters are combined to form valid command strings.


One of the variables that are provided with Oracle Developer (e.g., &Logical Page, DESTYPE).


A named collection of related information, stored in a relational database or server, in a two-dimensional grid that is made up of rows and columns.


A collection type, which defines a data unit made up of an unordered set of elements, all of the same data type.  The corresponding data unit is a single column nested table, the column type of which is a built-in type or an object type.  (Oracle8)


A default layout displaying labels at the top of the page and rows of data underneath the labels.


In Project Builder, any item in the middle of the dependency tree; for example, an executable is a (compile) target for a library; while a library is a target for a group of objects; and an object is a target for a source file.


A node denoting a target.  See also: target.


Acronym for Transmission Control Protocol. The underlying communication protocol for exchanging HTTP requests between clients and Web servers.


A submenu that the user can remove from its source using a mouse or other pointing device and drag to another part of the display screen.


In Form Builder, an item that displays a character string value.


The horizontal area at the top of a window that displays the name of the application or interface element in that window.


See header.


To turn a setting alternately on or off.  For example, you can hide or show the Object Navigator.


An iconic button used to create and manipulate objects in an application.


Collection of iconic buttons that perform product commands.  Usually aligned horizontally along the top, or vertically down the side of a window.


A collection of tools.


(Oracle Express) A Selector dialog box tool that allows the user to select dimension values based on the top x, bottom y, or top x and bottom y data values. For example, the user can produce a dimension selection that contains products whose Volume Sales were among the top 10 percent of products in the Total U.S. for the four-week time period ending December 30, 1995.


A optional report region that can contain closing material for the report, including text, graphics, data, and computations.  The report trailer appears last, following the header and body.


A sequence of SQL statements treated as a single unit.


A PL/SQL procedure that is executed, or "fired," upon a specific event.


A description of a file type, such as a form, a document, etc, containing such information as type name and description. Each description applies to a single file type.  In addition, there are four meta-types: "updtext", which applies to all text files, “updproj” which applies to all project files, “updlink”, which applies to all project links, and "all" which applies to any file type.  Types are the foundation for defining actions and macros.


To change the values for table data, in particular, by altering data values using the SQL command UPDATE, but also by deleting values using the SQL command DELETE or by inserting values using the command INSERT.


Acronym for Uniform Resource Locator. The "address" used to specify a WWW server and home page.  For example:
http://www.acme.com/
which indicates that the host's address is www.acme.com. An URL most often is a filename (possibly with a long path to it and usually with an extension of .HTML, or .HTM (for PC-DOS filenames).


A way in which to pass control (and possibly arguments) from Oracle Developer to another Oracle product or 3GL, and then return control (and possibly arguments) back to Oracle Developer.


A custom action defined by a Project Builder user.  Such actions may apply to any single file type, or to all file types.  See also:  action, pre-defined action.


See object type.


A custom macro defined by a Project Builder user.  Such macros may be used to modify both built-in actions and user-defined actions.  See also:: macro, built-in macro, action, pre-defined action.


A project file storing configuration information on a per-user basis.  This enables users to customize their individual development environments.  The user registry inherits type information from the Global Registry, and may define new types as well as override aspects of type definitions stored in the Global Registry.  It also stores environment and preference information, such as the user's preferred connection string, UI settings such as "Display Toolbar" and certain general project information such as "Author."  See also:  registry, Global Registry.


See continuous axis.


A PL/SQL code structure that returns a value.  Value constructs include functions and expressions.


A named object that can be assigned a value and whose assigned value may change over time.


A collection type, which defines a data unit made up of an ordered set of elements, all of the same datatype.  The corresponding data unit is a varying array or VARRAY.  The number of elements in a VARRAY (size) is variable.  However, you must specify a maximum size when you declare a varying array type.


Visual Basic extension.  Used as the file extension for special dynamic link libraries distributed on files that contain predefined VBX controls.


A widget, built with the Microsoft Visual Basic language, used to enhance the user interface, obtain user input, and display output  (e.g. sliders, grids, and control knobs).


A virtual table whose rows do not actually exist in the database, but which is based on a table that is physically stored in the database.


A directory mnemonic (essentially a shortcut or synonym) that the virtual file system uses to map files stored in the file system on the host machine.


A mapping that associates the pathnames used in URL to the file system maintained by the host machine's operating system.


The font, color, and pattern properties that you set for objects that appear in your application interface.


See WWW.


A program that end users utilize to read HTML documents and programs stored on a computer (serviced by a Web server).


A program executed on a Web server via the Web Request Broker.


A server process (HTTP daemon) running at a Web site which sends out Web pages in response to HTTP requests from remote Web browsers.


A character used to mean 'any one character' or 'a contiguous set of characters' within a word or phase.


1. A rectangular area of the desktop that contains an application.  Each window has an area where you can interact with the application.  Windows can be opened, resized, moved, reduced to an icon, or enlarged to fill the entire desktop.  2. In Form Builder, an object type you can associate with a canvas view.


The file format used by some spreadsheet programs, such as SQL*Calc, or by Lotus 1-2-3 programs.


Acronym for Oracle Web Request Broker.  Provides a powerful distributed runtime environment for developing and deploying applications for the Web.  The WRB runtime platform enables application developers to write applications that are independent of, and work with a number of, Web servers.


Acronym for World Wide Web.  The network of servers on the Internet, each of which has one or more home pages, which provide information and links to other documents on that and (usually) other servers.


To expand an object to allow more room for editing the contents of the field.


The secondary toolbar docked (by default) to the left of the Project Navigator.  It provides simple organization and application launching abilities.