Copyright: Copyright (c) 1994, Oracle Corporation.


Entry Topic
PL/SQL 2.1 and Oracle Precompilers, Release 1.6 Addendum, First Edition

PL/SQL™ Release 2.1 and Oracle Precompilers Release 1.6 Addendum

PREFACE

CHAPTER 1. SQL STANDARDS CONFORMANCE

CHAPTER 2. PL/SQL ENHANCEMENTS

CHAPTER 3. ORACLE PRECOMPILER ENHANCEMENTS

APPENDIX A. OPERATING SYSTEM DEPENDENCIES

APPENDIX B. MIGRATING TO ORACLE7


This help file created via:

Oracle Book to Microsoft Help Version 1.0p

© 1994 Oracle Corporation Inc.

Authored by KM .


PL/SQLä Release 2.1 and Oracle Precompilers Release 1.6 Addendum
PL/SQL™ Release 2.1 and Oracle Precompilers Release 1.6 Addendum

First Edition

PL/SQL Release 2.1 and Oracle Precompilers Release 1.6 Addendum

Part No. A11828-3

Copyright © Oracle Corporation 1994

Contributing Authors:  Tom Portfolio, Tim Smith

Contributors:  Amr Assal, Cailein Barclay, Gray Clossman, Bhushan Fotedar, Radhakrishna Hari, Ken Jacobs, Richard Lim, Virgina Lyons, Valarie Moore,  Lee Osborne, Cetin Ozbutun, Gael Turk, Scott Urman

This software/documentation contains proprietary information of Oracle Corporation; it is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited.

If this software/documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable: Restricted Rights Legend

, duplication, or disclosure by the Government is subject to restrictions

set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in

Data and Computer Software (October 1988). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA  94065.

If this software/documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with "Restricted Rights," as defined in FAR 52.227-14, Rights in Data - General, including Alternate III (June 1987).

The information in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing.  Oracle Corporation does not warrant that this document is error-free.

Oracle, Pro*Ada, Pro*COBOL, Pro*FORTRAN, Pro*Pascal, and Pro*PL/I are registered trademarks of Oracle Corporation. Oracle7, Oracle Forms, Oracle Graphics, Oracle Reports, PL/SQL, and Pro*C are trademarks of Oracle Corporation.

IBM is a registered trademark of International Business Machines Corporation. UNIX is a registered trademark of UNIX System Labs. VAX and VMS are registered trademarks of Digital Equipment Corporation. All other products or company names are used for identification purposes only, and may be trademarks of their respective owners.


PREFACE

This addendum describes the improvements and new features available with PL/SQL Release 2.1 and the Oracle Precompilers  Release 1.6. It supplements the following manuals and will be incorporated into their next release:

•	PL/SQL User's Guide and Reference, Version 2

•	Programmer's Guide to the Oracle Precompilers, Release 1.5

•	Programmer's Guide to the Pro*Ada Precompiler, Release 1.5

•	Pro*C Supplement to the Oracle Precompilers Guide, Release 1.5

•	Pro*COBOL Supplement to the Oracle Precompilers Guide, Release 1.5

•	Pro*FORTRAN Supplement to the Oracle Precompilers Guide,  Release 1.5

•	Pro*Pascal Supplement to the Oracle Precompilers Guide, Release 1.5

•	Pro*PL/I Supplement to the Oracle Precompilers Guide, Release 1.5

We recommend that Pro*C users upgrade directly to Pro*C Version 2. See the section "Upgrading to Pro*C Version 2" on page 3 - 15.

 ________________________________________

Audience

This addendum was written for programmers developing applications for the Oracle7 Server. To use this addendum effectively, you need a working knowledge of the following subjects:

•	a procedural programming language such as Ada, C, COBOL, FORTRAN, Pascal, or PL/I

•	the database language SQL

•	Oracle Server concepts and terminology

•	Oracle Server application development tools such as Oracle Forms and Oracle Reports

You will not find installation instructions in this addendum. Refer to the Oracle installation or user's guide for your system.

 ________________________________________

How This Addendum Is Organized

This addendum contains three chapters and two appendices. A brief summary of what you will find in each chapter and appendix follows:

 Chapter 1:  SQL Standards Conformance  This chapter describes the enhancements that allow PL/SQL Release 2.1 and the Oracle Precompilers Release 1.6 to comply with the new ANSI/ISO SQL standard. You learn how PL/SQL supports column aliases in the ORDER BY clause. You also learn how the Oracle Precompilers support the status variable SQLSTATE, the warning flag SQLWARN(3), and same-named host variables.

 Chapter 2:  PL/SQL Enhancements This chapter describes other enhancements offered by PL/SQL Release 2.1. You learn how to call stored functions from SQL expressions and how to define unconstrained subtypes. You also learn why PL/SQL does not support SQL data definition language (DDL) or dynamic SQL and how to solve the problem.

 Chapter 3:  Oracle Precompiler Enhancements This chapter describes other enhancements offered by the Oracle Precompilers Release 1.6. You learn how to use configuration files, EXEC TOOLS statements, new SQLLIB routines, the command-line option AUTO_CONNECT, and the external datatype CHARF. Also, Pro*FORTRAN users learn about the new command-line option MULTISUBPROG, and Pro*C users learn why they might want to upgrade directly to Pro*C Version 2.

 Appendix A:  Operating System Dependencies Some details of Oracle Precompiler programming vary from system to system. For convenience, this appendix collects all external references to system-specific information.

 Appendix B:  Migrating to Oracle7 Oracle7 conforms fully to the new ANSI/ISO SQL standard. As a result, Oracle7 and Oracle Version 6 behave differently in a few areas. By pointing out those areas, this appendix helps you migrate your application programs to Oracle7.

 ________________________________________

Notation Used in This Addendum

This addendum uses the following notation:

< >	Angle brackets enclose the name of a syntactic element.

- -	Two hyphens begin a single-line PL/SQL comment, which extends to the end of a line.

/*	A slash-asterisk begins a multiline PL/SQL comment, which can extend from one line to another.

*/	An asterisk-slash ends a multiline PL/SQL comment.

.	A dot separates an object name from a component name and so qualifies a reference. For example, dot notation is used to specify declarations within a PL/SQL package.

. .	Two dots separate the lowest and highest values in a range.

. . .	An ellipsis shows that statements or clauses irrelevant to the discussion were left out.

 Syntax Notation

Syntax is described using a variant of Backus-Naur Form (BNF), which includes the following symbols:

[ ]	Square brackets enclose optional items.

{ }	Braces enclose items only one of which is required.

|	A vertical bar separates alternatives within brackets or braces.

. . .	An ellipsis shows that the preceding parameter can be repeated.

 ________________________________________

Related Publications

The following publications are recommended for use with this addendum:

•	Oracle Forms Reference Manual, Vol 2, Part No. A11989-1

•	Oracle7 Server Application Developer's Guide, Part No. 6695-70-1292

•	Oracle7 Server Documentation Addendum, Part No. A12042-3

•	Oracle7 Server SQL Language Reference Manual, Part No. 778-70-1292

•	PL/SQL User's Guide and Reference, Part No. 800-20-1292

•	Programmer's Guide to the Oracle Precompilers, Part No. 5315-15-1292

•	Programmer's Guide to the Oracle Pro*C Precompiler, Version 2, Part No. A16458-1

•	Programmer's Guide to the Pro*Ada Precompiler, Part No. A16148-1

•	Any of the following manuals:

•	Pro*C Supplement to the Oracle Precompilers Guide, Part No. A16084-1

•	Pro*COBOL Supplement to the Oracle Precompilers Guide, Part No. A16083-1

•	Pro*FORTRAN Supplement to the Oracle Precompilers Guide, Part No. A16086-1

•	Pro*Pascal Supplement to the Oracle Precompilers Guide, Part No. A16146-1

•	Pro*PL/I Supplement to the Oracle Precompilers Guide, Part No. A16145-1

 ________________________________________

Your Comments Are Welcome

The Oracle Corporation technical staff values your comments. As we write and revise, your opinions are the most important feedback we receive. Please use the Reader's Comment Form at the back of this addendum to tell us what you like and dislike about this Oracle publication. If you want to contact us, please fax us at (415) 506-7200 or write to us at the following address: Languages Documentation Manager

Corporation

Oracle Parkway

City, CA 94065


CHAPTER 1. SQL STANDARDS CONFORMANCE

In late 1992, the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) adopted the current SQL standard known informally as SQL92, which greatly extends the previous SQL standard (SQL89). SQL92 specifies a "conforming SQL language" and, to allow implementation in stages, defines three language levels:

•	Full SQL

•	Intermediate SQL (a subset of Full SQL)

•	Entry SQL (a subset of Intermediate SQL)

A conforming SQL implementation must support at least Entry SQL. This chapter describes the enhancements that allow PL/SQL Release 2.1 and the Oracle Precompilers Release 1.6 to support Entry SQL.

Note: SQL92 is known officially as International Standard ISO/IEC 9075:1992, Database Language SQL, which is also available as ANSI Document ANSI X3.135-1992.

 ________________________________________

PL/SQL Conformance

PL/SQL Release 2.1 supports Entry SQL by letting you

•	reference column aliases in the ORDER BY clause

•	use the keyword AS to introduce aliases in the SELECT clause

 ORDER BY Aliases

Conceptually, the rows returned by a multirow SELECT statement form a result table. Like the rows in a database table, the rows in a result table are not arranged in any particular order. However, you can use the ORDER BY clause to sort the rows in a result table by one or more columns.

With PL/SQL Version 2, if you want to sort the rows in a result table by a calculated (and therefore nameless) column, you must specify the corresponding select-item or its ordinal position. In the following example, the number 3 denotes the calculated column sal+comm, which is the third select-item:

