Index

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

C++, 1-11, 7-1

cache, 8-4

CACHE FREE ALL SQL command, 8-7

CASE OTT parameter, 16-30

case sensitivity

in precompiler options, 9-5

CHAR datatype, 3-25

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

character data, 3-50

character strings

multibyte, 4-7

semantics of, 3-9

CHARF datatype, 3-26, 3-61

CHARZ datatype, 3-25

child cursor, 6-20

CLOSE command

examples, F-12

CLOSE SQL command, F-11

CLOSE statement, 5-15

dependence on precompiler options, 5-14

example of, 5-14

purpose of, 5-11, 5-14

use in dynamic SQL method 4, 14-37

closing

cursors, F-11

CODE

precompiler option, 7-4

CODE OTT parameter, 16-28

code page, 4-3

CODE precompiler option, 9-11

coding conventions, 3-11

collection object types

collection types

using, 8-34

column list

in INSERT statements, 5-9

when permissible to omit, 5-9

COMMENT clause

of COMMIT command, F-13

Comments

restriction on, 13-31

COMMIT command, F-12

ending a transaction, F-62

examples, F-14

COMMIT SQL command, F-12

COMMIT statement, 10-4

effect of, 10-4

example of, 10-4

purpose of, 10-4

RELEASE option in, 10-4

using in a PL/SQL block, 10-14

where to place, 10-4

commits

automatic, 10-3

explicit versus implicit, 10-3

function of, 10-3

committing

transactions, F-12

communicating over a network, 4-24

COMP_CHARSET precompiler option, 9-12

compiling, 9-41

specifying include file locations, 3-8

compliance

ANSI, 1-7

ISO, 1-7

NIST, 1-7

concurrency

concurrent connections, 4-23

conditional precompilation, 9-39

defining symbols, 9-40

example, 9-40

CONFIG OTT parameter, 16-29

CONFIG precompiler option, 9-13

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-26

connecting to Oracle with, 4-21

requirements for, 4-21

USING clause in, 4-26

using to enable a semantic check, D-4

connecting to Oracle, 4-21

automatic connections, 4-22

connections

default versus non-default, 4-24

Explicit connections, 4-25

implicit, 4-30

naming of, 4-25

CONTEXT ALLOCATE command, 4-44, F-16

CONTEXT ALLOCATE SQL command, F-16

context block

definition of, 15-4

CONTEXT FREE command, 4-46, F-17

CONTEXT OBJECT OPTION GET SQL command, 8-19

CONTEXT OBJECT OPTION SET SQL Command, 8-18

CONTEXT USE directive, 4-44

CONTEXT USE SQL directive, F-20

CONTINUE action

CONTINUE clause in WHENEVER statement, F-78

result of, 11-25

CONTINUE option

of WHENEVER command, F-77

CONVBUFSZ clause, 4-5

conventions

description of, ix

notation, ix

CPP_SUFFIX

precompiler option, 7-5

CPP_SUFFIX precompiler option, 9-13

CPP_SUFFIX precompiler options, 9-13

CREATE PROCEDURE statement

embedded, 6-21

creating

savepoints, F-64

curly brackets, ix

CURRENT OF clause, 12-4

example of, 5-16

mimicking with ROWID, 10-12, 12-27

purpose of, 5-16

restrictions on, 5-16

current row

definition of, 2-8

using FETCH to retrieve, 5-11

cursor control statements

example of typical sequence, 5-17

cursor operations

overview of, 5-11

cursor variables, 4-11, F-8

allocating, 4-12

declaring, 4-11

restrictions on, 4-16

use of in recursive functions, 1-13

cursors, 9-41

cv_demo.pc, 4-18

cv_demo.sql, 4-17

Data Definition Language

creating CHAR objects with DBMS=V6, 9-15

data definition statements

in transactions, 10-3

database

database link

creating synonym for, 4-31

defining, 4-31

example using, 4-31

using in INSERT command, F-47

where stored, 4-31

database types

new, 8-37

datatype codes

used in descriptors, 14-15

datatype conversions, 3-58

datatype equivalencing, 2-7, 3-58

purpose of, 2-7

datatype mappings, 16-9

datatypes

coercing NUMBER to VARCHAR2, 14-15

dealing with ORACLE internal, 14-15

internal, 3-17

internal versus external, 2-7

list of internal, 14-13

need to coerce, 14-15

Oracle, 2-7

restrictions on using, 8-40

user-defined type equivalencing, F-69

when to reset, 14-15

DATE datatype, 3-23

DBMS

precompiler option

used in application migration, 3-9

DBMS option, 3-61

DBMS precompiler option, 9-14

deadlock

effect on transactions, 10-8

how broken, 10-8

declaration

of cursors, 5-11

of host arrays, 12-2

of pointer variables, 3-41

of SQLCA, 11-17

declarative SQL statements

in transactions, 10-3

uses for, 2-3

where allowed, 2-3

DECLARE CURSOR command

examples, F-23

DECLARE CURSOR SQL directive, F-22

DECLARE CURSOR statement

AT clause in, 4-27

use in dynamic SQL method 4, 14-25

DECLARE DATABASE SQL directive, F-24

Declare Section

when MODE=ANSI, 3-62

DECLARE statement, 5-12

