Index

A B C D E F G H I J K L M N O P Q R S T U V W Z

Symbols

:= assignment operator, 4.3.6

A

ACCESS_INTO_NULL exception, 4.4.1

ADD_MONTHS function, 3.4.5

adding data to a table with Object Browser, 2.3.13

AFTER triggers

correlation names and, 6.1.5

specifying, 6.1.4

aggregate functions

AVG, 3.4.7

COUNT, 3.4.7

DENSE_RANK, 3.4.7

MAX, 3.4.7

MIN, 3.4.7

PERCENT_RANK, 3.4.7

RANK, 3.4.7

STDDEV, 3.4.7

SUM, 3.4.7

with ALL clause, 3.4.7

with DISTINCT clause, 3.4.7

with GROUP BY clause, 3.4.7

with HAVING clause, 3.4.7

aliases

tables, 3.3.6

ALL clause, 3.4.7

ALTER INDEX statement, 3.7.6

ALTER TABLE statement, 3.7.2, 3.7.3, 3.7.4

DISABLE ALL TRIGGERS clause, 6.3.12

ENABLE ALL TRIGGERS clause, 6.3.13

ALTER TRIGGER statement

DISABLE clause, 6.3.12

ENABLE clause, 6.3.13

anonymous PL/SQL blocks, 4.1

apostrophes, 4.3.7

Application Builder

calling procedures and functions, 5.2.8

Oracle Application Express tool, 1.2.7

application development

basic concepts, 1.1.5

arrays

PL/SQL, 4.3.14.2

assignment operator, 4.3.6

assignments

IN OUT parameters, 4.3.12, 4.3.12

attributes

%ROWTYPE, 4.3.10.2

%TYPE, 4.3.10.1

cursors, 4.3.13.3

AVG

aggregate function, 3.4.7

B

BEFORE triggers

correlation names and, 6.1.5

specifying, 6.1.4

BEGIN

block structure, 4.3.1

BFILE datatypes, 2.2.4

BINARY_DOUBLE

datatypes, 2.2.2.3

BINARY_FLOAT

datatypes, 2.2.2.3

bind variables

PL/SQL, 4.3.15

with SQL Commands page, 3.3.7

BLOB datatypes, 2.2.4

blocks

anonymous, 4.1

BEGIN, 4.3.1

DECLARE, 4.3.1

END, 4.3.1

EXCEPTION, 4.3.1

structure, 4.3.1

Boolean

literals, 4.3.7

browser-based user interface

development tool, 1.1.6.1

C

calendars

parameter, 7.2.5

calling

stored subprograms, 5.2.8

subprograms in packages, 5.3.8

calls

functions, 5.2.8

procedures, 5.2.8

subprograms, 5.2.8

CASE expressions

overview, 4.3.11.2

CASE function, 3.4.9

case sensitivity

identifier, 4.3.5

string literal, 4.3.7

CASE_NOT_FOUND exception, 4.4.1

century

date format models, 3.4.6

character data

storing, 2.2.1

character literals, 4.3.7

quoting, 3.3.3.1

using in SQL statements, 3.3.3.1

character sets

national, 7.3.2.1, 7.3.2.2

check constraints, 2.3.1.3

how to use, 2.3.1.3

CLOB datatypes, 2.2.4

COLLECTION_IS_NULL exception, 4.4.1

collections

PL/SQL, 4.3.14.2

column alias

using with SELECT, 3.3.2

column constraints, 2.3.1

NOT NULL, 2.3.1.2

columns

accessing in triggers, 6.1.5

adding to a table with Object Browser, 2.3.3

constraints, 2.3.1

datatypes, 2.2

dropping from a table with Object Browser, 2.3.5

listing in an UPDATE trigger, 6.1.6

modifying in a table with Object Browser, 2.3.4

unique key, 2.3.1.4

comments

in PL/SQL, 4.3.3

COMMIT statement, 3.6, 3.6.1

comparison operators

BETWEEN, 3.3.3

equality, 3.3.3

IN, 3.3.3

LIKE, 3.3.3

NULL, 3.3.3

composite keys

foreign, 2.3.1.6

conditional predicates

trigger bodies, 6.1.6

CONSTANT

for declaring constants, 4.3.4

constants

declaring, 4.3.4

declaring in PL/SQL, 4.3.4

constraints

adding to a table with Object Browser, 2.3.6, 2.3.7

altering with SQL, 3.7.3

check, 2.3.1.3

column, 2.3.1

creating with SQL, 3.7.3

dropping with Object Browser, 2.3.12

ensuring data integrity, 2.3.1

indexes for use with, 2.4.2

modifying with Object Browser, 2.3.11

NOT NULL, 4.3.8

table, 2.3.1

table-level, 2.3.1

viewing with Object Browser, 2.3.10

control structures

PL/SQL, 4.3.11

correlation names, 6.1.4.3

NEW, 6.1.5

OLD, 6.1.5

when preceded by a colon, 6.1.5

COUNT

aggregate function, 3.4.7

countries table

HR user, 2.1.2

CREATE

with PROCEDURE statement, 5.2, 5.2.4

