Index

of DECLARE CURSOR command, F-15

of CONNECT command, F-13

of DECLARE STATEMENT command, F-18

of EXECUTE command, F-28

of EXECUTE IMMEDIATE command, F-31

of INSERT command, F-35

of SAVEPOINT command, F-44

of SELECT command, F-48

of UPDATE command, F-50

restrictions, 3-50

AUTO_CONNECT option, 7-13

instead of CONNECT statement, 3-45

automatic logon, 3-44, 3-47

B

batch fetch, 10-4

example, 10-4

number of rows returned, 10-5

bind descriptor, 12-4

information in, 11-27

bind SQLDA, 12-3

bind variable, 11-26

binding, 11-5

BNDDFCLP variable (SQLDA), 12-13

BNDDFCRCP variable (SQLDA), 12-13

BNDDFMT variable (SQLDA), 12-9

BNDDH-CUR-VNAMEL variable (SQLDA), 12-12

BNDDH-MAX-VNAMEL variable (SQLDA), 12-12

BNDDH-VNAME variable (SQLDA), 12-12

BNDDI variable (SQLDA), 12-11

BNDDI-CUR-VNAMEL variable (SQLDA), 12-13

BNDDI-MAX-VNAMEL variable (SQLDA), 12-13

BNDDI-VNAME variable (SQLDA), 12-13

BNDDV variable (SQLDA), 12-8

BNDDVLN variable (SQLDA), 12-9

BNDDVTYP variable (SQLDA), 12-10

C

callback, user exit, 13-14

CHAR column

maximum width, 4-3

CHAR datatype

external, 4-10

internal, 4-3

character host variables

as output variables, 3-41

handling, 3-39

server handling, 3-41

character sets

character strings

CHARF datatype

CHARF datatype specifier, 4-25

using in TYPE statement, 4-25

using in VAR statement, 4-25

child cursor, 6-19

CLOSE command, F-9

examples, F-10

CLOSE statement, 5-14, 6-34

example, 5-14

in dynamic SQL Method 4, 12-39

CLOSE_ON_COMMIT precompiler option, 7-14

closing

cursors, F-9

COBOL datatypes, 3-13

COBOL-74 restrictions, 3-8

code page, 4-32

coding area

for paragraph names, 3-7

coding conventions, 3-2

column list, 5-9

column, ROWLABEL, 4-8

Comment, 11-31

COMMENT clause

embedded SQL statements, 3-3

Comments

ANSI SQL-style, 3-3

C-style, 3-3

commit, 8-3

automatic, 8-3

explicit versus implicit, 8-3

COMMIT command, F-10

ending a transaction, F-42

examples, F-12

COMMIT statement, 8-4

effects, 8-4

example, 8-4

RELEASE option, 8-4

using in a PL/SQL block, 8-15

where to place, 8-4

committing

transactions, F-10

communicating over a network, 3-47

compilation, 7-42

compliance, ANSI/ISO, xxix

composite type, 12-18

concurrency, 8-2

concurrent logon, 3-46

conditional precompilation, 7-39

defining symbols, 7-40

example, 7-40

CONFIG option, 7-14, 7-15, 7-17, 7-32

configuration file

system versus user, 7-15

CONNECT command, F-12

examples, F-14

CONNECT statement

ALTER AUTHORIZATION clause, 3-55

AT clause, 3-49

enabling a semantic check, E-4

logging on to Oracle, 3-43

placement, 3-43

requirements, 3-43

USING clause, 3-49

when not required, 3-45

connecting to Oracle, 3-43

connection

default versus non-default, 3-47

implicit, 3-53

naming, 3-48

continuation lines

syntax, 3-4

CONTINUE action, 9-29

CONTINUE option

CONVBUFSZ clause in VAR statement, 4-23

CREATE PROCEDURE statement, 6-21

creating

savepoints, F-44

CURRENT OF clause, 5-15

example, 5-15

mimicking with ROWID, 8-12, 10-14

of embedded SQL DELETE command, F-24

of embedded SQL UPDATE command, F-51

restrictions, 5-15

current row, 2-8

CURRVAL pseudocolumn, 4-7

cursor, 5-11

analogy, 2-8

association with query, 5-11

child, 6-19

declaring, 5-11

effects on performance, D-7

explicit versus implicit, 2-8

naming, 5-12

parent, 6-19

reopening, 5-13, 5-14

restricted scope of, 7-42

restrictions, 5-12

scope, 5-12

using for multirow queries, 5-11

using more than one, 5-12

when closed automatically, 5-14

cursor cache, 6-19, 9-37

gathering statistics about, 9-39

purpose, 9-35, D-9

cursor variable, 6-30, F-8

cursor variables

error conditions, 6-35

heap memory usage, 6-31

opening

anonymous block, 6-33

stored procedure, 6-31

cursors

fetching rows from, F-32

opening, F-37

D

data definition danguage (DDL)

description, 5-2

data description language (DDL)

embedded, 3-6

data integrity, 8-2

data lock, 8-2

Data Manipulation Language (DML), 5-7

database link

creating a synonym, 3-54

defining, 3-53

database links

using in DELETE command, F-24

using in INSERT command, F-36

using in UPDATE command, F-51

