CACHE FREE ALL (Executable Embedded SQL Extension) command, F-10

C++, 1-10, 7-1

cache, 8-5

CACHE FREE ALL SQL command, 8-8

CASE OTT parameter, 16-28

case sensitivity

in precompiler options, 9-5

CHAR datatype, 3-26

CHAR_MAP precompiler option, 3-50, 9-10, A-3

character data, 3-49

character strings

multibyte, 4-8

semantics of, 3-10

CHARF datatype, 3-27, 3-60

CHARZ datatype, 3-27

child cursor, 6-17

CLOSE command

examples, F-12

CLOSE SQL command, F-11

CLOSE statement, 5-16

dependence on precompiler options, 5-16

example of, 5-16

purpose of, 5-13, 5-16

use in dynamic SQL method 4, 14-35

closing

cursors, F-11

CODE

precompiler option, 7-4

CODE OTT parameter, 16-26

code page, 4-3

CODE precompiler option, 9-10

coding conventions, 3-12

collection object types

collection types

using, 8-30

column list

in INSERT statements, 5-10

when permissible to omit, 5-10

COMMENT clause

of COMMIT command, F-14

Comments

restriction on, 13-29

COMMIT command, F-12

ending a transaction, F-59

examples, F-14

COMMIT SQL command, F-12

COMMIT statement, 10-5

effect of, 10-4

example of, 10-5

purpose of, 10-4

RELEASE option in, 10-5

using in a PL/SQL block, 10-14

where to place, 10-5

commits

automatic, 10-4

explicit versus implicit, 10-4

function of, 10-3

committing

transactions, F-12

communicating over a network, 4-21

COMP_CHARSET precompiler option, 9-11

compiling, 9-41

specifying include file locations, 3-9

compliance

ANSI, 1-6

ISO, 1-6

NIST, 1-6

concurrency

concurrent connections, 4-21

conditional precompilation, 9-39

defining symbols, 9-39

example, 9-40

CONFIG OTT parameter, 16-28

CONFIG precompiler option, 9-12

configuration file location, 9-5

configuration files, 9-5

and the Object Type Translator, 16-5

system, 9-3

user, 9-3

CONNECT command, F-14

examples, F-16

CONNECT SQL command, F-14

CONNECT statement

AT clause in, 4-23

connecting to Oracle with, 4-18

requirements for, 4-19

USING clause in, 4-23

using to enable a semantic check, D-4

connecting to Oracle, 4-18

automatic connections, 4-19

connections

default versus non-default, 4-22

Explicit connections, 4-22

implicit, 4-28

naming of, 4-23

CONTEXT ALLOCATE command, 4-40, F-16

CONTEXT ALLOCATE SQL command, F-16

context block

definition of, 15-5

CONTEXT FREE command, 4-41, F-17

CONTEXT FREE SQL command, F-17

CONTEXT USE directive, 4-41

CONTEXT USE SQL directive, F-18

CONTINUE action

CONTINUE option

CPP_SUFFIX precompiler option, 9-12

CONVBUFSZ clause, 4-5

conventions

description of, v

notation, v

CPP_SUFFIX

precompiler option, 7-5

CPP_SUFFIX precompiler options, 9-12

CREATE PROCEDURE statement

embedded, 6-19

creating

savepoints, F-61

curly brackets, v

CURRENT OF clause, 12-4

example of, 5-17

mimicking with ROWID, 10-12, 12-27

purpose of, 5-17

restrictions on, 5-18

current row

definition of, 2-8

using FETCH to retrieve, 5-13

cursor, 4-9

cursor cache, 6-17

definition of, 11-36

purpose of, C-9

cursor control statements

example of typical sequence, 5-18

cursor operations

overview of, 5-12

cursor variables, 4-9, F-8

allocating, 4-11

declaring, 4-10

restrictions on, 4-15

use of in recursive functions, 1-12

cursors, 9-40

cv_demo.pc, 4-16

cv_demo.sql, 4-15

Data Definition Language

creating CHAR objects with DBMS=V6, 9-15

data definition statements

in transactions, 10-4

database

database link

creating synonym for, 4-29

defining, 4-28

example using, 4-28

using in INSERT command, F-44

where stored, 4-28

database types

new, 8-34

datatype codes

used in descriptors, 14-14

datatype conversions, 3-57

datatype equivalencing, 2-7, 3-57

purpose of, 2-7

datatype mappings, 16-9

datatypes

coercing NUMBER to VARCHAR2, 14-14

dealing with ORACLE internal, 14-14

internal, 3-18

internal versus external, 2-7

list of internal, 14-12

need to coerce, 14-14

Oracle, 2-6

restrictions on using, 8-35

user-defined type equivalencing, F-65

when to reset, 14-14

DATE datatype, 3-25

DBMS

precompiler option

used in application migration, 3-10

DBMS option, 3-60

DBMS precompiler option, 9-13

deadlock

effect on transactions, 10-9

how broken, 10-9

declaration

of cursors, 5-13

of host arrays, 12-2

of pointer variables, 3-41

of SQLCA, 11-17

declarative SQL statements

in transactions, 10-4

uses for, 2-3

where allowed, 2-3

DECLARE CURSOR command

examples, F-21

DECLARE CURSOR SQL directive, F-20

DECLARE CURSOR statement

AT clause in, 4-25

use in dynamic SQL method 4, 14-24

DECLARE DATABASE SQL directive, F-22

Declare Section

when MODE=ANSI, 3-61

DECLARE statement, 5-14

example of, 5-13

purpose of, 5-12

required placement of, 5-13

use in dynamic SQL method 3, 13-18

DECLARE STATEMENT command

examples, F-24

scope of, F-24

DECLARE STATEMENT SQL directive, F-23