CREATE FUNCTION statement, 5.2, 5.2.7

CREATE INDEX statement, 3.7.6

CREATE PACKAGE BODY statement, 5.3, 5.3.5

CREATE PACKAGE statement, 5.3, 5.3.5

CREATE PROCEDURE statement, 5.2, 5.2.4

CREATE SEQUENCE statement, 3.7.8

CREATE SYNONYM statement, 3.7.9

CREATE TABLE statement, 3.7.1, 3.7.7

CREATE TRIGGER statement, 6.1

creating

functions, 5.2, 5.2.7

packages, 5.3, 5.3.5

procedures, 5.2, 5.2.4

creating a constraint

using SQL statement, 3.7.3

creating a table

using SQL statement, 3.7.1, 3.7.7

with Object Browser, 2.3.2

creating an index

SQL statement, 3.7.6

creating indexes with Object Browser, 2.4.4

currencies

formats, 7.2.7.1

CURSOR_ALREADY_OPEN exception, 4.4.1

cursors

attributes, 4.3.13.3

definition, 4.3.13

explicit, 4.3.13

FOUND attribute, 4.3.13.3

implicit, 4.3.13

ISOPEN attribute, 4.3.13.3

NOT FOUND attribute, 4.3.13.3

REF CURSOR, 4.3.13.2, 5.3.10

ROWCOUNT attribute, 4.3.13.3

variables, 4.3.13.2

D

data definition language (DDL)

SQL statements, 3.7

data integrity rules

as table-level constraints, 2.3.1

data manipulation language

described, 3.5

data structures

PL/SQL, 4.3.14

database home page

available tools, 1.1.6.1

logging in, 1.1.6.1

database objects

displaying, 2.1.2

managing, 2.1

naming, 2.1.1

database resident program units, 5.1

database triggers, 6.1

datatypes

BINARY_DOUBLE, 2.2.2.3

BINARY_FLOAT, 2.2.2.3

CHAR, 2.2.1

character, 2.2.1

floating-point number, 2.2.2.3

in table columns, 2.2

introduction, 1.1.4

large objects, 2.2.4

LOBs, 2.2.4

NCHAR, 2.2.1

NUMBER, 2.2.2.2

numeric, 2.2.2

NVARCHAR2, 2.2.1

overview, 2.2

VARCHAR2, 2.2.1

date and time data

storing, 2.2.3

date and time parameters, 7.2.4

DATE datatype, 2.2.3.1, 2.2.3.1

date formats, 7.2.4.1, 7.2.4.1.1

dates

ISO standard, 7.2.5.1.2

NLS_DATE_LANGUAGE parameter, 7.2.4.1.2

datetime

literals, 4.3.7

days

format element, 7.2.4.1.2

DBMS_OUTPUT package

displaying output, 4.3.2

displaying output from PL/SQL, 5.4.2.1

procedures for displaying output in PL/SQL, 4.3.2

using PUT_LINE to display output, 4.3.9

DBMS_RANDOM package, 5.4.2.2

declarations

constants, 4.3.4

exceptions in PL/SQL, 4.4.3

PL/SQL %ROWTYPE and %TYPE, 4.3.10

PL/SQL subprograms, 4.3.12

using NOT NULL constraint, 4.3.8

declarative part

of PL/SQL block, 4.3.1

DECLARE

block structure, 4.3.1

DECODE function, 3.4.9

DELETE statement, 3.5, 3.5.3

column values and triggers, 6.1.5

DENSE_RANK

aggregate function, 3.4.7

departments table

HR user, 2.1.2

dependencies

in stored triggers, 6.3.14.2

schema objects

trigger management, 6.2.2

DESCRIBE command

SQL Command Line, A.2.4

design considerations

Oracle Database Express Edition, 2

detail pane

Object Browser, 2.1.2

developing applications

overview with Oracle Database Express Edition, 1

disabled trigger

definition, 6.1.7

disabling

triggers, 6.1.7, 6.3.12

displaying a table, 2.1.2

displaying database objects, 2.1.2

displaying output

from PL/SQL, 4.3.9

setting SERVEROUTPUT, 4.3.2, 5.4.2.1

using DBMS_OUTPUT.PUT_LINE, 4.3.9

displaying PL/SQL output

with DBMS_OUTPUT, 4.3.2

DISTINCT clause, 3.4.7

distributed databases

triggers and, 6.2.2

dot notation, 4.3.10.1

DROP INDEX statement, 3.7.6

DROP SEQUENCE statement, 3.7.8

DROP SYNONYM statement, 3.7.9

DROP TABLE statement, 3.7.5

DROP TRIGGER statement, 6.3.10

dropping

indexes, 2.4.3.3

triggers, 6.3.10

dropping a sequence

using SQL statement, 3.7.8

dropping a synonym

using SQL statement, 3.7.9

dropping a table

using SQL statement, 3.7.5

dropping a table with Object Browser, 2.3.16

DUAL

dummy table, 3.4.1

dummy table

DUAL, 3.4.1

DUP_VAL_ON_INDEX exception, 4.4.1

dynamic SQL, 4.3.16

E

