Pro*COBOL Precompiler Programmer's Guide -- Index

abbreviations permitted, 2-11

abnormal termination

automatic rollback, F-17

ALLOCATE DESCRIPTOR statement, F-12

ALLOCATE statement, F-10

use with ROWID, 4-37

allocating

cursors, F-10

thread context, F-19

allocating cursor variables, 6-33

ALTER AUTHORIZATION

changing password, 3-11

ANSI dynamic SQL, A-2

See also dynamic SQL (ANSI), 10-1

ANSI Entry SQL compliance, xxxiv

ANSI format

COBOL statements, 2-12

ANSI/ISO SQL

compliance, xxxii

extensions, 14-22

application development process, 2-2

ARRAYLEN statement, 6-17

ASACC precompiler option, 14-12

ASSUME_SQLCODE precompiler option, 14-13

AT clause

CONNECT statement, 3-6

DECLARE CURSOR statement, 3-7

DECLARE STATEMENT statement, 3-8

EXECUTE IMMEDIATE statement, 3-8

of COMMIT statement, F-16

of CONNECT statement, F-18

of DECLARE CURSOR directive, F-24

of DECLARE STATEMENT directive, F-28

of EXECUTE IMMEDIATE statement, F-44

of EXECUTE statement, F-39

of INSERT statements, F-55

of SAVEPOINT statement, F-78

of SELECT statement, F-81

of UPDATE statement, F-86

restrictions, 3-7

AUTO_CONNECT option

instead of CONNECT statement, 3-11

AUTO_CONNECT precompiler option, 14-14

automatic logons, 3-5, 3-10

batch fetch, 7-7

example, 7-7

number of rows returned, 7-8

BFILES

definition, 13-2

bind descriptor, 11-4

bind descriptors

information in, 9-26

bind SQLDA, 11-3

bind variables, 9-26

binding host variables, 9-4

BNDDFCLP variable (SQLDA), 11-14

BNDDFCRCP variable (SQLDA), 11-14

BNDDFMT variable (SQLDA), 11-9

BNDDH-CUR-VNAMEL variable (SQLDA), 11-13

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

BNDDH-VNAME variable (SQLDA), 11-12

BNDDI variable (SQLDA), 11-11

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

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

BNDDI-VNAME variable (SQLDA), 11-13

BNDDV variable (SQLDA), 11-8

BNDDVLN variable (SQLDA), 11-9

BNDDVTYP variable (SQLDA), 11-11

CALL SQL statement, 6-30

CALL statement, A-2, F-13

example, 6-30

callbacks, user exit, 12-14

case-insensitivity, 2-11

CHAR columns

maximum width, 4-2

CHAR datatypes

external, 4-9

internal, 4-2

character host variables

as output variables, 4-34

handling, 4-32

server handling, 4-34

types, 4-32

character sets

multi-byte, 4-41

character strings

multi-byte, 4-42

CHARF datatype specifier, 4-51

using in TYPE statement, 4-51

using in VAR statement, 4-51

CHARF datatypes

external, 4-9

CHARZ datatype

external, 4-10

CLOSE command

examples, F-15

CLOSE statement, F-14

example, 5-15

in dynamic SQL Method 4, 11-38

CLOSE_ON_COMMIT

precompiler option, 3-22, 5-13

CLOSE_ON_COMMIT precompiler option, 14-14

closing

cursors, F-14

COBOL datatypes, 4-17

COBOL datatypes, additional, A-4

COBOL versions supported, 2-11, B-2

COBOL-74, B-2

COBOL-74 restrictions, 2-20

COBOL-85, B-2

code page, 4-40

coding area

for paragraph names, 2-18

coding conventions, 2-11

column list, 5-9

COMMENT clause

of COMMIT statement, F-16

comments

ANSI SQL-style, 2-13

C-style, 2-13

embedded SQL statements, 2-13

in embedded SQL, 2-12

comments in dynamic PL/SQL blocks, 9-31

comments, your, xxv

commit, 3-19

automatic, 3-20

explicit versus implicit, 3-20

COMMIT statement, 3-20, F-15

effects, 3-20

ending a transaction, F-76

example, 3-21

examples, F-17

RELEASE option, 3-21

using in a PL/SQL block, 3-31

where to place, 3-21

committing

transactions, F-15

communicating over networks, 3-13

compilation, 2-28

compliance, ANSI/ISO, xxxii

composite types, 11-18

concurrency, 3-18

concurrent logons, 3-14

conditional precompilation, 2-26

defining symbols, 2-27

example, 2-26

CONFIG precompiler option, 14-14, 14-15

configuration file

system versus user, 14-15

configuration file name, A-7

CONNECT statement, F-17

AT clause, 3-6

enabling a semantic check, E-4

examples, F-19

logging on to Oracle, 3-3

placement, 3-3

requirements, 3-3

USING clause, 3-6

when not required, 3-11

connecting to Oracle, 3-3

connections

default versus non-default, 3-4

implicit, 3-15

naming, 3-5

context

default (same as global), 4-36

global, 4-36

CONTEXT ALLOCATE statement, F-19

CONTEXT FREE statement, F-20

CONTEXT USE SQL directive, F-21

contexts, user-specified, A-3

continuation lines

syntax, 2-13

CONTINUE action

of WHENEVER directive, 8-29, F-92

CONVBUFSZ clause, A-8

CONVBUFSZ clause in VAR statement, 4-49

conventions used in this manual, xxxi

CREATE PROCEDURE statement, 6-21

creating

savepoints, F-78

CURRENT OF clause, 5-16, 7-5

example, 5-16

mimicking with ROWID, 3-29, 7-19

of embedded SQL DELETE statement, F-33

of embedded SQL UPDATE statement, F-88

restrictions, 5-17

current row, 5-12

CURRVAL pseudocolumn, 4-6

cursor cache, 8-38

gathering statistics about, 8-40

purpose, 8-36, D-9

cursor variables, 6-32, F-10

error conditions, 6-37

fetching from, 6-35