DECLARE STATEMENT statement

AT clause in, 4-26

example of using, 13-26

using with dynamic SQL, 13-26

when required, 13-26

DECLARE TABLE command

examples, F-26

DECLARE TABLE SQL directive, F-25

DECLARE TABLE statement

need for with AT clause, 4-24

using with the SQLCHECK option, D-5

DECLARE TYPE command, F-27

DEF_SQLCODE precompiler option, 9-15

default connections, 4-22

default database, 4-22

default file name extensions, 16-36

default name mapping, 16-36

DEFINE precompiler option, 9-16

used in application migration, 3-10

DELETE CASCADE, 11-21

DELETE command, F-28

embedded SQL examples, F-31

DELETE SQL command, F-28

DELETE statement

example of, 5-11

purpose of, 5-11

using host arrays in, 12-12

WHERE clause in, 5-11

delimiters

C versus SQL, 3-13

DEPT table, 2-10

DESCRIBE BIND VARIABLES statement

use in dynamic SQL method 4, 14-24

DESCRIBE command

example, F-33

use with PREPARE command, F-31

DESCRIBE SELECT LIST statement

use in dynamic SQL method 4, 14-29

DESCRIBE SQL command, F-31

DESCRIBE statement

use in dynamic SQL method 4, 13-23

descriptors, 14-4

bind descriptor, 13-23

definition of, 13-23

need for, 14-4

select descriptor, 13-23

using the sqlald() function to allocate, 14-5

using the sqlclu() function to deallocate, 14-35

distributed processing

support for, 4-21

using SQL*Net for, 4-21

distributed transactions, F-60

DO action

DO option

DURATION precompiler option, 8-20, 9-17

dots, vi

DTP model, 4-55

dummy host variables

placeholders, 13-3

dynamic PL/SQL

rules for, 13-27

versus dynamic SQL, 13-27

dynamic SQL

advantages and disadvantages of, 13-2

cannot use cursor variables with, 4-15

choosing the right method, 13-6

restrictions on use of datatypes, 8-35

restrictions on using datatypes in, 8-35

use of PL/SQL with, 6-25

uses for, 13-2

using the AT clause in, 4-26

when to use, 13-3

dynamic SQL method 1

commands used with, 13-5

description of, 13-9

example of, 13-9

how to use, 13-8

requirements for, 13-5

use of EXECUTE IMMEDIATE with, 13-9

use of PL/SQL with, 13-28

dynamic SQL method 2

commands used with, 13-5

description of, 13-12

example of, 13-14

requirements for, 13-5

use of DECLARE STATEMENT with, 13-26

use of EXECUTE with, 13-12

use of PL/SQL with, 13-28

use of PREPARE with, 13-12

dynamic SQL method 3

commands used with, 13-5

compared to method 2, 13-17

example program, 13-20

requirements for, 13-5

sequence of statements used with, 13-18

use of DECLARE STATEMENT with, 13-26

use of DECLARE with, 13-18

use of FETCH with, 13-19

use of OPEN with, 13-19

use of PL/SQL with, 13-28

use of PREPARE with, 13-18

dynamic SQL method 4

dynamic SQL methods

overview of, 13-4

dynamic SQL statements

binding of host variables in, 13-4

definition of, 13-2

how processed, 13-4

parsing of, 13-4

requirements for, 13-3

use of placeholders in, 13-3

using host arrays in, 13-27

versus static SQL statements, 13-2

embedded PL/SQL

procedures and functions, 6-3

requirements for, 6-6

SQLCHECK option, 6-7

support for SQL, 2-5

user-defined records, 6-5

using %TYPE, 6-2

using the VARCHAR pseudotype with, 6-11

using to improve performance, C-4

where allowed, 6-6

embedded SQL

ALLOCATE command, F-8

CLOSE command, F-11

COMMIT command, F-12

CONNECT command, F-14

CONTEXT ALLOCATE command, 4-40, F-16

CONTEXT FREE command, 4-41, F-17

DECLARE CURSOR command, F-20

DECLARE TABLE command, F-25

definition of, 2-2

DESCRIBE command, F-31

difference from interactive SQL, 2-5

ENABLE THREADS command, 4-40, F-33

EXEC SQL CACHE FREE ALL, 8-8

EXECUTE command, F-34, F-36

FETCH command, F-39

INSERT command, F-42

key concepts of, 2-2

mixing with host-language statements, 2-4

OPEN command, F-54

overview of, 2-2

PREPARE command, F-56

requirements for, 2-4

SAVEPOINT command, F-61

SELECT command, F-62

syntax for, 2-4

testing with SQL*Plus, 1-4

TYPE command, F-65

UPDATE command, F-66

using LOBs in, 4-9

using OCI types in, 8-33

using REFs in, 8-32

VAR command, F-71

when to use, 1-3

WHENEVER command, F-73

embedded SQL statements

labels for, 3-16

referencing host arrays in, 12-3

referencing host variables in, 3-32

suffixes and prefixes not allowed in, 3-13

terminator for, 3-16

use of apostrophes in, 3-13

use of quotation marks in, 3-13

embedding

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

EMP table, 2-10

ENABLE THREADS command, 4-40, F-33

ENABLE THREADS SQL extension, F-33

enabling

threads, 4-40, F-33

encoding scheme, 4-3

enqueues

locking, 10-2

entering options, 3-5, 9-7

equivalencing

host variable equivalencing, F-71

user-defined type equivalencing, F-65

equivalencing of datatypes

datatype equivalencing, 2-7

error detection

error reporting, F-74

SQLCA versus WHENEVER statement, 11-3

use of ROLLBACK statement in, 10-8

error messages

maximum length of, 11-24

use in error reporting, 11-16