datatype

internal versus external, 2-7

datatype conversion

between internal and external types, 4-18

datatype equivalencing, 2-8

advantages, 4-20

example, 4-23

guidelines, 4-26

datatypes

COBOL, 3-13

coercing NUMBER to VARCHAR2, 12-19

conversions, 4-17

dealing with Oracle internal, 12-19

descriptor codes, 12-19

equivalencing

description, 4-20

example, 4-23

internal, 12-15

need to coerce, 12-19

PL/SQL equivalents, 12-18

DATE datatype

internal format, 4-11

DATE String Format, explicit control over, 4-19

DATE_FORMAT precompiler option, 7-15

DBMS option, 7-16

DDL, 3-6

DDL (data definition language), 5-2

deadlock, 8-2

effect on transactions, 8-7

how broken, 8-7

DECIMAL datatype, 4-12

declaration

cursor, 5-11

host array, 10-2

host variable, 2-14

indicator variable, 2-15

declarative SQL statement, 2-3

using in transactions, 8-3

DECLARE CURSOR command, F-14

examples, F-16

DECLARE CURSOR statement

AT clause, 3-50

in dynamic SQL Method 4, 12-30

DECLARE DATABASE directive, F-17

Declare Section

example, 3-10

using more than one, 3-10

declare section

allowable statements, 3-9

COBOL datatypes supported, 3-13

defining usernames and passwords, 3-43

purpose, 3-9

requirements, 3-9

rules for defining, 3-9

DECLARE statement, 5-11

example, 5-11

using in dynamic SQL Method 3, 11-20

where to place, 5-12

DECLARE STATEMENT command, F-18

examples, F-19

scope of, F-19

DECLARE STATEMENT statement

AT clause, 3-51

example, 11-29

using in dynamic SQL, 11-29

when required, 11-29

DECLARE TABLE command, F-20

examples, F-21

DECLARE TABLE statement

need for with AT clause, 3-50

using with the SQLCHECK option, E-4

DECLARE_SECTION, 7-17

DECLARE_SECTION precompiler option, 7-17

declaring

cursor variables, 6-30

host tables, 3-33

host variables, 3-13

indicator variables, 3-30

ORACA, 9-36

SQLCA, 9-20

SQLDA, 12-7

VARCHAR variables, 3-36

default

error handling, 9-27

setting of FORMAT option, 3-2

setting of LITDELIM option, 3-4, 7-25

setting of ORACA option, 9-37

default connection, 3-47

default database, 3-47

DEFINE option, 7-18

DELETE command, F-21

embedded SQL examples, F-25

DELETE statement, 5-10

example, 5-10

using host arrays, 10-9

WHERE clause, 5-10

DEPENDING ON clause, 3-33

DEPT table, 2-15

DESCRIBE BIND VARIABLES statement

in dynamic SQL Method 4, 12-30

DESCRIBE command, F-26

example, F-27

use with PREPARE command, F-26

DESCRIBE SELECT LIST statement

in dynamic SQL Method 4, 12-35

DESCRIBE statement

using in dynamic SQL Method 4, 11-26

descriptor, 11-26

naming, F-26

descriptors

bind descriptor, 12-4

purpose, 12-4

select descriptor, 12-4

SQLADR subroutine, 12-3

dimension of host tables, 3-33

directory, 2-12

current, 2-12

path for INCLUDE files, 2-12

directory path

INCLUDE files, 3-11

DISPLAY datatype, 4-12

distributed processing, 3-46

distributed transactions, F-43

DML (Data Manipulation Language), 5-7

DNSTIAR error codes, 9-27

DO action, 9-29

DO option

advantages and disadvantages, 11-3

DSNTIAR routine, 9-27

DTP model, 4-36

dummy host variable, 11-4

dynamic PL/SQL, 11-30

dynamic SQL

choosing the right method, 11-7

using the AT clause, 3-51

dynamic SQL Method 1

using EXECUTE IMMEDIATE, 11-9

dynamic SQL Method 2

using the DECLARE STATEMENT statement, 11-29

using the EXECUTE statement, 11-14

using the PREPARE statement, 11-14

dynamic SQL Method 3

commands, 11-6

compared to Method 2, 11-19

requirements, 11-6

using PL/SQL, 11-31

using the DECLARE statement, 11-20

using the DECLARE STATEMENT statement, 11-29

using the FETCH statement, 11-21

using the OPEN statement, 11-21

using the PREPARE statement, 11-20

dynamic SQL Method 4

CLOSE statement, 12-39

DECLARE CURSOR statement, 12-30

DESCRIBE statement, 12-30, 12-35

external datatypes, 12-16

FETCH statement, 12-38

internal datatypes, 12-15

OPEN statement, 12-35

PREPARE statement, 12-30

prerequisites, 12-14

purpose of descriptors, 12-4

requirements, 11-6, 12-2

sequence of statements used, 12-23

SQLDA, 12-4

steps for, 12-22

using descriptors, 11-26

using PL/SQL, 11-31

using the DECLARE STATEMENT statement, 11-29

using the DESCRIBE statement, 11-26

using the FOR clause, 11-30

using the SQLDA, 11-26

when needed, 11-26

