Index

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

Symbols

%FOUND cursor attribute, 5.9.4

%ISOPEN cursor attribute, 5.9.4

%NOTFOUND cursor attribute, 5.9.4

%ROWCOUNT cursor attribute, 5.9.4

%ROWTYPE attribute, 5.9.1

%TYPE attribute

purpose of, 5.7.2

tutorial for, 5.7.3

A

accent-insensitive sort, 7.5.12

accessing Oracle Database, 1.3.2

B

base type, 5.4

basic LOOP statement, 5.8.6

BEFORE trigger, 6.1

row-level example, 6.2.3

system example, 6.2.5

bind argument, 8.1.2

block

anonymous, 5.1

parts of, 1.3.2.4

body of subprogram, 5.5.1

browsing HR sample schema, 2.5.1

built-in data type, 4.2.1

BULK COLLECT INTO clause, 5.10.4

bulk SQL, 8.1.3.5

byte semantics, 7.1.7

C

C numeric format element, 7.5.10

calendar format, 7.1.4

Cart, 10.3.1

CASE expression in query, 2.6.9.9

case sensitivity

in PL/SQL identifiers, 5.3

in sort, 7.5.12

CASE statement, 5.8.3

character function in query, 2.6.9.4

character semantics, 7.1.7

character set

conversion and data loss, 7.6.2

length semantics and, 7.1.7

Check Constraint, 4.2.3.1

adding with Add Check tool, 4.2.3.2

checking validity of installation, 10.5

CLR (Common Language Runtime), 1.3.2.5.9

collapsing displayed information in SQL Developer, 2.5.1

collating sequence, 7.1.6

collection, 5.10.1

collection method, 5.10.1

COUNT, 5.10.5

FIRST, 5.10.6

invoking, 5.10.1

NEXT, 5.10.6

column

alias for, 2.6.5

new heading for, 2.6.5

qualifying name of, 2.6.8

relationship to field, 1.3.1

selecting specific one in table, 2.6.4

comment in PL/SQL code, 5.5.1

Commit Changes icon, 3.3

COMMIT statement

explicit, 3.3

implicit, 3.3

committing transaction

explicitly, 3.3

implicitly, 3.3

Common Language Runtime (CLR), 1.3.2.5.9

comparing programming methods, 8.1.6

composite variable

collection, 5.10.1

record, 5.9.1

compound trigger, 6.1

concatenation operator in query, 2.6.9.3

concurrency, 8.1.4

concurrent sessions, 8.1.5

conditional predicate, 6.2.2

conditional selection statement, 5.8.1

CASE, 5.8.3

IF, 5.8.2

connecting to Oracle Database

as user HR, 2.3

from SQL Developer, 2.2

from SQL*Plus, 2.1

constant, 5.7

declaring, 5.7.1

ensuring correct data type of, 5.7.2

in package body, 5.7

in package specification, 5.7

local, 5.7

constraint, 4.2.3

adding to table

with ALTER TABLE statement, 4.2.3.2

with Edit Table tool, 4.2.3.2

application deployment and, 10.2.2

enabled or disabled, 4.2.3

types of, 4.2.3.1

viewing, 2.5.2

controlling program flow, 5.8

conversion function in query, 2.6.9.6

COUNT collection method, 5.10.5

Create Body tool, 5.6.4

Create Database Synonym tool, 4.5.1

CREATE FUNCTION statement, 5.5.3

Create Function tool, 5.5.3

CREATE INDEX statement

changing index with, 4.2.7.2

creating index with, 4.2.7.1

Create Index tool, 4.2.7.1

CREATE PACKAGE BODY statement, 5.6.4

CREATE PACKAGE statement

changing package specification with, 5.6.3

creating package specification with, 5.6.2

Create Package tool, 5.6.2

CREATE PROCEDURE statement, 5.5.2

Create Procedure tool, 5.5.2

CREATE SEQUENCE statement, 4.4.1

in installation script, 10.3.3

Create Sequence tool, 4.4.1