DECLARE

   CURSOR c1 IS

      SELECT empno, ename, sal+comm

         FROM emp

         WHERE job = 'SALESPERSON'

         ORDER BY 3;  -- or ORDER BY sal+comm

  ...

However, under SQL92, this method is a "deprecated feature" retained only for compatibility with SQL89 and likely to be removed from future versions of the standard.

With PL/SQL Release 2.1, a less error-prone method that ensures compliance with future SQL standards is available. You can specify a column alias for use in the ORDER BY clause. In the following example, the alias wages denotes the calculated column sal+comm:

DECLARE

   CURSOR c1 IS

      SELECT empno, ename, sal+comm  wages

         FROM emp

         WHERE job = 'SALESPERSON'

         ORDER BY wages;

  ...

Furthermore, you can use the optional keyword AS in the SELECT clause to improve readability, as follows:

DECLARE

   CURSOR c1 IS

      SELECT empno, ename, sal+comm AS wages

         FROM emp

         WHERE job = 'SALESPERSON'

         ORDER BY wages;

  ...

 Name Precedence

Avoid using column names as aliases. In the ORDER BY clause, an alias takes precedence over a column name. For example, the following ORDER BY clause sorts rows by the calculated column sal+comm, not by the column sal:

DECLARE

   CURSOR c1 IS

      SELECT empno, ename, sal+comm AS sal

         FROM emp

         WHERE job = 'SALESPERSON'

         ORDER BY sal;  -- sorts by sal+comm

  ...

If you use the name of a selected column as the alias of a calculated column, the Oracle Server issues an error message. An example of this mistake follows:

DECLARE

   CURSOR c1 IS

      SELECT empno, ename, sal, sal+comm AS sal

         FROM emp

         WHERE job = 'SALESPERSON'

         ORDER BY sal;  -- causes execution error

  ...

 Restriction

You cannot use a column alias in the other clauses of a SELECT statement. For more information about the SELECT statement and its clauses, see the Oracle7 Server SQL Language Reference Manual.

 ________________________________________

Oracle Precompilers Conformance

The Oracle Precompilers Release 1.6 support Entry SQL by letting you

•	use the status variable SQLSTATE

•	check the SQLCA warning flag SQLWARN(3) for data exceptions

•	declare multiple host variables with the same name, provided their scopes do not overlap

•	specify the USAGE IS BINARY clause (Pro*COBOL only)

 SQLSTATE Status Variable

The precompiler command-line option MODE governs ANSI/ISO compliance. When MODE=ANSI or MODE=ANSI14, declaring the SQLCA data structure is optional. However, you must declare a status variable named SQLCODE (SQLCOD in FORTRAN). SQL92 specifies a similar status variable named SQLSTATE (SQLSTA in FORTRAN), which you can use with or without SQLCODE.

After executing a SQL statement, the Oracle Server returns a status code to the SQLSTATE variable currently in scope. The status code indicates whether the SQL statement executed successfully or raised an exception (error or warning condition). To promote interoperability (the ability of systems to exchange information easily), SQL92 predefines all the common SQL exceptions.

Unlike SQLCODE, which stores only error codes and the "not found" condition, SQLSTATE stores error and warning codes. Furthermore, the SQLSTATE reporting mechanism uses a standardized coding scheme. Thus, SQLSTATE is the preferred status variable. Under SQL92, SQLCODE is a deprecated feature retained only for compatibility with SQL89 and likely to be removed from future versions of the standard.

 Declaring SQLSTATE

When MODE=ANSI or MODE=ANSI14, you must declare SQLSTATE or SQLCODE. Declaring the SQLCA is optional. When MODE=ANSI13 or MODE=ORACLE, you need not declare SQLSTATE; if you do, Oracle ignores it.

Unlike SQLCODE, which stores signed integers and can be declared inside or outside the Declare Section, SQLSTATE stores five-character, alphanumeric strings and must be declared inside the Declare Section.

In C, COBOL, FORTRAN, Pascal, and PL/I, you declare SQLSTATE as shown below. In C, uppercase and null-termination are required.

char SQLSTATE[6];                       -- C

01 SQLSTATE PIC X(5).                   -- COBOL

CHARACTER*5 SQLSTA                      -- FORTRAN

SQLSTATE: Packed Array [1..5] of Char;  -- Pascal

DCL SQLSTATE CHARACTER(5);              -- PL/I

 Using SQLSTATE

Table 1 - 1 gives the rules that apply to using SQLSTATE with SQLCODE or the SQLCA.

SQLSTATE 	MODE=ANSI or MODE=ANSI14	MODE=ORACLE

------------------------------------------------------------------------

declared inside 	Declaring SQLCODE is optional. If you de-	Oracle ignores SQLSTATE and

Declare Section	clare SQLCODE inside the Declare Sec-	SQLCODE.

tion, Oracle returns status codes to

SQLSTATE and SQLCODE after every 	Declaring the SQLCA outside

SQL operation. However, if you declare 	the Declare Section is required.

SQLCODE outside the Declare Section,

Oracle returns a status code only to

SQLSTATE.

Declaring the SQLCA is optional. If you de-

clare the SQLCA, Oracle returns status

codes to SQLSTATE and the SQLCA.

declared outside 	Oracle ignores SQLSTATE.	Oracle ignores SQLSTATE and

Declare Section	SQLCODE.

You must declare SQLCODE inside or out-

side the Declare Section. Oracle returns a 	Declaring the SQLCA outside

status code to SQLCODE after every SQL 	the Declare Section is required.

operation.

Declaring the SQLCA is optional. If you de-

clare the SQLCA, Oracle returns status

codes to SQLCODE and the SQLCA.

not declared	You must declare SQLCODE inside or out-	Oracle ignores SQLCODE.

side the Declare Section. Oracle returns a

status code to SQLCODE after every SQL 	Declaring the SQLCA outside

operation.	the Declare Section is required.

Declaring the SQLCA is optional. If you de-

clare the SQLCA, Oracle returns status

codes to SQLCODE and the SQLCA.

Table 1 - 1.  SQLSTATE Rules

You can learn the outcome of the most recent SQL operation by checking SQLSTATE explicitly with your own code or implicitly with the WHENEVER SQLERROR and WHENEVER SQLWARNING statements.

 SQLSTATE Values