dynamic SQL statement, 11-3

binding of host variables, 11-5

how processed, 11-4

requirements, 11-4

using host arrays, 11-30

using placeholders, 11-4

versus static SQL statement, 11-3

E

embedded PL/SQL

indicator variables, 4-30

multi-byte NLS features, 4-29

need for SQLCHECK option, 6-7

need for USERID option, 6-7

user-defined record, 6-5

using %TYPE, 6-2

using the VARCHAR pseudotype, 6-10

using to improve performance, D-3

VARCHAR variables, 4-29

where allowed, 4-29, 6-6

embedded SQL

ALLOCATE command, F-8

CLOSE command, F-9

COMMIT command, F-10

CONNECT command, F-12

DECLARE CURSOR command, F-14

DECLARE STATEMENT command, F-18

DECLARE TABLE command, F-20

DELETE command, F-21

DESCRIBE command, F-26

EXECUTE command, F-27, F-29

EXECUTE IMMEDIATE command, F-30

PREPARE command, F-39

SAVEPOINT command, F-44

SELECT command, F-45

UPDATE command, F-49

VAR command, F-53

versus interactive SQL, 2-5

when to use, 1-3

WHENEVER command, F-55

embedded SQL statement

mixing with host-language statements, 2-5

syntax, 2-5

embedded SQL statements

associating paragraph names with, 3-7

Comments, 3-3

continuing from one line to the next, 3-3

figurative constants, 3-5

referencing host tables, 3-34

referencing host variables, 3-19

referencing indicator variables, 3-30

requirements, 3-4

syntax, 3-4

terminator, 3-8

embedding

PL/SQL blocks in Oracle7 precompiler programs, F-27

EMP table, 2-15

encoding scheme, 4-32

END, 7-19

END_OF_FETCH, 7-19

END_OF_FETCH precompiler option, 7-19

Entry SQL, xxx

equivalencing

host variable equivalencing, F-53

equivalencing datatypes, 4-20

error conditions

cursor variable, 6-35

error detection

error reporting, F-55

error handling

using status variables

SQLCA, 9-3, 9-19

SQLCODE, 9-3, 9-5

SQLSTATE, 9-3

using the ROLLBACK statement, 8-6

using the SQLGLS function, 9-32

error message text

SQLGLM subroutine, 9-25

error messages

maximum length, 9-26

error reporting

error message text, 9-22

key components of, 9-21

parse error offset, 9-21

rows-processed count, 9-21

status codes, 9-21

warning flags, 9-21

WHENEVER command, F-55

ERRORS option, 7-19

exception, PL/SQL, 6-12

EXEC ORACLE DEFINE statement, 7-39

EXEC ORACLE ELSE statement, 7-39

EXEC ORACLE ENDIF statement, 7-39

EXEC ORACLE IFDEF statement, 7-39

EXEC ORACLE IFNDEF statement, 7-39

EXEC ORACLE statement

scope of, 7-8

syntax for, 7-7

uses for, 7-8

using to enter options inline, 7-7

EXEC SQL clause, 2-5, 3-4

EXEC TOOLS statement, 13-14

GET, 13-15

MESSAGE, 13-16

SET, 13-14

EXECUTE command, F-27, F-29

examples, F-28, F-30

EXECUTE IMMEDIATE command, F-30

examples, F-31

EXECUTE IMMEDIATE statement

AT clause, 3-51

using in dynamic SQL Method 1, 11-9

EXECUTE optional keyword of ARRAYLEN statement, 6-17

EXECUTE statement

using in dynamic SQL Method 2, 11-14

execution plan, D-5

EXPLAIN PLAN statement

dynamic SQL Method 4, 12-16

explicit logon, 3-47

multiple, 3-51

single, 3-48

external datatype

CHAR, 4-10

CHARF, 4-10

DATE, 4-11

FLOAT, 4-12

LONG, 4-12

RAW, 4-14

ROWID, 4-14

external datatypes

general, 4-9

F

FETCH command, F-32

examples, F-34

used after OPEN command, F-38

FETCH statement, 5-13, 5-14, 6-33

cursor variable, 6-34

example, 5-13

in dynamic SQL Method 4, 12-38

INTO clause, 5-13

using in dynamic SQL Method 3, 11-21

fetch, batch, 10-4

fetching

rows from cursors, F-32

figurative constants

embedded SQL statements, 3-5

file extension

for INCLUDE files, 3-11

flags, 9-21

of embedded SQL EXECUTE command, F-30

of embedded SQL INSERT command, F-36

restrictions, 10-12

using with host arrays, 10-11

FOR UPDATE OF clause, 8-11

FORCE clause

of ROLLBACK command, F-42

format mask, 4-19

FORMAT option, 7-21

purpose, 3-2

forward reference, 5-12

full scan, D-6

G

GENXTB form

running, 13-12

GOTO action, 9-29

GOTO option

allowed as host variables, 3-20

group items

implicit VARCHAR, 3-37

guidelines

datatype equivalencing, 4-26

dynamic SQL, 11-7

host variable, 2-15

separate precompilation, 7-41

transaction, 8-14

user exit, 13-13

H

heap, 9-37

heap memory

allocating cursor variables, 6-31

