Index

( ) (parentheses)

controlling order of operations, 38

grouping for readability, 69, 252

& (ampersand)

in substitution variable names, 20, 22, 25

in variable names, 31

: (colon), in bind arguments, 260

-- (dashes), single-line comments, 29, 40

/ (slash), block terminator, 16, 264

:= (colon, equal sign), assignment operator, 37

‘ ‘ (single quotes), enclosing substitution variables, 25

/*...*/ (slash asterisk...), multiline comments, 29, 40

&& (double ampersand), in substitution variable names, 20, 24, 25

; (semicolon)

block terminator, 16–17

SQL and PL/SQL statement terminator, 264–265

variable terminator, 36–37

A

ACCESSIBLE BY clause, xxvii–xxviii

Accessors

new for Oracle 12c, xxvii–xxviii

specifying, xxvii–xxviii

white lists, xxvii–xxviii

Actual parameters, 317–318

AFTER triggers, 201–204

ALL_DEPENDENCIES view, 376–377

ALL_OBJECTS view, 374

ALL_USER_OBJECTS view, 314–315

ALL_USER_SOURCE view, 314–315

ALTER SYSTEM command, 411

ALTER TRIGGER command, 194

Ampersand (&)

in substitution variable names, 20, 22, 25

in variable names, 31

ANALYZE routine, 437

Anchored data types, 34

Anonymous blocks. See also Modular code; Named blocks.

definition, 5

description, 312

executing, 8

Application exception, profiling, 436–437

Application processing tier, 3

Architecture. See also Blocks.

application processing tier, 3

client-server, 5

data management tier, 3

Oracle server, 2–4

overview, 2–5

presentation tier, 3

three-tier, 3

Arithmetic operators, 38

Arrays. See Associative arrays; Varrays.

Associative arrays

declaring, 227

EXTEND method, 233

LIMIT method, 238

vs. nested tables and varrays, 239–240

NO_DATA_FOUND exception, 228–229

of objects, populating with data, 392

populating, 227

referencing individual elements, 227–228

syntax, 226

TRIM method, 233

upper bounds, specifying, 238–239

Attributes (data), object types, 386

Autonomous transactions, triggers, 203–204

AUTONOMOUS_TRANSACTION pragma, 204

B

BACKTRACE_DEPTH function, 424, 426–427

BACKTRACE_LINE function, 424, 426–427

BACKTRACE_UNIT function, 424, 426–427

Batch processing. See Bulk SQL.

BEFORE triggers, 195–201

BEGIN keyword, 7

BEQUEATH CURRENT_USER clause, xxxii

BEQUEATH DEFINER clause, xxxii

Bind arguments

in CREATE TABLE statements, 263–264

definition, 260

passing run-time values to, 272

Binding, definition, 9

Binding collections with

CLOSE statements, 306–309

EXECUTE IMMEDIATE statements, 299–305

FETCH statements, 306–309

OPEN-FOR statements, 306–309

Blank lines, inserting in output, 242

Blocks

; (semicolon), block terminator, 16

anonymous, 5, 8

binding, 9

compilation errors, 7–8

creating subroutines, 5

declaration section, 6

definition, 5

displaying variable values. See DBMS_OUTPUT.PUT_LINE statements.

error types, 7–8

exception-handling section, 7–8

executable section, 6–7

executing, 8–9

named, 5, 8–9

nested, 5, 39–41

runtime errors, 7–8

sections, 6–8

semantic checking, 9

sequences in, 48–49

syntax checking, 8–9

terminating, 16, 264–265

vs. transactions, 50, 54–55

VALID vs. INVALID, 9

Books and publications

Database Object-Relational Developer’s Guide, 385

Oracle Forms Developer: The Complete Video Course, xxiii

Oracle PL/SQL by Example, Fifth Edition, xvii

Oracle SQL by Example, 414

Oracle Web Application Programming for PL/SQL Developers, xxiii

Boolean expressions, in WHILE loops, 101

BROKEN procedure, 410

Built-in exceptions, 126–132

BULK COLLECT clause, 291–299

BULK COLLECT INTO clause, xxix

BULK EXECUTE IMMEDIATE statements, 260

BULK FETCH statements, 260

Bulk SQL

BULK COLLECT clause, 291–299

DELETE statements, in batches. See FORALL statements.

fetching results, 291–299

INSERT statements, in batches. See FORALL statements.

limiting result sets, 292–293

NO_DATA_FOUND exception, 292

UPDATE statements, in batches. See FORALL statements.

Bulk SQL, FORALL statements

description, 282–285

error messages, displaying, 287–288

exception handling, 285–288

implicit loop counter, 283

INDICES OF option, 283, 288

looping, 283, 288–290

SAVE EXCEPTIONS option, 285–288

SQL%BULK_EXCEPTIONS attribute, 286–287

VALUES OF option, 289–290

C

Calling packages, 339–341

CASE abbreviations. See COALESCE function; NULLIF function.

CASE expressions, 80–84

Case sensitivity

formatting guide, 455

passwords, 10

PL/SQL, 29

variables, 29

CASE statements

Boolean results. See Searched CASE statements.

vs. CASE expressions, 81–84

description, 72–74

searched CASE statements, 74–80

CHANGE procedure, 410, 412

CHAR data type, 35

Character types, 28

CLEAR_PLSQL_TRACE routine, 434–436

Client-server architecture, 5

CLOSE statements

binding collections with, 306–309

closing cursors, 271–280

Closing

cursor variables, 349

cursors, 167–168, 170

dynamic SQL cursors, 271–280

explicit cursors, 162, 167–168, 172–173

files, 407

COALESCE function, 87–89. See also NULLIF function.

Code generation, 9

COLLECT INTO statements, 260

Collection methods, 232–235

Collections. See also Tables.

counting elements, 232–235

defined on user-defined records, 255–256

definition, 225

deleting elements, 233–235

extending, 231

multilevel, 240–242