SQLSTATE status codes consist of a two-character class code followed by a three-character subclass code. Aside from class code 00 (successful completion), the class code denotes a category of exceptions. Aside from subclass code 000 (not applicable), the subclass code denotes a specific exception within that category. For example, the SQLSTATE value `22012' consists of class code 22 (data exception) and subclass code 012 (division by zero).

Each of the five characters in a SQLSTATE value is a digit (0..9) or an uppercase Latin letter (A..Z). Class codes that begin with a digit in the range 0..4 or a letter in the range A..H are reserved for predefined conditions (those defined in SQL92). All other class codes are reserved for implementation-defined conditions. Within predefined classes, subclass codes that begin with a digit in the range 0..4 or a letter in the range A..H are reserved for predefined subconditions. All other subclass codes are reserved for implementation-defined subconditions. Figure 1 - 1 shows the coding scheme.

Figure 1 - 1. SQLSTATE Coding Scheme

Table 1 - 2 shows the classes predefined by SQL92.

Class	Condition

------------------------------------------------------------------------

00	successful completion

01	warning

02	no data

07	dynamic SQL error

08	connection exception

0A	feature not supported

21	cardinality violation

22	data exception

23	integrity constraint violation

24	invalid cursor state

25	invalid transaction state

26	invalid SQL statement name

27	triggered data change violation

28	invalid authorization specification

2A	direct SQL syntax error or access rule violation

2B	dependent privilege descriptors still exist

2C	invalid character set name

2D	invalid transaction termination

2E	invalid connection name

33	invalid SQL descriptor name

34	invalid cursor name

35	invalid condition number

37	dynamic SQL syntax error or access rule violation

3C	ambiguous cursor name

3D	invalid catalog name

3F	invalid schema name

40	transaction rollback

42	syntax error or access rule violation

44	with check option violation

HZ	remote database access

Table 1 - 2.  Predefined Classes

Note: The class code HZ is reserved for conditions defined in International Standard ISO/IEC DIS 9579-2, Remote Database Access.

Table 1 - 3 shows how Oracle errors map to SQLSTATE status codes. In some cases, several Oracle errors map to the status code. In other cases, no Oracle error maps to the status code (so the last column is empty). Status codes in the range 60000 .. 99999 are implementation-defined.

Code	Condition	Oracle Error

------------------------------------------------------------------------

00000	successful completion	ORA-00000

01000	warning

01001	cursor operation conflict

01002	disconnect error

01003	null value eliminated in set function

01004	string data - right truncation

01005	insufficient item descriptor areas

01006	privilege not revoked

01007	privilege not granted

01008	implicit zero-bit padding

01009	search condition too long for info schema

0100A	query expression too long for info schema

02000	no data	ORA-01095

ORA-01403

07000	dynamic SQL error

07001	using clause does not match parameter specs

07002	using clause does not match target specs

07003	cursor specification cannot be executed

07004	using clause required for dynamic parameters

07005	prepared statement not a cursor specification

07006	restricted datatype attribute violation

07007	using clause required for result fields

07008	invalid descriptor count	SQL-02126

07009	invalid descriptor index

08000	connection exception

08001	SQL client unable to establish SQL connection

08002	connection name in use

08003	connection does not exist	SQL-02121

08004	SQL server rejected SQL connection

08006	connection failure

08007	transaction resolution unknown

0A000	feature not supported	ORA-03000 .. 03099

0A001	multiple server transactions

21000	cardinality violation	ORA-01427

SQL-02112

22000	data exception

22001	string data - right truncation	ORA-01401

ORA-01406

22002	null value - no indicator parameter	ORA-01405

SQL-02124

22003	numeric value out of range	ORA-01426

ORA-01438

ORA-01455

ORA-01457

22005	error in assignment

22007	invalid datetime format

22008	datetime field overflow	ORA-01800 .. 01899

22009	invalid time zone displacement value

22011	substring error

22012	division by zero	ORA-01476

22015	interval field overflow

22018	invalid character value for cast

22019	invalid escape character	ORA-00911

ORA-01425

22021	character not in repertoire

22022	indicator overflow	ORA-01411

22023	invalid parameter value	ORA-01025

ORA-01488

ORA-04000 .. 04019

22024	unterminated C string	ORA-01479 .. 01480

22025	invalid escape sequence	ORA-01424

22026	string data - length mismatch

22027	trim error

23000	integrity constraint violation	ORA-00001

ORA-02290 .. 02299

24000 	invalid cursor state	ORA-01001 .. 01003

ORA-01410

ORA-08006

SQL-02114

SQL-02117

SQL-02118

SQL-02122

25000	invalid transaction state

26000	invalid SQL statement name

27000	triggered data change violation

28000	invalid authorization specification

2A000	direct SQL syntax error or access rule violation

2B000	dependent privilege descriptors still exist

2C000	invalid character set name

2D000	invalid transaction termination

2E000	invalid connection name

33000	invalid SQL descriptor name

34000	invalid cursor name

35000	invalid condition number

37000	dynamic SQL syntax error or access rule violation

3C000	ambiguous cursor name

3D000	invalid catalog name

3F000	invalid schema name

40000	transaction rollback	ORA-02091 .. 02092

40001	serialization failure

40002	integrity constraint violation

40003	statement completion unknown

42000  	syntax error or access rule violation	ORA-00022

ORA-00251

ORA-00900 .. 00999

ORA-01031

ORA-01490 .. 01493

ORA-01700 .. 01799

ORA-01900 .. 02099

ORA-02140 .. 02289

ORA-02420 .. 02424

ORA-02450 .. 02499

ORA-03276 .. 03299

ORA-04040 .. 04059

ORA-04070 .. 04099

44000	with check option violation	ORA-01402

60000  	system errors	ORA-00370 .. 00429

ORA-00600 .. 00899

ORA-06430 .. 06449

ORA-07200 .. 07999

ORA-09700 .. 09999

61000  	resource error	ORA-00018 .. 00035

ORA-00050 .. 00068

ORA-02376 .. 02399

ORA-04020 .. 04039

62000  	multi-threaded server and detached process errors	ORA-00100 .. 00120

ORA-00440 .. 00569

63000   	Oracle*XA and two-task interface errors	ORA-00150 .. 00159

ORA-02700 .. 02899

ORA-03100 .. 03199

ORA-06200 .. 06249

64000  	control file, database file, and redo file errors;	ORA-00200 .. 00369

archival and media recovery errors  	ORA-01100 .. 01250

65000	PL/SQL errors	ORA-06500 .. 06599

66000  	SQL*Net driver errors	ORA-06000 .. 06149

ORA-06250 .. 06429

ORA-06600 .. 06999

ORA-12100 .. 12299

ORA-12500 .. 12599

67000	licensing errors	ORA-00430 .. 00439

69000  	SQL*Connect errors	ORA-00570 .. 00599

ORA-07000 .. 07199

72000  	SQL execute phase errors	ORA-01000 .. 01099

ORA-01400 .. 01489

ORA-01495 .. 01499

ORA-01500 .. 01699

ORA-02400 .. 02419

ORA-02425 .. 02449

ORA-04060 .. 04069

ORA-08000 .. 08190

ORA-12000 .. 12019

ORA-12300 .. 12499

ORA-12700 .. 21999

82100	out of memory (could not allocate)	SQL-02100

82101	inconsistent cursor cache (UCE/CUC mismatch)	SQL-02101

82102	inconsistent cursor cache (no CUC entry for UCE)	SQL-02102

82103	inconsistent cursor cache (out-of-range CUC ref)	SQL-02103

82104	inconsistent cursor cache (no CUC available)	SQL-02104

82105	inconsistent cursor cache (no CUC entry in cache)	SQL-02105

82106	inconsistent cursor cache (invalid cursor number)	SQL-02106

82107	program too old for runtime library	SQL-02107

82108	invalid descriptor passed to runtime library	SQL-02108

82109	inconsistent host cache (out-of-range SIT ref)	SQL-02109

82110	inconsistent host cache (invalid SQI type)	SQL-02110

82111	heap consistency error	SQL-02111

82112	unable to open message file	SQL-02113

82113	code generation internal consistency failed	SQL-02115

82114	reentrant code generator gave invalid context	SQL-02116

82115	invalid hstdef argument	SQL-02119

82116	first and second arguments to sqlrcn both null	SQL-02120

82117	invalid OPEN or PREPARE for this connection	SQL-02122

82118	application context not found	SQL-02123

82119	connect error; can't get error text	SQL-02125

90000	debug events	ORA-10000 .. 10999

99999	catch all	all others

HZ000	remote database access

Table 1 - 3.

SQLSTATE Codes

 SQLWARN(3) Warning Flag

The SQLCA contains warning, error, and status information updated by the Oracle Server whenever a SQL statement is executed. SQLWARN is an eight-element array of one-character warning flags in the SQLCA. When set to `W', the flags indicate warning conditions. For example, SQLWARN(2) is set when Oracle assigns a truncated column value to an output host variable.

With previous Oracle Precompilers, SQLWARN(3) was not used. However, with the Oracle Precompilers Release 1.6, SQLWARN(3) indicates that one or more nulls were ignored in the evaluation of a SQL group function such as AVG, COUNT, or MAX. This behavior is expected because, except for COUNT(*), all group functions ignore nulls. If necessary, you can use the SQL function NVL to temporarily assign values (zeros, for example) to the null column entries.

If you declare the SQLCA, you can learn the outcome of the most recent SQL operation by checking SQLWARN(3) explicitly with your own code or implicitly with the WHENEVER SQLWARNING statement.

Note: The SQLCA variable names used in this section are generic, not language-specific.

 Same-Named Host Variables

Block-structured host languages let you redeclare global variables locally in procedures and functions. The Oracle Precompilers Release 1.6 support that feature. Within a given compilation unit, you can declare multiple host variables with the same name provided their scopes do not overlap. In the following Pro*C example, you define a global and a local host variable named salary:

#include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;

    ...

    char   job_title[10];

    float  salary;

EXEC SQL END DECLARE SECTION;

EXEC SQL INCLUDE sqlca;

void raise_salary();

main()

{

    raise_salary();

    ...

    EXEC SQL SELECT job, sal

        INTO :job_title, :salary  /* refers to global variable */

        FROM emp

        WHERE ...

    ...

}  /* ends main */

raise_salary()

{

    EXEC SQL BEGIN DECLARE SECTION;

        ...

        char   emp_name[11];

        float  salary;

    EXEC SQL END DECLARE SECTION;

    ...

    EXEC SQL SELECT ename, sal

        INTO :emp_name, :salary  /* refers to local variable */

        FROM emp

        WHERE ...

    ...

}

 Overlapping Scopes

The precompiler processes an input file from top to bottom, ignoring all host-language scoping rules. If scopes overlap, the precompiler does not generate an error, but your program might fail at run time. In the following Pro*C example, the INSERT statement fails at run time with an "invalid number" error:

#include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;

    ...

    int  new_dept = 60;

EXEC SQL END DECLARE SECTION;

EXEC SQL INCLUDE sqlca;

void add_dept();

main()

{

    EXEC SQL BEGIN DECLARE SECTION;

        ...

        char  new_dept[15] = "CREDIT";

    EXEC SQL END DECLARE SECTION;

    add_dept();

    ...

}  /* ends main */

add_dept()

{

    ...

    EXEC SQL INSERT INTO dept  /* fails at run time */

        VALUES (:new_dept, ... );

}

By the scoping rules of C, the name new_dept in the VALUES clause refers to the global int variable. However, the precompiler uses the last declaration of new_dept, which specifies a char variable. The insert fails when the Oracle Server tries to convert the character string "CREDIT" to a number before storing it in the deptno column.

Note: This example does not apply to Pro*C Version 2, which observes the scoping rules of C. For more information, see the section "Upgrading to Pro*C Version 2" on page 3 - 15.

USAGE IS BINARY Clause

In COBOL, the USAGE clause specifies how a data item is represented internally--not how it is used in your program. The USAGE IS BINARY clause specifies that a numeric item is a binary integer.

With Pro*COBOL Release 1.6, you can specify USAGE IS BINARY when declaring host variables. As usual, the keywords USAGE IS are optional. For example, you might declare the following numeric item:

EXEC SQL BEGIN DECLARE SECTION END-EXEC.

    ...

    01  EMP-COUNT  PIC S9(4) BINARY VALUE ZERO.

EXEC SQL END DECLARE SECTION END-EXEC.

BINARY numbers are the most efficient numbers for computations because computers are binary machines. Typically, BINARY numbers are used for subscripts and counters.

On most systems, COMP numbers are equivalent to BINARY numbers.

Note: On platforms that support the command-line option COMP5, when COMP5=YES, the keyword BINARY must be followed on the same line by punctuation or another keyword.


CHAPTER 2. PL/SQL ENHANCEMENTS

Chapter 1 described the enhancements that allow PL/SQL to comply with the new ANSI/ISO SQL standard. This chapter describes other enhancements offered by PL/SQL Release 2.1. You learn how to call stored functions from SQL expressions and how to define  unconstrained subtypes. You also learn why PL/SQL does not support SQL data definition language (DDL) or dynamic SQL and how to solve the problem.