CREATE SYNONYM statement, 4.5.1

CREATE TABLE statement, 4.2.2.2

Create Table tool, 4.2.2.1

CREATE TRIGGER statement

changing trigger with, 6.3

creating trigger with, 6.2

Create Trigger tool, 6.2

CREATE VIEW statement

changing query in view with, 4.3.2

creating view with, 4.3.1.2

Create View tool, 4.3.1.1

creation script

See installation script

CURRVAL pseudocolumn, 4.4

cursor, 5.9.4

declared, 5.9.4

declaring associative array with, 5.10.3

implicit, 5.9.4

populating associative array with, 5.10.4

cursor attribute, 5.9.4

%FOUND, 5.9.4

%ISOPEN, 5.9.4

%NOTFOUND, 5.9.4

%ROWCOUNT, 5.9.4

possible values of, 5.9.4

syntax for value of, 5.9.4

cursor variable, 5.9.7

disadvantages of, 8.1.3.3

retrieving result set rows one at a time with

procedure, 5.9.8

tutorial, 5.9.9

D

data concurrency, 8.1.4

data consistency, 8.1.4

data definition language statement

See DDL statement

data integrity

See constraint

data loss during character-set conversion, 7.6.2

data manipulation language statement

See DML statement

Data pane, 4.2.5

data type

base, 5.4

built-in, 4.2.1

of associative array key, 5.10.2

of constant, 5.4

of function return value, 5.4

of subprogram parameter, 5.4

of table column, 4.2.1

of variable, 5.4

PL/SQL, 5.4

SQL, 4.2.1

SQL national character, 7.1.8

subtype of, 5.4

Unicode, 7.1.8

user-defined, 4.2.1

Database Export wizard, 10.3.2

database initialization parameter, 7.2

date format, 7.1.3

datetime format model, 2.6.9.6

datetime function in query, 2.6.9.5

DBMS_APPLICATION_INFO package, 8.2.1

DBMS_OUTPUT.PUT_LINE procedure, 5.8.3

DBMS_SESSION package, 8.2.1

DBMS_SQL package, 8.1.3.4

DBMS_STANDARD.RAISE_APPLICATION_ERROR procedure, 5.11.1

DDL statement, 4.1

as triggering event, 6.1

decimal character, 7.5.8

declarative language, 1.3.2.3

declarative part

of block, 1.3.2.4

of subprogram, 5.5.1

declared cursor, 5.9.4

advantages over cursor variable, 8.1.3.3

retrieving result set rows one at a time with, 5.9.5

DECODE function in query, 2.6.9.10

Delete Selected Row(s) tool, 4.2.6

DELETE statement, 3.1.3

DELETING conditional predicate, 6.2.2

deleting entire table, 4.2.8

deleting row from table

with Delete Selected Row(s) tool, 4.2.6

with DELETE statement, 3.1.3

dense associative array, 5.10.2

populating, 5.10.4

traversing, 5.10.5

dependencies between schema objects

installation and, 10.2.1

trigger compilation and, 6.5

deploying application, 10

deployment environment, 10.1

development environment, 10.1

choice of, 1.3.2.5

disabled trigger, 6.1

disabling triggers, 6.4

all triggers in table, 6.4.2

in installation script, 10.3.4

DL (long date) format, 7.5.4

DML statement, 3.1

as triggering event, 6.1

associative arrays and, 5.10.2

implicit cursor for, 5.9.4

dot notation

for accessing record field, 5.9.1

for invoking collection method, 5.10.1

DROP FUNCTION statement, 5.5.6

DROP INDEX statement, 4.2.7.2, 4.2.7.3

DROP PACKAGE statement, 5.6.5

DROP PROCEDURE statement, 5.5.6

DROP SEQUENCE statement, 4.4.2

DROP SYNONYM statement, 4.5.2

DROP TABLE statement, 4.2.8

Drop tool

for index, 4.2.7.3

for package, 5.6.5

for sequence, 4.4.2