heap memory usage, 6-33

opening

anonymous block, 6-35

stored procedure, 6-34

association with query, 5-12

closing, F-14

declaring, 5-12

effects on performance, D-7

explicit versus implicit, 5-12

fetching rows from, F-45, F-48

naming, 5-13

opening, F-69, F-71

reopening, 5-14, 5-15

restricted scope of, 2-28

restrictions, 5-13

scope, 5-14

using for multirow queries, 5-12

using more than one, 5-14

when closed automatically, 5-16

Data Definition Language (DDL)

description, 5-2

data definition language (DDL)

embedded, 2-17

data integrity, 3-18

data lock, 3-19

Data Manipulation Language (DML), 5-7

database links

creating a synonym, 3-15

defining, 3-15

using in DELETE statement, F-32

using in INSERT statement, F-55

using in UPDATE statement, F-87

datatype

internal versus external, 2-8

datatype conversion

between internal and external types, 4-44

datatype equivalencing

datatypes

coercing NUMBER to VARCHAR2, 11-19

conversions, 4-43

dealing with Oracle internal, 11-19

descriptor codes, 11-19

equivalencing

description, 4-46

example, 4-49

internal, 11-16

need to coerce, 11-19

PL/SQL equivalents, 11-18

DATE datatype

DATE datatypes

internal format, 4-10

DATE String Format

explicit control over, 4-45

DATE_FORMAT precompiler option, 14-16

DB2 compatibility features, A-4

DBMS precompiler option, 14-17

DDL, 2-17

DDL (Data Definition Language), 5-2

deadlock, 3-19

effect on transactions, 3-24

how broken, 3-24

DEALLOCATE DESCRIPTOR statement, F-23

DECIMAL datatype, 4-10

Decimal-Point is Comma, A-3

declaration

cursor, 5-12

host variable, 2-8

declarative SQL statement, 2-4

using in transactions, 3-20

declarative statements

also known as directives, 2-4

DECLARE CURSOR directive, F-24

example, 5-12

examples, F-25

DECLARE CURSOR statement

AT clause, 3-7

in dynamic SQL Method 4, 11-29

where to place, 5-13

DECLARE DATABASE directive, F-26

Declare Section

allowable statements, 2-20

example, 2-21

purpose, 2-20

requirements, 2-20

rules for defining, 2-20

using more than one, 2-22

declare section

COBOL datatypes supported, 4-17

defining usernames and passwords, 3-3

DECLARE SECTION is optional, A-4

DECLARE statement

using in dynamic SQL Method 3, 9-20

DECLARE STATEMENT directive, F-27

examples, F-28

scope of, F-28

DECLARE STATEMENT statement

AT clause, 3-8

example, 9-28

using in dynamic SQL, 9-28

when required, 9-28

DECLARE TABLE directive, F-29

examples, F-30

DECLARE TABLE directives

using with the SQLCHECK option, E-4

DECLARE TABLE statement

need for with AT clause, 3-7

DECLARE_SECTION precompiler option, 14-18

declaring

cursor variables, 6-33

host tables, 7-2

host variables, 4-17

indicator variables, 4-26

ORACA, 8-37

SQLCA, 8-21

SQLDA, 11-7

VARCHAR variables, 4-29

default

connection, 3-4

error handling, 8-28

setting of LITDELIM option, 2-14, 14-27

setting of ORACA option, 8-38

DEFINE precompiler option, 14-19

DELETE statement, F-31

embedded SQL examples, F-34

example, 5-11

restrictions with tables, 7-15

using host tables, 7-15

WHERE clause, 5-11

DEPENDING ON clause, 7-3

DEPT table, 2-29

DESCRIBE BIND VARIABLES statement

in dynamic SQL Method 4, 11-29

DESCRIBE DESCRIPTOR statement, F-36

DESCRIBE SELECT LIST statement

in dynamic SQL Method 4, 11-34

DESCRIBE statement, F-34

example, F-35

use with PREPARE statement, F-34

using in dynamic SQL Method 4, 9-25

descriptor

naming, F-35

descriptors

bind descriptor, 11-4

purpose, 11-4

select descriptor, 11-4

SQLADR subroutine, 11-3

dimension of host tables, 7-3

directives

also known as declarative statements, 2-4

directory path

INCLUDE files, 2-23

DISPLAY datatype, 4-11

distributed processing, 3-14

DML (Data Manipulation Language), 5-7

DML returning clause, 5-9, A-2

DNSTIAR subprogram, A-6

DO CALL, A-3

DO CALL action

of WHENEVER directive, 8-30, 8-31, F-92

DO PERFORM action

of WHENEVER directive, 8-29, F-92

DSNTIAR

DB2 compatible feature, 8-27

DSNTIAR routine, 8-27

DTP model, 3-32

DYNAMIC option

effect on functionality, 10-12

dynamic PL/SQL, 9-30

dynamic SQL

advantages and disadvantages, 9-3

choosing the right method, 9-7

using the AT clause, 3-8

when useful, 9-3

dynamic SQL (ANSI)

dynamic SQL Method 1

using EXECUTE IMMEDIATE, 9-9

dynamic SQL Method 2

using the DECLARE STATEMENT statement, 9-28

using the EXECUTE statement, 9-13

using the PREPARE statement, 9-13

dynamic SQL Method 3

commands, 9-6

compared to Method 2, 9-19

requirements, 9-6

using PL/SQL, 9-30

using the DECLARE statement, 9-20

using the DECLARE STATEMENT statement, 9-28

using the FETCH statement, 9-21

using the OPEN statement, 9-20

using the PREPARE statement, 9-19

dynamic SQL Method 4

dynamic SQL statement, 9-3

binding of host variables, 9-4

how processed, 9-4

requirements, 9-4

using host tables, 9-29

versus static SQL statement, 9-3

embedded DDL, 2-17

embedded PL/SQL

embedded SQL

ALLOCATE DESCRIPTOR statement, F-12

ALLOCATE statement, 4-37, 6-33, F-10