in nested records, 252–253

NULL vs. empty, 232

of object types, 391–394

records, 253–256

testing for elements, 232–235

upper bounds, specifying, 238–239

variable-size arrays. See Varrays.

Collections, binding with

CLOSE statements, 306–309

EXECUTE IMMEDIATE statements, 299–305

FETCH statements, 306–309

OPEN-FOR statements, 306–309

Colon, equal sign (:=), assignment operator, 37

Colon (:), in bind arguments, 260

Columns

aliases, 175

invisible, xxxiii–xxxiv

in a table, describing, 377–378

Comments

formatting, 29, 456–459

single-line vs. multiline, 29

COMMIT statements

description, 49–52

placing, 188, 314

in triggers, 195

Companion Website, URL for, xviii

Comparing objects

map methods, 400–401

order methods, 401–404

overview, 399–400

Comparison operators, 38

Compatibility, record types, 249–250

Compilation errors, 7–8, 124–126

Complex functions, creating, 328–329

Complex nested cursors, 185–187

Compound triggers

definition, 218

firing order, 219

resolving mutating table issues, 220–223

restrictions, 219

structure, 218

Conditional control. See CASE statements; ELSIF statements; IF statements.

Connecting to a database

SQL Developer, 10–11

SQL*Plus, 13

Connection name, SQL Developer, 10

Constructor methods, 395–397

Contiguous numbers, generating, 48

CONTINUE statements, 111–115

CONTINUE WHEN statements, 115–118

COUNT method, 232–235

Counting collection elements, 232–235

CREATE reserved word, 192–193

CREATE TABLE statements, 263–264

CREATE TYPE statements, 229–230

Creating

cursor variables, 345–346, 349–350

error messages, 149–153

event triggers on PDBs, xxx

nested tables, 229–230

object types, 386–390

procedures, 312–315

triggers, 192–195, 197–201

Creating functions

complex functions, 328–329

stored functions, 322–325

using a WITH clause, 329–330

using the UDF pragma, 330–331

Creating packages

information hiding, 335

package body, 335–336, 337–339

package specification, 335

package variables, 367–368

private elements, 341–344

Creating user-defined functions with a

WITH clause, xxxiv

UDF pragma, xxxiv–xxxv

CREDENTIAL clause, xxx–xxxi

Currency conversion example, 334

CURRVAL pseudocolumn, 48

Cursor attributes, 170–174. See also specific attributes.

Cursor FOR loops, 175–177

Cursor loops

closing a cursor, 167–168, 170

explicit cursors, 165–168

fetching rows in a cursor, 166–167

opening a cursor, 165–166

Cursor variables

closing, 349

creating, 345–346, 349–350

vs. cursors, 346

definition, 345

explicit, 345

in packages, 347–348, 350–352

processing, 346–347

query results, printing automatically, 348

rules for using, 353

sharing result sets, 348–352

strong (restrictive), 345–346

weak (nonrestrictive), 345–346

Cursor-based records

compatibility, 249–250

creating, 163–165

defining a collection on, 253–255

definition, 163

description, 244–246

Cursors. See also Dynamic SQL cursors.

column aliases, 175

vs. cursor variables, 346

definition, 159

explicit, 160

expressions in a select list, 175

fetch status, getting, 170–174

implicit, 160–161

locking rows for update, 187–189

most recently opened, 160

number of records fetched, getting, 170–174

number of rows updated, getting, 161

open, detecting, 170–174

parameterized, 183–185

scope, 175

select list, 175

SQL, 160

tips for using, 175

types of, 159–165

FOR UPDATE clause, 187–189

FOR UPDATE OF clause, 189

updating tables in a database, 187–190

WHERE CURRENT OF clause, 189–190

Cursors, explicit

associating with SELECT statements, 162

closing, 162, 167–168, 172–173

cursor-based records, 163–165

declaring, 162–163, 172–173

definition, 160

fetching rows in a cursor, 162, 166–167, 170–174

naming conventions, 162–163

opening, 162, 165–166, 172–173

processing, 165–168

record types, 163–165

records, 163–165

table-based records, 163

user-defined records, 168–170

Cursors, nested

complex, 185–187

looping through data, 177–181, 185–187

processing, 177–181

D

Dashes (--), single-line comments, 29, 40

Data (attributes), object types, 386

Data dictionary, examining stored code

ALL_DEPENDENCIES view, 376–377

ALL_OBJECTS view, 374

DBA_DEPENDENCIES view, 376–377

DBA_OBJECTS view, 374

debugging, 376

dependencies, displaying, 376–377

DESC command, 377–378

describing columns in a table, 377–378

displaying errors, 375–376

identifying procedures, packages, and functions, 377–378

modules with duplicate names. See Overloading.

overloading modules, 378–382

retrieving specified line numbers, 374–375

SHO ERR command, 376

USER_DEPENDENCIES view, 376–377

USER_ERRORS view, 375–376

USER_OBJECTS view, 374

Data dictionary queries

ALL_USER_OBJECTS view, 314–315

ALL_USER_SOURCE view, 314–315

DBA_USER_OBJECTS view, 314–315

DBA_USER_SOURCE view, 314–315

displaying source code, 314–315

object information, 314–315

procedure information, 314–315

USER_OBJECTS view, 314–315

USER_SOURCE view, 314–315

Data management tier, 3

Data Manipulation Language (DML)

definition, 46

and transaction control, 53–55

Data types

based on database objects. See Anchored data types.

common, summary of, 35–36. See also specific types.

displaying maximum size, xxx

extended maximum size, xxx

for file handles, 407

new for Oracle 12c, xxx

passing to procedures, 318

Database Object-Relational Developer’s Guide, 385

Database triggers. See Triggers.

Databases

edition-based redefinition, 193

erasing changes. See ROLLBACK statements.

saving changes. See COMMIT statements.

setting a save point. See SAVEPOINT statements.

STUDENT schema, 461–468