example of, 5-11

purpose of, 5-11

required placement of, 5-12

use in dynamic SQL method 3, 13-19

DECLARE STATEMENT command

examples, F-26

scope of, F-26

DECLARE STATEMENT SQL directive, F-25

DECLARE STATEMENT statement

AT clause in, 4-28

example of using, 13-28

using with dynamic SQL, 13-28

when required, 13-28

DECLARE TABLE command

examples, F-28

DECLARE TABLE SQL directive, F-27

DECLARE TABLE statement

need for with AT clause, 4-27

using with the SQLCHECK option, D-5

DECLARE TYPE command, F-29

DEF_SQLCODE precompiler option, 9-15

default connections, 4-24

default database, 4-24

default file name extensions, 16-38

default name mapping, 16-38

DEFINE precompiler option, 9-16

used in application migration, 3-8

DELETE CASCADE, 11-21

DELETE command, F-30

embedded SQL examples, F-33

DELETE SQL command, F-30

DELETE statement

example of, 5-10

using host arrays in, 12-13

WHERE clause in, 5-10

delimiters

C versus SQL, 3-12

DEPT table, 2-11

DESCRIBE BIND VARIABLES statement

use in dynamic SQL method 4, 14-25

DESCRIBE command

example, F-35

use with PREPARE command, F-34

DESCRIBE SELECT LIST statement

use in dynamic SQL method 4, 14-30

DESCRIBE SQL command, F-34

DESCRIBE statement

use in dynamic SQL method 4, 13-25

descriptors, 14-4

bind descriptor, 13-25

definition of, 13-25

need for, 14-4

select descriptor, 13-25

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

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

distributed processing

support for, 4-24

using SQL*Net for, 4-24

distributed transactions, F-63

DO action

of WHENEVER command, F-78

result of, 11-25

DO option

dots, ix

DTP model, 4-61

dummy host variables

placeholders, 13-3

DURATION precompiler option, 8-20, 9-18

dynamic PL/SQL

rules for, 13-29

versus dynamic SQL, 13-29

dynamic SQL

advantages and disadvantages of, 13-2

cannot use cursor variables with, 4-17

choosing the right method, 13-6

restrictions on use of datatypes, 8-40

restrictions on using datatypes in, 8-40

use of PL/SQL with, 6-28

uses for, 13-2

using the AT clause in, 4-28

when to use, 13-2

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-29

dynamic SQL method 2

commands used with, 13-5

description of, 13-13

example of, 13-15

requirements for, 13-5

use of DECLARE STATEMENT with, 13-28

use of EXECUTE with, 13-13

use of PL/SQL with, 13-30

use of PREPARE with, 13-13

dynamic SQL method 3

commands used with, 13-5

compared to method 2, 13-19

example program, 13-21

requirements for, 13-5

sequence of statements used with, 13-19

use of DECLARE STATEMENT with, 13-28

use of DECLARE with, 13-19

use of FETCH with, 13-20

use of OPEN with, 13-20

use of PL/SQL with, 13-30

use of PREPARE with, 13-19

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-29

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-6

user-defined records, 6-5

using %TYPE, 6-2

using the VARCHAR pseudotype with, 6-11

using to improve performance, C-3

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-44, F-16

CONTEXT FREE command, 4-46, F-17

DECLARE CURSOR command, F-22

DECLARE TABLE command, F-27

definition of, 2-2

DESCRIBE command, F-34

difference from interactive SQL, 2-5

ENABLE THREADS command, 4-44, F-36

EXEC SQL CACHE FREE ALL, 8-7

EXECUTE command, F-37, F-38

FETCH command, F-41

INSERT command, F-45

key concepts of, 2-2

mixing with host-language statements, 2-5

OPEN command, F-58

overview of, 2-2

PREPARE command, F-60

requirements for, 2-5

SAVEPOINT command, F-64

SELECT command, F-66

syntax for, 2-5

testing with SQL*Plus, 1-3

TYPE command, F-69

UPDATE command, F-71

using LOBs in, 4-8

using OCI types in, 8-37

using REFs in, 8-36

VAR command, F-75

when to use, 1-3

WHENEVER command, F-77

embedded SQL statements

labels for, 3-15

referencing host arrays in, 12-3

referencing host variables in, 3-31

suffixes and prefixes not allowed in, 3-11

terminator for, 3-15

use of apostrophes in, 3-12

use of quotation marks in, 3-12

embedding

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

EMP table, 2-11

ENABLE THREADS command, 4-44, F-36

ENABLE THREADS SQL extension, F-36

enabling

threads, 4-44, F-36

encoding scheme, 4-3

enqueues

locking, 10-2

entering options, 3-4, 9-8

equivalencing

host variable equivalencing, F-75

user-defined type equivalencing, F-69

equivalencing of datatypes

datatype equivalencing, 2-7

error detection

error reporting, F-78

SQLCA versus WHENEVER statement, 11-3

use of ROLLBACK statement in, 10-7

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-15

use of rows-processed count in, 11-15

use of warning flags in, 11-15

WHENEVER command, F-78

ERRORS precompiler option, 9-18

ERRTYPE

precompiler option, 9-19

ERRTYPE OTT parameter, 16-30

ERRTYPE precompiler option, 8-21, 9-19

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-8