CALL statement, 6-30, F-13

CLOSE statement, 5-15, 6-36, F-14

COMMIT statement, F-15

CONNECT statement, F-17

CONTEXT ALLOCATE statement, F-19

CONTEXT FREE statement, F-20

CONTEXT USE directive, F-21

DEALLOCATE DESCRIPTOR statement, F-23

DECLARE [CURSOR] directive, 5-12

DECLARE CURSOR directive, F-24

DECLARE DATABASE directive, F-26

DECLARE STATEMENT directive, F-27

DECLARE TABLE directive, F-29

DELETE statement, 5-11, F-31

DESCRIBE DESCRIPTOR statement, F-36

DESCRIBE statement, F-34

EXECUTE IMMEDIATE statement, F-43

EXECUTE statement, F-40

EXECUTE...END-EXEC statement, F-38

FETCH DESCRIPTOR statement, F-48

FETCH statement, 5-14, 6-35, F-45, F-48

FREE statement, 6-37, F-50

GET DESCRIPTOR staement, F-51

INSERT statement, 5-9, 7-12, F-54

key concepts, 2-2

OPEN DESCRIPTOR statement, F-71

OPEN statement, 5-14, F-69, F-70, F-71

PREPARE statement, F-73

ROLLBACK statement, F-74

SAVEPOINT statement, 3-24, F-78

SELECT statement, 5-8, 7-6, F-79

SET DESCRIPTOR statement, F-82

SET TRANSACTION statement, 3-26

UPDATE statement, 5-10, F-85

VAR directive, F-89

versus interactive SQL, 2-6

when to use, 1-3

WHENEVER directive, F-91

embedded SQL statements

associating paragraph names with, 2-18

comments, 2-13

continuation, 2-13

figurative constants, 2-15

mixing with host-language statements, 2-6

referencing host tables, 7-3

referencing host variables, 4-23

referencing indicator variables, 4-27

embedding

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

EMP table, 2-29

encoding scheme, 4-40

END, 14-20

END_OF_FETCH precompiler option, 14-20

END-OF_FETCH clause, A-5

entry SQL, xxxiii

equivalencing

host variable equivalencing, F-89

equivalencing datatypes, 4-46

error conditions

cursor variable, 6-37

error detection

error reporting, F-92

error handling

using status variables

SQLCA, 8-3, 8-19

SQLCODE, 8-3, 8-5

SQLSTATE, 8-3

using the ROLLBACK statement, 3-23

using the SQLGLS routine, 8-34

error message text

SQLGLM subroutine, 8-26

error messages

maximum length, 8-27

error reporting

error message text, 8-22

key components of, 8-22

parse error offset, 8-22

rows-processed count, 8-22

status codes, 8-22

warning flags, 8-22

WHENEVER directive, F-92

ERRORS precompiler option, 14-21

exception, PL/SQL, 6-13

EXEC ORACLE DEFINE statement, 2-26

EXEC ORACLE ELSE statement, 2-26

EXEC ORACLE ENDIF statement, 2-26

EXEC ORACLE IFDEF statement, 2-26

EXEC ORACLE IFNDEF statement, 2-26

EXEC ORACLE statement

scope of, 14-8

syntax for, 14-8

uses for, 14-8

using to enter options inline, 14-8

EXEC SQL clause, 2-6, 2-15

EXEC TOOLS statement, 12-14

GET, 12-15

MESSAGE, 12-16

SET, 12-14

EXECUTE IMMEDIATE statement, F-43

AT clause, 3-8

examples, F-44

using in dynamic SQL Method 1, 9-9

EXECUTE optional keyword of ARRAYLEN statement, 6-18

EXECUTE statement, F-40

examples, F-39, F-41

using in dynamic SQL Method 2, 9-13

EXECUTE...END-EXEC statement, F-38

execution plan, D-5

EXPLAIN PLAN statement

using to improve performance, D-6

explicit logon

single, 3-5

explicit logons, 3-5

multiple, 3-9

external datatypes

FETCH SQL statement, F-48

FETCH statement, 5-14, 5-15, F-45

cursor variable, 6-36

example, 5-14

examples, F-47

in dynamic SQL Method 4, 11-37

INTO clause, 5-14

used after OPEN statement, F-70, F-72

using in dynamic SQL Method 3, 9-21

fetch, batch, 7-7

fetching

rows from cursors, F-45, F-48

figurative constants

embedded SQL statements, 2-15

file extension

for INCLUDE files, 2-22

file length limit, 2-16

FILLER support, A-7

FIPS flagger

warns of array usage, 7-5

FIPS flagger and its uses, xxxiv

FIPS precompiler option, xxxiv, 14-21

of embedded SQL EXECUTE statement, F-41, F-42

of embedded SQL INSERT statement, F-55

restrictions, 7-17

using with host tables, 7-16

FOR UPDATE OF clause, 3-28

FORCE clause

of COMMIT statement, F-17

of ROLLBACK statement, F-76

format mask, 4-45

FORMAT precompiler option, 14-23

purpose, 2-12

formats of COBOL statements

ANSI, 2-12

TERMINAL, 2-12

forward reference, 5-13

freeing

GENXTB form

GET DESCRIPTOR statement, F-51

GOTO action

of WHENEVER directive, 8-30, F-92

group items

allowed as host variables, 4-24

implicit VARCHAR, 4-30

Group Items as host variables, A-4

guidelines

datatype equivalencing, 4-52

dynamic SQL, 9-6

separate precompilation, 2-27

transaction, 3-31

user exit, 12-13

HEADERS, optional, A-3

heap, 8-38

heap memory

allocating cursor variables, 6-33

heap tables, 4-36

hint, optimizer, D-5

hints

in DELETE statements, F-34

in SELECT statements, F-82

in UPDATE statements, F-88

HOLD_CURSOR option

of ORACLE Precompilers, F-15

using to improve performance, D-11

what it affects, D-7

HOLD_CURSOR precompiler option, 14-23