using the sqlglm() function to get, 11-23

where available in SQLCA, 11-16

error reporting

key components of, 11-15

use of error messages in, 11-16

use of parse error offset in, 11-16

use of rows-processed count in, 11-15

use of warning flags in, 11-15

WHENEVER command, F-74

ERRORS precompiler option, 9-18

ERRTYPE

precompiler option, 9-18

ERRTYPE OTT parameter, 16-28

ERRTYPE precompiler option, 8-21, 9-18

exception, PL/SQL

definition of, 6-13

EXEC ORACLE DEFINE statement, 9-39

EXEC ORACLE ELSE statement, 9-39

EXEC ORACLE ENDIF statement, 9-39

EXEC ORACLE IFDEF statement, 9-39

EXEC ORACLE IFNDEF statement, 9-39

EXEC ORACLE OPTION

used to set option values in a file, 9-7

EXEC ORACLE statement

scope of, 9-8

syntax for, 9-8

uses for, 9-8

using to enter options inline, 9-8

EXEC SQL CACHE FREE ALL command, 8-8

EXEC SQL clause

using to embed SQL, 2-4

EXEC SQL INCLUDE

contrasted with #include, 3-9

EXEC SQL VAR statement

CONVBUFSZ clause, 4-5

EXEC TOOLS

GET CONTEXT statement, 15-17

GET statement, 15-16

MESSAGE statement, 15-18

SET CONTEXT statement, 15-17

SET statement, 15-16

EXEC TOOLS statements, 15-15

executable SQL statements

EXECUTE ... END-EXEC SQL extension, F-34

EXECUTE command

examples, F-35, F-37

EXECUTE IMMEDIATE command, F-37

examples, F-38

EXECUTE IMMEDIATE SQL command, F-37

EXECUTE IMMEDIATE statement

AT clause in, 4-26

use in dynamic SQL method 1, 13-9

EXECUTE SQL command, F-36

EXECUTE statement

use in dynamic SQL method 2, 13-12

execution of statements, 13-4

execution plan, C-5-C-6

EXPLAIN PLAN statement

function of, C-6

using to improve performance, C-6

explicit connections, 4-23

description of, 4-23

multiple, 4-26

single, 4-23

extensions

default file name, 16-36

external datatypes

definition of, 2-7

FLOAT, 3-22

INTEGER, 3-22

STRING, 3-22

F variable in SQLDA

how value is set, 14-10

purpose of, 14-10

FETCH command

examples, F-40

used after OPEN command, F-55

FETCH SQL command, F-39

FETCH statement

example of, 5-15

INTO clause in, 5-15

purpose of, 5-13, 5-15

results of, 5-15

use in dynamic SQL method 3, 13-19

use in dynamic SQL method 4, 14-33

fetching

rows from cursors, F-39

fetching in batches

batch fetch, 12-6

FIPS flagger

warns of array usage, 12-4

warns of missing Declare Section, 3-28

warns of use of pointers as host variables, 3-55

FIPS precompiler option, 9-19

flags

warning flags, 11-15

FLOAT datatype, 3-22

FOR clause

example of using, 12-14

of embedded SQL EXECUTE command, F-36

of embedded SQL INSERT command, F-44

purpose of, 12-14

requirements for, 12-14

restrictions on, 12-14

using in dynamic SQL method 4, 14-35

using with host arrays, 12-14

when variable negative or zero, 12-14

FOR UPDATE OF clause

locking rows with, 10-11

purpose of, 10-11

when to use, 10-11

FORCE clause

of COMMIT command, F-14

of ROLLBACK command, F-59

forward references

why not allowed, 5-13

FREE SQL command, 8-7

free() function, 14-35

example of using, 14-35

freeing

thread context, 4-41, F-17

full scan

description of, C-6

function prototype

definition of, 9-10

functions

cannot serve as host variables, 3-32

GENXTB form

how to run, 15-13

use with user exits, 15-13

GENXTB utility

how to run, 15-13

use with user exits, 15-13

GOTO action

GOTO option

for separate precompilations, 9-40

guidelines

for dynamic SQL, 13-6

for the WHENEVER statement, 11-27

for transactions, 10-14

for user exits, 15-14

heap

definition of, 11-36

HFILE OTT parameter, 16-27

hints

COST, C-5

for the ORACLE SQL statement optimizer, 5-16

in DELETE statements, F-31

in SELECT statements, F-64

in UPDATE statements, F-70

HOLD_CURSOR

precompiler option

used to improved performance, C-12

what it affects, C-7

HOLD_CURSOR option

of ORACLE Precompilers, F-12

HOLD_CURSOR precompiler option, 9-21

host arrays

host language

definition of, 2-2-2-3

host program

host structures

assigning values to, 2-6

declarations, 8-31

declaring, 8-31

definition of, 2-6

dummy, 13-3

host variable equivalencing, F-71

in EXECUTE command, F-36

in OPEN command, F-55

in user exits, 15-5

input versus output, 5-3

must resolve to an address, 3-32

overview of, 2-6

purpose of, 5-3

requirements for, 2-6

restrictions on, 3-32

rules for naming, 3-14

scope in PL/SQL, 6-7

using in PL/SQL, 6-7

where allowed, 2-6

I variable in SQLDA

how value is set, 14-9

purpose of, 14-9

IAF GET statement

example of using, 15-6

in user exits, 15-5

purpose of, 15-5

specifying block and field names in, 15-6

syntax for, 15-5

IAF PUT statement

example of using, 15-7

in user exits, 15-7

purpose of, 15-7

specifying block and field names in, 15-7

IAP in SQL*Forms

identifiers, ORACLE

implicit connections, 4-28

multiple, 4-29

single, 4-28