used in this book, 461–468

DATE data type, 36

DBA_DEPENDENCIES view, 376–377

DBA_OBJECTS view, 374

DBA_USER_OBJECTS view, 314–315

DBA_USER_SOURCE view, 314–315

DBMS_HPROF package, 436–437

DBMSHPTAB.sql script, 437

DBMS_JOB package, 410–412

DBMS_OUTPUT.PUT_LINE statements, 18–19, 21

DBMS_PROFILER package, 432–433

DBMS_SQL package, 417–418

DBMS_TRACE package, 433–436

DBMS_UTILITY package, 419–424

DBMS_XPLAN package, 414–417

Debugging

new for Oracle 12c, xxxvii

stored code, 376

Declaration section, 6

DECLARE keyword, 6

Declaring

associative arrays, 227

explicit cursors, 162–163, 172–173

variables, 36–39

varrays, 236–238

exceptions, 137–141

Definer rights (DR) subprogram, xxvi–xxvii

DELETE method

deleting collection elements, 233–235

deleting varray elements, 239

DELETE statements. See also DML (Data Manipulation Language).

batch processing. See FORALL statements.

with BULK COLLECT clause, 295

Deleting

collection elements, 233–235

statements, 295

varray elements, 239

Delimiters, 29

Dependencies, displaying, 376–377

DESC command, 377–378

Development environment. See PL/SQL Scripts; SQL Developer; SQL*Plus.

DIRECTORY objects, defining LIBRARY objects as, xxx–xxxi

DISABLE option, 194

Disabling substitution variable verification, 23

Disconnecting from a database

SQL Developer, 11–12

SQL*Plus, 13

Displaying

code dependencies, 376–377

code errors, 375–376

data type maximum size, xxx

data type size, xxx

error messages, 287–288

errors, 375–376

invalid procedures, 315

passwords, 13

procedures, 314–315

source code, 314–315

stored code dependencies, 376–377

variable values. See DBMS_OUTPUT.PUT_LINE statements.

DML (Data Manipulation Language)

definition, 46

and transaction control, 53–55

DML statements. See also DELETE statements; INSERT statements; UPDATE statements.

in blocks, 47–49

as triggering events, 47–49

Double ampersand (&&), in substitution variable names, 20, 24, 25

DR (definer rights) subprogram, xxvi–xxvii

Duplicate names. See Overloading.

DUP_VALUE_ON_INDEX exception, 129

Dynamic SELECT statements, 259

Dynamic SQL, optimizing, 260

Dynamic SQL cursors. See also Cursors.

closing, 271–280

fetching from, 271–280

opening, 271–280

passing run-time values to bind arguments, 272

Dynamic SQL statements

CLOSE, 271–280

example, 260

FETCH, 271–280

multirow queries, 271–280

OPEN-FOR, 271–280

passing NULLS to, 265–266

single-row queries, 261–271

terminating, 264

Dynamic SQL statements, EXECUTE IMMEDIATE

avoiding ORA errors, 262–271

binding collections, 299–305

description, 260–261

RETURNING INTO clause, 261–262

USING clause, 261–262

DYNAMIC_DEPTH function, 424–426

E

EDITIONABLE property, xxxiv, 193

Edition-based redefinition, 193

ELSIF statements, 63–67. See also IF statements.

Empty vs. NULL, 232

ENABLE option, 194

Encapsulation, 386

Erasing database changes. See ROLLBACK statements.

Error handling. See also Error messages.

compilation errors, 7–8, 124–126

runtime errors, 7–8, 124–126, 141–147. See also Exception propagation; Exceptions.

Error isolation, SQL*Plus, 314

Error messages. See also Error handling.

creating, 149–153

displaying, 287–288

getting, 155–158, 424, 428–429

names, associating with numbers, 153–155

references to line numbers and keywords, 126

Error numbers, getting, 155–158, 424, 428–429

Error reporting

DBMS_UTILITY package, 419–424

UTL_CALL_STACK package, 424–429

Error types, 7–8

ERROR_DEPTH function, 424, 428–429

error_message parameter, 150

ERROR_MSG function, 424, 428–429

ERROR_NUMBER function, 424, 428–429

error_number parameter, 150

Errors, displaying, 375–376

Event triggers, creating on PDBs, xxx

Exception handling. See also User-defined exceptions.

built-in, 126–132

EXCEPTION keyword, 8

EXCEPTION_INIT pragma, 153–155

file location not valid, 408

filename not valid, 408

FORALL statements, 285–288

INTERNAL_ERROR, 408

invalid file handle, 408

invalid mode, 408

invalid operation, 408

INVALID_FILEHANDLE, 408

INVALID_MODE, 408

INVALID_OPERATION, 408

INVALID_PATH, 408

predefined, 128–129. See also OTHERS exception; specific exceptions.

raising implicitly, 127

read error, 408

READ_ERROR, 408

re-raising, 146–148

scope, 133–137

unspecified PL/SQL error, 408

UTL_FILE, 408

write error, 408

WRITE_ERROR, 408

EXCEPTION keyword, 8

Exception propagation, 141–147

Exception-handling section, 7–8

EXCEPTION_INIT pragma, 153–155

Exceptions, raising

explicitly, 144–145

implicitly, 127

re-raising, 147

user-defined, 138

Executable section, 6–7

EXECUTE IMMEDIATE statements

avoiding ORA errors, 262–271

binding collections with, 299–305

description, 260–261

RETURNING INTO clause, 261–262

USING clause, 261–262

Executing blocks

overview, 8–9

SQL Developer, 14–16

Executing queries

SQL Developer, 14

SQL*Plus, 15

Execution times

baseline, computing, 432–433

for SQL and PL/SQL, separating, 436–437

EXISTS method, 232–235

EXIT statements, 93–97

EXIT WHEN statements, 97–98

Explain plan, generating, 414–417

Explicit cursor variables, 345

Expressions

( ) (parentheses), controlling order of operations, 38