hint, optimizer, D-5

hints

in DELETE statements, F-25

in SELECT statements, F-48

in UPDATE statements, F-52

HOLD_CURSOR option, 7-22

of ORACLE Precompilers, F-9

using to improve performance, D-12

restrictions, 10-6, 10-8, 10-9, 10-10

using in dynamic SQL statements, 11-30

using in the DELETE statement, 10-9

using in the INSERT statement, 10-7

using in the SELECT statement, 10-3

using in the UPDATE statement, 10-8

using in the WHERE clause, 10-13

using the FOR clause, 10-11

using to improve performance, D-3

host tables

multi-dimensional, 3-33

referencing, 3-34

restrictions, 3-33

support for, 3-18

variable-length, 3-33

host variable, 5-2

assigning a value, 2-6

declaring, 2-14

dummy, 11-4

guidelines, 2-15

input versus output, 5-2

using in EXEC TOOLS statements, 13-14

using in PL/SQL, 6-7

using in user exit, 13-5

where allowed, 2-7

host variables

declaring, 3-2, 3-9, 3-13

definition, 3-5

host variable equivalencing, F-53

in EXECUTE command, F-30

in OPEN command, F-38

initializing, 3-18

naming, 3-20, 3-22

referencing, 3-19

restrictions, 3-5, 3-22

with PL/SQL, 4-29

hyphenation

of host variable names, 3-5

I

IAF GET statement

example, 13-6

specifying block and field names, 13-6

using in user exit, 13-5

IAF PUT statement

example, 13-7

specifying block and field names, 13-7

using in user exit, 13-6

IAP, 13-12

identifiers, ORACLE

IN OUT parameter mode, 6-4

IN parameter mode, 6-4

INAME option, 7-23

when a file extension is required, 7-2

INCLUDE file, 2-12

INCLUDE option, 7-24

INCLUDE statement, 2-12

case-sensitive operating systems, 3-12

declaring the ORACA, 9-36

declaring the SQLCA, 9-20

declaring the SQLDA, 12-7

effect of, 3-11

index

association with host variable, 5-3

indicator array, 10-2

indicator tables

example, 3-35

purpose, 3-35

indicator variable, 5-3

declaring, 2-15

interpreting value, 5-3

referencing, 2-15

using in PL/SQL, 6-11

using to detect truncated values, 5-4

using to handle nulls, 5-4, 5-5

using to test for nulls, 5-6

indicator variables

association with host variables, 3-30

nulls, 4-30

truncated values, 4-30

used with multi-byte character strings, 4-33

with PL/SQL, 4-30

in-doubt transaction, 8-13

input host variable

restrictions, 5-2

where allowed, 5-2

INSERT command, F-34

embedded SQL examples, F-37

INSERT statement, 5-9

column list, 5-9

example, 5-9

INTO clause, 5-9

using host arrays, 10-7

VALUES clause, 5-9

inserting

rows into tables and views, F-34

INTEGER datatype, 4-12

interface

native, 4-36

XA, 4-36

internal datatype

CHAR, 4-3

DATE, 4-3

definition, 2-7

LONG, 4-3

LONG RAW, 4-4

MLSLABEL, 4-4

NUMBER, 4-4

RAW, 4-5

ROWID, 4-5

VARCHAR2, 4-5

internal datatypes

dynamic SQL Method 4, 12-15

general, 4-2

INTO clause, 5-2, 6-33

FETCH statement, 5-13

INSERT statement, 5-9

of FETCH command, F-33

of SELECT command, F-48

SELECT statement, 5-8

IRECLEN option, 7-24

IS NULL operator

for testing null values, 3-6

J

Julian date, 4-3

L

language support, 1-3

LDA, 4-34

LEVEL pseudocolumn, 4-7

link, database, 3-53

linking, 7-42

LITDELIM option, 3-4, 7-25

purpose, 7-25

LNAME option, 7-26

location transparency, 3-54

lock

released by ROLLBACK statement, F-42

LOCK TABLE statement, 8-12

example, 8-12

using the NOWAIT parameter, 8-12

locking, 8-2, 8-11

explicit versus implicit, 8-11

modes, 8-2

overriding default, 8-11

privileges needed, 8-14

using the FOR UPDATE OF clause, 8-11

using the LOCK TABLE statement, 8-12

logging on

logon

Logon Data Area (LDA), 4-34

LONG column

maximum width, 4-3

LONG datatype

compared with CHAR, 4-3

LONG RAW column

LONG RAW datatype

compared with LONG, 4-4

LONG VARCHAR datatype, 4-13

LONG VARRAW datatype, 4-13

LRECLEN option, 7-26

LTYPE option, 7-27

M

MAXLITERAL option, 7-27

MAXOPENCURSORS option, 7-28

using for separate precompilation, 7-41

MLSLABEL datatype

status variables, 9-2

mode, parameter, 6-4

monitor, transaction processing, 4-35

multi-byte character sets, 4-32

multi-byte NLS features

datatypes, 3-6

with PL/SQL, 4-29

N

namespaces

reserved by Oracle, C-8

naming

host variables, 3-5

of database objects, F-7

select-list items, 12-4

naming conventions

cursor, 5-12

default database, 3-47