host language, 2-4

HOST precompiler option, 14-24

host programs, 2-4

host table example, 7-10

host tables, 7-2

host variables, 5-2

hyphenation

of host variable names, 2-16

IAF GET statement

example, 12-6

specifying block and field names, 12-6

using in user exit, 12-5

IAF PUT statement

example, 12-7

specifying block and field names, 12-7

using in user exit, 12-6

IAP, 12-12

identifiers, ORACLE

how to form, F-10

implicit logons, 3-15

multiple, 3-16

single, 3-15

implicit VARCHAR, 4-30

IN OUT parameter mode, 6-5

IN parameter mode, 6-5

INAME option

when a file extension is required, 14-2

INAMEprecompiler option, 14-25

INCLUDE precompiler option, 14-25

INCLUDE statement

case-sensitive operating systems, 2-23

declaring the ORACA, 8-37

declaring the SQLCA, 8-21

declaring the SQLDA, 11-7

effect of, 2-22

INCLUDE statements, B-2

index

using to improve performance, D-6

index-organized table, 4-37

indicator table, 7-2

indicator tables

example, 7-4

purpose, 7-4

indicator variable

using to handle NULLs, 5-4, 5-5

indicator variables, 5-3

in-doubt transaction, 3-30

input host variable

restrictions, 5-2

where allowed, 5-2

INSERT statement, F-54

column list, 5-9

example, 5-9

INTO clause, 5-9

using host tables, 7-12

VALUES clause, 5-9

inserting

rows into tables and views, F-54

INTEGER datatype, 4-11

interface

native, 3-32

XA, 3-32

internal datatype

definition, 2-8

internal datatypes

CHAR, 4-2

DATE, 4-3

dynamic SQL Method 4, 11-15

lLONG, 4-3

LONG RAW, 4-4

NCHAR, 4-3

NUMBER, 4-4

NVARCHAR2, 4-5

RAW, 4-4

ROWID, 4-5

table of, 4-2

VARCHAR2, 4-5

INTO clause, 5-2, 6-36

FETCH statement, 5-14

INSERT statement, 5-9

of FETCH statement, F-46, F-49

of SELECT statement, F-81

SELECT statement, 5-8

IRECLEN precompiler option, 14-26

IS NULL operator

for testing NULL values, 2-18

Java methods

calling from Pro*COBOL, 6-21

Java stored procedures, A-2

Julian date, 4-3

language support, 1-3

LDA, 3-16

LEVEL pseudocolumns, 4-7

line continuation, 2-13

linking, 2-28

links, database, 3-15

LITDELIM option

purpose, 14-27

LITDELIM precompiler option, 2-14, 14-27

LNAME precompiler option, 14-27

LOB and precompiler datatypes, 13-21

LOB APPEND statement, F-57

LOB ASSIGN statement, F-58

LOB CLOSE statement, F-58

LOB COPY statement, F-59

LOB CREATE statement, F-59

LOB DESCRIBE statement, F-60

LOB DISABLE BUFFERING statement, F-61

LOB ENABLE BUFFERING statement, F-62

LOB ERASE statement, F-62

LOB FILE CLOSE statement, F-63

LOB FILE SET statement, F-64

LOB FLUSH BUFFER statement, F-64

LOB FREE TEMPORARY, F-65

LOB LOAD statement, F-65

LOB OPEN statement, F-66

LOB READ statement, F-67

LOB CREATE TEMPORARY, 13-14

LOB DISABLE BUFFERING, 13-14

LOB ENABLE BUFFERING, 13-15

LOB ERASE, 13-15

LOB FILE SET, 13-16

LOB FLUSH BUFFER, 13-17

LOB FREE TEMPORARY, 13-18

LOB LOAD FROM FILE, 13-18

LOB TRIM statement, F-67

LOB WRITE statement, F-68

LOBs

location transparency, 3-16

lock

released by ROLLBACK statement, F-76

LOCK TABLE statement, 3-28

example, 3-28

using the NOWAIT parameter, 3-29

locking, 3-19, 3-27

explicit versus implicit, 3-27

modes, 3-19

overriding default, 3-27

privileges needed, 3-31

using the FOR UPDATE OF clause, 3-27

using the LOCK TABLE statement, 3-28

Logon Data Area (LDA), 3-16

logons

LONG columns

LONG datatype

LONG datatypes

compared with CHAR, 4-3

internal, 4-3

where allowed, 4-3

LONG RAW column

maximum width, 4-4

LONG RAW datatype

compared with LONG, 4-4

LONG RAW datatypes

LONG VARCHAR datatype, 4-12

LONG VARRAW datatype, 4-12

LRECLEN precompiler option, 14-28

LTYPE precompiler option, 14-28

MAXLITERAL, B-3

MAXLITERAL precompiler option, 14-29

MAXOPENCURSORS option, D-7

using for separate precompilation, 2-28

MAXOPENCURSORS precompiler option, 14-30

message text, 8-22

migration

error message codes, A-8

migration from earlier releases, A-9

MODE

equivalent values, 14-31

MODE option

effects of, 4-32

status variables, 8-2

MODE precompiler option, 14-31

mode, parameter, 6-5

monitor, transaction processing, 3-32

multi-byte character sets, 4-41

multi-byte NLS features

datatypes, 2-17

with PL/SQL, 4-41

namespaces

reserved by Oracle, C-4

naming

host variables, 2-16

of database objects, F-10

select-list items, 11-4

naming conventions

cursor, 5-13

default database, 3-4

host variable, 2-8

SQL*Forms user exit, 12-13

National Language Support (NLS), 4-39

native interface, 3-32

NCHAR datatypes

internal, 4-3

NESTED precompiler option, A-3

nested programs, A-7

support for, 2-24

NESTEDprecompiler option, 14-32

Net8

concurrent logons, 3-14

connecting using, 3-13

connection syntax, 3-13

function of, 3-13

using ROWID datatype, 4-14

using to connect to Oracle, 3-14

networks

communicating over, 3-13