CASE expressions, 80–84

comparing. See COALESCE function; NULLIF function.

in a cursor select lists, 175

operands, 38

operators, 38–39. See also specific operators.

EXTEND method, 231, 232–235

Extending collections, 232–235

Extending packages

with additional procedures, 353–366

final_grade function, 355–366

manage_grades package specification, 354–356

median_grade function, 362–365

F

FCLOSE function, 407

FCLOSE_ALL procedure, 407

FETCH command, 166–167

FETCH FIRST clause, xxviii–xxix

FETCH statements, 271–280, 306–309

Fetch status, getting, 170–174

Fetching records

from dynamic SQL cursors, 271–280

results in bulk SQL, 291–299

rows in a cursor, 166–167

FFLUSH procedure, 407

File handle invalid, exception, 408

File location not valid exception, 408

Filename not valid, exception, 408

Files, accessing within PL/SQL, 406–410

FILE_TYPE data type, 407

Firing order, compound triggers, 219

Firing triggers, 192, 194

FIRST method, 233–235

Flushing the data buffer, 407

FLUSH_PROFILER routine, 433

FOLLOWS option, 194

FOPEN function, 407

FOR loops. See Numeric FOR loops.

FOR reserved word, 104

FOR UPDATE clause, 187–189

FOR UPDATE OF clause, 189

FORALL statements

description, 282–285

error messages, displaying, 287–288

exception handling, 285–288

implicit loop counter, 283

improving performance, 260

INDICES OF option, 283, 288

looping, 283, 288–290

SAVE EXCEPTIONS option, 285–288

SQL%BULK_EXCEPTIONS attribute, 286–287

VALUES OF option, 289–290

Formal parameters, 317–318

FORMAT_CALL_STACK function, 419–421

FORMAT_ERROR_BACKTRACE function, 419, 421–422

FORMAT_ERROR_STACK function, 419, 422–424

Formatting guide

case sensitivity, 455

comments, 456–459

naming conventions, 456–457

white space, 455–456

Formatting guide, for readability by humans

dynamic SQL statements, 275

EXCEPTION_INIT pragma, 155

formatting IF statements, 66–67

formatting SELECT statements, 275

grouping with parentheses, 69, 252

inserting blank lines, 242

inserting blank spaces, 275

labels on nested blocks, 39–40

labels on nested loops, 120

WORK keyword, 51–52

%FOUND attribute, 170–174

Functions. See also Modular code.

collections of. See Packages.

final_grade function, 355–366

identifying, 377–378

invoking in SQL statements, 327–328

IR (invoker rights), xxvi–xxvii

median_grade function, 362–365

optimizing execution, 329–331

vs. procedures, 322

syntax, 322–327

user-defined. See User-defined functions.

uses for, 325–327

Functions, creating

complex functions, 328–329

stored functions, 322–325

using a WITH clause, 329–330

using the UDF pragma, 330–331

G

GET_LINE procedure, 407

GET_NEXT_RESULT procedure, xxxi–xxxii

GET_PLSQL_TRACE_LEVEL routine, 434–436

Getting records. See Fetching records.

Grouping transactions, 49

H

Help, Oracle online, 193

Hierarchical Profiler, 436–437

I

Identifiers, 29, 33–34. See also Variables.

IF statements. See also ELSIF statements.

description, 58

formatting for readability, 66–67

inner, 67

logical operators, 68–70

nested, 67–70

outer, 67

IF-THEN statements

description, 58–60

inner IF, 67

IF-THEN-ELSE statements

description, 60–63

outer IF, 60–63

Implicit cursors, 160–161

Implicit statement results, xxxi–xxxii

Implicit statement results, generating, 417–418

IN option, 105–107

IN OUT parameter, 316–317

IN parameter, 315–319

Index-by tables. See Associative arrays.

INDICES OF option, 283, 288

Infinite loops

definition, 93

simple, 95

WHILE, 100

Information hiding, 335

INHERIT ANY PRIVILEGES clause, xxxii–xxxiii

INHERIT PRIVILEGES clause, xxxii–xxxiii

Initializing

nested tables, 230–232

object attributes, 389–390

packages, 367–368

Initializing variables

with an assignment operator, 36–39

with CASE expressions, 83–84

to a null value, 32

with SELECT INTO statements, 44–47, 83–84

Inner IF statements, 67

INSERT statements. See also DML (Data Manipulation Language).

batch processing. See FORALL statements.

with BULK COLLECT clause, 295

Instantiating packages, 366

INSTEAD OF triggers, 206–211

INTERNAL_ERROR exception, 408

Interpreted mode code generation, 9

INTERVAL parameter, 411

INTERVAL procedure, 410

Invalid

file handle exception, 408

mode exception, 408

operation exception, 408

procedures, 315

INVALID blocks vs. VALID, 9

INVALID_FILEHANDLE exception, 408

INVALID_MODE exception, 408

INVALID_OPERATION exception, 408

INVALID_PATH exception, 408

Invisible columns, xxxiii–xxxiv

IR (invoker rights) unit

creating views, xxxii

new for Oracle 12c, xxvi–xxvii, xxxii–xxxiii

permissions, xxxii–xxxiii

%ISOPEN attribute, 170–174

IS_OPEN function, 407

Iterative control. See CONTINUE statements; Loops.

J

JOB parameter, 411

Job queue

changing items in the queue, 410

changing job intervals, 410

DBMS_JOB package, 410–412

disabling jobs, 410, 412

examining, 412

flagging jobs as broken, 412

forcing a job to run, 410, 412

job numbers, assigning, 411

removing jobs from, 410, 412

scheduling the next run date, 410

submitting jobs, 410, 411–412

K

keep_errors parameter, 150

L

Labels on

nested blocks, 39–40

nested loops, 120

Language components

anchored data types, 34

character types, 28

comments, 29

delimiters, 29

identifiers, 29, 33–34. See also Variables.

lexical units, 28–29