host variable, 2-14

SQL*Forms user exit, 13-13

national language support (NLS), 4-30

native interface, 4-36

nested programs

sample, 3-24

support for, 3-23

Net8

connecting using, 3-44

function of, 3-47

network

communicating over, 3-47

protocols, 3-47

reducing traffic, D-4

NEXTVAL pseudocolumn, 4-7

nibble, 4-27

NIST

compliance, xxix

NLS (national language support), 4-30

multi-byte character strings, 4-32

NLS parameter

NLS_CURRENCY, 4-31

NLS_DATE_FORMAT, 4-31

NLS_DATE_LANGUAGE, 4-31

NLS_ISO_CURRENCY, 4-31

NLS_LANG, 4-31

NLS_LANGUAGE, 4-31

NLS_NUMERIC_CHARACTERS, 4-31

NLS_SORT, 4-31

NLS_TERRITORY, 4-31

NLS_LOCAL

precompiler option, 7-30

node

definition of, 3-47

NOT FOUND condition, 9-28

using in LOCK TABLE statement, 8-12

NOWAIT parameter, 8-12

null

nulls

handling

in dynamic SQL Method 4, 12-21

indicator variables, 4-30

meaning in SQL (NVL function), 3-6

SQLNUL subroutine, 12-21

null-terminated string, 4-15

NUMBER datatype

external, 4-13

internal, 4-4

using the SQLPRC subroutine with, 12-20

NVL function

for retrieving null values, 3-6

O

OCI

declaring LDA, 4-34

embedding calls, 4-34

in dynamic SQL Method 4, 12-35

using in dynamic SQL Method 3, 11-21

OPEN_CURSORS parameter, 6-20

opening

cursors, F-37

opening a cursor variable, 6-31

operators

options

ORACA, 9-4

gathering cursor cache statistics, 9-39

precompiler option, 9-37

Oracle Call Interface, 4-34

Oracle Communications Area

ORACA, 9-35

Oracle datatypes, 2-7

Oracle Forms

using EXEC TOOLS statements, 13-14

ORACLE identifiers

how to form, F-7

Oracle namespaces, C-8

Oracle Open Gateway

using ROWID datatype, 4-15

Oracle Precompilers

advantages, 1-3

function, 1-2

language support, 1-3

Oracle Toolset, 13-14

ORACOC

ORANEX

ORASFNM, in ORACA, 9-39

ORASLNR

OUT parameter mode, 6-4

output host variable, 5-2

P

PAGELEN option, 7-32

paragraph names

associating with SQL statements, 3-7

coding area for, 3-7

parameter mode, 6-4

parent cursor, 6-19

parse, 11-4

parse error offset, 9-21

parsing dynamic statements

PREPARE command, F-39

password

defining, 3-43

hardcoding, 3-43

passwords

changing at runtime, 3-55

passwords, changing at runtime, 3-55

performance

improving, D-3

reasons for poor, D-2

PICX, 7-32

new default, 3-39

PICX precompiler option, 7-32

PL/SQL, 1-4

, 9-25

advantages, 1-4

cursor FOR loop, 6-3

datatype equivalents, 12-18

embedded, 4-29

exception, 6-12

integration with server, 6-2

opening a cursor variable

anonymous block, 6-33

stored procedure, 6-31

package, 6-4

relationship with SQL, 1-4

subprogram, 6-3

user-defined record, 6-5

PL/SQL blocks

embedded in Oracle7 precompiler programs, F-27

PL/SQL table, 6-5

supported datatype conversions, 6-15

placeholder

duplicate, 11-15, 11-31

naming, 11-15

using in dynamic SQL statements, 11-4

precompilation unit, 7-9

precompiler, 1-2

precompiler command

optional arguments of, 7-3

required arguments, 7-2

precompiler directives

EXEC SQL DECLARE DATABASE, F-17

precompiler options

abbreviating name, 7-4

ASACC, 7-12

ASSUME_SQLCODE, 7-12

AUTO_CONNECT, 3-45, 7-13

CLOSE_ON_COMMIT, 7-14

CONFIG, 7-14, 7-15, 7-17, 7-32

DATE_FORMAT, 7-15

DBMS, 7-16

DECLARE_SECTION, 7-17

entering on the command line, 7-7

ERRORS, 7-19

FIPS, 7-20

FORMAT, 7-21

HOLD_CURSOR, 7-22

HOST, 7-23

INAME, 7-23

LNAME, 7-26

LTYPE, 7-27

MODE, 3-39, 7-29, 9-2, 9-4

ONAME, 7-30

PICX, 7-32

preface

Send Us Your Comments, xxi

PREPARE command, F-39

examples, F-40

PREPARE statement

effect on data definition statements, 11-6

in dynamic SQL Method 4, 12-30

using in dynamic SQL, 11-14, 11-20

private SQL area

association with cursors, 2-8

opening, 2-8

purpose, D-9

Program Global Area (PGA), 6-19

program termination, 8-9

programming guidelines, 3-2

programming language support, 1-3

LEVEL, 4-7

ROWID, 4-8

pseudotype, VARCHAR, 2-14

Q

query, 5-7

association with cursor, 5-11

multirow, 5-7

single-row versus multirow, 5-8