protocols, 3-13

reducing traffic, D-4

NEXTVAL pseudocolumns, 4-7

nibbles, 4-53

NIST

compliance, xxxii

NLS (National Language Support), 4-39

multi-byte character strings, 4-41

NLS parameter

NLS_CURRENCY, 4-39

NLS_DATE_FORMAT, 4-39

NLS_DATE_LANGUAGE, 4-39

NLS_ISO_CURRENCY, 4-39

NLS_LANG, 4-40

NLS_LANGUAGE, 4-39

NLS_NUMERIC_CHARACTERS, 4-39

NLS_SORT, 4-39

NLS_TERRITORY, 4-39

NLS_LOCAL

precompiler option, 14-32

nodes

definition of, 3-13

NOT FOUND condition, 8-28

of WHENEVER directive, 8-28, F-92

notation used in this manual, xxxi

NOWAIT parameter, 3-29

using in LOCK TABLE statement, 3-29

null characters in COBOL

not supported, 2-18

NULLs

definition, 2-7

detecting, 4-26, 5-4

handling

in dynamic SQL Method 4, 11-21

indicator variables, 6-2

hardcoding, 5-4

inserting, 5-4

meaning in SQL (NVL function), 2-18

restrictions, 5-6

retrieving, 5-5

SQLNUL subroutine, 11-21

testing for, 5-6

NULLs in SQL

how to detect, 2-18

null-terminated strings, 4-14

NUMBER datatype

external, 4-12

internal, 4-4

using the SQLPRC subroutine with, 11-20

NVARCHAR2 datatype

internal, 4-5

NVL function

for retrieving NULL values, 2-18

OCI

declaring LDA, 3-16

embedding calls, 3-16

using with Oracle Precompilers, 3-16

ODESSP function

information about stored subprograms, 6-31

ONAME precompiler ption, 14-33

OPEN DESCRIPTOR statement, F-71

OPEN SQL statement, F-71

OPEN statement, F-69

example, 5-14

examples, F-70

in dynamic SQL Method 4, 11-34

using in dynamic SQL Method 3, 9-20

OPEN_CURSORS parameter, 6-20

opening

cursors, F-69, F-71

opening a cursor variable, 6-34

operators

relational, 2-19

optimizer hint, D-5

optional division headers, 2-15

options

precompiler concepts, 14-3

ORACA, 8-4

ORACA precompiler option, 14-34

ORACABC field, 8-38

ORACAID field, 8-38

ORACCHF flag, 8-38

Oracle Call Interface(OCI), 3-16

Oracle Communications Area

ORACA, 8-36

Oracle datatypes, 2-8

Oracle dynamic SQL

when to use, 10-1

Oracle Forms

using EXEC TOOLS statements, 12-14

ORACLE identifiers

how to form, F-10

Oracle namespaces, C-4

Oracle Open Gateway

using ROWID datatype, 4-14

Oracle Precompilers

advantages, 1-3

language support, 1-3

NLS support, 4-40

running, 14-1

using PL/SQL, 6-7

Oracle Toolset, 12-14

Oracle8 SQL Reference, 4-46

ORACOC

ORANEX

ORASFNM, in ORACA, 8-40

ORASLNR

ORECLEN precompiler option, 14-34

OUT parameter mode, 6-5

output host variable, 5-2

output vs input, 5-2

PAGELEN precompiler option, 14-34

paragraph names

associating with SQL statements, 2-18

coding area for, 2-18

parameter mode, 6-5

parse error offset, 8-22

parsing dynamic statements

PREPARE statement, F-73

password, changing, A-8

passwords

changing at runtime with ALTER AUTHORIZATION, 3-11

performance

PIC G for NLS characters, B-3

PIC N for NLS characters, B-3

PICX precompiler option, 4-32, 14-35

PL/SQL

advantages, 1-4

cursor FOR loop, 6-4

datatype equivalents, 11-18

embedded, 6-2

exception, 6-13

integration with server, 6-3

opening a cursor variable

anonymous block, 6-35

stored procedure, 6-34

package, 6-5

relationship with SQL, 1-4

subprogram, 6-4

user-defined record, 6-6

PL/SQL block execution

effect on SQLCA components, 8-26

PL/SQL blocks

embedded in Oracle7 precompiler programs, F-38

PL/SQL subprogram

calling from Pro*COBOL, 6-21

PL/SQL table, 6-6

supported datatype conversions, 6-16

placeholders

precompilation

precompilation unit, 14-9

precompiler command

required arguments, 14-2

precompiler options

abbreviating name, 14-4

ANSI Dynamic SQL, 10-12

ASACC, 14-12

ASSUME_SQLCODE, 14-13

AUTO_CONNECT, 3-11, 14-14

CLOSE_ON_COMMIT, 5-13, 14-14, A-5

CONFIG, 14-15

current values, 14-6

DATE_FORMAT, 14-16, A-6

DBMS, 14-17

DECLARE_SECTION, 2-21, 14-18

DEFINE, 14-19

displaying, 14-4

displaying syntax, default, purpose, 14-10

DYNAMIC, 10-12, 14-20

END_OF_FETCH, 14-20

entering, 14-7

entering inline, 14-8

entering on the command line, 14-2

ERRORS, 14-21

FIPS, 14-21

FORMAT, 14-23

HOLD_CURSOR, 14-23, D-7

LITDELIM, 2-14, 14-27

LNAME, 14-27

LRECLEN, 14-28

LTYPE, 14-28

macro and micro, 14-5

MAXLITERAL, 14-29

MAXOPENCURSORS, 2-28, 14-30, D-7

MODE, 4-32, 8-2, 8-4, 10-12, 14-31

name of the system configuration file, 14-7

NESTED, 14-32, A-3

NLS_LOCAL, 2-17, 14-32

PICX, 4-32, 14-35, A-8

precedence, 14-4

PREFETCH, 5-20, 14-36, A-2

RELEASE_CURSOR, 14-36, D-7

table of how macro options set micro options, 14-5