literals, 29

reserved words, 29, 32–33

variables, 29–32, 36–39. See also Identifiers; Substitution variables.

LAST method, 233–235

Lexical units, 28–29

LIBRARY objects, defining as DIRECTORY objects, xxx–xxxi

LIMIT method, 238, 292–293

Limiting result sets, bulk SQL, 292–293

Line terminators, inserting, 408

Literals

definition, 29

in expressions, 38

LOB data type, 36

Locking rows for update, 187–189

Logical operators, 39, 68–70

LOGIN_DENIED exception, 128

LONG data type, 36

LONG RAW data type, 36

Loop labels, 120–122

LOOP reserved word, 92

Looping

FORALL statements, 283, 288–290

INDICES OF option, 283, 288

VALUES OF option, 289–290

Loops, nested, 118–120. See also Nested cursors.

Loops, numeric FOR

description, 104–105

IN option, 105–107

premature termination, 108–109

REVERSE option, 107–108

Loops, simple

description, 92–93

EXIT statements, 93–97

EXIT WHEN statements, 97–98

infinite, 93, 95

inner loops, 119

RETURN statements, 96

terminating, 93–98

Loops, WHILE

Boolean expressions as test conditions, 101

description, 98–101

infinite, 100

outer loops, 119

premature termination, 101–103

M

Map methods, 400–401

MAX_STRING_SIZE parameter

displaying data type size, xxx

Member methods, 398

Methods (functions and procedures), 386

Modes

code generation, 9

invalid, exception, 408

procedure parameters, 317–318

Modular code

anonymous blocks, 312

benefits of, 312

block structure, 312

definition, 311

types of, 312. See also specific types.

Multilevel collections, 240–242

Multirow queries, 271–280

Mutating table errors, 214

Mutating tables

definition, 214

resolving issues, 215–223

N

Named blocks, 5, 8–9. See also Anonymous blocks.

Named notation, procedure parameters, 318–319

Naming conventions

explicit cursors, 162–163

formatting guide, 456–457

variables, 29–30

Native code, 9

Native dynamic SQL. See Dynamic SQL.

Native mode code generation, 9

Nested

blocks, 5, 39–41

collections in object types, 393

cursors, 177–181

IF statements, 67–70

loops, 118–120

records, 250–253

varrays, 240–242

Nested cursors

complex, 185–187

looping through data, 177–181, 185–187

processing, 177–181

Nested tables

vs. associative arrays and varrays, 239–240

creating, 229–230

initializing, 230–232

LIMIT method, 238

populating with the BULK COLLECT clause, 292

upper bounds, specifying, 238–239

New features, summary of, xxv–xxvi. See also specific features.

:NEW pseudorecords, 196–199

NEW_LINE function, 408

NEXT DATE procedure, 410

NEXT method, 233–235

NEXT_DATE parameter, 411

NEXTVAL pseudocolumn, 48

NO_DATA_FOUND exception, 128

associative arrays, 228–229

bulk SQL, 292

NONEDITIONABLE property, xxxiv, 193

Nonrestrictive (weak) cursor variables, 345–346

NO_PARSE parameter, 411

Not null, constraining variables to, 32

%NOTFOUND attribute, 170–174

Null condition, IF-THEN-ELSE statements, 61–63

Null values

assigning to expressions in NULLIF functions, 86–87

variables, 32

NULL vs. empty, 232

NULLIF function, 84–87. See also COALESCE function.

NULLS, passing to dynamic SQL statements, 265–266

NUMBER data type, 35

Numeric FOR loops

in cursors, 175–177

description, 104–105

IN option, 105–107

premature termination, 108–109

REVERSE option, 107–108

NVACHAR2 data type, xxx

O

Object attributes, initializing, 389–390

Object instances. See Objects.

Object specification, 388

Object type methods

comparing objects, 399–404

constructor, 395–397

definition, 395

functions and procedures, 386

member, 398

parameter, 395

SELF parameter, 395, 397, 398, 401

static, 398–399

Object types

attributes (data), 386

with collections, 391–394

components of, 386

creating, 386–390

encapsulation, 386

methods (functions and procedures), 386

nesting collections in, 393

Objects

associative arrays, populating with data, 392

comparing, 399–404

getting information about, 314–315

initial value, 389

schema, editionable vs. noneditionable, xxxiv

:OLD pseudorecords, 196–199

Open cursors, testing for, 170–174

Open files

testing for, 407

writing to, 408

OPEN-FOR statements

binding collections with, 306–309

opening cursors, 271–280

Opening

dynamic SQL cursors, 271–280

explicit cursors, 162, 165–166, 172–173

files, 407

Operands

definition, 38

in expressions, 38

Operation invalid, exception, 408

Operators

definition, 38

in expressions, 38

precedence, 39

Optimization levels

examples of, 439–444

performance optimizer, 438

PLSQL_OPTIMIZE_LEVEL parameter, 438

summary of, 438

Optimizing

dynamic SQL, 260

function execution, 329–331

Optimizing PL/SQL, tuning tools

ANALYZE routine, 437

CLEAR_PLSQL_TRACE routine, 434–436

computing execution time baseline, 432–433

DBMS_HPROF package, 436–437

DBMSHPTAB.sql script, 437

DBMS_PROFILER package, 432–433

DBMS_TRACE package, 433–436

FLUSH_PROFILER routine, 433

GET_PLSQL_TRACE_LEVEL routine, 434–436

Hierarchical Profiler, 436–437

PAUSE_PROFILER routine, 433

Profiler API, 432–433

profiling execution of applications, 436–437

PROFLOAD.sql script, 432–433

PROFTAB.sql script, 432–433

RESUME_PROFILER routine, 433

separating execution times for SQL and PL/SQL, 436–437

SET_PLSQL_TRACE routine, 434–436

START_PROFILER routine, 432–433

START_PROFILING routine, 437

STOP_PROFILER routine, 432–433