R

RAW column

maximum width, 4-5

RAW datatype

compared with CHAR, 4-5

RAWTOHEX function, 4-27

read consistency, 8-2

READ ONLY parameter

using in SET TRANSACTION, 8-10

read-only transaction, 8-10

ending, 8-10

example, 8-10

record, user-defined, 6-5

REDEFINES clause

reference

indicator variable, 2-15

reference cursor, 6-29

referencing

host tables, 3-34

host variables, 3-19

indicator variables, 3-30

VARCHAR variables, 3-38

relational operators, 3-8

RELEASE option, 8-4, 8-9

COMMIT statement, 8-4

omitting, 8-10

restrictions, 8-9

ROLLBACK statement, 8-6

RELEASE_CURSOR option, 7-33

of ORACLE Precompilers, F-9

using to improve performance, D-12

what it affects, D-7

remote database

declaration of, F-17

resource manager, 4-35

restrictions

AT clause, 3-50

COBOL-74, 3-8

CURRENT OF clause, 5-15

cursor declaration, 5-12

cursor variables, 6-34

dynamic SQL, 3-6

FOR clause, 10-12

host array, 10-6, 10-8, 10-9, 10-10

host tables, 3-33

host variables, 3-22

naming, 3-5

referencing, 3-22

input host variable, 5-2

LONG datatype, 4-4

LONG RAW datatype, 4-4

RAW datatype, 4-5

REDEFINES clause, 3-7

RELEASE option, 8-9

separate precompilation, 7-41

SET TRANSACTION statement, 8-10

SQLCHECK option, E-2

SQLGLM subroutine, 9-26

SQLIEM subroutine, 9-26

TO SAVEPOINT clause, 8-9

retrieving rows from a table

embedded SQL, F-45

return code, 13-8

roll back

to a savepoint, F-44

to the same savepoint multiple times, F-42

rollback

automatic, 8-6

purpose, 8-3

statement-level, 8-7

ROLLBACK command, F-41

ending a transaction, F-42

examples, F-43

rollback segment, 8-2

ROLLBACK statement, 8-5

effects, 8-5

example, 8-6

RELEASE option, 8-6

TO SAVEPOINT clause, 8-5

using in a PL/SQL block, 8-15

using in error-handling routines, 8-6

where to place, 8-6

rolling back

transactions, F-41

row lock

acquiring with FOR UPDATE OF, 8-11

using to mimic CURRENT OF, 8-12, 10-14

ROWID datatype

ROWID pseudocolumn, 4-8

ROWLABEL column, 4-8

ROWNUM pseudocolumn, 4-8

rows

fetching from cursors, F-32

inserting into tables and views, F-34

updating, F-49

rows-processed count, 9-21

S

sample database table

DEPT table, 2-15

EMP table, 2-15

sample programs

calling a stored procedure, 6-24

cursor operations, 5-17

cursor variables

PL/SQL source, 6-35

Pro*COBOL source, 6-36

datatype equivalencing, 5-19

dynamic SQL Method 1, 11-10

dynamic SQL Method 2, 11-15

dynamic SQL Method 3, 11-21

dynamic SQL Method 4, 12-45

fetching in batches, 10-15

Oracle Forms user exit, 13-9

simple query, 2-17

savepoint, 8-7

when erased, 8-9

SAVEPOINT command, F-44

examples, F-44

SAVEPOINT statement, 8-7

example, 8-7

savepoints

creating, F-44

SAVEPOINTS parameter, 8-9

scalar type, 12-18

Scale

using SQLPRC to extract, 4-22

scale, 4-4

definition of, 4-22

when negative, 4-22

scope

cursor variables, 6-31

of DECLARE STATEMENT command, F-19

of precompiler options, 7-9

of the EXEC ORACLE statement, 7-8

WHENEVER statement, 9-31

search condition, 5-10

using in the WHERE clause, 5-10

SELDFCLP variable (SQLDA), 12-13

SELDFCRCP variable (SQLDA), 12-13

SELDFMT variable (SQLDA), 12-9

SELDH-CUR-VNAMEL variable (SQLDA), 12-12

SELDH-MAX-VNAMEL variable (SQLDA), 12-12

SELDH-VNAME variable (SQLDA), 12-12

SELDI variable (SQLDA), 12-11

SELDI-CUR-VNAMEL variable (SQLDA), 12-13

SELDI-MAX-VNAMEL variable (SQLDA), 12-13

SELDI-VNAME variable (SQLDA), 12-13

SELDV variable (SQLDA), 12-8

SELDVLN variable (SQLDA), 12-9

SELDVTYP variable (SQLDA), 12-10

SELECT command, F-45

embedded SQL examples, F-48

select descriptor, 12-4

information in, 11-27

select list, 5-8

select SQLDA

purpose of, 12-3

SELECT statement, 5-8

available clauses, 5-9

example, 5-8

INTO clause, 5-8

using host arrays, 10-3

SELECT_ERROR option, 5-8, 7-34

select-list items

naming, 12-4

semantic checking, E-2

enabling, E-3

using the SQLCHECK option, E-2

Send Us Your Comments

boilerplate, xxi

separate precompilation

sessions

using a subquery, 5-10