EXEC ORACLE statement

scope of, 9-9

syntax for, 9-8

uses for, 9-9

using to enter options inline, 9-8

EXEC SQL CACHE FREE ALL command, 8-7

EXEC SQL clause

using to embed SQL, 2-5

EXEC SQL INCLUDE

contrasted with #include, 3-8

EXEC SQL VAR statement

CONVBUFSZ clause, 4-5

EXEC TOOLS

GET CONTEXT statement, 15-16

GET statement, 15-15

MESSAGE statement, 15-17

SET CONTEXT statement, 15-16

SET statement, 15-15

EXEC TOOLS statements, 15-14

executable SQL statements

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

EXECUTE command

examples, F-38, F-39

EXECUTE IMMEDIATE command

examples, F-41

EXECUTE IMMEDIATE SQL command, F-40

EXECUTE IMMEDIATE statement

AT clause in, 4-28

use in dynamic SQL method 1, 13-9

EXECUTE optional keyword of ARRAYLEN statement, 6-18

EXECUTE SQL command, F-38

EXECUTE statement

use in dynamic SQL method 2, 13-13

execution of statements, 13-4

execution plan, C-5, C-6

EXPLAIN PLAN statement

function of, C-6

used after OPEN command, F-59

explicit connections, 4-25

description of, 4-25

multiple, 4-29

single, 4-25

extensions

default file name, 16-38

external datatypes

definition of, 2-7

FLOAT, 3-21

INTEGER, 3-21

STRING, 3-21

F variable in SQLDA

how value is set, 14-10

purpose of, 14-10

FETCH command

examples, F-43

FETCH SQL command, F-41

FETCH statement

example of, 5-13

INTO clause in, 5-13

purpose of, 5-11, 5-13

results of, 5-13

use in dynamic SQL method 3, 13-20

use in dynamic SQL method 4, 14-35

fetching

rows from cursors, F-41

fetching in batches

batch fetch, 12-6

FIPS flagger

warns of array usage, 12-4

warns of missing Declare Section, 3-27

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

FIPS precompiler option, 9-19

flags

warning flags, 11-15

FLOAT datatype, 3-21

FOR clause

example of using, 12-14

of embedded SQL EXECUTE command, F-39

of embedded SQL INSERT command, F-46

purpose of, 12-14

requirements for, 12-15

restrictions on, 12-15

using in dynamic SQL method 4, 14-37

using with host arrays, 12-14

when variable negative or zero, 12-15

FOR UPDATE OF clause

locking rows with, 10-10

purpose of, 10-10

when to use, 10-10

FORCE clause

of COMMIT command, F-14

of ROLLBACK command, F-62

forward references

why not allowed, 5-12

FREE SQL command, 8-6, F-44

free() function, 14-37

example of using, 14-37

freeing

thread context, 4-46, F-17

full scan

description of, C-6

function prototype

definition of, 9-11

functions

cannot serve as host variables, 3-32

GENXTB form

how to run, 15-12

use with user exits, 15-12

GENXTB utility

how to run, 15-12

use with user exits, 15-12

GOTO action

in the WHENEVER statement, 11-26

result of, 11-26

GOTO option

of WHENEVER command, F-77

guidelines

for dynamic SQL, 13-6

for separate precompilations, 9-40

for the WHENEVER statement, 11-29

for transactions, 10-13

for user exits, 15-13

heap

definition of, 11-38

HFILE OTT parameter, 16-29

hints

COST, C-5

for the ORACLE SQL statement optimizer, 5-15

in DELETE statements, F-33

in SELECT statements, F-69

in UPDATE statements, F-74

HOLD_CURSOR

precompiler option

used to improved performance, C-11

what it affects, C-7

HOLD_CURSOR option

of ORACLE Precompilers, F-12

HOLD_CURSOR precompiler option, 9-20

host arrays

host language

definition of, 2-2, 2-3

host program

host structures

assigning values to, 2-6

declarations, 8-34

declaring, 8-34

definition of, 2-6

dummy, 13-3

host variable equivalencing, F-75

in EXECUTE command, F-39

in OPEN command, F-58

in user exits, 15-4

input versus output, 5-2

must resolve to an address, 3-32

overview of, 2-6

purpose of, 5-2

requirements for, 2-6

restrictions on, 3-32

rules for naming, 3-13

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-10

purpose of, 14-10

IAF GET statement

example of using, 15-5

in user exits, 15-4

purpose of, 15-4

specifying block and field names in, 15-5

syntax for, 15-4

IAF PUT statement

example of using, 15-6

in user exits, 15-5

purpose of, 15-5

specifying block and field names in, 15-6

IAP in SQL*Forms

identifiers, ORACLE

implicit connections, 4-30

multiple, 4-31

single, 4-31

IN OUT parameter mode, 6-3

IN parameter mode, 6-3

INAME

precompiler option, 9-21

INAME precompiler option, 9-21

INCLUDE

precompiler option, use of, 3-8

using to include the SQLCA, 11-17

INCLUDE precompiler option, 9-22

indexes

in-doubt transaction, 10-13

indicator arrays, 12-4

example of using, 12-4

uses for, 12-4

INDICATOR keyword, 3-32

indicator variables

INITFILE OTT parameter, 16-28