Note: In release 7.1 of the Oracle Server, the ability to use stored subprograms and triggers--previously available only to licensees of the procedural option--is a standard feature.

 ________________________________________

Calling Stored Functions from SQL Expressions

A stored function is a user-defined PL/SQL function created with an Oracle tool and stored in the data dictionary. It is a database object, which can be referenced by any number of applications connected to that database. There are two types of stored functions: packaged and standalone. Packaged functions are defined within a PL/SQL package; standalone functions are defined independently.

With PL/SQL Version 2, calls to stored functions can appear only in procedural statements. With PL/SQL Release 2.1, calls to stored functions can also appear in SQL statements. Now you can call stored functions from the SELECT, VALUES, SET, WHERE, START WITH, GROUP BY, HAVING, and ORDER BY clauses--wherever expressions are allowed in a SQL statement.

That means you can use stored functions as if they were built-in SQL functions such as ROUND, LENGTH, and MONTHS_BETWEEN. For example, you might define a function named miles_between that returns the distance between two cities, then use the function in the WHERE clause of a SELECT statement to find employees who live within a given distance from company headquarters.

By extending SQL in this way, you can do complex data analysis within the Oracle Server; you need not retrieve the data into your application. This increases data independence.

Note: Unlike functions, which are called as part of an expression, procedures are called as statements. Therefore, procedures cannot be called directly from SQL expressions.

 Calling Syntax

To call a stored function from a SQL expression, you use the following syntax:

[schema.][package.]function[@dblink][(arg[, arg] ... )]

You must write the arguments (actual parameters) using positional notation; you cannot use named or mixed notation. In the following example, you call the standalone function gross_pay, which is stored in a remote Oracle database:

SELECT gross_pay@newyork(eenum,stime,otime) INTO pay FROM dual;

 Using Default Values

The stored function gross_pay initializes two of its the formal parameters to default values using the DEFAULT clause, as follows:

CREATE FUNCTION gross_pay

      (emp_id IN NUMBER,

       st_hrs IN NUMBER DEFAULT 40,

       ot_hrs IN NUMBER DEFAULT 0) RETURN NUMBER AS

   ...

When calling gross_pay from a procedural statement, you can always accept the default value of st_hrs. That is because you can use named notation, which lets you skip parameters, as in

IF gross_pay(eenum,ot_hrs => otime) > pay_limit THEN ...

However, when calling gross_pay from a SQL expression, you cannot accept the default value of st_hrs unless you accept the default value of ot_hrs. That is because you cannot use named notation.

 Meeting Basic Requirements

To be callable from SQL expressions, a user-defined PL/SQL function must meet the following basic requirements:

•	It must be a stored function, not a function defined within a PL/SQL block or subprogram.

•	It must be a row function, not a column (group) function; that is, it cannot take an entire column of data as its argument.

•	All its formal parameters must be IN parameters; none can be an OUT or IN OUT parameter.

•	The datatypes of its formal parameters must be Oracle Server internal types such as CHAR, DATE, or NUMBER, not PL/SQL types such as BOOLEAN, RECORD, or TABLE.

•	Its return type (the datatype of its result value) must be an Oracle Server internal type.

For example, the following stored function meets the basic requirements:

CREATE FUNCTION gross_pay

      (emp_id IN NUMBER,

       st_hrs IN NUMBER DEFAULT 40,

       ot_hrs IN NUMBER DEFAULT 0) RETURN NUMBER AS

   st_rate  NUMBER;

   ot_rate  NUMBER;

BEGIN

   SELECT srate, orate INTO st_rate, ot_rate FROM payroll

      WHERE acctno = emp_id;

   RETURN st_hrs * st_rate + ot_hrs * ot_rate;

END gross_pay;

 Controlling Side Effects

To execute a SQL statement that calls a stored function, the Oracle Server must know the purity level of the function. That is, the extent to which the function is free of side effects. In this context, side effects are references to database tables or packaged variables.

Side effects can prevent the parallelization of a query, yield order-dependent (and therefore indeterminate) results, or require that package state be maintained across user sessions (which is not allowed). Therefore, the following rules apply to stored functions called from SQL expressions:

•	The function cannot modify database tables; therefore, it cannot execute an INSERT, UPDATE, or DELETE statement.

•	Remote or parallelized functions cannot read or write the values of packaged variables.

•	Only functions called from a SELECT, VALUES, or SET clause can write the values of packaged variables.

•	The function cannot call another subprogram that breaks one of the foregoing rules. Also, the function cannot reference a view that breaks one of the foregoing rules. (Oracle replaces references to a view with a stored SELECT operation, which can include function calls.)

For standalone functions, Oracle can enforce these rules by checking the function body. However, the body of a packaged function is hidden; only its specification is visible. So, for packaged functions, you must use the pragma (compiler directive) RESTRICT_REFERENCES to enforce the rules.

The pragma tells the PL/SQL compiler to deny the packaged function read/write access to database tables, packaged variables, or both. If you try to compile a function body that violates the pragma, you get a compilation error.

 Calling Packaged Functions

To call a packaged function from SQL expressions, you must assert its purity level by coding the pragma RESTRICT_REFERENCES in the package specification (not in the package body). The pragma must follow the function declaration but need not follow it immediately. Only one pragma can reference a given function declaration.

To code the pragma RESTRICT_REFERENCES, you use the syntax

PRAGMA RESTRICT_REFERENCES (

    function_name, WNDS [, WNPS] [, RNDS] [, RNPS]);

where:

WNDS	means "writes no database state" (does not modify database tables)

WNPS	means "writes no package state" (does not change the values of packaged variables)

RNDS	means "reads no database state" (does not query database tables)

RNPS	means "reads no package state" (does not reference the values of packaged variables)

You can pass the arguments in any order., but you must pass the argument WNDS. No argument implies another. For instance, RNPS does not imply WNPS.

In the example below, the function compound neither reads nor writes database or package state, so you can assert the maximum purity level. Always assert the highest purity level a function allows. That way, the PL/SQL compiler will never reject the function unnecessarily.

CREATE PACKAGE finance AS  -- package specification

   ...

   FUNCTION compound

         (years  IN NUMBER,

          amount IN NUMBER,

          rate   IN NUMBER) RETURN NUMBER;

   PRAGMA RESTRICT_REFERENCES (compound, WNDS, WNPS, RNDS, RNPS);

END finance;

CREATE PACKAGE BODY finance AS  --package body

   ...

   FUNCTION compound

         (years  IN NUMBER,

          amount IN NUMBER,

          rate   IN NUMBER) RETURN NUMBER IS

   BEGIN

      RETURN amount * POWER((rate / 100) + 1, years);

   END compound;

                   -- no pragma in package body

END finance;

Later, you might call compound from a PL/SQL block, as follows:

BEGIN

   ...

   SELECT finance.compound(yrs,amt,rte)  -- function call

      INTO interest

      FROM accounts

      WHERE acctno = acct_id;

 Referencing Packages with an Initialization Part

Packages can have an initialization part, which is hidden in the package body. Typically, the initialization part holds statements that initialize public variables. In the following example, the SELECT statement initializes the public variable prime_rate:

CREATE PACKAGE loans AS

   prime_rate  REAL;  -- public packaged variable

   ...

END loans;

CREATE PACKAGE BODY loans AS

   ...

BEGIN  -- initialization part

   SELECT prime INTO prime_rate FROM rates;

END loans;

The initialization code is run only once--the first time the package is referenced. If the code reads or writes database state or package state other than its own, it can cause side effects. Moreover, a stored function that references the package (and thereby runs the initialization code) can cause side effects indirectly. So, to call the function from SQL expressions, you must use the pragma RESTRICT_REFERENCES to assert or imply the purity level of the initialization code.

To assert the purity level of the initialization code, you use a variant of the pragma RESTRICT_REFERENCES, in which the function name is replaced by a package name. You code the pragma in the package specification, where it is visible to other users. That way, anyone referencing the package can see the restrictions and conform to them.

To code the variant pragma RESTRICT_REFERENCES, you use the syntax

PRAGMA RESTRICT_REFERENCES (

    package_name, WNDS [, WNPS] [, RNDS] [, RNPS]);

where the arguments WNDS, WNPS, RNDS, and RNPS have the usual meaning.

In the example below, the initialization code reads database state and writes package state. However, you can assert WNPS because the code is writing the state of its own package, which is permitted. So, you assert WNDS, WNPS, RNPS--the highest purity level the function allows. (If the public variable prime_rate were in another package, you could not assert WNPS.)

CREATE PACKAGE loans AS

   PRAGMA RESTRICT_REFERENCES (loans, WNDS, WNPS, RNPS);

   prime_rate  REAL;

   ...

END loans;

CREATE PACKAGE BODY loans AS

   ...

BEGIN

   SELECT prime INTO prime_rate FROM rates;

END loans;

You can place the pragma anywhere in the package specification, but placing it at the top (where it stands out) is a good idea.

To imply the purity level of the initialization code, your package must have a RESTRICT_REFERENCES pragma for one of the functions it declares. From the pragma, Oracle can infer the purity level of the initialization code (because the code cannot break any rule enforced by a pragma). In the next example, the pragma for the function discount implies that the purity level of the initialization code is at least WNDS:

CREATE PACKAGE loans AS

   ...

   FUNCTION discount (...) RETURN NUMBER;

   PRAGMA RESTRICT_REFERENCES (discount, WNDS);

END loans;

...

To draw an inference, Oracle can combine the assertions of all RESTRICT_REFERENCES pragmas. For example, the following pragmas (combined) imply that the purity level of the initialization code is at least WNDS, RNDS:

CREATE PACKAGE loans AS

   ...

   FUNCTION discount (...) RETURN NUMBER;

   FUNCTION credit_ok (...) RETURN CHAR;

   PRAGMA RESTRICT_REFERENCES (discount, WNDS);

   PRAGMA RESTRICT_REFERENCES (credit_ok, RNDS);

END loans;

...

 Avoiding Problems

To call a packaged function from SQL expressions, you must assert its purity level using the pragma RESTRICT_REFERENCES. However, if the package has an initialization part, the PL/SQL compiler might not let you assert the highest purity level the function allows. As a result, you might be unable to call the function remotely, in parallel, or from certain SQL clauses.

This happens when a packaged function is purer than the package initialization code. Remember, the first time a package is referenced,  its initialization code is run. If that reference is a function call, any additional side effects caused by the initialization code occur during the call. So, in effect, the initialization code lowers the purity level of the function.

To avoid this problem, move the package initialization code into a subprogram. That way, your application can run the code explicitly (rather than implicitly during package instantiation) without affecting your packaged functions.

 Name Precedence

In SQL statements, the names of database columns take precedence over the names of parameterless functions. For example, if user scott executes the statements

CREATE TABLE stats (rand_num NUMBER, ...);

CREATE FUNCTION rand_num RETURN NUMBER AS ...

then the following select-item refers to the column rand_num:

SELECT rand_num INTO start_val FROM stats WHERE ...

In this case, to call the stored function rand_num, you must specify the schema, as follows:

SELECT scott.rand_num INTO start_val FROM stats WHERE ...

 Overloading

PL/SQL lets you overload packaged (but not standalone) functions. That is, you can use the same name for different functions if their formal parameters differ in number, order, or datatype family.

However, a RESTRICT_REFERENCES pragma can apply to only one function declaration. So, a pragma that references the name of overloaded functions always applies to the nearest foregoing function declaration. In the following example, the pragma applies to the second declaration of valid:

CREATE PACKAGE tests AS

   FUNCTION valid (x NUMBER) RETURN CHAR;

   FUNCTION valid (x DATE) RETURN CHAR;

   PRAGMA RESTRICT_REFERENCES (valid, WNDS);

   ...

 ________________________________________

Defining Unconstrained Subtypes

Each PL/SQL datatype specifies a set of values and a set of operations applicable to objects of that type. Subtypes specify the same set of operations as their base type but only a subset of its values. Thus, a subtype does not introduce a new type; it merely places an optional constraint on its base type.

 Kinds of Subtypes

PL/SQL predefines several subtypes in package STANDARD. For example, the subtype NATURAL is defined as follows:

SUBTYPE NATURAL IS BINARY_INTEGER RANGE 0..2147483647;

The RANGE constraint restricts objects of type NATURAL to the subset of BINARY_INTEGER values in the range 0 .. 2147483647. Thus, NATURAL is a constrained subtype. Constrained subtypes can increase reliability by detecting nulls or out-of-range values. For example, if you try to store a negative value in a NATURAL variable, PL/SQL raises the predefined exception VALUE_ERROR.

PL/SQL predefines the subtype CHARACTER in package STANDARD as follows:

SUBTYPE CHARACTER IS CHAR;

The subtype CHARACTER specifies the same set of values as its base type CHAR. Thus, CHARACTER is an unconstrained subtype. Unconstrained subtypes can provide compatibility with ANSI/ISO and IBM types. Also, they can improve readability by indicating the intended use of constants and variables.

PL/SQL Release 2.1 lets you define your own unconstrained subtypes. Future releases of PL/SQL will let you define constrained subtypes as well.

 Declaring Subtypes

You can declare unconstrained subtypes in the declarative part of any PL/SQL block, subprogram, or package using the syntax

SUBTYPE subtype_name IS base_type;

where subtype_name is a type specifier used in subsequent declarations and base_type stands for the following syntax:

{type_name | subtype_name | variable%TYPE |

    table.column%TYPE | table%ROWTYPE}

The base type can be a predefined type, a user-defined RECORD or TABLE type, a predefined subtype, or a user-defined subtype. For example, all of the following declarations are legal:

DECLARE

   SUBTYPE EmpDate IS DATE;       -- based on predefined type

   TYPE NameTab IS TABLE OF CHAR(10)

      INDEX BY BINARY_INTEGER;

   SUBTYPE EnameTab IS NameTab;   -- based on user-defined type

   SUBTYPE Counter IS NATURAL;    -- based on predefined subtype

   SUBTYPE Totalizer IS Counter;  -- based on user-defined subtype

   ...

The base type cannot be constrained. For example, the following declarations are illegal:

DECLARE

   SUBTYPE Accumulator IS NUMBER(9,2); -- illegal; must be NUMBER

   SUBTYPE Delimiter IS CHAR(1);       -- illegal; must be CHAR

   SUBTYPE Text IS VARCHAR2(n);        -- illegal

   ...

 Using Subtypes

Once you define a subtype, you can declare objects of that type. In the next example, you declare two variables of type Counter:

DECLARE

   SUBTYPE Counter IS NATURAL;

   rows       Counter;

   employees  Counter;

   ...

Notice how the subtype name indicates the intended use of the variables.

 Type Compatibility

Objects of a subtype are always compatible with objects of its base type. For example, given the following declarations, the value of salary can be assigned to total without conversion:

DECLARE

   SUBTYPE Accumulator IS NUMBER;

   salary  NUMBER(7,2);

   total   Accumulator;

   ...

Objects of different subtypes are compatible if the subtypes have the same base type. For instance, given the following declarations, the value of finished can be assigned to debugging:

DECLARE

   SUBTYPE Sentinel IS BOOLEAN;

   SUBTYPE Switch IS BOOLEAN;

   finished   Sentinel;

   debugging  Switch;

   ...

Objects of different subtypes are also compatible if their base types belong to the same datatype family. For example, given the following declarations, the value of comma can be assigned to line:

DECLARE

   SUBTYPE Delimiter IS CHAR;

   SUBTYPE Text IS LONG;

   comma  Delimiter;

   line   Text;

   ...

 Overloading Subprograms

You cannot overload two subprograms if their formal parameters differ only in subtype and the different subtypes are based on types in the same family. For example, you cannot overload the following procedures because the base types CHAR and LONG are in the same family:

DECLARE

   SUBTYPE Delimiter IS CHAR;

   SUBTYPE Text IS LONG;

   ...

   PROCEDURE scan (x Delimiter) IS

      BEGIN ... END;

   PROCEDURE scan (x Text) IS

      BEGIN ... END;

EGIN

   ...

 ________________________________________

Using DDL and Dynamic SQL

In this section, you learn why PL/SQL does not support SQL data definition language (DDL) or dynamic SQL and how to solve the problem.

 Efficiency versus Flexibility

Before a PL/SQL program can be executed, it must be compiled. The PL/SQL compiler resolves references to Oracle objects by looking up their definitions in the data dictionary. Then, the compiler assigns storage addresses to program variables that will hold Oracle data so that Oracle can look up the addresses at run time. This process is called binding.

How a database language implements binding affects runtime efficiency and flexibility. Binding at compile time, called static or early binding, increases efficiency because the definitions of database objects are looked up then, not at run time. On the other hand, binding at run time, called dynamic or late binding, increases flexibility because the definitions of database objects can remain unknown until then.

Designed primarily for high-speed transaction processing, PL/SQL increases efficiency by grouping SQL statements and avoiding runtime compilation. Unlike SQL, which is compiled and executed statement-by-statement at run time (late binding), PL/SQL is processed into machine-readable p-code at compile time (early binding). At run time, the PL/SQL engine simply executes the p-code.

 Some Limitations

However, this design imposes some limitations. For example, the p-code includes references to database objects such as tables and stored procedures. The PL/SQL compiler can resolve such references only if the database objects are known at compile time. In the following example, the compiler cannot process the procedure until the table is defined, but the table is undefined until the procedure is executed:

CREATE PROCEDURE create_table AS

BEGIN

   CREATE TABLE dept (deptno NUMBER(2), ...);  -- illegal

   ...

END create_table;

In the next example, the compiler cannot bind the table reference in the DROP TABLE statement because the table name is unknown until the procedure is executed:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS

BEGIN

   DROP TABLE table_name;  -- illegal

   ...

END drop_table;

 Overcoming the Limitations

However, help is available. A new package named DBMS_SQL, which is supplied with release 7.1 of the Oracle Server, allows PL/SQL to execute SQL data definition and data manipulation statements dynamically at run time. For example, when called, the following stored procedure drops a specified database table:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS

   cid INTEGER;

BEGIN

   /* Open new cursor and return cursor ID. */

   cid := dbms_sql.open_cursor;

   /* Parse and immediately execute dynamic SQL statement built by

      concatenating table name to DROP TABLE command.  (Unlike DML

      statements, DDL statements are executed at parse time.) */

   dbms_sql.parse(cid, 'DROP TABLE ' || table_name, dbms_sql.v7);

   /* Close cursor. */

   dbms_sql.close_cursor(cid);

EXCEPTION

   /* If an exception is raised, close cursor before exiting. */

   WHEN OTHERS THEN

      dbms_sql.close_cursor(cid);

END drop_table;

For more information about package DBMS_SQL, see the Oracle7 Server Documentation Addendum.


CHAPTER 3. ORACLE PRECOMPILER ENHANCEMENTS

Chapter 1 described the enhancements that allow the Oracle Precompilers to comply with the new ANSI/ISO SQL standard. This chapter describes other enhancements offered by the Oracle Precompilers Release 1.6. You learn how to use the following new features:

•	configuration files

•	EXEC TOOLS statements

•	the runtime library routines SQLGLS and SQLVCP

•	the command-line option AUTO_CONNECT

•	the external datatype CHARF