IN OUT parameter mode, 6-3

IN parameter mode, 6-3

INAME

precompiler option, 9-22

INAME precompiler option, 9-22

INCLUDE

precompiler option, use of, 3-9

using to include the SQLCA, 11-17

INCLUDE precompiler option, 9-22

indexes

using to improve performance, C-6

indicator arrays, 12-4

example of using, 12-4

uses for, 12-4

INDICATOR keyword, 3-33

indicator variables

in-doubt transaction, 10-13

INITFILE OTT parameter, 16-27

INITFUNC OTT parameter, 16-27

initialization function

calling, 16-19

tasks of, 16-21

input host variables

assigning values to, 5-4

INSERT command

embedded SQL examples, F-45

insert of no rows

cause of, 11-20

INSERT SQL command, F-42

INSERT statement

requirements for, 5-10

using host arrays in, 12-10

VALUES clause in, 5-10

inserting

rows into tables and views, F-42

INTEGER datatype, 3-22

interface

native, 4-55

XA, 4-55

internal datatypes

definition of, 2-7

INTO clause

for output host variables, 5-3

in FETCH statements, 5-15

in INSERT statements, 5-10

in SELECT statements, 5-9

of FETCH command, F-39

of SELECT command, F-64

used with FETCH instead of SELECT, 5-13

intype file, 16-30

providing when running OTT, 16-8

structure of, 16-30

INTYPE OTT parameter, 16-26

INTYPE precompiler option, 9-23

invalid use

of precompiler preprocessor, 3-7

ISO

compliance, 1-6

joins

restriction on, 5-18

L variable in SQLDA

how value is set, 14-8

purpose of, 14-8

label name

maximum length of, 3-16

large objects (LOBs)

declaring, 4-8

handling, 4-8

locators for columns in relational tables, 4-9

operations in embedded PL/SQL, 4-9

operations in OCI, 4-9

using in embedded SQL, 4-9

LINES

precompiler option, 9-24

LINES precompiler option, 9-24

link

LNAME precompiler option, 9-25

LNPROC

VMS link script, 1-12

LOBs

declaring, 4-8

handling, 4-8

locators for columns in relational tables, 4-9

operations in embedded PL/SQL, 4-9

operations in OCI, 4-9

using in embedded SQL, 4-9

location transparency

how provided, 4-29

locators

for LOB columns, 4-9

lock

released by ROLLBACK statement, F-59

LOCK TABLE statement

example of, 10-12

locking tables with, 10-12

NOWAIT parameter in, 10-12

purpose of, 10-12

specifying lock mode in, 10-12

locking, 10-11

explicit versus implicit, 10-11

modes of, 10-2

overriding default, 10-11

privileges needed to obtain, 10-14

table versus row, 10-11

uses for, 10-11

with FOR UPDATE OF, 10-11

with the LOCK TABLE statement, 10-12

logging on, 4-18

LONG datatype, 3-23

LONG RAW datatype, 3-26

LONG VARCHAR

datatype, 3-26

LONG VARRAW datatype, 3-26

LTYPE precompiler option, 9-25

lvalue, 3-28

M variable in SQLDA

how value is set, 14-10

purpose of, 14-10

malloc()

example of using, 14-31

purpose of, 14-31

MAXLITERAL

default value for, 3-15

precompiler option, 9-26

MAXLITERAL precompiler option, 9-26

MAXOPENCURSORS

precompiler option

effect on performance, C-11

for multiple cursors, 5-14

specifying for separate precompilation, 9-41

what it affects, C-7

MAXOPENCURSORS precompiler option, 9-26

migration

error message codes, 3-11

include files, 3-11

indicator variables, 3-11

Migration from Pro*C/C++ Release 2, A-4

MLSLABEL datatype, 3-28

MODE

precompiler option, 3-10

effect on OPEN, 5-14

MODE precompiler option, 9-27

modes, parameter, 6-3

multi-threaded applications, 4-35

, 4-37, 4-43

sample program, 4-44

user-interface features

embedded SQL statements and directives, 4-40

N variable in SQLDA

how value is set, 14-7

purpose of, 14-7

namespaces

reserved by Oracle, B-7

naming

of cursors, 5-13

of database objects, F-8

of select-list items, 14-4

of SQL*Forms user exits, 15-14

national language support (NLS), 4-2

NATIVE

NESTED TABLE, collection type, 8-30

native interface, 4-55

nested tables, 8-3

network

communicating over, 4-21

protocols, 4-22

reducing traffic on, C-4

NIST

compliance, 1-6

NLS (national language support), 4-2

support for character sets, A-2

NLS parameter

NLS_CURRENCY, 4-2

NLS_DATE_FORMAT, 4-2

NLS_DATE_LANGUAGE, 4-2

NLS_ISO_CURRENCY, 4-2

NLS_LANG, 4-3

NLS_LANGUAGE, 4-2

NLS_NUMERIC_CHARACTERS, 4-2

NLS_SORT, 4-2

NLS_TERRITORY, 4-2

NLS_CHAR precompiler option, 9-28

NLS_LOCAL precompiler option, 9-29

node

current, 4-22

definition of, 4-21

NOT FOUND condition

meaning of, 11-25

in LOCK TABLE statements, 10-12

notation

conventions, v

rules for, v

NOWAIT parameter

effect of, 10-12

omitting, 10-12

nulls

definition of, 2-6

detecting, 5-5

handling in dynamic SQL method 4, 14-16

using the sqlnul() function to test for, 14-16

null-terminated string, 3-23

NUMBER datatype, 3-21

using the sqlprc() function with, 14-15

numeric expressions

cannot serve as host variables, 3-32

object cache, 8-5

OBJECT CREATE SQL command, 8-11