emp_details_view view

HR user, 2.1.2

employees table

HR user, 2.1.2

enabled trigger

definition, 6.1.7

enabling

triggers, 6.1.7, 6.3.13

END

block structure, 4.3.1

ensuring data integrity

with constraints, 2.3.1

error handling

PL/SQL, 4.4

EXCEPTION

block structure, 4.3.1

exception handlers

PL/SQL, 4.4

exception-handling part

of PL/SQL block, 4.3.1

exceptions

advantages of PL/SQL, 4.4.2

declaring in PL/SQL, 4.4.3

during trigger execution, 6.1.8

list of predefined in PL/SQL, 4.4.1

predefined in PL/SQL, 4.4.1

scope rules in PL/SQL, 4.4.4

executable part

of PL/SQL block, 4.3.1

EXIT-WHEN statement

overview, 4.3.11.3

EXTRACT function, 3.4.5

F

FALSE value, 4.3.7

file I/O, 5.4.2.4

floating-point number

datatypes, 2.2.2.3

floating-point numbers, 2.2.2

FOR EACH ROW clause, 6.1.4.2

foreign key

adding with Object Browser, 2.3.9

referential integrity between tables, 2.3.1.6

format elements

day, 7.2.4.1.2

month, 7.2.4.1.2

format models, 7.2.6.2

C number, 7.2.7.3

formats

currency, 7.2.7.1

date, 7.2.4.1.1

numeric, 7.2.6.1

time, 7.2.4.2

FOUND

cursor attribute, 4.3.13.3

functions, 4.1

calling, 5.2.8

calling from Application Builder, 5.2.8

creating, 5.2, 5.2.7

execute, 5.1.1

local, 4.3.12

NULL value, 3.4.8

overview, 5.1

SQL aggregate, 3.4.7

SQL arithmetic operators, 3.4.2

SQL character, 3.4.4

SQL conditional, 3.4.9

SQL conversion, 3.4.6

SQL date, 3.4.5

SQL group, 3.4.7

SQL numeric, 3.4.3

stored functions, 4.1

G

globalization support

altering NLS parameter settings, 7.2.2

calendar definitions, 7.2.5

date and time parameter settings, 7.2.4

default values for NLS parameters in SQL functions, 7.4.1

features, 7.1.1

language and territory parameter settings, 7.2.3

linguistic sorting and searching, 7.2.8

locale, 7.2.1

monetary parameter settings, 7.2.7

National Language Support (NLS) features, 7.1

NLS_LANG parameter setting, 7.2.1

numeric and list parameter settings, 7.2.6

overview, 7.1

programming with unicode, 7.3

setting up the environment, 7.2

specifying values for NLS parameters in SQL functions, 7.4.2

unacceptable NLS parameters in SQL functions, 7.4.3

GOTO statement

overview, 4.3.11.4

GROUP BY clause, 3.4.7

H

HAVING clause, 3.4.7

HELP command

SQL Command Line, A.2.2

described, 1.1.7

sample schema, 1.1.7

user account, 1.1.7

HR user

countries table, 2.1.2

departments table, 2.1.2

emp_details_view view, 2.1.2

employees table, 2.1.2

job_history table, 2.1.2

jobs table, 2.1.2

locations table, 2.1.2

locked account, 1.1.6.1

logging in, 1.1.6.1, 2.1.2

regions table, 2.1.2

HTP package, 5.4.2.3

I

identifiers

maximum length, 4.3.5

IF-THEN-ELSE statement

overview, 4.3.11.1

indexes

altering with SQL, 3.7.6

ascending, 2.4.1

column, 2.4.1

concatenated, 2.4.1

creating with Object Browser, 2.4.4

creating with SQL, 3.7.6

descending, 2.4.1

displaying with Object Browser, 2.4.5

drop if not required, 2.4.3.3

dropping, 2.4.3.3

dropping with Object Browser, 2.4.6

for use with constraints, 2.4.2

function-based, 2.4.1

guidelines, 2.4.3.1

limit the number, 2.4.3.2

managing with Object Browser, 2.4

normal type, 2.4.1

single column, 2.4.1

text type, 2.4.1

types, 2.4.1

INITCAP function, 3.4.4

initialization

using DEFAULT, 4.3.8

inner joins, 3.3.6

INSERT statement, 3.5, 3.5.1

column values and triggers, 6.1.5

INSTEAD OF triggers, 6.1.4.4, 6.3.6

integrity constraints

CHECK, 2.3.1.3

triggers vs., 6.2.1

INTERVAL DAY TO SECOND datatype, 2.2.3.1

INTERVAL YEAR TO MONTH datatype, 2.2.3.1

introduction

OCCI, 1.2.1

OCI, 1.2.1

Open Database Connectivity (ODBC), 1.2.2

Oracle Application Express, 1.2.7

Oracle Data Provider for .NET, 1.2.4

Oracle Database Express Edition, 1.1.1

Oracle Database Extensions for .NET, 1.2.5

Oracle Developer Tools for Visual Studio, 1.2.6

Oracle JDBC, 1.2.8, D.1

Oracle Provider for OLE DB, 1.2.3

