Programmer's Guide to the Oracle Precompilers, 1.8
Library
Product
Contents
Index
Index
@
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
@
A
abnormal termination, automatic rollback
active set
changing
[
2
]
definition
when empty
ALLOCATE command
allocating, cursors
ANSI/ISO SQL
compliance
extensions
application development process
array
[
2
]
array fetch
array, elements
ARRAYLEN statement
array, operations
ASACC option
ASSUME_SQLCODE option
AT clause
CONNECT statement
DECLARE CURSOR statement
DECLARE STATEMENT statement
EXECUTE IMMEDIATE statement
of COMMIT command
of CONNECT command
of DECLARE CURSOR command
of DECLARE STATEMENT command
of EXECUTE command
of EXECUTE IMMEDIATE command
of INSERT command
of SAVEPOINT command
of SELECT command
of UPDATE command
restrictions
AUTO_CONNECT option
instead of CONNECT statement
automatic logon
[
2
]
B
batch fetch
example
number of rows returned
bind descriptor, information in
bind variable
[
2
]
binding
blank padding, in multi-byte character strings
block data subprogram, used by precompiler
C
callback, user exit
CHAR column, maximum width
CHAR datatype
external
internal
character sets, multi-byte
character strings, multibyte
CHARF datatype specifier
using in TYPE statement
using in VAR statement
CHARF datatype, external
CHARZ datatype
child cursor
CLOSE command
examples
CLOSE statement
[
2
]
example
closing, cursors
code page
column list
column, ROWLABEL
comment
COMMENT clause, of COMMIT command
commit
COMMIT command
ending a transaction
examples
COMMIT statement
effects
example
RELEASE option
using in a PL/SQL block
where to place
commit
automatic
explicit versus implicit
committing, transactions
COMMON_NAME option
communicating over a network
compilation
compliance, ANSI/ISO
concurrency
concurrent logon
conditional precompilation
defining symbols
example
CONFIG option
[
2
]
configuration file
advantages
system versus user
[
2
]
CONNECT command
examples
CONNECT statement
AT clause
enabling a semantic check
requirements
USING clause
using to log on
when not required
connecting to Oracle
automatically
concurrently
example
via SQL*Net
connection
concurrent
default versus non-default
implicit
naming
CONTINUE action
CONTINUE option, of WHENEVER command
conventions
description
notation
CREATE PROCEDURE statement
creating, savepoints
CURRENT OF clause
example
mimicking with ROWID
[
2
]
of embedded SQL DELETE command
of embedded SQL UPDATE command
restrictions
current row
CURRVAL pseudocolumn
cursor
cursor cache
[
2
]
gathering statistics about
purpose
cursor variable
[
2
]
closing
fetching from
opening
[
2
]
cursor
analogy
association with query
child
declaring
effects on performance
explicit versus implicit
naming
parent
reopening
[
2
]
restricted scope of
restrictions
cursors
allocating
closing
cursor, scope
cursors
fetching rows from
opening
cursor
using for multirow queries
using more than one
when closed automatically
D
data definition danguage (DDL)
creating CHAR objects with DBMS=V6
description
data integrity
data lock
Data Manipulation Language (DML)
database link
creating a synonym
defining
database links
using in DELETE command
using in INSERT command
using in UPDATE command
datatype conversion
between internal and external types
supported
datatype equivalencing
[
2
]
advantages
example
guidelines
datatype
host-language
internal versus external
user-defined
DATE datatype
converting
default format
default value
external
internal
internal format
DBMS option
using to migrate applications
DDL (data definition language)
deadlock
effect on transactions
how broken
DECIMAL datatype
declaration
cursor
host array
host variable
indicator variable
of ORACA
SQLCA
declarative SQL statement
using in transactions
DECLARE CURSOR command
examples
DECLARE CURSOR statement, AT clause
DECLARE DATABASE directive
Declare Section
defining username and password
example
using more than one
DECLARE statement
DECLARE STATEMENT command
examples
scope of
DECLARE STATEMENT statement
AT clause
example
using in dynamic SQL
when required
DECLARE statement
example
using in dynamic SQL Method 3
where to place
DECLARE TABLE command
examples
DECLARE TABLE statement
need for with AT clause
using with the SQLCHECK option
default connection
default database
default, setting of LITDELIM option
DEFINE option
delete cascade
DELETE command
embedded SQL examples
DELETE statement
example
using host arrays
using the SQLERRD(3) field
WHERE clause
DEPT table
DESCRIBE command
example
use with PREPARE command
DESCRIBE statement, using in dynamic SQL Method 4
descriptor
naming
directory
current
path for INCLUDE files
DISPLAY datatype
distributed processing
distributed transactions
DML (Data Manipulation Language)
DO action
DO option, of WHENEVER command
DTP model
dummy host variable
dynamic PL/SQL
dynamic SQL Method 1
commands
description
example
requirements
using EXECUTE IMMEDIATE
using PL/SQL
dynamic SQL Method 2
commands
description
example
requirements
using PL/SQL
using the DECLARE STATEMENT statement
using the EXECUTE statement
using the PREPARE statement
dynamic SQL Method 3
commands
compared to Method 2
example
requirements
using PL/SQL
using the DECLARE statement
using the DECLARE STATEMENT statement
using the FETCH statement
using the OPEN statement
using the PREPARE statement
dynamic SQL Method 4
overview
requirements
using descriptors
using PL/SQL
using the DECLARE STATEMENT statement
using the DESCRIBE statement
using the FOR clause
using the SQLDA
when needed
dynamic SQL statement
binding of host variables
how processed
requirements
using host arrays
using placeholders
versus static SQL statement
dynamic SQL
advantages and disadvantages
choosing the right method
guidelines
multi-byte character data restrictions
overview
[
2
]
using PL/SQL
using the AT clause
when useful
E
ellipsis
embedded SQL
DECLARE CURSOR command
DECLARE TABLE command
EXECUTE command
embedded PL/SQL
advantages
cursor FOR loop
example
[
2
]
need for SQLCHECK option
need for USERID option
overview
package
PL/SQL table
requirements
subprogram
support for SQL
user-defined record
using %TYPE
using the VARCHAR pseudotype
using to improve performance
where allowed
embedded SQL statement
mixing with host-language statements
referencing host variables
referencing indicator variables
syntax
embedded SQL
ALLOCATE command
CLOSE command
COMMIT command
CONNECT command
DECLARE STATEMENT command
DELETE command
DESCRIBE command
EXECUTE command
EXECUTE IMMEDIATE command
FETCH command
INSERT command
key concepts
OPEN command
PREPARE command
SAVEPOINT command
SELECT command
UPDATE command
VAR command
versus interactive SQL
when to use
WHENEVER command
embedding, PL/SQL blocks in Oracle7 precompiler programs
EMP table
encoding scheme
Entry SQL
equivalencing, datatype
equivalencing, host variable equivalencing
error detection, error reporting
error handling
alternatives
benefits
overview
SQLCA versus WHENEVER statement
SQLCODE status variable
SQLSTATE status variable
using the ORACA structure
using the ROLLBACK statement
using the SQLCA structure
using the SQLCODE status variable
using the SQLGLM function
using the SQLGLS function
using the WHENEVER statement
error message
available in SQLCA
maximum length
using in error reporting
using the SQLGLM function
error reporting
key components
using error messages
using status codes
using the parse error offset
using the rows-processed count
using warning flags
WHENEVER command
ERRORS option
exception, PL/SQL
EXEC ORACLE DEFINE statement
EXEC ORACLE ELSE statement
EXEC ORACLE ENDIF statement
EXEC ORACLE IFDEF statement
EXEC ORACLE IFNDEF statement
EXEC ORACLE statement
scope of
syntax for
uses for
using to enter options inline
EXEC SQL clause
EXEC TOOLS statement
GET
GET CONTEXT
MESSAGE
SET
SET CONTEXT
executable SQL statement
EXECUTE command
[
2
]
examples
[
2
]
EXECUTE IMMEDIATE command
examples
EXECUTE IMMEDIATE statement
AT clause
using in dynamic SQL Method 1
EXECUTE statement, using in dynamic SQL Method 2
execution plan
EXPLAIN PLAN statement, using to improve performance
explicit logon
multiple
single
external datatype
CHAR
CHARF
CHARZ
DATE
DECIMAL
definition
description
DISPLAY
FLOAT
INTEGER
LONG
LONG RAW
LONG VARCHAR
LONG VARRAW
MLSLABEL
NUMBER
parameters
RAW
ROWID
STRING
UNSIGNED
VARCHAR
VARCHAR2
VARNUM
VARRAW
F
features, new
FETCH command
examples
used after OPEN command
FETCH statement
[
2
] [
3
]
example
INTO clause
using in dynamic SQL Method 3
using the SQLERRD(3) field
fetch, batch
fetching, rows from cursors
file extension
FIPS option
flag, warning
FLOAT datatype
FOR clause
example
of embedded SQL EXECUTE command
of embedded SQL INSERT command
restrictions
using with host arrays
FOR UPDATE clause
FOR UPDATE OF clause
FORCE clause
of COMMIT command
of ROLLBACK command
format mask
FORMAT option
forward reference
full scan
G
GENXTB form, running
GOTO action
GOTO option, of WHENEVER command
guidelines
datatype equivalencing
dynamic SQL
host variable
indicator variable
separate precompilation
transaction
user exit
WHENEVER statement
H
heap
hint, optimizer
hints
in DELETE statements
in SELECT statements
in UPDATE statements
HOLD_CURSOR option
of ORACLE Precompilers
using to improve performance
what it affects
host program
host array
advantages
declaring
dimensioning
maximum size
referencing
restrictions
[
2
] [
3
] [
4
]
using in dynamic SQL statements
using in the DELETE statement
using in the INSERT statement
using in the SELECT statement
using in the UPDATE statement
using in the WHERE clause
using the FOR clause
using to improve performance
when not allowed
host language
HOST option
host variable
assigning a value
declaring
dummy
guidelines
input versus output
naming
overview
referencing
requirements
host variables
host variable equivalencing
in EXECUTE command
in OPEN command
multi-byte character strings
host variable
undeclared
using in EXEC TOOLS statements
using in PL/SQL
using in user exit
where allowed
host-language datatype
I
IAF GET statement
example
specifying block and field names
using in user exit
IAF PUT statement
example
specifying block and field names
using in user exit
IAP
identifiers, ORACLE, how to form
implicit logon
multiple
single
IN OUT parameter mode
IN parameter mode
in-doubt transaction
INAME option
when a file extension is required
INCLUDE file
INCLUDE option
INCLUDE statement
using to declare the ORACA
using to declare the SQLCA
index, using to improve performance
indicator array
INDICATOR keyword
indicator variable
association with host variable
declaring
guidelines
interpreting value
referencing
indicator variables, used with multi-byte character strings
indicator variable
using in PL/SQL
using to detect truncated values
using to handle nulls
[
2
] [
3
]
using to test for nulls
input host variable
restrictions
where allowed
INSERT command
embedded SQL examples
insert of no rows
cause of
INSERT statement
column list
example
INTO clause
using host arrays
using the SQLERRD(3) field
VALUES clause
inserting, rows into tables and views
INTEGER datatype
interface
native
XA
internal datatype
CHAR
DATE
definition
descriptions
LONG
LONG RAW
MLSLABEL
NUMBER
RAW
ROWID
VARCHAR2
INTO clause
[
2
]
FETCH statement
INSERT statement
of FETCH command
of SELECT command
SELECT statement
IRECLEN option
J
Julian date
K
keywords
L
language support
LDA
LEVEL pseudocolumn
link, database
linking
LITDELIM option
purpose
LNAME option
location transparency
LOCK TABLE statement
example
using the NOWAIT parameter
locking
[
2
]
explicit versus implicit
modes
overriding default
privileges needed
using the FOR UPDATE OF clause
using the LOCK TABLE statement
lock, released by ROLLBACK statement
logging on
Logon Data Area (LDA)
logon
automatic
concurrent
explicit
LONG column, maximum width
LONG datatype
compared with CHAR
external
internal
restrictions
where allowed
LONG RAW column, maximum width
LONG RAW datatype
compared with LONG
converting
external
internal
restrictions
LONG VARCHAR datatype
LONG VARRAW datatype
LRECLEN option
LTYPE option
M
MAXLITERAL option
MAXOPENCURSORS option
using for separate precompilation
what it affects
migration to Oracle7
handling character strings
handling nulls
handling truncated values
MLSLABEL datatype
external
internal
MODE option
effect on OPEN
mode, parameter
monitor, transaction processing
multi-byte character sets
MULTISUBPROG option
N
namespaces, reserved by Oracle
naming conventions
cursor
default database
host variable
SQL*Forms user exit
naming, of database objects
national language support (NLS)
native interface
network
communicating over
protocols
reducing traffic
NEXTVAL pseudocolumn
nibble
NIST, compliance
NLS (national language support)
multi-byte character strings
NLS parameter
NLS_CURRENCY
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_ISO_CURRENCY
NLS_LANG
NLS_LANGUAGE
NLS_NUMERIC_CHARACTERS
NLS_SORT
NLS_TERRITORY
NLS_LOCAL, precompiler option
node, definition of
NOT FOUND condition
of WHENEVER command
notation
conventions
rules
NOWAIT parameter
using in LOCK TABLE statement
null-terminated string
null
definition
detecting
hardcoding
inserting
restrictions
retrieving
testing for
NUMBER datatype
external
internal
O
OCI
declaring LDA
embedding calls
ONAME option
OPEN command
examples
OPEN statement
example
using in dynamic SQL Method 3
OPEN-FOR statement
[
2
]
OPEN_CURSORS parameter
opening, cursors
optimizer hint
options, precompiler
ORACA
ORACA option
ORACABC field
ORACA
declaring
enabling
example
fields
gathering cursor cache statistics
ORACAID field
ORACA
ORACABC field
ORACAID field
ORACCHF flag
ORACOC field
ORADBGF flag
ORAHCHF flag
ORAHOC field
ORAMOC field
ORANEX field
ORANOR field
ORANPR field
ORASFNMC field
ORASFNML field
ORASLNR field
ORASTXTC field
ORASTXTF flag
ORASTXTL field
using more than one
ORACCHF flag
Oracle Call Interface
ORACLE Communications Area
Oracle datatypes
Oracle Forms, using EXEC TOOLS statements
ORACLE identifiers, how to form
Oracle keywords
Oracle namespaces
Oracle Open Gateway, using ROWID datatype
Oracle Precompilers
advantages
function
language support
new features
NLS support
running
using PL/SQL
using with OCI
Oracle reserved words
Oracle Toolset
ORACOC field
ORADBGF flag
ORAHCHF flag
ORAHOC field
ORAMOC field
ORANEX field
ORANOR field
ORANPR field
ORASFNMC field
ORASFNML field
ORASLNR field
ORASTXTC field
ORASTXTF flag
ORASTXTL field
ORECLEN option
OUT parameter mode
output host variable
[
2
]
P
PAGELEN option
parameter mode
parent cursor
parse
parse error offset
interpreting
using in error reporting
parsing dynamic statements, PREPARE command
password
defining
prompting for
performance
improving
reasons for poor
PL/SQL
PL/SQL blocks, embedded in Oracle7 precompiler programs
PL/SQL reserved words
PL/SQL table
supported datatype conversions
PL/SQL
advantages
and the SQLCA
cursor FOR loop
exception
integration with server
package
relationship with SQL
subprogram
user-defined record
placeholder, duplicate
[
2
]
naming
using in dynamic SQL statements
plan, execution
precision
precompilation
[
2
]
precompilation unit
precompilation
conditional
separate
precompiler
precompiler command
optional arguments of
required arguments
precompiler directives, EXEC SQL DECLARE DATABASE
precompiler options
abbreviating name
ASACC
ASSUME_SQLCODE
AUTO_CONNECT
[
2
]
COMMON_NAME
CONFIG
[
2
]
DBMS
DEFINE
displaying
[
2
]
entering from a configuration file
entering inline
entering on the command line
ERRORS
FIPS
FORMAT
HOLD_CURSOR
HOST
INAME
INCLUDE
IRECLEN
LITDELIM
[
2
]
LNAME
LRECLEN
LTYPE
MAXLITERAL
MAXOPENCURSORS
MODE
MULTISUBPROG
NLS_LOCAL
ONAME
ORACA
ORECLEN
PAGELEN
RELEASE_CURSOR
respecifying
scope of
[
2
]
SELECT_ERROR
specifying
[
2
]
SQLCHECK
syntax for
UNSAFE_NULL
[
2
]
USERID
VARCHAR
XREF
PREPARE command
examples
PREPARE statement
effect on data definition statements
using in dynamic SQL
[
2
]
private SQL area
association with cursors
opening
purpose
Program Global Area (PGA)
program termination
programming language support
pseudocode conventions
pseudocolumn
CURRVAL
LEVEL
NEXTVAL
ROWID
ROWNUM
pseudotype, VARCHAR
Q
query
association with cursor
multirow
single-row versus multirow
R
RAW column, maximum width
RAW datatype
compared with CHAR
converting
external
internal
restrictions
RAWTOHEX function
read consistency
READ ONLY parameter, using in SET TRANSACTION
read-only transaction
ending
example
record, user-defined
reference
host array
host variable
indicator variable
RELEASE option
[
2
]
COMMIT statement
omitting
restrictions
ROLLBACK statement
RELEASE_CURSOR option
of ORACLE Precompilers
using to improve performance
what it affects
remote database, declaration of
reserved words
PL/SQL
resource manager
restrictions
AT clause
CURRENT OF clause
cursor declaration
FOR clause
host array
[
2
] [
3
] [
4
]
input host variable
LONG datatype
LONG RAW datatype
RAW datatype
RELEASE option
separate precompilation
SET TRANSACTION statement
SQLCHECK option
SQLGLS function
TO SAVEPOINT clause
retrieving rows from a table, embedded SQL
return code
roll back
to a savepoint
to the same savepoint multiple times
ROLLBACK command
ending a transaction
examples
rollback segment
ROLLBACK statement
[
2
]
effects
example
RELEASE option
TO SAVEPOINT clause
using in a PL/SQL block
using in error-handling routines
where to place
rollback
automatic
purpose
statement-level
rolling back, transactions
row lock
acquiring with FOR UPDATE OF
using to improve performance
when acquired
when released
ROWID datatype
external
internal
ROWID pseudocolumn
using to mimic CURRENT OF
[
2
]
ROWLABEL column
ROWNUM pseudocolumn
rows-processed count
using in error reporting
rows
fetching from cursors
inserting into tables and views
updating
S
sample database table
DEPT table
EMP table
savepoint
SAVEPOINT command
examples
SAVEPOINT statement
example
SAVEPOINTS parameter
savepoints, creating
savepoint, when erased
scale
definition of
using SQLPRC to extract
when negative
scope
of DECLARE STATEMENT command
of precompiler options
[
2
]
of the EXEC ORACLE statement
WHENEVER statement
search condition
using in the WHERE clause
SELECT command
embedded SQL examples
select descriptor, information in
select list
SELECT statement
available clauses
example
INTO clause
using host arrays
using the SQLERRD(3) field
SELECT_ERROR option
[
2
]
semantic checking
enabling
using the SQLCHECK option
separate precompilation
guidelines
restrictions
session
sessions, beginning
SET clause
using a subquery
SET TRANSACTION statement
example
READ ONLY parameter
restrictions
snapshot
SQL codes, returned by SQLGLS function
SQL Communications Area
[
2
]
SQL Descriptor Area
SQL standards conformance
SQL statement
controlling transactions
executable versus declarative
optimizing to improve performance
static versus dynamic
using to control a cursor
[
2
]
using to manipulate Oracle data
SQL*Connect, using ROWID datatype
SQL*Forms
Display Error screen
IAP constants
returning values to
Reverse Return Code switch
user exit
SQL*Net
concurrent logons
connecting using Version 2
connection syntax
function of
using to connect to Oracle
SQL*Plus
SQL92
conformance
deprecated feature
minimum requirement
SQL_CURSOR
SQLCA
SQLCABC field
SQLCA
components set for a PL/SQL block
declaring
explicit versus implicit checking
fields
SQLCAID field
SQLCA
interaction with Oracle
overview
restrictions on
SQLCABC field
SQLCAID field
SQLCODE field
SQLERRD(3) field
SQLERRD(5) field
SQLERRMC field
SQLERRML field
SQLWARN(2) flag
SQLWARN(4) flag
SQLWARN(5) flag
using in separate precompilations
using more than one
using with SQL*Net
SQLCHECK option
restrictions
using the DECLARE TABLE statement
using to check syntax/semantics
SQLCODE field
[
2
]
interpreting its value
SQLCODE status variable
SQLCODE variable, interpreting values of
SQLDA
[
2
]
bind versus select
information stored in
SQLERRD(3) field
[
2
]
purpose
using with batch fetch
using with the FETCH statement
SQLERRD(5) field
[
2
]
SQLERRMC field
SQLERRML field
SQLERROR condition
SQLERROR, WHENEVER command condition
SQLFC parameter
SQLGLM function
example
SQLGLS function
parameters
restrictions
SQL codes returned by
syntax
using to obtain SQL text
SQLIEM function
replacement for
using in user exit
SQLLDA routine
SQLSTATE status variable
[
2
]
class code
coding scheme
declaring
interpreting values
predefined classes
predefined status codes and conditions
subclass code
SQLSTM parameter
SQL, summary of commands
SQLWARN(2) flag
SQLWARN(4) flag
SQLWARN(5) flag
SQLWARNING condition
SQLWARNING, WHENEVER command condition
statement-level rollback
breaking deadlocks
status code
STMLEN parameter
STOP action
STOP option, of WHENEVER command
stored subprogram
calling
creating
packaged versus standalone
stored versus inline
using to improve performance
STRING datatype
string-comparison semantics
subprogram, PL/SQL
[
2
]
subquery
example
[
2
]
using in the SET clause
using in the VALUES clause
syntactic checking
syntax diagram
description of
how to read
how to use
symbols used in
syntax, embedded SQL
SYSDATE function
system failure, effect on transactions
System Global Area (SGA)
T
table lock
acquiring with LOCK TABLE
exclusive
row share
when released
tables
inserting rows into
updating rows in
TO clause, of ROLLBACK command
TO SAVEPOINT clause
restrictions
using in ROLLBACK statement
trace facility, using to improve performance
transaction
transaction processing
overview
statements used
transaction, read-only
transaction, contents
[
2
]
guidelines
how to begin
how to end
in-doubt
making permanent
transactions
committing
distributed
rolling back
[
2
]
transaction
subdividing with savepoints
undoing
undoing parts of
when rolled back automatically
[
2
]
truncated value
detecting
truncation error, when generated
tuning, performance
TYPE statement, using the CHARF datatype specifier
U
UID function
unconditional delete
under brace
undo a transaction
UNSAFE_NULL option
[
2
]
UNSIGNED datatype
update cascade
UPDATE command
embedded SQL examples
UPDATE statement
example
SET clause
using host arrays
using the SQLERRD(3) field
updating, rows in tables and views
user exit
calling from a SQL*Forms trigger
common uses
example
guidelines
linking into IAP
meaning of codes returned by
naming
passing parameters
requirements for variables
running the GENXTB form
statements allowed in
steps in developing
using EXEC IAF statements
using EXEC TOOLS statements
using the WHENEVER statement
USER function
user session
user-defined datatype
user-defined record
USERID option
using with the SQLCHECK option
username
defining
prompting for
USING clause
CONNECT statement
of FETCH command
of OPEN command
[
2
]
using in the EXECUTE statement
using indicator variables
using dbstring, SQL*Net database id specification
V
VALUES clause
INSERT statement
of embedded SQL INSERT command
of INSERT command
using a subquery
VAR command
examples
VAR statement
parameters
using the CHARF datatype specifier
VARCHAR datatype
VARCHAR pseudotype
maximum length
using with PL/SQL
VARCHAR2 column, maximum width
VARCHAR2 datatype
external
internal
VARCHAR, precompiler option
variable
VARNUM datatype
example of output value
VARRAW datatype
views
inserting rows into
updating rows in
W
warning flag
WHENEVER command
examples
WHENEVER statement
CONTINUE action
DO action
GOTO action
guidelines
handling end-of-data conditions
maintaining addressability
NOT FOUND condition
overview
scope
SQLERROR condition
SQLWARNING condition
STOP action
using to check SQLCA automatically
where to place
WHERE clause
DELETE statement
of DELETE command
of UPDATE command
search condition
SELECT statement
UPDATE statement
using host arrays
WHERE CURRENT OF clause
WORK option
of ROLLBACK command
of COMMIT command
X
X/Open application
XA interface
XREF option
Y
Z