OBJECT DELETE SQL command, 8-13

OBJECT DEREF SQL command, 8-12

OBJECT FLUSH SQL command, 8-14

OBJECT GET SQL command, 8-18

object support, 8-1

Object Type Translator

datatype mappings, 16-9

using with Pro*C/C++, 16-21

Object Type Translator (OTT), A-3

command line, 16-6

command line syntax, 16-24

creating types in the database, 16-5

outtype file, 16-16

parameters, 16-25, 16-29

providing an intype file, 16-8

reference, 16-23

restrictions, 16-37

using, 16-1-16-2

OBJECT UPDATE SQL command, 8-13

objects

accessing with OCI, 16-18

introduction to, 8-3

large, declaring, 4-8

large, handling, 4-8

manipulating with OCI, 16-18

persistent, 8-6

persistent versus transient copies of, 8-6

references to, 8-4

transient, 8-6

types, 8-3

using object types in Pro*C/C++, 8-5

OBJECTS precompiler option, 8-20, 9-29

OCI applications

using the OTT with, 16-17

OCI calls, 1-10

embedding, 4-32

in an X/A environment, 4-56

OCI onblon() call

not used to connect, 4-32

OCI orlon() call

not used to connect, 4-32

OCI Release 8, 4-50

accessing and manipulating objects, 16-18

interfacing to, 4-51

parameters in the environment handle, 4-50

OCI types

using in embedded SQL, 8-33

ONAME option

usage notes for, 9-30

ONAME precompiler option, 9-30

open

a cursor variable, 4-11

OPEN command

examples, F-56

OPEN SQL command, F-54

OPEN statement, 5-14

dependence on precompiler options, 5-14

effect of, 5-14

example of, 5-14

purpose of, 5-12, 5-14

use in dynamic SQL method 3, 13-19

use in dynamic SQL method 4, 14-29

OPEN_CURSORS parameter, 6-17

opening

cursors, F-54

operators

C versus SQL, 3-15

restrictions on, 3-15

optimization approach, C-5

optimizer hints, C-5

in C, 5-16

in C++, 5-17, 7-4

ORACA, 11-3

example of using, 11-39

precompiler option, 9-30

using to gather cursor cache statistics, 11-39

ORACA option

usage notes for, 9-31

ORACA precompiler option, 9-30

ORACAID component, 11-36

Oracle

datatypes, 2-6

Forms Version 4, 15-15

Open Gateway

using the ROWID datatype with, 3-24

precompilers

definition of, 1-2

function of, 1-2

Toolset, 15-15

Oracle Call Interface, 4-32

Oracle Communications Area, 11-33

ORACLE identifiers

how to form, F-8

Oracle namespaces, B-7

OTT parameters

CASE, 16-28

CODE, 16-26

where they appear, 16-29

OTT. See Object Type Translator, 16-1

OUT parameter mode, 6-3

output host variables

assigning values to, 5-3

definition of, 5-3

outtype file, 16-30

when running OTT, 16-16

OUTTYPE OTT parameter, 16-26

overhead

reducing, C-3

parameter modes, 6-3

parent cursor, 6-17

PARSE

precompiler option, 7-4

parse

definition of, 13-4

parse error offset

how to interpret, 11-16

use in error reporting, 11-16

PARSE precompiler option, 9-31

parsing dynamic statements

PREPARE command, F-56

password

defining, 4-19

passwords

changing at runtime, 4-30, A-2

performance

eliminating extra parsing to improve, C-7

optimizing SQL statements to improve, C-5

reasons for poor, C-2

using embedded PL/SQL to improve, C-4

using HOLD_CURSOR to improve, C-12

using host arrays to improve, C-3

using indexes to improve, C-6

using RELEASE_CURSOR to improve, C-12

using row-level locking to improve, C-7

persistent copies of objects, 8-6

persistent objects, 8-6

PL/SQL, 1-4

anonymous block

used to open a cursor variable, 4-12

cursor FOR loop, 6-3

description of, 1-4

difference from SQL, 1-4

executing a block using the AT clause, 4-24

integration with Server, 6-2

main advantage of, 1-4

packages, 6-4

PL/SQL tables, 6-4

procedures and functions, 6-3

RECORD type

cannot be bound to a C struct, 3-37

relationship with SQL, 1-4

setting SQLCA, 11-23

user-defined records, 6-5

PL/SQL blocks

embedded in Oracle7 precompiler programs, F-34

placeholders

duplicate, 13-14, 13-28

naming, 13-14

proper order of, 13-14

use in dynamic SQL statements, 13-3

pointer

definition of, 3-41

to cursor variables

restrictions on, 4-10

pointer variables

declaring, 3-41

determining size of referenced value, 3-41

referencing, 3-41

referencing struct members with, 3-41

precedence of precompiler options, 9-3

precision

definition of, 14-15

using sqlprc() to extract, 14-15

when not specified, 14-15

precompilation, 9-6

conditional, 9-39

precompilation unit, 4-19, 9-6

precompiler

Oracle precompilers, 1-2

precompiler directives

CONTEXT USE, 4-41, F-18

EXEC SQL DECLARE DATABASE, F-22

precompiler option

DURATION, 9-17

precompiler option VERSION, 9-38

precompiler options

precompiler options, scope of, 9-6

PREPARE command

examples, F-57

PREPARE SQL command, F-56

PREPARE statement

effect on data definition statements, 13-5

use in dynamic SQL, 13-12, 13-18

use in dynamic SQL method 4, 14-24

preprocessor directives

directives not supported by Pro*C, 3-4

Preprocessor, support of, 3-3

private SQL area

association with cursors, 2-7

Pro*C Precompiler

use of PL/SQL with, 6-6

Pro*C/C++ Precompiler