PHP, 1.2.9

PL/SQL, 1.1.3

SQL, 1.1.2

INVALID_CURSOR exception, 4.4.1

INVALID_NUMBER exception, 4.4.1

ISO standard date format, 7.2.5.1.2

ISOPEN

cursor attribute, 4.3.13.3

J

Java

calling procedures and functions, 5.2.8

JDBC

introduction, 1.2.8, D.1

using with PL/SQL, D

job_history table

HR user, 2.1.2

jobs table

HR user, 2.1.2

joins

displaying data from multiple tables with SELECT, 3.3.6

inner, 3.3.6

outer, 3.3.6

self, 3.3.6

K

keywords

in PL/SQL, B.2

list of PL/SQL, B.2

use in PL/SQL, 4.3.5

L

language support, 7.1.1

large object datatypes, 2.2.4

LAST_DAY function, 3.4.5

LENGTH function, 3.4.4

length semantics, 7.2.9

linguistic sorts

parameters, 7.2.8

list parameter, 7.2.6

literals

Boolean, 4.3.7

character, 4.3.7

datetime, 4.3.7

numeric, 4.3.7

string, 4.3.7

types of PL/SQL, 4.3.7

LOB datatypes, 2.2.4

local subprograms, 5.1.1

locale

globalization support, 7.2.1

locations table

HR user, 2.1.2

logging into database home page, 1.1.6.1

LOGIN_DENIED exception, 4.4.1

LOOP statement

overview, 4.3.11.3

LOWER function, 3.4.4

LTRIM function, 3.4.4

M

managing database objects

Object Browser, 2.1.2

overview, 2.1

managing indexes

with Object Browser, 2.4

managing tables

with Object Browser, 2.3

MAX

aggregate function, 3.4.7

maximum size

identifier, 4.3.5

MIN

aggregate function, 3.4.7

MOD function, 3.4.3

modifying a constraint

using SQL statement, 3.7.3

modifying a table

using SQL statement, 3.7.2, 3.7.4

modifying an index

SQL statement, 3.7.6

modifying data in a table with Object Browser, 2.3.14

modularity, 5.3.1

monetary parameters, 7.2.7

months

format element, 7.2.4.1.2

MONTHS_BETWEEN function, 3.4.5

multi-line comments, 4.3.3

N

national character literals

quoting, 3.3.3.1

national character set, 7.3.2.1

national character sets, 7.3.2.2

National Language Support (NLS)

features, 7.1

globalization support, 7.1

NCHAR datatype, 2.2.1, 7.3.2.1

NCHR SQL function, 7.3.3, 7.3.3

NCLOB datatypes, 2.2.4

nested tables

PL/SQL, 4.3.14.2

NEW correlation name, 6.1.5

NLS parameters

default values in SQL functions, 7.4.1

settings, 7.2.2

specifying in SQL functions, 7.4.2

unacceptable in SQL functions, 7.4.3

NLS_CALENDAR parameter, 7.2.5.2

NLS_COMP parameter, 7.2.8.2

NLS_CURRENCY parameter, 7.2.7.2

NLS_DATE_FORMAT parameter, 7.2.4.1.1

NLS_DATE_LANGUAGE parameter, 7.2.4.1.2

NLS_DUAL_CURRENCY parameter, 7.2.7.4

NLS_INITCAP SQL function, 7.4

NLS_ISO_CURRENCY parameter, 7.2.7.3

NLS_LANG parameter, 7.2.1

choosing a locale, 7.2.1

NLS_LANGUAGE parameter, 7.2.3.1

NLS_LOWER SQL function, 7.4

NLS_NUMERIC_CHARACTERS parameter, 7.2.6.2

NLS_SORT parameter, 7.2.8.1

NLS_TERRITORY parameter, 7.2.3.2, 7.2.3.2

NLS_TIMESTAMP_FORMAT parameter

parameters

NLS_TIMESTAMP_FORMAT, 7.2.4.2.1, 7.2.4.2.2

NLS_UPPER SQL function, 7.4

NLSSORT SQL function, 7.4

NO_DATA_FOUND exception, 4.4.1

NOT NULL constraint

column constraint, 2.3.1.2

using in variable declaration, 4.3.8

NOT_LOGGED_ON exception, 4.4.1

notation

positional versus named, 5.2.8

NOTFOUND

cursor attribute, 4.3.13.3

NUMBER datatype, 2.2.2.2

integrity checking on input, 2.2.2.2

numeric datatypes, 2.2.2

numeric formats, 7.2.6.1

numeric literals, 4.3.7

numeric parameters, 7.2.6

NVARCHAR2 datatype, 2.2.1, 7.3.2.2

NVL function, 3.4.8

NVL2 function, 3.4.8

O

Object Browser

detail pane, 2.1.2

managing database objects, 2.1.2

object details, 2.1.2

object list, 2.1.2

object selection pane, 2.1.2

object details

Object Browser, 2.1.2

object list

Object Browser, 2.1.2

Object Reports

viewing database objects, 2.1.4

object selection pane

Object Browser, 2.1.2

OCCI

introduction, 1.2.1