STOP_PROFILING routine, 437

Trace API, 433–436

TRACE_ALL_CALLS constant, 434–436

TRACE_ALL_EXCEPTIONS constant, 434–436

TRACE_ALL_SQL constant, 434–436

TRACE_ENABLED_CALLS constant, 434–436

TRACE_ENABLED_EXCEPTION constant, 434–436

TRACE_ENABLED_SQL constant, 434–436

TRACE_PAUSE constant, 434–436

TRACE_RESUME constant, 434–436

TRACE_STOP constant, 434–436

TRACETAB.sql script, 433–436

tracing order of execution, 433–436

ORA errors, avoiding, 262–271

Oracle Forms Developer: The Complete Video Course, xxiii

Oracle online help, 193

Oracle PL/SQL by Example, Fifth Edition, xvii

Oracle sequences. See Sequences.

Oracle server, 2–4

Oracle SQL by Example, 414

Oracle SQL Developer. See SQL Developer.

Oracle Web Application Programming for PL/SQL Developers, xxiii

Oracle-supplied packages

accessing files within PL/SQL, 406–410

DBMS_JOB, 410–412

DBMS_SQL, 417–418

DBMS_XPLAN, 414–417

explain plan, generating, 414–417

implicit statement results, generating, 417–418

scheduling jobs, 410–413

text file capabilities, 406–410

UTL_FILE, 406–410

Oracle-supplied packages, error reporting

DBMS_UTILITY package, 419–424

UTL_CALL_STACK package, 424–429

Order methods, 401–404

Order of execution, tracing, 433–436

OTHERS exception, 131, 155–156. See also SQLCODE function; SQLERRM function.

OUT parameter, 315–319

Outer IF statements, 67

Overloading

construction methods, 397

modules, 378–382

P

Packages. See also Modular code.

benefits of, 334

currency conversion example, 334

definition, 333

granting roles to, xxix–xxx

identifying, 377–378

initialization, 367–368

instantiation, 366

manage_grades package specification, 354–356

referencing packaged elements, 336–337. See also Cursor variables.

serialization, 368–371

stored, calling, 339–341

supplied by Oracle. See Oracle-supplied packages.

Packages, creating

information hiding, 335

package body, 335–336, 337–339

package specification, 335

package variables, 367–368

private elements, 341–344

Packages, extending

with additional procedures, 353–366

final_grade function, 355–366

manage_grades package specification, 354–356

median_grade function, 362–365

Parameterized cursors, 183–185

Parameters, passing to procedures

actual parameters, 317–318

data types, 318

default values, 318–319

formal parameters, 317–318

modes, 317–318

named notation, 318–319

OUT parameter, 315–319

IN OUT parameter, 316–317

IN parameter, 315–319

positional notation, 318–319

Parentheses ( )

controlling order of operations, 38

grouping for readability, 69, 252

Parse trees, 8

Passing

data types to procedures, 318

NULLS to dynamic SQL statements, 265–266

run-time values to bind arguments, 272

Passing parameters to procedures

actual parameters, 317–318

data types, 318

default values, 318–319

formal parameters, 317–318

modes, 317–318

named notation, 318–319

OUT parameter, 315–319

IN OUT parameter, 316–317

IN parameter, 315–319

positional notation, 318–319

Passwords

SQL Developer, case sensitivity, 10

SQL*Plus, displaying, 13

PAUSE_PROFILER routine, 433

P-code, 9

PDBs (pluggable databases), xxx

Performance. See Optimizing.

Performance optimizer, 438. See also Optimizing PL/SQL.

PL/SQL Scripts, 14–16

PL/SQL statements, 44. See also SQL statements; specific statements.

PLSQL_CODE_TYPE parameter, 9

PLSQL_DEBUG parameter, xxxvii

$$PLSQL_LINE directive, xxxvi–xxxvii

PL/SQL-only data types, xxvi–xxvii

PLSQL_OPTIMIZE_LEVEL parameter, 438

$$PLSQL_UNIT directive, xxxvi–xxxvii

$$PLSQL_UNIT_OWNER directive, xxxvi–xxxvii

$$PLSQL_UNIT_TYPE directive, xxxvi–xxxvii

Populating associative arrays, 227

Positional notation, procedure parameters, 318–319

PRAGMA INLINE statement, 445

Pragmas, definition, 153

PRECEDES option, 194

Predefined exceptions, 128–129

Predefined inquiry directives, new for Oracle 12c, xxxvi–xxxvii

Presentation tier, 3

Primary key values, generating. See Sequences.

Printing query results automatically, 348

PRIOR method, 233–235

Privileges for creating views, 207

Procedures. See also Modular code.

collections of. See Packages.

creating, 312–315

vs. functions, 322

getting information about, 314–315

identifying, 377–378

invalid, recompiling, 315

Procedures, displaying

data dictionary queries, 314–315

invalid, recompiling, 315

invalid vs. valid, 315

red X, 315

with SQL Developer, 315

Procedures, passing parameters

actual parameters, 317–318

data types, 318

default values, 318–319

formal parameters, 317–318

modes, 317–318

named notation, 318–319

OUT parameter, 315–319

IN OUT parameter, 316–317

IN parameter, 315–319

positional notation, 318–319

Profiler API, 432–433

PROFLOAD.sql script, 432–433

PROFTAB.sql script, 432–433

PROGRAM_ERROR exception, 128

PUT procedure, 408

PUTF procedure, 408

PUT_LINE procedure, 408

Q

Queries. See SQL queries.

Query results

printing automatically, 348

sharing. See Cursor variables.

R

RAISE statements

in conjunction with IF statements, 140

raising exceptions explicitly, 144–145

raising user-defined exceptions, 138

re-raising exceptions, 147

RAISE_APPLICATION_ERROR procedure, 149–153

Raising exceptions

explicitly, 144–145

implicitly, 127

re-raising exceptions, 147

user-defined, 138

RAW data type, xxx, 36