new database types, 8-34

new features, A-1, A-4

object support in, 8-1

runtime context, 4-50

using OTT with, 16-21

Pro*C/C++ Precompiler Release 2

migration from, A-4

procedural database extension, 6-4

Program Global Area (PGA), 6-17

program termination

normal versus abnormal, 10-9

programming guidelines, 3-12

queries

association with cursors, 5-12

forwarding, 4-29

incorrectly coded, 5-9

kinds of, 5-8

requirements for, 5-8

returning more than one row, 5-8

single-row versus multirow, 5-9

RAW datatype, 3-25

read consistency

in SET TRANSACTION statement, 10-10

READ ONLY parameter

read-only transactions

description of, 10-10

example of, 10-10

how ended, 10-10

record, 6-5

REF

structure for, 8-32

REF (reference to object), 8-4

REFERENCE clause

in TYPE statement, 3-60

references to objects (REFs)

declaring, 8-32

using, 8-32

using in embedded SQL, 8-32

referencing

of host arrays, 12-2-12-3

REFs

declaring, 8-32

using, 8-32

using in embedded SQL, 8-32

RELEASE option, 10-9

if omitted, 10-9

in COMMIT statement, 10-5

in ROLLBACK statement, 10-8

purpose of, 10-5

restriction on, 10-8

RELEASE_CURSOR

precompiler option

what it affects, C-7

RELEASE_CURSOR option

of ORACLE Precompilers, F-12

using to improve performance, C-12

RELEASE_CURSOR precompiler option, 9-31

remote database

declaration of, F-22

resource manager, 4-55

restrictions

on AT clause, 4-25

on Comments, 13-29

on CURRENT OF clause, 5-18

on declaring cursors, 5-13

on FOR clause, 12-14

on host arrays, 12-4, 12-9, 12-11-12-13

on input host variables, 5-3

on nulls, 5-7

on separate precompilation, 9-40

on SET TRANSACTION statement, 10-10

use of CURRENT OF clause, 12-4

retrieving rows from a table

embedded SQL, F-62

return codes

user exits, 15-9

roll back

to a savepoint, F-61

to the same savepoint multiple times, F-59

ROLLBACK command

ending a transaction, F-59

examples, F-60

rollback segment

function of, 10-2

ROLLBACK SQL command, F-58

ROLLBACK statement, 10-8

effect of, 10-7

example of, 10-8

in error handling routines, 10-8

purpose of, 10-7

RELEASE option in, 10-8

TO SAVEPOINT clause in, 10-7

using in a PL/SQL block, 10-14

where to place, 10-8

rollbacks

automatic, 10-8

function of, 10-3

statement-level, 10-9

rolling back

transactions, F-58

row locks

acquiring with FOR UPDATE OF, 10-11

advantage of, C-7

using to improve performance, C-7

when acquired, 10-12

when released, 10-12

ROWID

pseudocolumn, 10-12

using to mimic CURRENT OF, 10-12, 12-27

rows

fetching from cursors, F-39

inserting into tables and views, F-42

updating, F-66

rows-processed count

in the SQLCA, 11-21

use in error reporting, 11-15

runtime context

establishing, 4-50

terminating, 4-50

runtime type checking, 8-22

S variable in SQLDA

how value is set, 14-10

purpose of, 14-10

sample database tables

DEPT table, 2-10

EMP table, 2-10

sample object code, 8-24

sample programs

cursor variable demos, 4-15

cv_demo.pc, 4-16

cv_demo.sql, 4-15

how to precompile, 2-11

SAVEPOINT command, F-61

examples, F-62

SAVEPOINT SQL command, F-61

SAVEPOINT statement

savepoints

Scale

using SQLPRC to extract, 4-7

scale

definition of, 4-7, 14-15

using sqlprc() to extract, 14-15

when negative, 4-7, 14-15

SCHEMA_NAMES OTT parameter, 16-29

usage, 16-34

scope

of a cursor variable, 4-10

of DECLARE STATEMENT command, F-24

of precompiler options, 9-6

of the EXEC ORACLE statement, 9-8

of WHENEVER statement, 11-27

search condition

definition of, 5-12

in the WHERE clause, 5-12

SELECT command

embedded SQL examples, F-64

select descriptor, 13-23, 14-4

definition of, 13-23

information in, 13-24

select list

definition of, 5-9

number of items in, 5-9

using the free() function for, 14-35

using the malloc() function for, 14-31

SELECT SQL command, F-62

select SQLDA

purpose of, 14-3

SELECT statement, 5-9

clauses available for, 5-9

using host arrays in, 12-5

WHERE clause in, 5-9

SELECT_ERROR

precompiler option, 5-9, 9-33

SELECT_ERROR precompiler option, 9-33

semantic checking

controlling with the SQLCHECK option, D-2

definition of, D-2

enabling, D-4

with the SQLCHECK option, D-2

separate precompilation

guidelines for, 9-40

referencing cursors for, 9-40

restrictions on, 9-40

specifying MAXOPENCURSORS for, 9-41

using a single SQLCA with, 9-41

server

integration with PL/SQL, 6-2

session

use of subqueries in, 5-11

sessions

beginning, F-14

SET clause

in UPDATE statements, 5-11

purpose of, 5-11

SET TRANSACTION statement

example of, 10-10

purpose of, 10-10

READ ONLY parameter in, 10-10

requirements for, 10-10

restrictions on, 10-10

SQL

SQL commands

SQL Communications Area, 11-3

SQL Descriptor Area

SQL directives

DECLARE DATABASE, F-22

DECLARE STATEMENT, F-23

DECLARE TABLE, F-25

TYPE, F-65

VAL, F-71

WHENEVER, F-73

SQL extensions