OCI

introduction, 1.2.1

OLD correlation name, 6.1.5

Open Database Connectivity (ODBC)

introduction, 1.2.2

Oracle Application Express

Application Builder tool, 1.2.7

introduction, 1.2.7

Oracle Data Provider for .NET

introduction, 1.2.4

Oracle Database Express Edition

design considerations, 2

developing applications overview, 1

introduction, 1.1.1

language distributions, 7.1

with PHP, C.1

Oracle Database Extensions for .NET

introduction, 1.2.5

Oracle Developer Tools for Visual Studio

introduction, 1.2.6

Oracle JDBC

introduction, 1.2.8, D.1

Oracle Provider for OLE DB

introduction, 1.2.3

ORDER BY clause

using with SELECT, 3.3.5

outer joins, 3.3.6

overview

datatypes, 2.2

developing applications with Oracle Database Express Edition, 1

functions, 5.1

globalization support, 7.1

managing database objects, 2.1

packages, 5.1

PL/SQL, 4.1

procedures, 5.1

SQL, 3.1

SQL Command Line, A.1

subprograms, 5.1

triggers, 6.1

P

package

writing with PL/SQL, 5.3.1

packaged subprograms, 5.1.1

packages, 4.1

accessing types, 5.3.10

accessing variables, 5.3.9

body, 5.1.2

calling subprograms in, 5.3.8

creating, 5.3, 5.3.5

creating with Object Browser, 5.3.3

creating with SQL Commands, 5.3.2

dropping, 5.3.7

editing, 5.3.6

guidelines for writing, 5.3.1.1

hidden declarations, 5.1.2

overview, 5.1

overview of Oracle supplied, 5.4

product-specific, 5.4

specification, 5.1.2

stored packages, 4.1

types, 5.3.10

understanding, 5.1.2

using with JDBC, D

using with PHP, C

variables, 5.3.9

viewing with Object Browser, 5.3.4

visibility of contents, 5.1.2

parameters

calendar, 7.2.5

linguistic sorts, 7.2.8

monetary, 7.2.7

NLS_CALENDAR, 7.2.5.2

NLS_COMP, 7.2.8.2

NLS_CURRENCY, 7.2.7.2

NLS_DATE_FORMAT, 7.2.4.1.1

NLS_DATE_LANGUAGE, 7.2.4.1.2

NLS_DUAL_CURRENCY, 7.2.7.4

NLS_ISO_CURRENCY, 7.2.7.3

NLS_LANG, 7.2.1

NLS_LANGUAGE, 7.2.3.1

NLS_NUMERIC_CHARACTERS, 7.2.6.2

NLS_SORT, 7.2.8.1

NLS_TERRITORY, 7.2.3.2, 7.2.3.2

numeric, 7.2.6

time and date, 7.2.4

time zone, 7.2.4.2.1, 7.2.4.2.2

PERCENT_RANK

aggregate function, 3.4.7

PHP

calling procedures and functions, 5.2.8

introduction, 1.2.9

using with PL/SQL, C

with Oracle Database Express Edition, C.1

PL/SQL, 4.1

%ROWTYPE attribute, 4.3.10

%TYPE attribute, 4.3.10

anonymous blocks, 4.1

arrays, 4.3.14.2

assigning values with SELECT INTO, 4.3.9

assignment operator, 4.3.6

bind variables, 4.3.15

block structure, 4.3.1

collections, 4.3.14.2

comments, 4.3.3

control structures, 4.3.11

cursor, 4.3.13

data structures, 4.3.14

declarations using DEFAULT, 4.3.8

declarations using NOT NULL, 4.3.8

declaring constants, 4.3.4

declaring variables, 4.3.4

DEFAULT keyword for assignments, 4.3.8

displaying output, 4.3.9, 5.4.2.1

entering and executing code, 4.2

entering code in SQL Commands, 4.2.1

error handling, 4.4

exception handling, 4.4

identifiers, 4.3.5

input data, 4.3.2

introduction, 1.1.3

keywords, B.2

literals, 4.3.7

local functions, 4.3.12

local procedures, 4.3.12

local subprograms, 4.3.12

nested tables, 4.3.14.2

output data, 4.3.2

overview of, 4.1

program units, 4.1

programming with unicode, 7.3

records, 4.3.14.1

reserved words, B.2

SELECT INTO, 4.3.9

stored functions, 4.1

stored packages, 4.1

stored procedures, 4.1

subprograms

calling, 5.2.8

trigger bodies, 6.1.5

triggers, 4.1

using dynamic SQL, 4.3.16

using features, 4.3

using with JDBC, D

using with PHP, C

variable assignments, 4.3.6

writing reusable code, 5.3.1

primary key

adding with Object Browser, 2.3.8

for a table, 2.3.1.5

PRIMARY KEY constraints

multiple columns in, 2.3.1.5

UNIQUE key constraint vs., 2.3.1.4

privileges

dropping triggers, 6.3.10

needed on triggers, 6.2.3

recompiling triggers, 6.3.14.3

triggers, 6.2.3

PROCEDURE

with CREATE statement, 5.2, 5.2.4