TYPE_CODE, 10-12, 14-40

PREFETCH precompiler option, 5-20, 14-36

PREPARE statement, F-73

effect on data definition statements, 9-6

examples, F-74

in dynamic SQL Method 4, 11-29

using in dynamic SQL, 9-13, 9-19

private SQL area

association with cursors, 5-11

opening, 5-11

purpose, D-9

Pro*COBOL

how it works, 1-2

program termination, 3-26

programming guidelines, 2-11

programming language support, 1-3

LEVEL, 4-7

queries

association with cursor, 5-12

multirow, 5-7

single-row versus multirow, 5-8

query, 5-7

RAW column

maximum width, 4-4

RAW datatype

compared with CHAR, 4-4

RAWTOHEX function, 4-53

read consistency, 3-19

READ ONLY parameter

using in SET TRANSACTION, 3-27

read-only transaction, 3-26

ending, 3-27

example, 3-27

record, user-defined, 6-6

REDEFINES clause

purpose, 2-18

restrictions, 2-18

REDEFINES support, A-7

reference

host variable, 2-8

reference cursor, 6-32

referencing

host tables, 7-3

host variables, 4-23

indicator variables, 4-27

VARCHAR variables, 4-31

relational operators

COBOL vs SQL, 2-19

RELEASE option, 3-21, 3-26

COMMIT statement, 3-21

omitting, 3-26

restrictions, 3-26

ROLLBACK statement, 3-22

RELEASE_CURSOR option, D-7

of ORACLE Precompilers, F-15

using to improve performance, D-12

RELEASE_CURSOR precompiler option, 14-36

remote database

declaration of, F-26

resource manager, 3-32

restrictions

retrieving rows from a table

embedded SQL, F-79

RETURN-CODE special register is unpredictable, B-3

returning clause, 5-9

in INSERT, 5-9

roll back

to a savepoint, F-78

to the same savepoint multiple times, F-76

rollback

automatic, 3-23

purpose, 3-19

statement-level, 3-23

rollback segment, 3-19

ROLLBACK statement, 3-22, F-74

effects, 3-22

ending a transaction, F-76

example, 3-22

examples, F-77

RELEASE option, 3-22

TO SAVEPOINT clause, 3-22

using in a PL/SQL block, 3-31

using in error-handling routines, 3-23

where to place, 3-22

rolling back

transactions, F-74

row lock

acquiring with FOR UPDATE OF, 3-28

using to improve performance, D-6

when acquired, 3-28

when released, 3-28

ROWID datatype

heap tables vs index-organized tables, 4-36

internal, 4-5

Universal, 4-36

use of, 4-36

use of ALLOCATE, 4-37

ROWID pseudocolumns, 4-5

retrieving with SQLROWIDGET, 4-38

universal ROWID, 4-37

using to mimic CURRENT OF, 3-29, 7-19

ROWNUM pseudocolumns, 4-7

rows

fetching from cursors, F-45, F-48

inserting into tables and views, F-54

updating, F-85

rows-processed count, 8-22

RR diagrams

see syntax diagrams, F-7

runtime context, 4-36

runtime contexts, A-3

sample database table

DEPT table, 2-29

EMP table, 2-29

sample programs

calling a stored procedure, 6-24

concurrent conections, 3-17

cursor operations, 5-18

cursor variable use, 6-38

cursor variables

PL/SQL source, 6-38

datatype equivalencing, 4-55

dynamic SQL Method 1, 9-10

dynamic SQL Method 2, 9-15

dynamic SQL Method 3, 9-21

dynamic SQL Method 4, 11-44

EXEC ORACLE scope, 14-8

fetching in batches, 7-10, 7-22

in demo directory, xxxii

LOB DESCRIBE example, 13-27

LOBDEMO1.PCO, 13-29

Oracle Forms user exit, 12-9

tables of group items, 7-22

WHENEVER...DO CALL example, 8-31

savepoint, 3-24

SAVEPOINT statement, 3-24, F-78

savepoints

Scale

using SQLPRC to extract, 4-48

scale, 4-4

definition of, 4-48

when negative, 4-48

scope

cursor variables, 6-33

of DECLARE STATEMENT directive, F-28

of precompiler options, 14-9

of the EXEC ORACLE statement, 14-8

WHENEVER directive, 8-33

search condition, 5-11

using in the WHERE clause, 5-11

SELDFCLP variable (SQLDA), 11-14

SELDFCRCP variable (SQLDA), 11-14

SELDFMT variable (SQLDA), 11-9

SELDH-CUR-VNAMEL variable (SQLDA), 11-13

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

SELDH-VNAME variable (SQLDA), 11-12

SELDI variable (SQLDA), 11-11

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

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

SELDI-VNAME variable (SQLDA), 11-13

SELDV variable (SQLDA), 11-8

SELDVLN variable (SQLDA), 11-9

SELDVTYP variable (SQLDA), 11-11

select descriptor, 11-4

select descriptors

information in, 9-26

select list, 5-8

select SQLDA

purpose of, 11-3

SELECT statement, F-79

available clauses, 5-9

embedded SQL examples, F-82

example, 5-8

INTO clause, 5-8

using host tables, 7-6

SELECT_ERROR option, 5-8

SELECT_ERROR precompiler option, 14-37

select-list items

naming, 11-4

semantic checking, E-2

enabling, E-3

using the SQLCHECK option, E-2

Send Us Your Comments page, xxv

separate precompilation

sessions

using a subquery, 5-10

SET DESCRIPTOR statement, F-82

SET TRANSACTION statement

example, 3-26

READ ONLY parameter, 3-27

restrictions, 3-27

snapshot, 3-19

SQL

summary of statements, F-4

SQL codes

returned by SQLGLS routine, 8-35

SQL Communications Area, 2-10

SQL Descriptor Area, 9-25, 11-4

SQL directives

DECLARE CURSOR, F-24

DECLARE DATABASE, F-26

DECLARE STATEMENT, F-27

DECLARE TABLE, F-29