Read error, exception, 408

Readability (by humans)

dynamic SQL statements, 275

EXCEPTION_INIT pragma, 155

formatting IF statements, 66–67

formatting SELECT statements, 275

grouping with parentheses, 69, 252

inserting blank lines, 242

inserting blank spaces, 275

labels on nested blocks, 39–40

labels on nested loops, 120

WORK keyword, 51–52

READ_ERROR exception, 408

Reading

records from a database. See Fetching records.

text from an open file, 407

Record types

compatibility, 249–250

cursor based, 244–246, 249–250, 253–255

explicit cursors, 163–165

table based, 244–246, 249–250

user defined, 246–250, 255–256

Records

collections of, 253–256

compatibility, 248–250

cursor-based, 163–165

enclosing, 250

explicit cursors, 163–165

nested, 250–253

reading. See Fetching records.

table-based, 163–165

testing values of, 244

user-defined, 168–170

Red X on displayed procedures, 315

REF CURSOR data type, 345–346. See also Cursor variables.

REMOVE procedure, 410, 412

REPLACE reserved word, 192–193

Re-raising exceptions, 146–148

Reserved words, 29, 32–33

Restricted mode, turning on/off, 411

Restrictive (strong) cursor variables, 345–346

Result sets, sharing. See Cursor variables.

Result-caching, IR (invoker rights) functions, xxvi–xxvii

RESUME_PROFILER routine, 433

RETURN statements, 96

RETURNING clause, with BULK COLLECT clause, 295

RETURNING INTO clause, 261–262

RETURN_RESULT procedure, xxxi–xxxii

REVERSE option, 107–108

Roles, granting to PL/SQL packages and standalone subprograms, xxix–xxx

ROLLBACK statements, 49–51, 52, 195

%ROWCOUNT attribute, 170–174

Row-level triggers, 194, 205–206

Rows, locking for update, 187–189

%ROWTYPE attribute, 163–165, 244–246

RUN procedure, 410, 412

Runtime errors. See also Error handling; Exceptions.

vs. compilation errors, 124–126

in a declaration section, 142–143. See also Exception propagation.

definition, 7–8

error handling, 141–147

in an exception-handling section, 143–144. See also Exception propagation.

S

SAVE EXCEPTIONS option, 285–288

SAVEPOINT statements

breaking down large PL/SQL statements, 44

setting a save point, 49–51, 52–53

in triggers, 195

Saving database changes. See COMMIT statements.

Scheduling jobs, 410–413

Scope

cursors, 175

exceptions, 133–137

labels, 39–41

nested blocks, 39–41

variables, 39

Searched CASE statements

vs. CASE statements, 76–80

description, 74–80

Sections of blocks, 6–8

SELECT INTO statements, 44–47

Select list, cursors, 175

SELECT statements

dynamic, 259. See also Dynamic SQL.

formatting for readability, 275

returning no rows, 47

returning too many rows, 47

static, 259

SELF parameter, 395, 397, 398, 401

Semantic checking, 9

Semicolon (;)

block terminator, 16–17

dynamic SQL statement terminator, 264–265

variable terminator, 36–37

Sequences

accessing, 48

in blocks, 48–49

of contiguous numbers, 48

definition, 47

drawing numbers from, 48

incrementing, 48

uses for, 47

Serialized packages, 368–371

SERIALLY_REUSABLE pragma, 368–371

SET_PLSQL_TRACE routine, 434–436

Setting a save point. See SAVEPOINT statements.

SHO ERR command, 376

SID, default, 10

Simple loops

description, 92–93

EXIT statements, 93–97

EXIT WHEN statements, 97–98

infinite, 95

inner loops, 119

RETURN statements, 96

terminating, 93–98

Single quotes (‘ ‘), enclosing substitution variables, 25

Single-row queries, 261–271

Slash (/), block terminator, 16, 264

Slash asterisk... (/*...*/), multiline comments, 29, 40

Source code, displaying, 314–315

SQL cursors, 160

SQL Developer

connecting to a database, 10–11

connection name, 10

default SID, 10

definition, 9

disabling substitution variable verification, 23

disconnecting from a database, 11–12

displaying procedures, 315

executing a block, 14–16

executing a query, 14

getting started with, 10–11

launching, 10

password, 10

substitution variables, 19–25

user input at runtime. See Substitution variables.

user name, 10

SQL queries

implicit statement results, xxxi–xxxii

multirow, 271–280

new for Oracle 12c, xxxi–xxxii

single-row, 261–271

SQL statements. See also PL/SQL statements.

; (semicolon), statement terminator, 15

vs. PL/SQL, 14

SQL%BULK_EXCEPTIONS attribute, 286–287

SQLCODE function, 155–158. See also OTHERS exception; SQLERRM function.

SQLERRM function, 155–158. See also OTHERS exception; SQLCODE function.

SQL*Plus

/ (slash), block terminator, 16

; (semicolon), block terminator, 16–17

accessing, 11, 13

connecting to a database, 13

definition, 9

disabling substitution variable verification, 23

disconnecting from a database, 13

error isolation, 314

executing a query, 15

getting started with, 11–13

password, 13

substitution variables, 19–25

sqlplus command, 13

START_PROFILER routine, 432–433

START_PROFILING routine, 437

Statement-level triggers, 194, 205–206

Statements. See PL/SQL statements.

Static methods, 398–399

Static SELECT statements, 259

STOP_PROFILER routine, 432–433

STOP_PROFILING routine, 437

Stored code, examining

ALL_DEPENDENCIES view, 376–377

ALL_OBJECTS view, 374

with the data dictionary, 374–378

DBA_DEPENDENCIES view, 376–377

DBA_OBJECTS view, 374

debugging, 376

dependencies, displaying, 376–377

DESC command, 377–378

describing columns in a table, 377–378

displaying errors, 375–376

identifying procedures, packages, and functions, 377–378

overloading modules, 378–382