INITFUNC OTT parameter, 16-29

initialization function

calling, 16-20

tasks of, 16-22

input host variables

assigning values to, 5-3

INSERT command

embedded SQL examples, F-48

insert of no rows

cause of, 11-20

INSERT SQL command, F-45

INSERT statement

requirements for, 5-9

using host arrays in, 12-11

VALUES clause in, 5-9

inserting

rows into tables and views, F-45

INTEGER datatype, 3-21

interface

native, 4-61

XA, 4-61

internal datatypes

definition of, 2-7

INTO clause

for output host variables, 5-2

in FETCH statements, 5-13

in INSERT statements, 5-9

in SELECT statements, 5-8

of FETCH command, F-42

of SELECT command, F-68

used with FETCH instead of SELECT, 5-12

intype file, 16-32

providing when running OTT, 16-8

structure of, 16-32

INTYPE OTT parameter, 16-27

INTYPE precompiler option, 9-23

invalid use

of precompiler preprocessor, 3-6

ISO

compliance, 1-7

joins

restriction on, 5-16

L variable in SQLDA

how value is set, 14-8

purpose of, 14-8

label name

maximum length of, 3-15

large objects (LOBs)

declaring, 4-8

handling, 4-8

locators for columns in relational tables, 4-8

operations in embedded PL/SQL, 4-9

operations in OCI, 4-9

using in embedded SQL, 4-8

LINES

precompiler option, 9-24

LINES precompiler option, 9-24

link

LNAME precompiler option, 9-24

LNPROC

VMS link script, 1-13

LOB Datatypes, sample program, 4-9

LOBs

declaring, 4-8

handling, 4-8

locators for columns in relational tables, 4-8

operations in embedded PL/SQL, 4-9

operations in OCI, 4-9

using in embedded SQL, 4-8

location transparency

how provided, 4-31

locators

for LOB columns, 4-8

lock

released by ROLLBACK statement, F-63

LOCK TABLE statement

example of, 10-11

locking tables with, 10-11

NOWAIT parameter in, 10-11

purpose of, 10-11

specifying lock mode in, 10-11

locking, 10-10

explicit versus implicit, 10-10

modes of, 10-2

overriding default, 10-10

privileges needed to obtain, 10-13

table versus row, 10-10

uses for, 10-10

with FOR UPDATE OF, 10-10

with the LOCK TABLE statement, 10-11

logging on, 4-21

LONG datatype, 3-22

LONG RAW datatype, 3-24

LONG VARCHAR

datatype, 3-25

LONG VARRAW datatype, 3-25

LTYPE precompiler option, 9-25

lvalue, 3-27

M variable in SQLDA

MAXLITERAL precompiler option, 9-26

malloc()

example of using, 14-32

purpose of, 14-32

MAXLITERAL

default value for, 3-14

precompiler option, 9-26

MAXOPENCURSORS

precompiler option

effect on performance, C-10

for multiple cursors, 5-12

specifying for separate precompilation, 9-41

what it affects, C-7

MAXOPENCURSORS precompiler option, 9-26

migration

error message codes, 3-10

include files, 3-10

indicator variables, 3-10

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

MLSLABEL datatype, 3-26

MODE

precompiler option, 3-9

effect on OPEN, 5-13

MODE precompiler option, 9-27

modes, parameter, 6-3

multi-threaded applications, 4-37

, 4-41, 4-47

sample program, 4-48

user-interface features

embedded SQL statements and directives, 4-44

N variable in SQLDA

how value is set, 14-7

purpose of, 14-7

namespaces

reserved by Oracle, B-8

naming

of cursors, 5-12

of database objects, F-8

of select-list items, 14-4

of SQL*Forms user exits, 15-13

national language support (NLS), 4-2

NATIVE

NESTED TABLE, collection type, 8-34

native interface, 4-61

nested tables, 8-2

network

communicating over, 4-24

protocols, 4-24

reducing traffic on, C-4

NIST

compliance, 1-7

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-24

definition of, 4-24

NOT FOUND condition

of WHENEVER command, F-77

meaning of, 11-25

notation

conventions, ix

rules for, ix

NOWAIT parameter

effect of, 10-11

in LOCK TABLE statements, 10-11

omitting, 10-11

nulls

definition of, 2-6

detecting, 5-3

handling in dynamic SQL method 4, 14-17

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

null-terminated string, 3-22

NUMBER datatype, 3-20

using the sqlprc() function with, 14-16

numeric expressions

cannot serve as host variables, 3-32

object cache, 8-4

OBJECT CREATE SQL command, 8-10, F-48

OBJECT DELETE SQL command, 8-12, F-50

OBJECT DEREF SQL command, 8-11, F-51

OBJECT FLUSH SQL command, 8-12, F-52

OBJECT GET SQL command, 8-17, F-53

OBJECT RELEASE SQL command, F-54

OBJECT SET SQL Command, 8-15

OBJECT SET SQL command, F-55

object support, 8-1

Object Type Translator

datatype mappings, 16-9

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

Object Type Translator (OTT), A-3

command line, 16-6

command line syntax, 16-26

creating types in the database, 16-5

outtype file, 16-16

parameters, 16-27 to 16-31

providing an intype file, 16-8

reference, 16-25

restrictions, 16-39