VAR, F-89

WHENEVER, F-91

SQL directives CONTEXT USE, F-21

SQL statement

controlling transactions, 3-19

optimizing to improve performance, D-5

static versus dynamic, 2-6

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

using to manipulate data, 5-7

SQL statements

ALLOCATE, F-10

ALLOCATE DESCRIPTOR, F-12

CALL, F-13

CLOSE, F-14

COMMIT, F-15

CONNECT, F-17

CONTEXT ALLOCATE, F-19

CONTEXT FREE, F-20

DEALLOCATE DESCRIPTOR, F-23

DELETE, F-31

DESCRIBE, F-34

DESCRIBE DESCRIPTOR, F-36

EXECUTE, F-40

EXECUTE DESCRIPTOR, F-42

EXECUTE IMMEDIATE, F-43

EXECUTE...END-EXEC, F-38

FETCH, F-45, F-48

FETCH DESCRIPTOR, F-48

FREE, F-50

LOB DISABLE BUFFERING, F-61

LOB ENABLE BUFFERING, F-62

LOB ERASE, F-62

LOB FILE CLOSE, F-63

LOB FILE SET, F-64

LOB FLUSH BUFFER, F-64

LOB FREE TEMPORARY, F-65

OPEN, F-69, F-70, F-71

OPEN DESCRIPTOR, F-71

SQL syntax used in this manual, xxxii

SQL*Forms

IAP constants, 12-8

returning values to, 12-8

SQL92

minimum requirement, xxxiii

SQL92 standards conformance, xxxiii

SQLADR subroutine

example, 11-26

parameters, 11-14

storing buffer addresses, 11-3

syntax, 11-14

SQLCA, 8-3

components set for a PL/SQL block, 8-26

fields, 8-23

interaction with Oracle, 2-10

SQLERRD(3) field, 8-24

SQLERRD(5) field, 8-25

SQLERRMC field, 8-24

SQLERRML field, 8-24

SQLWARN(4) flag, 8-25

using in separate precompilations, 2-28

using with Net8, 8-19

SQLCA status variable

data structure, 8-20

declaring, 8-21

effect of MODE option, 8-4

explicit versus implicit checking, 8-3

SQLCHECK option

using the DECLARE TABLE statement, E-4

using to check syntax/semantics, E-1

SQLCHECK precompiler option, 14-38

SQLCODE

declaring, 8-5

SQLCODE field, 8-23

interpreting its value, 8-23

SQLCODE status variable

declaring, 8-5

description, 8-3

effect of MODE option, 8-4

SQL92 deprecated feature, 8-3

usage, 8-4

SQLCODE variable

interpreting values of, 8-10

SQLDA, 9-25, 9-26

bind versus select, 9-26

BNDDFCLP variable, 11-14

BNDDFCRCP variable, 11-14

BNDDFMT variable, 11-9

BNDDH-CUR-VNAMEL variable, 11-13

BNDDH-MAX-VNAMEL variable, 11-12

BNDDH-VNAME variable, 11-12

BNDDI variable, 11-11

BNDDI-CUR-VNAMEL variable, 11-13

BNDDI-MAX-VNAMEL variable, 11-13

BNDDI-VNAME variable, 11-13

BNDDV variable, 11-8

BNDDVLN variable, 11-9

BNDDVTYP variable, 11-11

declaring, 11-7

example, 11-7

information stored in, 9-26

purpose, 11-4

SELDFCLP variable, 11-14

SELDFCRCP variable, 11-14

SELDFMT variable, 11-9

SELDH-CUR-VNAMEL variable, 11-13

SELDH-MAX-VNAMEL variable, 11-12

SELDH-VNAME variable, 11-12

SELDI variable, 11-11

SELDI-CUR-VNAMEL variable, 11-13

SELDI-MAX-VNAMEL variable, 11-13

SELDI-VNAME variable, 11-13

SELDV variable, 11-8

SELDVLN variable, 11-9

SELDVTYP variable, 11-11

SQLADR subroutine, 11-14

SQLDFND variable, 11-8

SQLDNUM variable, 11-8

structure, 11-8

SQLDFND variable (SQLDA), 11-8

SQLDNUM variable (SQLDA), 11-8

SQLERRD(3) field, 8-24

using with batch fetch, 7-8

SQLERRD(3) variable, 8-22

SQLERRD(5) field, 8-25

SQLERRMC field, 8-24

SQLERRMC variable, 8-22

SQLERRML field, 8-24

SQLERROR condition, 8-28

of WHENEVER directive, 8-28

WHENEVER directive, F-92

SQLFC parameter, 8-35

SQLGLM subroutine

example, 8-27

parameters, 8-26

provides DSNTIAR support for DB2 conversions, 8-27

purpose, 8-26

restrictions, 8-27

syntax, 8-26

SQLGLS routine, 8-34, 8-35

parameters, 8-35

SQL codes returned by, 8-35

syntax, 8-35

using to obtain SQL text, 8-34

SQLIEM function

replacement for, 12-14

using in user exit, 12-8

SQLIEM subroutine

SQLNUL subroutine

SQLPR2 subroutine, 11-21

SQLPRC subroutine

SQLROWIDGET

retrieving ROWID of last row inserted, 4-38

SQLSTATE

declaring, 8-6

SQLSTATE status variable

class code, 8-10

coding scheme, 8-10

effect of MODE option, 8-4

interpreting values, 8-10

predefined classes, 8-11

predefined status codes and conditions, 8-13

subclass code, 8-10

usage, 8-4

SQLSTM parameter, 8-35

SQLSTM routine, 8-35

SQLWARN(4) flag, 8-25

SQLWARNING

conditionWHENEVER directive, F-92

SQLWARNING condition, 8-28

of WHENEVER directive, 8-28

statement-level rollback, 3-23

breaking deadlocks, 3-24

status codes for error reporting, 8-22

STMLEN parameter, 8-35

STOP action

of WHENEVER directive, 8-30, F-92

stored procedure