ENABLE THREADS, F-33

EXECUTE ... END-EXEC, F-34

SQL statements

concerns when executing, 5-7

executable versus declarative, 2-3

for defining and controlling transactions, 10-3

for manipulating a cursor, 5-8, 5-12

for manipulating ORACLE data, 5-8

for querying Oracle data, 5-8

optimizing to improve performance, C-5

rules for executing, C-5

types of, 2-3

SQL*Forms

display error screen in, 15-9

IAP constants in, 15-9

returning values to, 15-9

reverse return code switch in, 15-9

SQL*Net

connecting to Oracle via, 4-21

connecting using Version 2, 4-19

connection syntax, 4-22

for concurrent connections, 4-21

function of, 4-21

SQL*Plus

using to test SELECT statements, 5-10

versus embedded SQL, 1-3

SQL_CURSOR, F-9

sqlald() function

example of using, 14-20

purpose of, 14-5

syntax for, 14-5

sqlaldt() function, 4-34

SQLCA, 11-3, 11-15

components in, 11-19

components set for a PL/SQL block, 11-23

declaring, 11-17

description of, 11-17

explicit versus implicit checking of, 11-3

including multiple times, 3-8

overview of, 2-8

SQLCABC component in, 11-20

SQLCAID component in, 11-20

sqlcode component in, 11-20

sqlerrd, 11-21

sqlerrd[2] component in, 11-21

sqlerrmc component in, 11-20

sqlerrml component in, 11-20

sqlwarn, 11-22

use in separate precompilations, 9-41

using more than one, 11-17

using with SQL*Net, 11-17

sqlca.h

listing of, 11-18

use of SQLCA_STORAGE_CLASS with, 9-41

SQLCAID component, 11-20

sqlcdat() function, 4-34

SQLCHECK

precompiler option, 9-33

SQLCHECK option, D-4-D-5

restrictions on, D-2

usage notes for, 9-34

what it affects, D-3

SQLCHECK precompiler option, 9-33

SQLCHECK support for objects, 8-21

SQLCHECK, precompiler option, 8-21

sqlclu() function

example of using, 14-35

purpose of, 14-35

syntax for, 14-35

sqlclut() function, 4-34

SQLCODE, 9-28

sqlcode

component in SQLCA, 11-3, 11-15

interpreting values of, 11-20

SQLCODE status variable

declaring, 11-14

when declared with the SQLCA, 11-15

when used, 11-14

sqlcpr.h, 11-23

sqlcurt() function, 4-34

SQLDA

bind versus select, 13-24

information stored in, 13-24

struct, contents of, 14-5

structure of, 14-7

T variable in, 14-8

use in dynamic SQL method 4, 14-4

sqlerrd

component, 11-16, 11-21

sqlerrd[2] component, 11-15, 11-21

returns N or rows fetched, 12-7

use with data manipulation statements, 12-27

sqlerrm

component in the SQLCA, 11-3

sqlerrmc component, 11-20

sqlerrml component, 11-20

SQLERROR

WHENEVER command condition, F-73

SQLERROR condition

sample program for, 11-33

meaning of, 11-25

sqlglm(), 11-23

sqlglm() function, 11-23

example of using, 11-24

parameters of, 11-23

sqlglmt() function, 4-34

sqlgls() function, 11-30

example of use, 3-46

sqlglst() function, 4-34

SQLIEM function

in user exits, 15-10

purpose of, 15-10

syntax for, 15-10

sqlld2() function, 4-56

sqlld2t() function, 4-34

sqllda() function, 4-32

sqlldat() function, 4-34

SQLLIB, 3-46

extensions for OCI interoperability, 4-50

new names for functions, A-3

sqlnul() function

example of using, 14-17

purpose of, 14-16

syntax for, 14-16

use of with T variable, 14-9

sqlnult() function, 4-34

sqlpr2() function, 14-16

sqlpr2t() function, 4-34

sqlprc() function, 14-15-14-16

sqlprct() function, 4-34

SQLSTATE, 9-28

class codes, 11-5

declaring, 11-4

mapping to Oracle errors, 11-8

predefined classes, 11-6

status codes, 11-8

status variable, 11-2, 11-4

using, 11-13

values, 11-5

sqlvcp() function, 3-46

sqlvcpt() function, 4-34

sqlwarn

flag, 11-22

SQLWARNING

WHENEVER command condition, F-73

SQLWARNING condition

meaning of, 11-25

statement-level rollback

description of, 10-9

to break deadlocks, 10-9

status codes

meaning of, 11-15

status variables, 11-2

STOP action

STOP option

packaged versus stand-alone, 6-19

stored procedures

program example, 6-21

stored subprograms

calling, 6-21

creating, 6-19

stored versus inline, 6-18

STRING datatype, 3-22

string host variables

declaring, 3-55

struct pointers as host variables, 3-42

structs

array of, 12-16, A-2

as host variables, 3-35

C, using, 8-29

for collection object types, 8-30

generating C structs for a REF, 8-32

structures

cannot be nested, 3-37

nested structs not permitted for host structures, 3-37

subqueries

definition of, 5-10

example of, 5-11

uses for, 5-10

using in the SET clause, 5-11

using in the VALUES clause, 5-11

syntax checking

controlling with the SQLCHECK option, D-2

syntax diagram

syntax, embedded SQL, 2-4

SYS_INCLUDE

, 7-5

SYS_INCLUDE precompiler option, 9-35

system configuration file, 9-3

system failure

effect on transactions, 10-4

System Global Area (SGA), 6-18

system header files

specifying the location of, 7-5

system-specific Oracle documentation, ii, 1-12, 3-5, 3-11, 4-21, 4-56, 9-41, 15-2