using, 16-1, 16-2

OBJECT UPDATE SQL command, 8-12, F-57

objects

accessing with OCI, 16-19

introduction to, 8-2

large, declaring, 4-8

large, handling, 4-8

manipulating with OCI, 16-19

persistent, 8-5

persistent versus transient copies of, 8-5

references to, 8-3

transient, 8-5

types, 8-2

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

OBJECTS precompiler option, 8-21, 9-29

OCI applications

using the OTT with, 16-18

OCI calls, 1-10

embedding, 4-34

in an X/A environment, 4-62

OCI onblon() call

not used to connect, 4-34

OCI orlon() call

not used to connect, 4-34

OCI Release 8, 4-55

accessing and manipulating objects, 16-19

interfacing to, 4-56

parameters in the environment handle, 4-56

OCI types

using in embedded SQL, 8-37

ONAME option

usage notes for, 9-30

ONAME precompiler option, 9-30

open

a cursor variable, 4-12

OPEN command

examples, F-59

OPEN SQL command, F-58

OPEN statement, 5-13

dependence on precompiler options, 5-13

effect of, 5-13

example of, 5-12

purpose of, 5-11, 5-12

use in dynamic SQL method 3, 13-20

use in dynamic SQL method 4, 14-30

OPEN_CURSORS parameter, 6-20

opening

cursors, F-58

operators

C versus SQL, 3-14

restrictions on, 3-14

optimization approach, C-5

optimizer hints, C-5

in C, 5-15

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

ORACA, 11-3

example of using, 11-41

precompiler option, 9-30

using to gather cursor cache statistics, 11-40

ORACA option

usage notes for, 9-31

ORACA precompiler option, 9-30

ORACAID component, 11-38

Oracle

datatypes, 2-7

Forms Version 4, 15-14

Open Gateway

using the ROWID datatype with, 3-23

precompilers

definition of, 1-2

function of, 1-2

Toolset, 15-14

Oracle Call Interface, 4-34

Oracle Communications Area, 11-35

ORACLE identifiers

how to form, F-8

Oracle namespaces, B-8

OTT parameters

CASE, 16-30

CODE, 16-28

where they appear, 16-31

OTT. See Object Type Translator

OUT parameter mode, 6-3

output host variables

assigning values to, 5-2

definition of, 5-2

outtype file, 16-32

when running OTT, 16-16

OUTTYPE OTT parameter, 16-28

overhead

reducing, C-2

parameter modes, 6-3

parent cursor, 6-20

PARSE

precompiler option, 7-4

parse

definition of, 13-4

parse error offset

how to interpret, 11-15

use in error reporting, 11-15

PARSE precompiler option, 9-31

parsing dynamic statements

PREPARE command, F-60

password

defining, 4-21

passwords

changing at runtime, 4-32, 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-3

using HOLD_CURSOR to improve, C-11

using host arrays to improve, C-3

using indexes to improve, C-6

using RELEASE_CURSOR to improve, C-11

using row-level locking to improve, C-6

persistent copies of objects, 8-5

persistent objects, 8-5

PL/SQL, 1-4

anonymous block

used to open a cursor variable, 4-14

cursor FOR loop, 6-3

description of, 1-4

difference from SQL, 1-4

executing a block using the AT clause, 4-27

integration with Server, 6-2

main advantage of, 1-4

packages, 6-4

PL/SQL tables, 6-5

procedures and functions, 6-3

RECORD type

cannot be bound to a C struct, 3-36

relationship with SQL, 1-4

setting SQLCA, 11-23

user-defined records, 6-5

PL/SQL blocks

embedded in Oracle7 precompiler programs, F-37

placeholders

duplicate, 13-14, 13-30

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-12

pointer variables

declaring, 3-41

determining size of referenced value, 3-41

referencing, 3-41

referencing struct members with, 3-42

precedence of precompiler options, 9-3

precision

definition of, 14-16

using sqlprc() to extract, 14-16

when not specified, 14-16

precompilation, 9-5

conditional, 9-39

precompilation unit, 4-21, 9-6

precompiler

Oracle precompilers, 1-2

precompiler directives

CONTEXT USE, 4-44

precompiler option

DURATION, 9-18

precompiler option VERSION, 9-38

precompiler options

precompiler options, scope of, 9-6

preface

Send Us Your Comments, iii

PREPARE command

examples, F-61

PREPARE SQL command, F-60

PREPARE statement

effect on data definition statements, 13-5

use in dynamic SQL, 13-13, 13-19

use in dynamic SQL method 4, 14-25

preprocessor directives

directives not supported by Pro*C, 3-3

Preprocessor, support of, 3-2

private SQL area

association with cursors, 2-8

Pro*C Precompiler

use of PL/SQL with, 6-6

Pro*C/C++ Precompiler

new database types, 8-37

new features, A-1 to A-4

object support in, 8-1

runtime context, 4-55

using OTT with, 16-22

Pro*C/C++ Precompiler Release 2

migration from, A-3

procedural database extension, 6-4

Program Global Area (PGA), 6-19

program termination

normal versus abnormal, 10-9

programming guidelines, 3-11

queries

association with cursors, 5-11

forwarding, 4-31

incorrectly coded, 5-8

kinds of, 5-7