procedures, 4.1

called by triggers, 6.2.2

calling, 5.2.8

calling from Application Builder, 5.2.8

calling from Java, 5.2.8

calling from PHP, 5.2.8

creating, 5.2, 5.2.4

execute, 5.1.1

local, 4.3.12

overview, 5.1

stored procedures, 4.1

using with JDBC, D

using with PHP, C

program units, 4.1, 5.3.1

pseudocolumns

ROWNUM, 3.4.1

SYSDATE, 3.4.1

USER, 3.4.1

using in SQL statements, 3.4.1

PUT_LINE

displaying PL/SQL output with, 4.3.9

Q

queries

in DML, 3.5

quoting character literals, 3.3.3.1

R

raising exceptions

triggers, 6.1.8

RANK

aggregate function, 3.4.7

records

definition, 4.3.10.2

PL/SQL, 4.3.14.1

REF CURSOR

cursor variable, 4.3.13.2, 5.3.10

defined in package, 5.3.10

strong type, 4.3.13.2

SYS_REFCURSOR, 4.3.13.2

using strongly-typed, 5.3.10

using weakly-typed, 5.3.10

weak type, 4.3.13.2

REGEXP_INSTR

regular expression function, 3.3.4

REGEXP_LIKE

regular expression condition, 3.3.4

REGEXP_REPLACE

regular expression function, 3.3.4

REGEXP_SUBSTR

regular expression function, 3.3.4

regions table

HR user, 2.1.2

regular expressions

functions and conditions, 3.3.4

using with SELECT, 3.3.4

with REGEXP_INSTR, 3.3.4

with REGEXP_LIKE, 3.3.4

with REGEXP_REPLACE, 3.3.4

with REGEXP_SUBSTR, 3.3.4

removing a row in a table with Object Browser, 2.3.15

REPLACE function, 3.4.4

reserved words

list of PL/SQL, B.2

list of SQL, B.1

PL/SQL, B.2

SQL, 3.1

syntactic meaning in PL/SQL, 4.3.5

restrictions

system triggers, 6.2.2

ROLLBACK statement, 3.6, 3.6.2

ROUND function, 3.4.3

row triggers

defining, 6.1.4.2

timing, 6.1.4

UPDATE statements and, 6.1.6

ROWCOUNT

cursor attributes, 4.3.13.3

ROWNUM

pseudocolumn, 3.4.1

ROWTYPE attribute

declaring, 4.3.10.2

ROWTYPE_MISMATCH exception, 4.4.1

RPAD function, 3.4.4

RTRIM function, 3.4.4

S

schema objects

accessing, 2.1

description, 2.1

tables, 2.3

schemas

description, 2.1

scientific notation, 4.3.7

scope

exceptions in PL/SQL, 4.4.4

Script Editor

executing SQL statements, 3.2.2

SELECT INTO

PL/SQL, 4.3.9

returning more than one row error, 4.4.1

TOO_MANY_ROWS exception, 4.4.1

SELECT statements, 3.3

self-joins, 3.3.6

sequences

creating with Object Browser, 2.6.1

creating with SQL, 3.7.8

description, 2.6

displaying with Object Browser, 2.6.2

dropping with Object Browser, 2.6.3

dropping with SQL, 3.7.8

managing with Object Browser, 2.6

using in SQL statements, 3.7.8

SERVEROUTPUT

displaying output from PL/SQL, 4.3.2

setting ON to display output, 5.4.2.1

SET command

for SQL Command Line settings, A.2.5

single-line comments, 4.3.3

sorting

specifying nondefault linguistic sorts, 7.2.8.1, 7.2.8.2

source types

description, 5.1

managing, 5.1

SPOOL command

SQL Command Line, A.2.7

SQL, 1.1.2

aggregate functions, 3.4.7

arithmetic operators, 3.4.2

character functions, 3.4.4

character literals in statements, 3.3.3.1

column alias, 3.3.2

committing changes with COMMIT, 3.6.1

comparison operators, 3.3.3

conditional functions, 3.4.9

conversion functions, 3.4.6

creating a constraint, 3.7.3

creating a table, 3.7.1, 3.7.7

creating an index, 3.7.6

data definition language (DDL), 3.7

data manipulation language (DML), 3.5

date functions, 3.4.5

deleting rows in a table with DELETE, 3.5.3

displaying data from multiple tables, 3.3.6

dropping a sequence, 3.7.8

dropping a synonym, 3.7.9

dropping a table, 3.7.5

dynamic, 4.3.16

executing statements, 3.2

executing statements in Script Editor, 3.2.2

executing statements in SQL Commands, 3.2.1

functions, 3.4

group functions, 3.4.7

inserting rows in a table with INSERT, 3.5.1

introduction of, 1.1.2

joining tables in SELECT, 3.3.6

modifying a constraint, 3.7.3

modifying a table, 3.7.2, 3.7.4

modifying an index, 3.7.6

NULL Value functions, 3.4.8

numeric functions, 3.4.3

ORDER BY clause in SELECT, 3.3.5

overview of, 3.1

PL/SQL and, 4.1

programming with unicode, 7.3