for synonym, 4.5.2, 5.5.6

for table, 4.2.8

for trigger, 6.6

for view, 4.3.4

DROP TRIGGER statement, 6.6

DROP VIEW statement, 4.3.4

DS (short date) format, 7.5.4

DUAL table, 2.6.9.5

E

Edit Index tool, 4.2.7.2

Edit Table tool, 4.2.3.2

Edit tool

changing standalone subprogram with, 5.5.4

changing trigger with, 6.3

editioning view, 8.2.4

in sample application, 9.4.2

education environment, 10.1

enabled trigger, 6.1

enabling triggers, 6.4

all triggers in table, 6.4.2

in installation script, 10.3.4

ending transaction

by committing, 3.3

by rolling back, 3.4

ensuring data integrity, 4.2.3

environment variables, 7.4

error

See exception

exception handler syntax, 5.11.1

exception handling, 5.11.1

for predefined exception, 5.11.3

EXCEPTION_INIT pragma, 5.11.1

exception-handling part

of block, 1.3.2.4

of subprogram, 5.5.1

executable part

of block, 1.3.2.4

of subprogram, 5.5.1

EXECUTE IMMEDIATE statement, 8.1.3.2

exhaustion of resources, 8.1.1

EXIT WHEN statement, 5.8.6

expanding displayed information in SQL Developer, 2.5.1

exploring Oracle Database

with SQL Developer, 2.5

with SQL*Plus, 2.4

expression in query, 2.6.9

F

FCL (Framework Class Libraries), 1.3.2.5.9

FETCH statement

explicit cursor and, 5.9.4

populating dense associative array with, 5.10.4

fetching results one row at a time, 5.9.4

field, 5.9.1

relationship to column, 1.3.1

FIRST collection method, 5.10.6

FOR LOOP statement, 5.8.4

Foreign Key constraint, 4.2.3.1

adding

to sample application, 9.4.6

with Add Foreign Key tool, 4.2.3.2

with ALTER TABLE statement, 4.2.3.2

format

calendar, 7.1.4

date, 7.1.3

datetime model, 2.6.9.6

monetary, 7.1.5

time, 7.1.3

Framework Class Libraries (FCL), 1.3.2.5.9

function, 5.1

in query, 2.6.9

locale-dependent SQL, 7.4.2

statistical, 2.6.9.7

structure of, 5.5.1

G

G numeric format element, 7.5.8

globalization support features, 7.1

H

hard parse, 8.1.2

HR sample schema, 1.4

browsing, 2.5.1

unlocking, 2.3.1

Hypertext Preprocessor (PHP), 1.3.2.5.3

I

icon names and keyboard equivalents, 1.3.2.2

identifier, 5.3

IF statement, 5.8.2

implicit COMMIT statement, 3.3

implicit cursor, 5.9.4

index, 1.3.1

adding, 4.2.7.1

changing, 4.2.7.2

dropping, 4.2.7.3

implicitly created, 4.2.7

index-by table

See associative array

initial value of constant or variable, 5.7

initialization parameter, 7.2

Insert Row tool, 4.2.4

INSERT statement, 3.1.1

in sample application, 9.4.5

INSERTING conditional predicate, 6.2.2

installation script, 10.2

archiving, 10.6

creating, 10.3

disabling and re-enabling triggers in, 10.3.4

editing CREATE SEQUENCE statement in, 10.3.3

INSTEAD OF trigger, 6.1

example, 6.2.4

instrumentation package, 8.2.1

integrity constraint

See constraint

intersecting tables, 2.6.8

invalidated trigger, 6.5

IW date format element, 7.1.4

J

JDBC (Oracle Java Database Connectivity), 1.3.2.5.2

joining tables, 2.6.8

K

keyboard equivalents of icons, 1.3.2.2

key-value pair

See associative array

L

L numeric format element, 7.5.9

language support, 7.1.1

latch, 8.1.4.2

length semantics, 7.1.7

linguistic sorting and string searching, 7.1.6