requirements for, 5-7

returning more than one row, 5-7

single-row versus multirow, 5-8

RAW datatype, 3-24

read consistency

in SET TRANSACTION statement, 10-9

READ ONLY parameter

read-only transactions

description of, 10-9

example of, 10-9

how ended, 10-9

record, 6-5

REF

structure for, 8-35

REF (reference to object), 8-3

REFERENCE clause

in TYPE statement, 3-61

references to objects (REFs)

declaring, 8-36

using, 8-35

using in embedded SQL, 8-36

referencing

of host arrays, 12-2, 12-3

REFs

declaring, 8-36

using, 8-35

using in embedded SQL, 8-36

RELEASE option, 10-9

if omitted, 10-9

in COMMIT statement, 10-4

in ROLLBACK statement, 10-7

purpose of, 10-4

restriction on, 10-7

RELEASE_CURSOR

precompiler option

what it affects, C-7

RELEASE_CURSOR option

of ORACLE Precompilers, F-12

using to improve performance, C-11

RELEASE_CURSOR precompiler option, 9-32

remote database

declaration of, F-24

resource manager, 4-60

restrictions

on AT clause, 4-27

on Comments, 13-31

on CURRENT OF clause, 5-16

on declaring cursors, 5-12

on FOR clause, 12-15

on host arrays, 12-4, 12-10, 12-12, 12-14

on input host variables, 5-2

on nulls, 5-6

on separate precompilation, 9-41

on SET TRANSACTION statement, 10-9

use of CURRENT OF clause, 12-4

retrieving rows from a table

embedded SQL, F-66

return codes

user exits, 15-8

roll back

to a savepoint, F-64

to the same savepoint multiple times, F-63

ROLLBACK command

ending a transaction, F-62

examples, F-63

rollback segment

function of, 10-2

ROLLBACK SQL command, F-61

ROLLBACK statement, 10-8

effect of, 10-7

example of, 10-7

in error handling routines, 10-7

purpose of, 10-6

RELEASE option in, 10-7

TO SAVEPOINT clause in, 10-6

using in a PL/SQL block, 10-14

where to place, 10-7

rollbacks

automatic, 10-8

function of, 10-3

statement-level, 10-8

rolling back

transactions, F-61

row locks

acquiring with FOR UPDATE OF, 10-10

advantage of, C-6

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

when acquired, 10-11

when released, 10-11

ROWID

pseudocolumn, 10-12

rows

fetching from cursors, F-41

inserting into tables and views, F-45

updating, F-71

rows-processed count

in the SQLCA, 11-21

use in error reporting, 11-15

runtime context

establishing, 4-55

terminating, 4-55

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-11

EMP table, 2-11

sample object code, 8-25

sample programs

cursor variable demos, 4-17

cv_demo.pc, 4-18

cv_demo.sql, 4-17

how to precompile, 2-12

SAVEPOINT command, F-64

examples, F-65

SAVEPOINT SQL command, F-64

SAVEPOINT statement

savepoints

Scale

using SQLPRC to extract, 4-6

scale

definition of, 4-6, 14-16

using sqlprc() to extract, 14-16

when negative, 4-6, 14-16

SCHEMA_NAMES OTT parameter, 16-31

usage, 16-36

scope

of a cursor variable, 4-12

of DECLARE STATEMENT command, F-26

of precompiler options, 9-6

of the EXEC ORACLE statement, 9-9

of WHENEVER statement, 11-29

search condition

definition of, 5-10

in the WHERE clause, 5-10

SELECT command

embedded SQL examples, F-69

select descriptor, 13-25, 14-4

definition of, 13-25

information in, 13-26

select list

definition of, 5-8

number of items in, 5-8

using the free() function for, 14-37

using the malloc() function for, 14-32

SELECT SQL command, F-66

select SQLDA

purpose of, 14-3

SELECT statement, 5-8

clauses available for, 5-8

using host arrays in, 12-5

WHERE clause in, 5-8

SELECT_ERROR

precompiler option, 5-8, 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

Send Us Your Comments

boilerplate, iii

separate precompilation

guidelines for, 9-40

referencing cursors for, 9-41

restrictions on, 9-41

specifying MAXOPENCURSORS for, 9-41

using a single SQLCA with, 9-41

server

integration with PL/SQL, 6-2

session

sessions

beginning, F-14

SET clause

in UPDATE statements, 5-10

use of subqueries in, 5-10

SET TRANSACTION statement

example of, 10-9

purpose of, 10-9

READ ONLY parameter in, 10-9

requirements for, 10-9

restrictions on, 10-9

SQL

SQL cmmands

SQL command

SQL commands

SQL commands OBJECT CREATE, F-48

SQL Communications Area, 11-2

SQL Descriptor Area

SQL directives

DECLARE DATABASE, F-24

DECLARE STATEMENT, F-25

DECLARE TABLE, F-27

EXEC SQL DECLARE DATABASE, F-24

TYPE, F-69

VAR, F-75

WHENEVER, F-77

SQL extensions

ENABLE THREADS, F-36

EXECUTE ... END-EXEC, F-37

SQL statements

concerns when executing, 5-6

executable versus declarative, 2-3

for defining and controlling transactions, 10-3

for manipulating a cursor, 5-7, 5-11