opening a cursor, 6-38

sample programs, 6-38

stored procedures

opening a cursor, 6-34

sample programs, 6-24

stored subprogram

calling, 6-23

creating, 6-21

packaged versus standalone, 6-21

stored versus inline, D-4

using to improve performance, D-4

stored subprogram, calling, 6-21

STRING datatype, 4-14

string literals

continuing on the next line, 2-13

subprogram, PL/SQL, 6-4

subprogram, PL/SQL or Java, 6-21

subquery, 5-10

example, 5-10

using in the SET clause, 5-10

using in the VALUES clause, 5-10

suggestions, your, xxv

synonym

database links, 3-15

syntactic checking, E-2

syntax

continuation lines, 2-13

embedded SQL statements, 2-15

SQLADR subroutine, 11-14

SQLGLM subroutine, 8-26

SQLNUL subroutine, 11-21

syntax diagrams

syntax, embedded SQL, 2-6

SYSDATE function, 4-7

SYSDBA privilege, A-3

SYSDBA privilegeshow to set, 3-12

SYSOPER privilege, A-3

how to set, 3-12

system failures

effect on transactions, 3-20

System Global Area (SGA), 6-21

table lock

acquiring with LOCK TABLE, 3-28

tables

inserting rows into, F-54

updating rows in, F-85

tables of group items, A-3

tables, host, 7-2

TERMINAL format

COBOL statements, 2-12

terminator for embedded SQL statements, 2-19

terminator, SQL statements, A-7

text conventions used in this manual, xxxi

threads, F-19

allocating context, F-19

freeing context, F-20

use context, F-21

TO SAVEPOINT clause, 3-24

restrictions, 3-26

using in ROLLBACK statement, 3-24

trace facility

using to improve performance, D-6

transactions

making permanent, 3-20

read-only, 3-26

rolling back, F-74

subdividing with savepoints, 3-24

undoing, 3-22

undoing parts of, 3-24

when rolled back automatically, 3-20, 3-23

truncated values, 6-14

detecting, 4-26, 5-4

indicator variables, 6-2

truncation errors

when generated, 5-6

tuning, performance, D-2

TYPE statements

using the CHARF datatype specifier, 4-51

TYPE_CODE option

effect on functionality, 10-13

TYPE_CODE precompiler option, 14-40

UID function, 4-7

undo a transaction, F-74

universal ROWID, A-3

ROWID pseudocolumns, 4-37

UNSAFE_NULL precompiler option, 14-40

UNSIGNED datatype, 4-14

UPDATE statement, F-85

embedded SQL examples, F-89

example, 5-10

SET clause, 5-10

using host tables, 7-13

updating

rows in tables and views, F-85

user exit

calling from a SQL*Forms trigger, 12-7

common uses, 12-4

defined, 12-3

guidelines, 12-13

linking into IAP, 12-12

meaning of codes returned by, 12-8

naming, 12-13

passing parameters, 12-8

requirements for variables, 12-5

return codes, 12-8

running the GENXTB form, 12-12

statements allowed in, 12-5

steps in developing, 12-4

using EXEC IAF statements, 12-5

using EXEC TOOLS statements, 12-14

using the WHENEVER statement, 12-9

USER function, 4-8

user session, 3-18

user-defined record, 6-6

USERID option

using with the SQLCHECK option, E-4

USERID precompiler option, 14-41

usernames

defining, 3-3

hardcoding, 3-3

USING clause

CONNECT statement, 3-6

of FETCH statement, F-46

of OPEN statement, F-69

using in the EXECUTE statement, 9-15

using indicator variables, 9-15

using dbstring

Net8 database id specification, F-18

VALUE clause

initializing host variables, 4-22

VALUES clause

INSERT statement, 5-9

of embedded SQL INSERT statement, F-56

of INSERT statement, F-56

using a subquery, 5-10

VAR directive, F-89

examples, F-90

VAR statement

CONVBUFSZ clause, 4-49

syntax for, 4-47

using the CHARF datatype specifier, 4-51

VARCHAR datatype, 4-14

VARCHAR group items

implict form, A-5

VARCHAR precompiler option, 14-42

VARCHAR pseudotype

using with PL/SQL, 6-12

VARCHAR variables

advantages, 4-35

as input variables, 4-34

as output variables, 4-35

declaring, 4-29

implicit group items, 4-30

length element, 4-30

maximum length, 4-30

referencing, 4-31

server handling, 4-34, 4-35

string element, 4-30

structure, 4-29

versus fixed-length strings, 4-35

VARCHAR2 column

VARCHAR2 datatype

VARNUM datatype, 4-15

example of output value, 4-52

VARRAW datatype, 4-15

VARYING keyword

versus VARYING phrase, 4-29

versions of COBOL supported, 2-11

views

inserting rows into, F-54

updating rows in, F-85

warning flags for error reporting, 8-22

WHENEVER

DO CALL example, 8-31

WHENEVER directive, 8-28, F-91

careless usage, 8-33

CONTINUE action, 8-29

DO CALL action, 8-29

DO PERFORM action, 8-29

example, 8-30

examples, F-93

GOTO action, 8-30

NOT FOUND condition, 8-28

overview, 2-11

purpose, 8-28

scope of, 8-33

SQLERROR condition, 8-28

SQLWARNING condition, 8-28

STOP action, 8-30

syntax, 8-30

using to check SQLCA automatically, 8-28

WHENEVER DO CALL, A-3

WHERE clause, 5-11

DELETE statement, 5-11

of DELETE statement, F-33

of UPDATE statement, F-87

search condition, 5-11

SELECT statement, 5-8

UPDATE statement, 5-10

using host tables, 7-18

WHERE CURRENT OF clause, 5-16

WITH HOLD

clause of DECLARE CURSOR statement, 5-13

WITH HOLD clause, A-5

WORK option

of COMMIT statement, F-16

of ROLLBACK statement, F-75

X/Open application, 3-32

XA interface, 3-32

XREF precompiler option, 14-42