Also, Pro*FORTRAN users learn about the new command-line option MULTISUBPROG, and Pro*C users learn why they might want to upgrade directly to Pro*C Version 2.

 ________________________________________

Configuration Files

The Oracle Precompilers Release 1.6 can use a configuration file containing preset command-line options. By default, a text file called the system configuration file is used. However, you can specify any of several alternative files, called user configuration files, on the command line.

 Advantages

Configuration files offer several advantages. The system configuration file lets you standardize a set of options for all projects. User configuration files let you customize a set of options for each project.

With configuration files, you need not enter long strings of options on the command line. Also, if your system limits the length of a command line, configuration files let you specify more options than the command line can hold.

Using Configuration Files

Each record (line) in a configuration file holds one command-line option. For example, a configuration file might contain the following lines, which set defaults for the FIPS, MODE, and SQLCHECK options:

FIPS=YES

MODE=ANSI

SQLCHECK=SEMANTICS

Each Oracle Precompiler can have its own system configuration file. The name and location of the file are system- and language-specific. With Pro*C for example, on most UNIX systems, the file specification is ORACLE_HOME/proc16/pccc.cfg. If the file is not found, you get a warning but the precompiler continues processing.

There is only one system configuration file for a given language, but you can create any number of user configuration files. You use the new command-line option CONFIG to specify the name and location of a particular user configuration file, as follows:

... CONFIG=<filename> ...

You cannot nest configuration files. Therefore, you cannot specify the option CONFIG in a configuration file. Also, you cannot specify CONFIG inline.

 How Option Values Are Set

Many precompiler runtime options have built-in default values, which can be reset in a configuration file or on the command line. Command-line settings override user configuration file settings, which override system configuration file settings.

For example, the option MAXOPENCURSORS specifies the maximum number of cursors cached. Its built-in default value is 10. However, if the system configuration file specifies MAXOPENCURSORS=20, the default becomes 20. Then, if a user configuration file specifies MAXOPENCURSORS=30, the default becomes 30. Finally, if you specify MAXOPENCURSORS=40 on the command line, the default becomes 40. (Note, however, that an option entered inline overrides the same option entered on the command line.)

 ________________________________________

EXEC TOOLS Statements

EXEC TOOLS statements support the basic Oracle Toolset (Oracle Forms, Oracle Reports, and Oracle Graphics) by providing a generic way to handle get, set, and exception callbacks from user exits. The following discussion focuses on Oracle Forms but the same concepts apply to Oracle Reports and Oracle Graphics.

 What Is a User Exit?

A user exit is a host-language subroutine written by you and called by Oracle Forms to do special-purpose processing. You can embed SQL statements and PL/SQL blocks in your user exit, then precompile it as you would a host program.

When called by an Oracle Forms trigger, the user exit runs, then returns a status code to Oracle Forms. Your exit can display messages on the Oracle Forms status line, get and set field values, do high-speed computations, and manipulate Oracle data. For details, see the Oracle Forms Reference Manual, Vol 2.

 Writing a User Exit

Besides EXEC SQL, EXEC ORACLE, and host language statements, you can use the following EXEC TOOLS statements to write an Oracle Forms user exit:

•	SET

•	GET

•	SET CONTEXT

•	GET CONTEXT

•	MESSAGE

The EXEC TOOLS GET and SET statements replace the EXEC IAF GET and PUT statements used with earlier versions of Oracle Forms. Unlike IAF GET and PUT, however, TOOLS GET and SET accept indicator variables. The EXEC TOOLS MESSAGE statement replaces the message-handling function SQLIEM.

Note: COBOL and FORTRAN do not have a pointer datatype, so you cannot use the SET CONTEXT and GET CONTEXT statements in a Pro*COBOL or Pro*FORTRAN program.

 EXEC TOOLS SET

The EXEC TOOLS SET statement passes values from your user exit to Oracle Forms. Specifically, it assigns the values of host variables and constants to Oracle Forms variables and items. The values are displayed after the user exit returns control to the form.

To code the EXEC TOOLS SET statement, you use the syntax

EXEC TOOLS SET form_variable[, ...]

    VALUES ({:host_variable[:indicator] | constant}[, ...]);

where form_variable is an Oracle Forms field, parameter, system variable, or global variable, or a host variable (prefixed with a colon) containing the name of one of the foregoing items.

In the following Pro*C example, your user exit passes an employee name (with optional indicator) to Oracle Forms:

EXEC SQL BEGIN DECLARE SECTION;

    ...

    char  ename[20];

    short ename_ind;

EXEC SQL END DECLARE SECTION;

strcpy(ename, "MILLER");

ename_ind = 0;

EXEC TOOLS SET emp.ename VALUES (:ename:ename_ind);

In this example, emp.ename is an Oracle Forms block.field.

 EXEC TOOLS GET

The EXEC TOOLS GET statement passes values from Oracle Forms to your user exit. Specifically, it assigns the values of Oracle Forms variables and items to host variables. As soon as the values are passed, the user exit can use them for any purpose.

To code the EXEC TOOLS GET statement, you use the syntax

EXEC TOOLS GET form_variable[, ...]

    INTO :host_variable[:indicator][, ...];

where form_variable is an Oracle Forms field, parameter, system variable, or global variable, or a host variable containing the name of one of the foregoing items.

In the following Pro*C example, Oracle Forms passes an employee name from the block.field emp.ename to your user exit:

EXEC SQL BEGIN DECLARE SECTION;

    ...

    char  ename[20];

EXEC SQL END DECLARE SECTION;

EXEC TOOLS GET emp.ename INTO :ename;

 EXEC TOOLS SET CONTEXT

The EXEC TOOLS SET CONTEXT statement lets you save context information from one user exit call to another. SET CONTEXT names a host-language pointer variable that you can reference later in an EXEC TOOLS GET CONTEXT statement. The pointer variable points to a block of memory in which the context information is stored. With SET CONTEXT, you need not declare a global variable to hold the information.

To code the EXEC TOOLS SET CONTEXT statement, you use the syntax

EXEC TOOLS SET CONTEXT :host_pointer_variable BY context_name;

where context_name is an undeclared identifier or a character host variable that names the context area.

In the following Pro*C example, your user exit saves context information for later use:

EXEC SQL BEGIN DECLARE SECTION;

    ...

    char  context1[30];

EXEC SQL END DECLARE SECTION;

strcpy(context1, "This is context1")

EXEC TOOLS SET CONTEXT :context1 BY my_app1;

In this example, the context name my_app1 is an undeclared identifier. Note that in C, when a char array is used as an argument, the array name is synonymous with a pointer to that array.

 EXEC TOOLS GET CONTEXT

The EXEC TOOLS GET CONTEXT statement retrieves the value of a host-language pointer variable into your user exit. The pointer variable points to a block of memory in which context information is stored.

To code the EXEC TOOLS GET CONTEXT statement, you use the syntax

EXEC TOOLS GET CONTEXT context_name INTO :host_pointer_variable;

where context_name is an undeclared identifier or a character host variable that names the context area.

In the following Pro*C example, your user exit retrieves a pointer to context information saved earlier:

EXEC SQL BEGIN DECLARE SECTION;

    ...

    char  *ctx_ptr;

EXEC SQL END DECLARE SECTION;

EXEC TOOLS GET CONTEXT my_app1 INTO :ctx_ptr;

In this example, the context name my_app1 is an undeclared identifier.

 EXEC TOOLS MESSAGE

The EXEC TOOLS MESSAGE statement passes a message from your user exit to Oracle Forms. The message is displayed on the Oracle Forms message line after the user exit returns control to the form.

To code the EXEC TOOLS MESSAGE statement, you use the syntax

EXEC TOOLS MESSAGE message_text [severity_code];

where message_text is a quoted string or a character host variable, and the optional severity_code is an integer constant or host variable. The MESSAGE statement does not accept indicator variables.

In the following Pro*C example, your user exit passes an error message and severity code to Oracle Forms:

EXEC TOOLS MESSAGE 'Bad field name! Please reenter.' 15;

 ________________________________________

Two New SQLLIB Routines

The Oracle Precompiler runtime library, SQLLIB, contains two new routines. Using these routines can add power and flexibility to your precompiler applications.

 Obtaining the Text of SQL Statements

In many precompiler applications, it is convenient to know the text of the statement being processed, its length, and the SQL command (such as INSERT or SELECT) that it contains. This is especially true for applications that use dynamic SQL.

The new routine SQLGLS, which is part of the SQLLIB runtime library, returns this information. SQLGLS is available with all the Oracle Precompilers Release 1.6 except Pro*Ada. The SQLGLS routine returns

•	the text of the most recently parsed SQL statement

•	the length of the statement

•	a function code for the SQL command used in the statement

You can call SQLGLS after issuing a static SQL statement. With dynamic SQL Method 1, you can call SQLGLS after the SQL statement is EXECUTEd. With dynamic SQL Method 2, 3, or 4, you can call SQLGLS after the statement is PREPAREd. To call SQLGLS, you use the following syntax:

SQLGLS(SQLSTM, STMLEN, SQLFC)

Table 3 - 1 shows the host-language datatypes available for the parameters.

Parameter	Language	Datatype

------------------------------------------------------------------------

SQLSTM	C	char[n]char*

COBOL	PIC X(n)

FORTRAN	CHARACTER*n

Pascal	Fixchar[n]

PL/I	CHARACTER(n)

STMLEN, SQLFC	C	unsigned long*

COBOL	PIC S9(9) COMP

FORTRAN	INTEGER*4

Pascal	integer

PL/I,	FIXED BINARY(31)

Table 3 - 1.  Parameter Datatypes

All parameters must be passed by reference. For C, this means that they must be pointers. For other languages, this is usually the default parameter passing convention; you need take no special action.