for manipulating ORACLE data, 5-7

for querying Oracle data, 5-7

optimizing to improve performance, C-5

rules for executing, C-5

types of, 2-3

SQL*Forms

display error screen in, 15-8

IAP constants in, 15-8

returning values to, 15-8

reverse return code switch in, 15-8

SQL*Net

connecting to Oracle via, 4-24

connecting using Version 2, 4-21

connection syntax, 4-24

for concurrent connections, 4-23

function of, 4-24

SQL*Plus

using to test SELECT statements, 5-9

versus embedded SQL, 1-3

SQL_CURSOR, F-9

SQL_SINGLE_RCTX defined constant, 4-35

sqlald() function

example of using, 14-21

purpose of, 14-5

syntax for, 14-5

sqlaldt() function, 4-36

SQLCA, 11-2, 11-15

components in, 11-19

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

declaring, 11-17

description of, 11-16

explicit versus implicit checking of, 11-3

including multiple times, 3-7

overview of, 2-9

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-21

sqlerrml component in, 11-20

sqlwarn, 11-22

use in separate precompilations, 9-41

using more than one, 11-16

using with SQL*Net, 11-16

sqlca.h

listing of, 11-18

use of SQLCA_STORAGE_CLASS with, 9-41

SQLCAID component, 11-20

sqlcdat() function, 4-36

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-22

SQLCHECK, precompiler option, 8-22

sqlclu() function

example of using, 14-37

purpose of, 14-37

syntax for, 14-37

sqlclut() function, 4-36

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-14

when used, 11-14

sqlcpr.h, 11-23

sqlcurt() function, 4-36

SQLDA

bind versus select, 13-26

information stored in, 13-26

struct, contents of, 14-5

structure of, 14-7

T variable in, 14-9

use in dynamic SQL method 4, 14-4

sqlerrd

component, 11-15, 11-21

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

returns N or rows fetched, 12-7

use with data manipulation statements, 12-28

sqlerrm

component in the SQLCA, 11-3

sqlerrmc component, 11-21

sqlerrml component, 11-20

SQLERROR

WHENEVER command condition, F-77

SQLERROR condition

sample program for, 11-34

meaning of, 11-25

sqlglm(), 11-23

sqlglm() function, 11-23

example of using, 11-24

parameters of, 11-23

sqlglmt() function, 4-36

sqlgls() function, 11-32

example of use, 3-47

sqlglst() function, 4-37

SQLIEM function

in user exits, 15-8

purpose of, 15-8

syntax for, 15-8

sqlld2() function, 4-62

sqlld2t() function, 4-37

sqllda() function, 4-34

sqlldat() function, 4-37

SQLLIB, 3-46

extensions for OCI interoperability, 4-55

new names for functions, A-3

sqlnul() function

example of using, 14-18

purpose of, 14-18

syntax for, 14-18

use of with T variable, 14-9

sqlnult() function, 4-37

sqlpr2() function, 14-17

sqlpr2t() function, 4-37

sqlprc() function, 14-16

sqlprct() function, 4-37

SQLSTATE, 9-28

class codes, 11-4

declaring, 11-4

mapping to Oracle errors, 11-7

predefined classes, 11-5

status codes, 11-7

status variable, 11-2, 11-3

using, 11-13

values, 11-4

sqlvcp() function, 3-46

sqlvcpt() function, 4-37

sqlwarn

flag, 11-22

SQLWARNING

WHENEVER command condition, F-77

SQLWARNING condition

in the WHENEVER statement, 11-26

meaning of, 11-25

statement-level rollback

description of, 10-8

to break deadlocks, 10-8

status codes

meaning of, 11-15

status variables, 11-2

STOP action

result of, 11-26

STOP option

of WHENEVER command, F-78

stored procedures

program example, 6-24

stored subprograms

calling, 6-23

creating, 6-21

packaged versus stand-alone, 6-21

stored versus inline, 6-21

STRING datatype, 3-21

string host variables

declaring, 3-56

struct pointers as host variables, 3-42

structs

array of, 12-17, A-2

as host variables, 3-34

C, using, 8-33

for collection object types, 8-34

generating C structs for a REF, 8-35

structures

cannot be nested, 3-37

nested structs not permitted for host structures, 3-37

subqueries

definition of, 5-9

example of, 5-9, 5-10

uses for, 5-9

using in the SET clause, 5-10

using in the VALUES clause, 5-9

syntax checking

controlling with the SQLCHECK option, D-2

syntax diagram

syntax, embedded SQL, 2-5

SYS_INCLUDE

, 7-5

SYS_INCLUDE precompiler option, 9-35

system configuration file, 9-3

system failure

effect on transactions, 10-3

System Global Area (SGA), 6-21

system header files

specifying the location of, 7-5

system-specific Oracle documentation, vi, 1-13, 3-4, 3-10, 4-24, 4-62, 9-42, 15-1

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

T variable in SQLDA

how value is set, 14-9

purpose of, 14-9

table locks

acquiring with LOCK TABLE, 10-11

tables

inserting rows into, F-45

nested, 8-2

updating rows in, F-71

terminal

encoding scheme, 4-3

termination, program

normal versus abnormal, 10-9

thread, F-16, F-17, F-20, F-36

THREADS, 4-44