pseudocolumns, 3.4, 3.4.1

querying data, 3.3

regular expression conditions, 3.3.4

regular expression functions, 3.3.4

regular expressions in SELECT, 3.3.4

reserved words, 3.1, B.1

retrieving data from tables, 3.3

SELECT statement, 3.3

sequences, 3.4, 3.7.8

statement types, 3.1

transaction control statements, 3.6

types of statements, 3.1

undoing changes with ROLLBACK, 3.6.2

updating data in a table with UPDATE, 3.5.2

using with Oracle Database Express Edition, 3

WHERE clause in SELECT, 3.3.3

SQL Command Line

defining a variable, A.2.8.3

DESCRIBE command, A.2.4

displaying help, A.2.2

entering commands and statements, A.2.3

executing commands and statements, A.2.3

exiting, A.2.1

HELP command, A.2.2

overview, A.1

prompting for a variable, A.2.8.1

reusing a variable value, A.2.8.2

running SQL scripts, A.2.6

SET command, A.2.5

settings, A.2.5

SPOOL command, A.2.7

spooling output, A.2.7

SQL*Plus, A.1

starting from the operating system command prompt, A.2.1

using variables, A.2.8

SQL Commands

executing SQL statements, 3.2.1

SQL Commands page

bind variables, 3.3.7

SQL functions

default values for NLS parameters, 7.4.1

NCHR, 7.3.3, 7.3.3

NLS_INITCAP, 7.4

NLS_LOWER, 7.4

NLS_UPPER, 7.4

NLSSORT, 7.4

specifying NLS parameters, 7.4.2

TO_CHAR, 7.4

TO_DATE, 7.4

TO_NUMBER, 7.4

unacceptable NLS parameters, 7.4.3

UNISTR, 7.3.3

SQL statements

categories, 3.1

in trigger bodies, 6.1.5, 6.2.2

not allowed in triggers, 6.2.2

standalone subprograms, 5.1.1

execute, 5.1.1

statement triggers

conditional code for statements, 6.1.6

specifying SQL statement, 6.1.3

timing, 6.1.4

UPDATE statements and, 6.1.6

valid SQL statements, 6.2.2

STDDEV

aggregate function, 3.4.7

stored functions, 4.1

stored packages, 4.1

stored procedures, 4.1

stored subprograms, 5.1.1

in Oracle database, 5.1.1

storing, 2.2.2

string literals, 4.3.7

Unicode, 7.3.3

Structured Query Language (SQL), 1.1.2, 3.1

subprograms

calling in packages, 5.3.8

calling with parameters, 5.2.8

CREATE FUNCTION, 5.2.7

CREATE PROCEDURE, 5.2.4

creating with Object Browser, 5.2.2

creating with SQL Commands, 5.2.1

declaring PL/SQL, 4.3.12

dropping, 5.2.10

editing, 5.2.9

functions, 5.1.1

local, 5.1.1

mixed notation parameters, 5.2.8

named parameters, 5.2.8

overview, 5.1

packaged, 5.1.1

positional parameters, 5.2.8

procedures, 5.1.1

standalone, 5.1.1

stored, 5.1.1, 5.1.1

viewing with Object Browser, 5.2.3

SUBSCRIPT_BEYOND_COUNT exception, 4.4.1

SUBSCRIPT_OUTSIDE_LIMIT exception, 4.4.1

SUBSTR function, 3.4.4

SUM

aggregate function, 3.4.7

synonyms

creating with Object Browser, 2.7.1

creating with SQL, 3.7.9

description, 2.7

displaying with Object Browser, 2.7.2

dropping with Object Browser, 2.7.3

dropping with SQL, 3.7.9

managing with Object Browser, 2.7

SYS_REFCURSOR

predefined REF CURSOR, 4.3.13.2

SYSDATE

pseudocolumn, 3.4.1

SYSTIMESTAMP function, 3.4.5

T

table-level constraints, 2.3.1

tables

adding a column with Object Browser, 2.3.3

adding a column with SQL, 3.7.2

adding a foreign key with Object Browser, 2.3.9

adding a new constraint with Object Browser, 2.3.6, 2.3.7

adding a primary key with Object Browser, 2.3.8

adding data with Object Browser, 2.3.13

aliases, 3.3.6

altering with SQL, 3.7.2

constraints, 2.3.1

creating with Object Browser, 2.3.2

creating with SQL, 3.7.1

displaying, 2.1.2

dropping a column with Object Browser, 2.3.5

dropping with Object Browser, 2.3.16

dropping with SQL, 3.7.5

managing with Object Browser, 2.3

modifying a column with Object Browser, 2.3.4

modifying data with Object Browser, 2.3.14

primary key, 2.3.1.5

removing a row with Object Browser, 2.3.15

renaming with SQL, 3.7.4

viewing data, 2.1.3

territory definition, 7.2.3.2

territory support, 7.1.1

time and date data

storing, 2.2.3

time and date parameters, 7.2.4

time zone

parameters, 7.2.4.2.1, 7.2.4.2.2

TIMESTAMP datatype, 2.2.3.1