The SQLSTM parameter is a blank-padded (not null-terminated) character buffer that holds the returned text of the SQL statement. Your program must statically declare the buffer or dynamically allocate memory for it.

The STMLEN parameter is a four-byte integer. Before calling SQLGLS, set this parameter to the actual size (in bytes) of the SQLSTM buffer. When SQLGLS returns, the SQLSTM buffer contains the SQL statement text blank padded to the length of the buffer. The STMLEN parameter returns the actual number of bytes in the returned statement text, not counting the blank padding.

The SQLFC parameter is a four-byte integer that returns the SQL function code for the SQL command in the statement. Table 3 - 2 shows the function code for each SQL command.

Code	SQL Function	Code	SQL Function

------------------------------------------------------------------------

01	CREATE TABLE	39	AUDIT

02	SET ROLE	40	NOAUDIT

03	INSERT	41	ALTER INDEX

04	SELECT	42	CREATE EXTERNAL DATABASE

05	UPDATE	43	DROP EXTERNAL DATABASE

06	DROP ROLE	44	CREATE DATABASE

07	DROP VIEW	45	ALTER DATABASE

08	DROP TABLE	46	CREATE ROLLBACK SEGMENT

09	DELETE	47	ALTER ROLLBACK SEGMENT

10	CREATE VIEW	48	DROP ROLLBACK SEGMENT

11	DROP USER	49	CREATE TABLESPACE

12	CREATE ROLE	50	ALTER TABLESPACE

13	CREATE SEQUENCE	51	DROP TABLESPACE

14	ALTER SEQUENCE	52	ALTER SESSION

15	(not used)	53	ALTER USER

16	DROP SEQUENCE	54	COMMIT

17	CREATE SCHEMA	55	ROLLBACK

18	CREATE  CLUSTER	56	SAVEPOINT

19	CREATE USER	57	CREATE CONTROL FILE

20	CREATE INDEX	58	ALTER TRACING

21	DROP INDEX	59	CREATE TRIGGER

22	DROP CLUSTER	60	ALTER TRIGGER

23	VALIDATE INDEX	61	DROP TRIGGER

24	CREATE PROCEDURE	62	ANALYZE TABLE

25	ALTER PROCEDURE	63	ANALYZE INDEX

26	ALTER TABLE	64	ANALYZE CLUSTER

27	EXPLAIN	65	CREATE PROFILE

28	GRANT	66	DROP PROFILE

29	REVOKE	67	ALTER PROFILE

30	CREATE SYNONYM	68	DROP PROCEDURE

31	DROP SYNONYM	69	(not used)

32	ALTER SYSTEM SWITCH LOG	70	ALTER RESOURCE COST

33	SET TRANSACTION	71	CREATE SNAPSHOT LOG

34	PL/SQL EXECUTE	72	ALTER SNAPSHOT LOG

35	LOCK TABLE	73	DROP SNAPSHOT LOG

36	(not used)	74	CREATE SNAPSHOT

37	RENAME	75	ALTER SNAPSHOT

38	COMMENT	76	DROP SNAPSHOT

Table 3 - 2.  SQL Function Codes

The length parameter STMLEN returns a zero if an error occurred. Some possible errors follow:

•	No SQL statement was parsed.

•	You passed an invalid parameter (for example, a negative length parameter).

•	An internal exception occurred in SQLLIB.

 Restrictions

SQLGLS does not return statements that contain the following commands:

•	CONNECT

•	COMMIT

•	ROLLBACK

•	RELEASE

•	FETCH

There are no SQL function codes for these statements.

Finding the Length of a VARCHAR Array

When the precompiler processes a VARCHAR declaration, the actual length of the array member in the generated structure can be longer than the declared length. For example, on a VAX/VMS system, the Pro*C declaration

VARCHAR my_varchar[12];

causes the C compiler to allocate an array member that is 14 bytes long. This is required to pad the total length of the structure to 16 bytes: 14 bytes for the padded array member and 2 bytes for the length member.

The new routine SQLVCP, which is part of the SQLLIB runtime library, returns the actual length (in bytes) of the array member. SQLVCP is available with all the Oracle Precompilers Release 1.6 except Pro*Ada.

You pass SQLVCP the declared length of the data field for a VARCHAR host variable or VARCHAR host pointer variable. Then, SQLVCP returns the total length of the VARCHAR array member, which includes any padding that your host-language compiler might have added.

To call SQLVCP, you use the following syntax:

SQLVCP(DATLEN, TOTLEN)

Both parameters are four-byte integers and must be passed by reference. Table 3 - 3 shows suggested host-language datatypes for the parameters. The next section shows how you might use SQLVCP in a Pro*C program.

Parameter	Language	Datatype

------------------------------------------------------------------------

DATLEN, TOTLEN	C	unsigned long*

COBOL	PIC S9(9) COMP

FORTRAN	INTEGER*4

Pascal	integer

PL/I	FIXED BINARY(31)

Table 3 - 3.  Parameter Datatypes

A Pro*C Sample Program

The following sample program shows how you might use SQLGLS and SQLVCP in a Pro*C application. The program declares a VARCHAR pointer, then uses the sqlvcp() function to determine the size required for the VARCHAR buffer. The program FETCHes employee names from the emp table and displays them. Finally, the program uses the sqlgls() function to display the SQL statement and its function code and length attributes.

#include <stdio.h>

EXEC SQL INCLUDE sqlca;

/* Fake a varchar pointer type. */

struct my_vc_ptr

{

    unsigned short len;

    unsigned char arr[32767];

};

typedef struct my_vc_ptr my_vc_ptr;

my_vc_ptr *vc_ptr;

EXEC SQL BEGIN DECLARE SECTION;

    varchar *names;    /* varchar can be in lower case */

    int      limit;    /* for use in FETCH FOR clause  */

    char    *username = "scott/tiger";

EXEC SQL END DECLARE SECTION;

void sql_error();

extern void sqlvcp();

extern void sqlgls();

main()

{

    unsigned long vcplen, function_code, padlen, buflen;

    int i;

    char stmt_buf[120];

    EXEC SQL WHENEVER SQLERROR DO sql_error();

    EXEC SQL CONNECT :username;

    printf("\nConnected.\n");

    /* Find number of rows in table. */

    EXEC SQL SELECT COUNT(*) INTO :limit FROM emp;

    /* Declare a cursor for the FETCH statement. */

    EXEC SQL DECLARE emp_name_cursor CURSOR FOR

        SELECT ename FROM emp;

    EXEC SQL FOR :limit OPEN emp_name_cursor;

    /* Set the desired DATA length for the VARCHAR. */

    vcplen = 12;

    /*Use SQLVCP to help find the length to malloc. */

    sqlvcp(&vcplen, &padlen);

    printf("Actual array length of varchar is %ld\n",padlen);

    /* Allocate the names buffer for names.

        Set the limit variable for the FOR clause. */

    names = (char *) malloc((sizeof (short) +

        (int) padlen) * limit);

    if (names == 0)

    {

        printf("Memory allocation error.\n");

        exit(1);

    }

    /* Set the maximum lengths before the FETCH.

       Note the "trick" to get an effective varchar *. */

    for (vc_ptr = names, i = 0; i < limit; i++)

    {

        vc_ptr->len = (short) padlen;

        vc_ptr = (my_vc_ptr *)((char *) vc_ptr +

            padlen + sizeof (short));

    }

    /* Execute the FETCH. */

    EXEC SQL FOR :limit FETCH emp_name_cursor INTO :names;

    /* Print the results. */

    printf("Employee names--\n");

    for (vc_ptr = names, i = 0; i < limit; i++)

    {

        printf("%.*s\n", vc_ptr->len, vc_ptr->arr);

        vc_ptr = (my_vc_ptr *)((char *) vc_ptr +

            padlen + sizeof (short));

    }

    /* Get statistics about the most recent

       SQL statement using SQLGLS. Note that

       the most recent statement in this example

       is not a FETCH, but rather "SELECT ENAME FROM EMP"

       (the cursor). */

    buflen = (long) sizeof (stmt_buf);

    /* The returned value should be 1, indicating no error. */

    if (buflen)

    {

        sqlgls(stmt_buf, &buflen, &function_code);

        /* Print out the SQL statement. */

        printf("The SQL statement was--\n%s\n", stmt_buf);

        /* Print the returned length. */

        printf("The statement length is %ld\n", buflen);

        /* Print the attributes. */

        printf("The function code is %ld\n", function_code);

        EXEC SQL COMMIT RELEASE;

        exit(0);

    }

    else

    {

        printf("The SQLGLS function returned an error.\n");

        EXEC SQL ROLLBACK RELEASE;

        exit(1);

    }

}

void sql_error()

{

    char err_msg[128];

    int buf_len, msg_len;

    EXEC SQL WHENEVER SQLERROR CONTINUE;

    buf_len = sizeof (err_msg);

    sqlglm(err_msg, &buf_len, &msg_len);

    printf("%.*s\n", msg_len, err_msg);

    EXEC SQL ROLLBACK RELEASE;

    exit(1);

}

 ________________________________________

AUTO_CONNECT Option

To increase compatibility with other precompilers, the Oracle Precompilers Release 1.6 let your program log on to the default database without using the CONNECT statement. Simply specify the new precompiler option AUTO_CONNECT on the command line. (You cannot specify it inline.)

When AUTO_CONNECT=YES, as soon as the precompiler encounters an executable SQL statement, your program tries to log on to Oracle automatically with the userid

<prefix><username>

where prefix is the value of the Oracle initialization parameter OS_AUTHENT_PREFIX (the default value is OPS$) and username is your operating system user or task name. For example, if the prefix is OPS$, your user name is TBARNES, and OPS$TBARNES is a valid Oracle userid, you log on to Oracle as user OPS$TBARNES.