threads

allocating context, 4-44, F-16

enabling, 4-44, F-36

freeing context, 4-46, F-17

use context, 4-44, F-20

THREADS precompiler option, 9-36

TO clause

of ROLLBACK command, F-62

TO SAVEPOINT clause

in ROLLBACK statement, 10-6

Toolset

trace facility

transaction processing monitor, 4-60

transaction processing

overview of, 2-8

statements used for, 2-8

transactions

transient copies of objects, 8-5

transient objects, 8-5

truncated values

detecting, 5-3, 6-14

truncation error

when generated, 5-6

tuning, performance, C-2

two-task

linking, 9-41

type checking at runtime, 8-22

TYPE command

examples, F-70

TYPE SQL directive, F-69

unconditional deletion

definition of, 11-22

undo a transaction, F-61

unions

cannot be nested in host structures, 3-37

not permitted as host structures, 3-37

UNIX

linking a Pro*C application under, 1-13

UNSAFE_NULL precompiler option, 9-37

UNSIGNED datatype, 3-24

UPDATE CASCADE, 11-21

UPDATE command

embedded SQL examples, F-74

UPDATE SQL command, F-71

UPDATE statement

example of, 5-10

using host arrays in, 12-12

SET clause in, 5-10

WHERE clause in, 5-10

updating

rows in tables and views, F-71

use

thread context, 4-44, F-20

user configuration file, 9-3

user exits

calling from a SQL*Forms trigger, 15-6

common uses for, 15-3

example of, 15-9

guidelines for, 15-13

kinds of statements allowed in, 15-4

linking into IAP, 15-13

meaning of codes returned by, 15-8

naming, 15-13

passing parameters to, 15-7

requirements for variables in, 15-4

running the GENXTB form, 15-12

running the GENXTB utility for, 15-12

steps in developing, 15-3

use of IAF GET statements in, 15-5

use of IAF PUT statements in, 15-6

use of WHENEVER statement in, 15-9

user session

used in WHERE clause, 5-10

user-defined record, 6-5

user-defined stored function

user-defined type equivalencing, F-69

USERID

precompiler option, 9-37

USERID option

using with the SQLCHECK option, D-4

when required, 9-38

USERID OTT parameter, 16-27

USERID precompiler option, 9-37

username

defining, 4-21

using C structures, 8-33

USING clause

in CONNECT statement, 4-26

in the EXECUTE statement, 13-14

of FETCH command, F-42

of OPEN command, F-58

purpose of, 13-14

using indicator variables in, 13-14

using collection types, 8-34

using dbstring

SQL*Net database id specification, F-15

Using REFs in Embedded SQL, 8-36

V variable in SQLDA

how value is set, 14-8

purpose of, 14-8

V6

V6_CHAR

V7

kinds of values allowed in, 5-9

VALUES clause

in INSERT statements, 5-9

of embedded SQL INSERT command, F-47

of INSERT command, F-47

purpose of, 5-9

requirements for, 5-9

use of subqueries in, 5-9

VAR command

examples, F-76

VAR SQL directive, F-75

VAR statement

syntax for, 3-59, 3-60

VARCHAR

arrays of, 12-2

VARCHAR datatype, 3-22

VARCHAR precompiler option, 9-38

VARCHAR pseudotype

requirements for using with PL/SQL, 6-11

VARCHAR variables

advantages of, 3-43

declaring, 3-43

length member in, 3-44

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

specifying the length of, 3-44

structure of, 3-43

using macros to define length of, 3-2

versus character arrays, 3-58

VARCHAR2 datatype, 3-20, 3-61

variables, 2-6

cursor, 4-11

host, 8-34

indicator, 8-34

VARNUM datatype, 3-22

VARRAW datatype, 3-24

VARRAY, collection type, 8-34

varying length arrays, 8-3

VERSION precompiler option, 8-20, 9-38

vertical bar, ix

views

inserting rows into, F-45

updating rows in, F-71

VMS

linking a precompiler application, 1-13

warning flags

use in error reporting, 11-15

WHENEVER command

examples, F-78

WHENEVER SQL directive, F-77

WHENEVER staement

DO BREAK action in, 11-25

WHENEVER statement

automatic checking of SQLCA with, 11-24

CONTINUE action in, 11-25

DO action in, 11-25

DO CONTINUE action in, 11-26

examples of, 11-26

GOTO action in, 11-26

guidelines for, 11-29

maintaining addressability for, 11-31

NOT FOUND condition in, 11-25

overview of, 2-9

scope of, 11-29

SQLERROR condition in, 11-25

SQLWARNING condition in, 11-25

STOP action in, 11-26

use in user exits, 15-9

using to avoid infinite loops, 11-30

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

where to place, 11-29

WHERE clause

host arrays in, 12-16

if omitted, 5-11

in DELETE statements, 5-10

in SELECT statements, 5-8

in UPDATE statements, 5-10

of DELETE command, F-32

of UPDATE command, F-73

search condition in, 5-10

WHERE CURRENT OF clause

CURRENT OF clause, 5-16

WORK option

of COMMIT command, F-13

of ROLLBACK command, F-62

X variable in SQLDA

application development, 4-60

X/Open, 4-61

XA interface, 4-61

Y variable in SQLDA