loading data

See INSERT statement

local constant, 5.7

local subprogram

in anonymous block, 5.1

in another subprogram, 5.1

in package, 5.6.1

local variable, 5.7

locale, 7.5.1

locale-dependent SQL function, 7.4.2

logical table

See view

long date (DL) format, 7.5.4

loop statement, 5.8.1

basic LOOP, 5.8.6

exiting early, 5.8.6

FOR LOOP, 5.8.4

populating associative array with, 5.10.4

WHILE LOOP, 5.8.5

M

master script

See installation script

method, 5.10.1

Microsoft .NET Framework, 1.3.2.5.9

Microsoft Visual Studio, 1.3.2.5.9

monetary format, 7.1.5

multiline comment in PL/SQL code, 5.5.1

multilingual applications, 7.1

N

naming convention

for sequences, 4.4

in sample application, 9.1.3

national character set, 7.1.8

National Language Support (NLS), 7.1

National Language Support (NLS) parameters

See NLS parameters

native language support, 7.1.1

NCHAR literal replacement, 7.6.2

nested subprogram

See local subprogram

nested table, 5.10.1

.NET assembly, 1.3.2.5.9

.NET stored procedure, 1.3.2.5.9

NEW pseudorecord, 6.2.1

NEXT collection method, 5.10.6

NEXTVAL pseudocolumn, 4.4

NLS (National Language Support), 7.1

NLS environment variables, 7.4

NLS parameters

of locale-dependent SQL functions, 7.4.2

values of

changing, 7.4

initial, 7.2

viewing, 7.3

what they are, 7.1

NLS_CALENDAR parameter, 7.5.7

NLS_COMP parameter, 7.5.13

NLS_CURRENCY parameter, 7.5.9

NLS_DATE_FORMAT parameter, 7.5.4

NLS_DATE_LANGUAGE parameter, 7.5.5

NLS_DUAL_CURRENCY parameter, 7.5.11

NLS_ISO_CURRENCY parameter, 7.5.10

NLS_LANG parameter, 7.5.1

NLS_LANGUAGE parameter, 7.5.2

NLS_LENGTH_SEMANTICS parameter, 7.5.14

NLS_NUMERIC_CHARACTERS parameter, 7.5.8

NLS_SORT parameter, 7.5.12

NLS_TERRITORY parameter, 7.5.3

NLS_TIMESTAMP_FORMAT parameter, 7.5.6

nonblocking reads and writes, 8.1.4.3

nonprocedural language, 1.3.2.3

Not Null constraint, 4.2.3.1

adding

with ALTER TABLE statement, 4.2.3.2

with Edit Table tool, 4.2.3.2

numeric format

elements

C, 7.5.10

G, 7.5.8

L, 7.5.9

in different countries, 7.1.5

numeric function in query, 2.6.9.2

NVL function, 2.6.9.8

NVL2 function, 2.6.9.8

O

objects

See schema object

OCCI (Oracle C++ Call Interface), 1.3.2.5.5

OCI (Oracle Call Interface), 1.3.2.5.4

ODBC (Open Database Connectivity), 1.3.2.5.6

ODP.NET, 1.3.2.5.9

ODT (Oracle Developer Tools for Visual Studio), 1.3.2.5.9

OLD pseudorecord, 6.2.1

Open Database Connectivity (ODBC), 1.3.2.5.6

OPEN FOR statement, 8.1.3.3

OR REPLACE clause in DDL statement, 4.1

Oracle Application Express, 1.3.2.5.1

Oracle C++ Call Interface (OCCI), 1.3.2.5.5

Oracle Call Interface (OCI), 1.3.2.5.4

Oracle Database Extensions for .NET, 1.3.2.5.9

Oracle Deployment Wizard for .NET, 1.3.2.5.9

Oracle Developer Tools for Visual Studio, 1.3.2.5.9

Oracle Java Database Connectivity (JDBC), 1.3.2.5.2

Oracle Provider for OLE DB (OraOLEDB), 1.3.2.5.10