When AUTO_CONNECT=NO (the default), you must use the CONNECT statement to log on to Oracle.

 ________________________________________

CHARF External Datatype

The Oracle Precompilers Release 1.6 introduce a new external datatype named CHARF, which is a fixed-length character string. You can use this new datatype in VAR and TYPE statements to equivalence host-language datatypes to the fixed-length ANSI datatype CHAR--regardless of the MODE setting.

When MODE=ANSI or MODE=ANSI14, specifying the external datatype CHAR in a VAR or TYPE statement equivalences the host-language datatype to the fixed-length ANSI datatype CHAR (Oracle external datatype code 96). However, when MODE=ANSI13 or MODE=ORACLE, the host-language datatype is equivalenced to the variable-length datatype VARCHAR2 (code 1), which might not be what you want.

Now you can always equivalence host-language datatypes to the fixed-length ANSI datatype CHAR. Simply specify the datatype CHARF in the VAR or TYPE statement. If you use CHARF, the host-language datatype is equivalenced to the fixed-length ANSI datatype CHAR even when MODE=ANSI13 or MODE=ORACLE.

 ________________________________________

New Pro*FORTRAN Option

With Pro*FORTRAN Release 1.6, the new option MULTISUBPROG controls the generation of COMMON statements and BLOCK DATA subprograms.

When MULTISUBPROG=YES (the default), the precompiler generates COMMON statements and BLOCK DATA subprograms like its Release 1.5 predecessor. Your host program does not access the COMMON blocks directly, but they allow two or more program units in the same precompilation unit to contain SQL statements.

However, the precompiler can give COMMON blocks defined in different output files the same name. If you link the files into an executable program, you get a link-time or runtime error. To solve this problem, you can rename the input files or override the default COMMON block names by specifying the option COMMON_NAME.

To avoid the problem, you can specify MULTISUBPROG=NO on the command line. When MULTISUBPROG=NO, the precompiler generates no COMMON statements or BLOCK DATA subprograms. If you specify MULTISUBPROG=NO for input files that contain more than one embedded SQL program unit, the precompiler generates the same declarations in each unit.

 Restriction

When MULTISUBPROG=NO, every program unit that contains executable SQL statements must have a Declare Section. Otherwise, you get a precompilation error.

 ________________________________________

Upgrading to Pro*C Version 2

If you are using Pro*C with C++ applications and want to continue doing so, upgrade to Pro*C Release 1.6. Otherwise, you can upgrade directly to Pro*C Version 2. Although Pro*C Version 2 does not support C++, it offers several new features, which are described below. For more information, see the Programmer's Guide to the Oracle Pro*C Precompiler.

 Preprocessor Support

Pro*C Version 2 includes a C preprocessor. So, for example, you can #define constants and macros, then use them in the EXEC SQL TYPE and VAR statements. Also, you can #include Pro*C header files such as sqlca.h, oraca.h, and sqlda.h.

 Host Structures

With Pro*C Version 2, you can declare a C structure that contains host variables and host arrays, then reference the struct in EXEC SQL statements. For example, you might declare the struct

struct

{

    int   emp_number;

    char  ename[10];

    float salary;

} emp_rec;

then reference it in a SELECT statement, as follows:

EXEC SQL SELECT empno, ename, sal INTO :emp_rec FROM ...

 Optional Declare Section

You need not declare host variables in a Declare Section. This is helpful if you use ANSI-C-style function prototypes. With earlier versions of Pro*C, to pass host variables to a function, you had to define the function as follows:

int  update_emp(emp_name, salary)

EXEC SQL BEGIN DECLARE SECTION;

    char  *emp_name;

    float  salary;

EXEC SQL END DECLARE SECTION;

{

    /* function body */

}

With Pro*C Version 2, you can define the function like this:

int  update_emp(char *emp_name, float salary)

{

    /* function body */

}

 Enhanced WHENEVER DO Statement

Now you can use the EXEC SQL WHENEVER DO statement to call functions that take arguments and return a value. For example, you might use the following statement to call an error-handling function that prints a message and returns a result value:

EXEC SQL WHENEVER SQLERROR

    DO result = sql_error("SQL Error Occurred");


APPENDIX A. OPERATING SYSTEM DEPENDENCIES

Some details of Oracle Precompiler programming vary from system to system. For convenience, this appendix collects all external references to system-specific information.

 ________________________________________

System-Specific Reference in Chapter 1

The reference below appears on page 1 - 15.

 USAGE IS BINARY Clause

With Pro*COBOL Release 1.6, you can specify USAGE IS BINARY when declaring host variables. As usual, the keywords USAGE IS are optional. For example, you might declare the following numeric item:

EXEC SQL BEGIN DECLARE SECTION END-EXEC.

    ...

    01  EMP-COUNT  PIC S9(4) BINARY VALUE ZERO.

EXEC SQL END DECLARE SECTION END-EXEC.

BINARY numbers are the most efficient numbers for computations because computers are binary machines. Typically, BINARY numbers are used for subscripts and counters.

On most systems, COMP numbers are equivalent to BINARY numbers.

Note: On platforms that support the command-line option COMP5, when COMP5=YES, the keyword BINARY must be followed on the same line by punctuation or another keyword.

 ________________________________________

System-Specific References in Chapter 3

The first reference below appears on page 3 - 2; the second reference appears on page 3 - 10.

 Using Configuration Files

Each Oracle Precompiler can have its own system configuration file. The name and location of the file are system- and language-specific. With Pro*C for example, on most UNIX systems, the file specification is ORACLE_HOME/proc16/pccc.cfg. If the file is not found, you get a warning but the precompiler continues processing.

 Finding the Length of a VARCHAR Array

When the precompiler processes a VARCHAR declaration, the actual length of the array member in the generated structure can be longer than the declared length. For example, on a VAX/VMS system, the Pro*C declaration

VARCHAR my_varchar[12];

causes the C compiler to allocate an array member that is 14 bytes long. This is required to pad the total length of the structure to 16 bytes: 14 bytes for the padded array member and 2 bytes for the length member.

The new routine SQLVCP, which is part of the SQLLIB runtime library, returns the actual length (in bytes) of the array member.


APPENDIX B. MIGRATING TO ORACLE7

Oracle7 conforms fully to the new ANSI/ISO SQL standard. As a result, Oracle7 and Oracle Version 6 behave differently in a few areas. By pointing out those areas, this appendix helps you migrate your application programs to Oracle7.

 ________________________________________

PL/SQL

 Using the VARCHAR Datatype

With PL/SQL Version 1 and Oracle Version 6, when declaring a variable of type VARCHAR or VARCHAR2, if you do not specify a maximum length, as in

DECLARE

   x  VARCHAR2;

the length defaults to 1. However, with PL/SQL Version 2 and Oracle7, you must specify a maximum length in the range 1 .. 32767, as follows:

DECLARE

   x  VARCHAR2(5);

Otherwise, you get a compilation error. You cannot use a constant or variable to specify the maximum length; you must use an integer literal.

 ________________________________________

Oracle Precompilers

 Fetching Nulls

When MODE=ORACLE (the default), if your program fetches a null into a host variable that lacks an indicator variable, Oracle Version 6 generates no error; however, Oracle7 generates the following error:

ORA-01405: fetched column value is NULL

Therefore, to migrate the program from Oracle Version 6 to Oracle7, you must either

•	re-precompile the program, specifying DBMS=V6 on the command line, which preserves the Oracle Version 6 behavior, or

•	revise the program by associating an indicator variable with every host variable into which nulls might be fetched

When DBMS=V6, regardless of the MODE setting, the following changes go into effect:

•	If you fetch a null into a host variable that lacks an indicator variable, Oracle7 generates no error (instead of generating error ORA-01405).

•	If you fetch a truncated value into a host variable that lacks an indicator variable, Oracle7 generates error ORA-01406 (instead of generating no error).

•	Oracle7 treats the following items like variable-length character values (instead of fixed-length character values):

•	string literals

•	local CHAR variables in a PL/SQL block

•	the return value of the function USER

•	If you process a multirow query that calls a SQL group function such as AVG or COUNT, the function is called at OPEN time (instead of at FETCH time).

When DBMS=V6, if you fetch nulls into a host array that lacks an indicator array, Oracle7 continues processing. So, always use an indicator array; otherwise, you cannot find the nulls. (The section "Using Indicator Variables" in Chapter 4 of the Programmer's Guide to the Oracle Precompilers shows how to find nulls and truncated values.)

 Fetching Truncated Values

When MODE=ORACLE, if your program fetches a truncated value into a host variable that lacks an indicator variable, Oracle7 generates no error; however, Oracle Version 6 generates the following error:

ORA-01406: fetched column value was truncated

Therefore, to migrate the program from Oracle Version 6 to Oracle7, you must either

•	re-precompile the program, specifying DBMS=V6 on the command line, which preserves the Oracle Version 6 behavior, or

•	revise the program by associating an indicator variable with every host variable into which truncated values might be fetched

When DBMS=V6, if you fetch a truncated value into a host array that lacks an indicator array, Oracle7 stops processing and sets SQLERRD(3) in the SQLCA to the number of rows processed. No other information is available. So, always use an indicator array; otherwise, you cannot find the truncated values and their original lengths.

 Handling Character Strings

Some Oracle Precompiler programs expect variable-length character strings (of type VARCHAR2, for example). However, by default, Oracle7 uses fixed-length character strings of type CHAR. This affects string-comparison semantics. For details, see the section "VARCHAR2 versus CHAR" in Chapter 3 of the Programmer's Guide to the Oracle Precompilers.

If your program expects variable-length character strings, specify DBMS=V6 on the precompiler command line. Among other things, this preserves Oracle Version 6 string-comparison semantics.