SET TRANSACTION statement, 8-10

example, 8-10

READ ONLY parameter, 8-10

restrictions, 8-10

snapshot, 8-2

SQL

summary of commands, F-3

SQL codes

returned by SQLGLS function, 9-33

SQL Communications Area, 2-14

SQL Descriptor Area, 11-26, 12-4

SQL standards conformance, xxix

SQL statement

controlling transactions, 8-3

optimizing to improve performance, D-5

static versus dynamic, 2-6

using to control a cursor, 5-8, 5-11

using to manipulate Oracle data, 5-7

SQL*Connect

using ROWID datatype, 4-15

SQL*Forms

IAP constants, 13-8

returning values to, 13-8

user exit, 13-3

SQL*Net

concurrent logons, 3-46

connection syntax, 3-47

using to connect to Oracle, 3-46

SQL*Plus, 1-4

SQL_CURSOR, F-8

SQL92

conformance, xxix

minimum requirement, xxx

SQLADR subroutine

example, 12-26

parameters, 12-14

storing buffer addresses, 12-3

syntax, 12-14

SQLCA, 9-3

components set for a PL/SQL block, 9-25

fields, 9-22

interaction with Oracle, 2-14

SQLERRD(3) field, 9-24

SQLERRD(5) field, 9-24

SQLERRMC field, 9-23

SQLERRML field, 9-23

SQLWARN(4) flag, 9-25

SQLWARN(5) flag, 9-25

using in separate precompilations, 7-41

using with SQL*Net, 9-19

SQLCA status variable

data structure, 9-20

declaring, 9-20

effect of MODE option, 9-4

explicit versus implicit checking, 9-3

purpose, 9-19

SQLCABC field, 9-22

SQLCAID field, 9-22

SQLCHECK option, 7-35

restrictions, E-2

using the DECLARE TABLE statement, E-4

using to check syntax/semantics, E-1

SQLCODE

declaring, 9-5

SQLCODE field, 9-22

interpreting its value, 9-22

SQLCODE status variable

declaring, 9-5

description, 9-3

effect of MODE option, 9-4

SQL92 deprecated feature, 9-3

usage, 9-4

SQLCODE variable

interpreting values of, 9-9

SQLDA, 11-26, 11-27

bind versus select, 11-27

BNDDFCLP variable, 12-13

BNDDFCRCP variable, 12-13

BNDDFMT variable, 12-9

BNDDH-CUR-VNAMEL variable, 12-12

BNDDH-MAX-VNAMEL variable, 12-12

BNDDH-VNAME variable, 12-12

BNDDI variable, 12-11

BNDDI-CUR-VNAMEL variable, 12-13

BNDDI-MAX-VNAMEL variable, 12-13

BNDDI-VNAME variable, 12-13

BNDDV variable, 12-8

BNDDVLN variable, 12-9

BNDDVTYP variable, 12-10

declaring, 12-7

example, 12-7

information stored in, 11-27

purpose, 12-4

SELDFCLP variable, 12-13

SELDFCRCP variable, 12-13

SELDFMT variable, 12-9

SELDH-CUR-VNAMEL variable, 12-12

SELDH-MAX-VNAMEL variable, 12-12

SELDH-VNAME variable, 12-12

SELDI variable, 12-11

SELDI-CUR-VNAMEL variable, 12-13

SELDI-MAX-VNAMEL variable, 12-13

SELDI-VNAME variable, 12-13

SELDV variable, 12-8

SELDVLN variable, 12-9

SELDVTYP variable, 12-10

SQLADR subroutine, 12-14

SQLDFND variable, 12-8

SQLDNUM variable, 12-8

structure, 12-8

SQLDFND variable (SQLDA), 12-8

SQLDNUM variable (SQLDA), 12-8

SQLERRD(3) field, 9-24

using with batch fetch, 10-5

SQLERRD(3) variable, 9-21

SQLERRD(5) field, 9-24

SQLERRMC field, 9-23

SQLERRMC variable, 9-22

SQLERRML field, 9-23

SQLERROR

WHENEVER command condition, F-55

SQLERROR condition, 9-28

SQLFC parameter, 9-33

SQLGLM subroutine

SQLGLS function

SQL codes returned by, 9-33

syntax, 9-32

using to obtain SQL text, 9-32

SQLGLS routine, 9-32, 9-33

SQLIEM function

replacement for, 13-14

using in user exit, 13-8

SQLIEM subroutine

SQLNUL subroutine

SQLPR2 subroutine, 12-21

SQLPRC subroutine

SQLSTATE

SQLSTATE status variable

class code, 9-10

coding scheme, 9-10

effect of MODE option, 9-4

interpreting values, 9-10

predefined classes, 9-11

predefined status codes and conditions, 9-12

subclass code, 9-10

usage, 9-4

SQLSTM parameter, 9-33

SQLSTM routine, 9-33

SQLWARN(4) flag, 9-25

SQLWARN(5) flag, 9-25

SQLWARNING

WHENEVER command condition, F-55

SQLWARNING condition, 9-28

statement-level rollback, 8-7

breaking deadlocks, 8-7

status codes for error reporting, 9-21

STMLEN parameter, 9-33

STOP action, 9-29