retrieving specified line numbers, 374–375

SHO ERR command, 376

USER_DEPENDENCIES view, 376–377

USER_ERRORS view, 375–376

USER_OBJECTS view, 374

Stored functions, creating, 322–325

Stored packages, calling, 339–341

Stored queries. See Views.

String operators, 39

Strong (restrictive) cursor variables, 345–346

STUDENT database schema, 461–468

SUBMIT procedure, 410

Submitting jobs, 410, 411–412. See also Job queue.

Subprogram inlining, 445–453

Subprograms, granting roles to, xxix–xxx

Substitution variables. See also Variables.

‘ ‘ (single quotes), enclosing in, 25

& (ampersand), name prefix, 20, 22, 25

&& (double ampersand), name prefix, 20, 24, 25

disabling, 25

disabling verification, 23

name prefix character, changing, 25

overview, 19–25

Syntax checking, 8–9

Syntax errors. See Compilation errors.

T

Table-based records

compatibility, 249–250

creating, 163–165

definition, 163

description, 244–246

Tables

mutating, 213–223

PL/SQL, 226. See also Associative arrays; Nested tables.

Tables, nested

vs. associative arrays and varrays, 239–240

creating, 229–230

initializing, 230–232

LIMIT method, 238

upper bounds, specifying, 238–239

Text file capabilities, 406–410

Three-tier architecture, 3

TOO_MANY_ROWS exception, 128

Trace API, 433–436

TRACE_ALL_CALLS constant, 434–436

TRACE_ALL_EXCEPTIONS constant, 434–436

TRACE_ALL_SQL constant, 434–436

TRACE_ENABLED_CALLS constant, 434–436

TRACE_ENABLED_EXCEPTION constant, 434–436

TRACE_ENABLED_SQL constant, 434–436

TRACE_PAUSE constant, 434–436

TRACE_RESUME constant, 434–436

TRACE_STOP constant, 434–436

TRACETAB.sql script, 433–436

Tracing order of execution, 433–436

Transaction control

and DML, 53–55

erasing changes. See ROLLBACK statements.

saving changes. See COMMIT statements.

setting a save point. See SAVEPOINT statements.

Transactional control statements, from triggers, 195

Transactions

vs. blocks, 50, 54–55

breaking down large statements, 44

definition, 43

grouping, 49

Triggering events, 192

Triggers. See also Modular code.

AFTER, 201–204

autonomous transactions, 203–204

BEFORE, 195–201

compound, 217–223

creating, 192–195, 197–201

defined on views, 206–211

definition, 192

in dropped tables, 195

enabling/disabling, 194

event, xxx

firing, 192

firing order, specifying, 194

INSTEAD OF clause, 206–211

issuing transactional control statements, 195

mutating table errors, 214–223

:NEW pseudorecords, 196–199

:OLD pseudorecords, 196–199

restrictions, 195

row-level, 194, 205–206

statement-level, 194, 205–206

types of, 205–211

uses for, 195

TRIM method, 233–235

Tuning PL/SQL. See Optimizing PL/SQL, tuning tools.

TYPE statements, 247–248

U

UDF pragma

creating functions, 330–331

creating user-defined functions, xxxiv–xxxv

Undoing database changes. See ROLLBACK statements.

Unique numbers, generating, 47–49

UPDATE statements. See also DML (Data Manipulation Language).

batch processing. See FORALL statements.

with BULK COLLECT clause, 295

Updating tables in a database, 187–190. See also UPDATE statements.

User name, SQL Developer, 10

User-defined exceptions

declaring, 137

description, 137–141

raising explicitly, 138–139

unhandled, 145

User-defined functions

creating with a UDF pragma, xxxiv–xxxv

creating with a WITH clause, xxxiv

running under SQL, xxxiv–xxxv

User-defined records

compatibility, 249–250

defining a collection on, 255–256

description, 168–170, 246–249

USER_DEPENDENCIES view, 376–377

USER_ERRORS view, 375–376

USER_OBJECTS view, 314–315, 374

USER_SOURCE view, 314–315

USING clause, 261–262

UTL_CALL_STACK package, 424–429

UTL_FILE package, 406–410

V

VALID blocks vs. INVALID, 9

VALUE_ERROR exception, 129

VALUES OF option, 289–290

VARCHAR2 data type, xxx, 35

Variables. See also Identifiers; Substitution variables.

; (semicolon), variable terminator, 36–37

case sensitivity, 29

constraining to not null, 32

declaring, 36–39

displaying values. See DBMS_OUTPUT.PUT_LINE statements.

in expressions, 38

with identical names, 121–122

naming conventions, 29–30

null values, 32

overview, 29–32

scope, 39

visibility, 40

Variables, initializing

with an assignment operator, 36–39

with CASE expressions, 83–84

to a null value, 32

with SELECT INTO statements, 44–47, 83–84

Varrays

declaring, 236–238

definition, 235–236

nested, 240–242

vs. nested tables and associative arrays, 239–240

upper bounds, setting, 238–239

View queries, 208. See also SELECT statements.

Views, creating

BEQUEATH CURRENT_USER clause, xxxii

BEQUEATH DEFINER clause, xxxii

as an IR (invoker rights) unit, xxxii

new for Oracle 12c, xxxii

privileges for, 207

Views, triggers defined on, 206–211

Visibility of variables, 40

W

Weak (nonrestrictive) cursor variables, 345–346

Website, companion to this book. See Companion Website.

WHAT parameter, 411

WHERE CURRENT OF clause, 189–190

WHILE loops

Boolean expressions as test conditions, 101

description, 98–101

infinite, 100

outer loops, 119

premature termination, 101–103

WHILE reserved word, 99

White space, formatting guide, 455–456

WITH clause

creating functions, 329–330

creating user-defined functions, xxxiv

WORK keyword, for readability, 51–52

Write error, exception, 408

WRITE_ERROR exception, 408

Z

ZERO_DIVIDE exception, 128