system-specific reference, 3-22, 9-2, 9-4-9-5, 9-23, 9-36

T variable in SQLDA

how value is set, 14-8

purpose of, 14-8

table locks

acquiring with LOCK TABLE, 10-12

tables

inserting rows into, F-42

nested, 8-3

updating rows in, F-66

terminal

encoding scheme, 4-3

termination, program

normal versus abnormal, 10-9

thread, F-16-F-18, F-33

THREADS, 4-40

threads

allocating context, 4-40, F-16

enabling, 4-40, F-33

freeing context, 4-41, F-17

use context, 4-41, F-18

THREADS precompiler option, 9-36

TO clause

of ROLLBACK command, F-58

TO SAVEPOINT clause

in ROLLBACK statement, 10-7

Toolset

trace facility

using to improve performance, C-6

transaction processing

overview of, 2-8

statements used for, 2-8

transaction processing monitor, 4-55

transactions

transient copies of objects, 8-6

transient objects, 8-6

truncated values

detecting, 5-5, 6-13

truncation error

when generated, 5-7

tuning, performance, C-2

two-task

linking, 9-41

type checking at runtime, 8-22

TYPE command

examples, F-66

TYPE SQL directive, F-65

unconditional deletion

definition of, 11-22

undo a transaction, F-58

unions

cannot be nested in host structures, 3-37

not permitted as host structures, 3-37

UNIX

linking a Pro*C application under, 1-12

UNSAFE_NULL precompiler option, 9-36

UNSIGNED datatype, 3-26

UPDATE CASCADE, 11-21

UPDATE command

embedded SQL examples, F-70

UPDATE SQL command, F-66

UPDATE statement

example of, 5-11

purpose of, 5-11

SET clause in, 5-11

using host arrays in, 12-11

WHERE clause in, 5-11

updating

rows in tables and views, F-66

use

thread context, 4-41, F-18

user configuration file, 9-3

user exits

calling from a SQL*Forms trigger, 15-8

common uses for, 15-4

example of, 15-10

guidelines for, 15-14

kinds of statements allowed in, 15-5

linking into IAP, 15-13

meaning of codes returned by, 15-9

naming, 15-14

passing parameters to, 15-8

requirements for variables in, 15-5

running the GENXTB form, 15-13

running the GENXTB utility for, 15-13

steps in developing, 15-4

use of IAF GET statements in, 15-6

use of IAF PUT statements in, 15-7

use of WHENEVER statement in, 15-10

user session

used in WHERE clause, 5-12

user-defined record, 6-5

user-defined stored function

user-defined type equivalencing, F-65

USERID

precompiler option, 9-37

USERID option

using with the SQLCHECK option, D-4

when required, 9-37

USERID OTT parameter, 16-25

USERID precompiler option, 9-37

username

defining, 4-19

using C structures, 8-29

USING clause

in CONNECT statement, 4-23

in the EXECUTE statement, 13-14

of FETCH command, F-40

of OPEN command, F-55

purpose of, 13-14

using indicator variables in, 13-14

using collection types, 8-30

using dbstring

SQL*Net database id specification, F-16

Using REFs in Embedded SQL, 8-32

V variable in SQLDA

how value is set, 14-7

purpose of, 14-7

V6

V6_CHAR

V7

kinds of values allowed in, 5-10

VALUES clause

in INSERT statements, 5-10

of embedded SQL INSERT command, F-44

of INSERT command, F-44

purpose of, 5-10

requirements for, 5-10

use of subqueries in, 5-11

VAR command

examples, F-72

VAR SQL directive, F-71

VAR statement

syntax for, 3-58-3-59

VARCHAR

arrays of, 12-2

VARCHAR datatype, 3-23

VARCHAR precompiler option, 9-37

VARCHAR pseudotype

requirements for using with PL/SQL, 6-11

VARCHAR variables

advantages of, 3-43

declaring, 3-43

length member in, 3-43

must be passed to a function by reference, 3-45

specifying the length of, 3-43

structure of, 3-43

using macros to define length of, 3-3

versus character arrays, 3-56

VARCHAR2 datatype, 3-20, 3-60

variables, 2-6

cursor, 4-9

host, 8-31

indicator, 8-31

VARRAW datatype, 3-25

VARRAY, collection type, 8-30

varying length arrays, 8-4

VERSION precompiler option, 8-19, 9-38

vertical bar, v

views

inserting rows into, F-42

updating rows in, F-66

VMS

linking a precompiler application, 1-12

warning flags

use in error reporting, 11-15

WHENEVER command

examples, F-74

WHENEVER SQL directive, F-73

WHENEVER statement

automatic checking of SQLCA with, 11-24

CONTINUE action in, 11-25

DO action in, 11-25

examples of, 11-26

GOTO action in, 11-25

guidelines for, 11-27

maintaining addressability for, 11-29

NOT FOUND condition in, 11-25

overview of, 2-9

scope of, 11-27

SQLERROR condition in, 11-25

SQLWARNING condition in, 11-25

STOP action in, 11-25

use in user exits, 15-10

using to avoid infinite loops, 11-28

using to handle end-of-data conditions, 11-28

where to place, 11-28

WHERE clause

host arrays in, 12-15

if omitted, 5-12

in DELETE statements, 5-11

in SELECT statements, 5-9

in UPDATE statements, 5-11

of DELETE command, F-30

of UPDATE command, F-69

purpose of, 5-12

search condition in, 5-12

WHERE CURRENT OF clause

CURRENT OF clause, 5-17

WORK option

of COMMIT command, F-13

of ROLLBACK command, F-58

X variable in SQLDA

application development, 4-55

X/Open, 4-55

XA interface, 4-55

Y variable in SQLDA

Z variable in SQLDA