Oracle Providers for ASP.NET, 1.3.2.5.9

OraOLEDB (Oracle Provider for OLE DB), 1.3.2.5.10

ORDER BY clause of SELECT statement, 2.6.7

P

package, 5.2

dropping, 5.6.5

in sample application

admin_pkg, 9.6

employees_pkg, 9.5

instrumentation, 8.2.1

reasons to use, 5.2

structure of, 5.6.1

package body, 5.6.1

changing, 5.7.1

creating, 5.6.4

package specification, 5.6.1

changing, 5.6.3

creating, 5.6.2

package subprogram, 5.1

parameter

See subprogram parameter

parse, 8.1.2

PHP (Hypertext Preprocessor), 1.3.2.5.3

PLS_INTEGER data type, 5.4

PL/SQL block

anonymous, 5.1

parts of, 1.3.2.4

PL/SQL data type, 5.4

PL/SQL identifier, 5.3

PL/SQL language, 1.3.2.4

scalability and, 8.1.3

PL/SQL table

See associative array

PL/SQL unit, 1.3.2.4

precompiler

Pro*C/C++, 1.3.2.5.7

Pro*COBOL, 1.3.2.5.8

predefined exception, 5.11.1

handling, 5.11.3

Primary Key constraint, 4.2.3.1

adding

with Add Primary Key tool, 4.2.3.2

with ALTER TABLE statement, 4.2.3.2

private SQL area, 5.9.4

privileges

for schemas of sample application, 9.3

for users of sample application

on admin_pkg, 9.6

on employees_pkg, 9.5.4, 9.6.4

on schema objects, 9.4.7

security and, 8.3

Pro*C/C++ precompiler, 1.3.2.5.7

Pro*COBOL precompiler, 1.3.2.5.8

Procedural Language/SQL (PL/SQL) language, 1.3.2.4

procedure, 5.1

structure of, 5.5.1

Q

qualifying column names, 2.6.8

query, 2.6.1

function in, 2.6.9

grouping results by column, 2.6.9.7

improving readability of, 2.6.8

operator in, 2.6.9

simple, 2.6.1

SQL expression in, 2.6.9

stored

See view

R

RAISE statement, 5.11.1

RAISE_APPLICATION_ERROR procedure, 5.11.1

recommended programming practices, 8.2

record, 5.9.1

creating, 5.9.1

creating type for, 5.9.2

relationship to row, 1.3.1

reducing disk input/output (I/O), 4.2.7

REF constraint, 4.2.3.1

REF CURSOR type, 5.9.7

REF CURSOR variable

See cursor variable

Refresh icon

DDL statements and, 4.1

DML statements and, 3.1

rolling back transactions and, 3.4

RENAME statement, 4.3.3

Rename tool, 4.3.3

resetting password of HR account, 2.3.1

resource exhaustion, 8.1.1

retrieving results one row at a time, 5.9.4

RETURN clause of function, 5.5.1

RETURN statement, 5.5.1

return type

of cursor variable, 5.9.7

of function, 5.4

of REF CURSOR type, 5.9.7

reversing transaction, 3.4

Rollback Changes icon, 3.4

ROLLBACK statement, 3.4

rolling back transaction, 3.4

row

adding

with Insert Row tool, 4.2.4

with INSERT statement, 3.1.1

relationship to record, 1.3.1

row-level trigger, 6.1

example, 6.2.3

pseudorecords and, 6.2.1

Run tool, 5.5.5

Runstats tool, 8.1.6

runtime error

See exception

S

sample application

deploying, 10.4

developing, 9

sample schema HR

See HR sample schema, 1.4

SAVEPOINT statement, 3.5

scalable application, 8.1.1

schema, 1.3

in sample application

creating, 9.2

description of, 9.1.2.2

privileges for, 9.3

schema object, 1.3

creating and managing, 4

dependent

installation and, 10.2.1

trigger compilation and, 6.5

in sample application

creating, 9.4

description of, 9.1.2.1