STOP option

opening a cursor, 6-31, 6-35

stored procedure

sample programs, 6-24, 6-35

stored subprogram, 6-21

calling, 6-23

creating, 6-21

packaged versus standalone, 6-21

stored versus inline, D-4

using to improve performance, D-4

STRING datatype, 4-15

string literals

continuing to the next line, 3-4

subprogram, PL/SQL, 6-3, 6-21

subquery, 5-9

example, 5-9, 5-10

using in the SET clause, 5-10

using in the VALUES clause, 5-9

syntactic checking, E-2

syntax

continuation lines, 3-4

embedded SQL statements, 3-4

SQLADR subroutine, 12-14

SQLGLM subroutine, 9-25

SQLNUL subroutine, 12-21

syntax diagram

syntax, embedded SQL, 2-5

SYSDATE function, 4-8

system failure

effect on transactions, 8-3

System Global Area (SGA), 6-21

T

table lock

acquiring with LOCK TABLE, 8-12

exclusive, 8-12

row share, 8-12

when released, 8-12

tables

inserting rows into, F-34

updating rows in, F-49

terminator for embedded SQL statements, 3-8

TO SAVEPOINT clause, 8-7

restrictions, 8-9

using in ROLLBACK statement, 8-7

trace facility

subdividing with savepoints, 8-7

making permanent, 8-4

undoing, 8-5

undoing parts of, 8-8

when rolled back automatically, 8-4, 8-6

transaction processing

overview, 2-8

statements used, 2-9

transaction, read-only, 8-10

transactions

committing, F-10

distributed, F-43

rolling back, F-41

truncated value, 6-12

detecting, 5-4

truncated values

indicator variables, 4-30

truncation error

when generated, 5-6

Trusted Oracle7, 12-18

tuning, performance, D-2

TYPE statement

using the CHARF datatype specifier, 4-25

U

UID function, 4-8

unconditional delete, 9-25

undo a transaction, F-41

UNSAFE_NULL option, 7-37

UNSIGNED datatype, 4-16

UPDATE command, F-49

embedded SQL examples, F-52

UPDATE statement, 5-10

example, 5-10

SET clause, 5-10

using host arrays, 10-8

updating

rows in tables and views, F-49

user exit, 13-3

calling from a SQL*Forms trigger, 13-7

common uses, 13-4

guidelines, 13-13

linking into IAP, 13-12

meaning of codes returned by, 13-8

naming, 13-13

passing parameters, 13-8

requirements for variables, 13-5

running the GENXTB form, 13-12

statements allowed in, 13-5

steps in developing, 13-4

using EXEC IAF statements, 13-5

using EXEC TOOLS statements, 13-14

using the WHENEVER statement, 13-9

USER function, 4-8

user session, 8-2

user-defined record, 6-5

USERID option, 7-38

using with the SQLCHECK option, E-4

username

defining, 3-43

hardcoding, 3-43

USING clause

CONNECT statement, 3-49

of FETCH command, F-33

of OPEN command, F-38

using in the EXECUTE statement, 11-15

using indicator variables, 11-15

using dbstring

SQL*Net database id specification, F-13

V

VALUE clause

initializing host variables, 3-18

VALUES clause

INSERT statement, 5-9

of embedded SQL INSERT command, F-36

of INSERT command, F-36

using a subquery, 5-9

VAR command, F-53

examples, F-54

VAR statement

CONVBUFSZ clause, 4-23

syntax for, 4-21

using the CHARF datatype specifier, 4-25

VARCHAR

precompiler option, 7-38

VARCHAR datatype, 4-16

VARCHAR precompiler option, 7-38

VARCHAR pseudotype, 2-14

maximum length, 2-14

using with PL/SQL, 6-10

VARCHAR variables

advantages, 3-42

as input variables, 3-42

as output variables, 3-42

declaring, 3-36

implicit group items, 3-37

length element, 3-37

maximum length, 3-36

referencing, 3-38

server handling, 3-42

string element, 3-37

structure, 3-36

versus fixed-length strings, 3-42

VARCHAR2 column

VARCHAR2 datatype

VARNUM datatype, 4-17

example of output value, 4-26

VARRAW datatype, 4-17

VARYING keyword

versus VARYING phrase, 3-36

views

inserting rows into, F-34

updating rows in, F-49

W

warning flags for error reporting, 9-21

WHENEVER command, F-55

examples, F-56

WHENEVER Statement, 9-27

WHENEVER statement

CONTINUE action, 9-29

DO action, 9-29

example, 9-30

GOTO action, 9-29

NOT FOUND condition, 9-28

overview, 2-9

purpose, 9-27

scope, 9-31

SQLERROR condition, 9-28

SQLWARNING condition, 9-28

STOP action, 9-29

syntax, 9-29

using to check SQLCA automatically, 9-27

WHENEVER statement, careless usage, 9-31

WHENEVER statement, scope of, 9-31

WHERE clause, 5-10

DELETE statement, 5-10

of DELETE command, F-24

of UPDATE command, F-51

search condition, 5-10

SELECT statement, 5-8

UPDATE statement, 5-10

using host arrays, 10-13

WHERE CURRENT OF clause, 5-15

WORK option