TIMESTAMP WITH LOCAL TIME ZONE datatype, 2.2.3.1

TIMESTAMP WITH TIME ZONE datatype, 2.2.3.1

TO_CHAR SQL function, 3.4.6, 7.4

default date format, 7.2.4.1.1

group separator, 7.2.6.2

language for dates, 7.2.4.1.2

spelling of days and months, 7.2.4.1.2

TO_DATE SQL function, 3.4.6, 7.4

default date format, 7.2.4.1.1

language for dates, 7.2.4.1.2

spelling of days and months, 7.2.4.1.2

TO_NUMBER SQL function, 3.4.6, 7.4

TOO_MANY_ROWS exception, 4.4.1

transaction control statements, 3.6

transactions

transaction control statements, 3.6

trigger

disabled

definition, 6.1.7

enabled

definition, 6.1.7

update_job_history, 6.3.3

triggers

accessing column values, 6.1.5, 6.1.5

AFTER, 6.1.4, 6.1.5

AFTER option, 6.1.4.1

as a stored PL/SQL subprogram, 6.1

BEFORE, 6.1.4, 6.1.5

BEFORE option, 6.1.4.1

body, 6.2.2

column list in UPDATE, 6.1.6

compiled, 6.3.14

compiling, 6.3.14

conditional predicates, 6.1.6

controlling when fired, 6.1.4

correlation names, 6.1.5

creating, 6.1, 6.2.2

creating LOGON and LOGOFF, 6.3.9

creating with AFTER and FOR EACH ROW, 6.3.4

creating with an exception handler, 6.3.7

creating with BEFORE and WHEN, 6.3.5

creating with Object Browser, 6.3.2

creating with SQL Commands, 6.3.1

dependencies, 6.3.14.2

designing, 6.2

detecting the operation that fired a trigger, 6.1.6

disabling, 6.1.7, 6.3.12, 6.3.12

dropping, 6.3.11

enabling, 6.1.7, 6.3.13

error conditions and exceptions, 6.1.8

errors, 6.1.8, 6.3.14.1

events, 6.1.3

exceptions, 6.1.8

fired multiple times with BEFORE option, 6.1.4.1

firing once for each update, 6.3.8

FOR EACH ROW clause, 6.1.4.2

FOR EACH ROW option, 6.1.4.2

guidelines for design, 6.2.1

illegal SQL statements, 6.2.2

INSTEAD OF, 6.1.4.4, 6.3.6

integrity constraints vs., 6.2.1

managing, 6.3

modifying, 6.3.10, 6.3.10

modifying views, 6.1.4.4, 6.3.6

naming, 6.1.2

NEW column value, 6.1.5

OLD column value, 6.1.5

overview, 6.1

privileges, 6.2.3

to drop, 6.3.10

privileges needed, 6.2.3

procedures and, 6.2.2

recompiling, 6.3.14.3, 6.3.14.3

remote dependencies and, 6.2.2

restrictions, 6.1.4.3, 6.2.2, 6.2.2

row, 6.1.4.2

stored, 6.3.14

triggering statement, 6.1.3

types, 6.1.1

viewing with Object Browser, 6.3.3

WHEN clause, 6.1.4.3, 6.1.4.3

when fired, 6.1.3

TRIM function, 3.4.4

TRUE value, 4.3.7

TRUNC function, 3.4.3

TYPE attribute

declaring, 4.3.10.1

types

accessing in packages, 5.3.10

U

Unicode

programming, 7.1.1

programming with SQL and PL/SQL, 7.3

string literals, 7.3.3

unique key

on a column, 2.3.1.4

UNIQUE key constraints

PRIMARY KEY constraint vs., 2.3.1.4

UNISTR SQL function, 7.3.3

UPDATE statement, 3.5, 3.5.2

column values and triggers, 6.1.5

triggers and, 6.1.6

update_job_history trigger, 6.3.3

UPPER function, 3.4.4

USER

pseudocolumn, 3.4.1

UTL_FILE package, 5.4.2.4

V

V$RESERVED_WORDS view

listing of reserved words and keywords, B

VALUE_ERROR exception, 4.4.1

VARCHAR2 datatype, 2.2.1

variables

accessing in packages, 5.3.9

bind, 4.3.15

cursor, 4.3.13.2

declaring in PL/SQL, 4.3.4

passing as IN OUT parameter, 4.3.12, 4.3.12

using with SQL Command Line, A.2.8

viewing table data, 2.1.3

views

creating, 2.5.1

creating with SQL, 3.7.7

description, 2.5

displaying with Object Browser, 2.5.2

dropping with Object Browser, 2.5.3

dropping with SQL, 3.7.7

managing with Object Browser, 2.5

visibility

of package contents, 5.1.2

W

WHEN clause, 6.1.4.3

cannot contain PL/SQL expressions, 6.1.4.3

correlation names, 6.1.5

examples, 6.3.5

WHERE clause

using with SELECT, 3.3.3

WHILE-LOOP statement

overview, 4.3.11.3

words

reserved in PL/SQL, B.2

reserved in SQL, B.1

Z

ZERO_DIVIDE exception, 4.4.1