schema-level subprogram

See standalone subprogram

script

See installation script

searched CASE expression, 2.6.9.9

searched CASE statement, 5.8.3

security

bind arguments and, 8.1.2

in sample application, 9.1.2.2

privileges and, 8.3

SELECT INTO statement

assigning value to variable with, 5.7.4.2

implicit cursor for, 5.9.4

T

table, 4.2

adding constraint to

with ALTER TABLE statement, 4.2.3.2

with Edit Table tool, 4.2.3.2

adding row to

with Insert Row tool, 4.2.4

with INSERT statement, 3.1.1

alias for, 2.6.8

changing data in

in Data pane, 4.2.5

with UPDATE statement, 3.1.2

creating, 4.2.2

for sample application, 9.4.1

deleting row from

with Delete Selected Row(s) tool, 4.2.6

with DELETE statement, 3.1.3

dropping, 4.2.8

ensuring data integrity in, 4.2.3

index on

See index

logical

See view

selecting data from, 2.6.1

and sorting it, 2.6.7

that matches specified conditions, 2.6.6

selecting specific columns of, 2.6.4

viewing properties and data of

with SQL Developer, 2.5.2

with SQL*Plus, 2.4.2

virtual

See view

territory support, 7.1.2

test environment, 10.1

time format, 7.1.3

timing point of trigger, 6.1

transaction, 3.2

committing

explicitly, 3.3

implicitly, 3.3

ending

by committing, 3.3

by rolling back, 3.4

rolling back, 3.4

serializable, 8.1.4

setting savepoints in, 3.5

visibility of, 3.3

transaction control statement, 3.2

trigger, 6.1

AFTER, 6.1

statement-level example, 6.2.2

system example, 6.2.5

BEFORE, 6.1

row-level example, 6.2.3

system example, 6.2.5

changing, 6.3

compiling, 6.5

compound, 6.1

creating, 6.2

for sample application, 9.4.3

disabled, 6.1

disabling, 6.4

in installation script, 10.3.4

dropping, 6.6

enabled, 6.1

enabling, 6.4

in installation script, 10.3.4

INSTEAD OF, 6.1

example, 6.2.4

invalidated, 6.5

on view, 6.2.4

recompiling, 6.5

row-level, 6.1

example, 6.2.3

pseudorecords and, 6.2.1

simple, 6.1

statement-level, 6.1

example, 6.2.2

system, 6.1

example, 6.2.5

timing point of, 6.1

U

undoing transaction, 3.4

Unicode, 7.1.8

data types for, 7.1.8

string literals in, 7.6.1

Unique constraint, 4.2.3.1

adding with Add Unique tool, 4.2.3.2

unlocking HR account, 2.3.1

unscalable application, 8.1.1

UPDATE statement, 3.1.2

UPDATING conditional predicate, 6.2.2

user-defined data type, 4.2.1

user-defined exception, 5.11.1

UTL_FILE package, 8.2.1

V

validity of installation, 10.5

variable, 5.7

assigning value to

with assignment operator, 5.7.4.1

with SELECT INTO statement, 5.7.4.2

composite

collection, 5.10.1

record, 5.9.1

cursor

See cursor variable

declaring, 5.7.1

ensuring correct data type of, 5.7.2

in package body, 5.7

in package specification, 5.7

local, 5.7

variable array (varray), 5.10.1

view, 4.3

changing name of, 4.3.3

changing query in, 4.3.2

creating, 4.3.1

for sample application, 9.4.2

dropping, 4.3.4

trigger on, 6.2.4

viewing table properties and data

with SQL Developer, 2.5.2

with SQL*Plus, 2.4.2

virtual table

See view

visibility of transaction, 3.3

Visual Studio, 1.3.2.5.9

W

warehousing system, 8.1.2

weak REF CURSOR type, 5.9.7

WHEN OTHERS exception handler, 5.11.1

WHERE clause of SELECT statement, 2.6.6

WHILE LOOP statement, 5.8.5