Programming Guidelines

The following sections (arranged alphabetically for quick reference) deal with coding conventions, embedded SQL syntax, and other features that are specific to host PL/I programs.

Comments

You can place PL/I comments (/* ... */) in a SQL statement wherever blanks can be placed (except between the keywords EXEC SQL and within quoted literals). You can also place ANSI SQL-style comments (-- ...) in SQL statements at the end of a line if the statement continues over multiple lines. However, you cannot place an ANSI SQL-style comment at the end of the last line, following the semicolon that terminates the SQL statement. The following example shows both comment styles:

Continuation Lines

You can continue SQL statements from one line to the next, even in the middle of a quoted string literal. For example, the following statement inserts the string value 'PURCHASING' into the DNAME column:

EXEC SQL INSERT INTO dept (deptno, dname) VALUES (50, 'PURCHASING');

Embedded SQL Syntax

When using SQL commands in your host program, you precede the SQL command with the EXEC SQL clause. Embedded SQL syntax is described in the Oracle7 Server SQL Language Reference Manual. The precompiler translates all EXEC SQL statements into calls to the runtime library SQLLIB.

Host Variable Names

Host variable names must consist only of letters and digits, and must begin with a letter. They can be any length, but only the first 31 characters are significant. The PL/I compiler normally converts variable names to upper case. Check your compiler documentation for the rules for forming PL/I identifiers on your system.

MAXLITERAL Default Value

The MAXLITERAL precompiler option lets you specify the maximum length of string literals generated by the precompiler, so that compiler limits are not exceeded. The MAXLITERAL default value is 256 for Pro*PL/I. But, you might have to specify a lower value if your PL/I compiler cannot handle string literals of that length.

Nulls

In SQL, a NULL column "value" is simply one that is missing, unknown, or inapplicable; it equates neither to zero nor to a blank. Therefore, use either the NVL function, the IS [NOT] NULL operator, or indicator variables when selecting or testing for nulls, and use indicator variables to insert nulls.

In PL/I, the built-in function NULL() simply returns a null pointer value; it is not related to the SQL NULL value in any way.

Operators, Logical

The logical operators are different in SQL and PL/I, as the table below shows.

SQL Operators

PL/I Operators

NOT

^ (prefix)

AND

&

OR

|
&:
|:
^ (infix)

PL/I logical operators are not allowed in SQL statements.

Operators, Relational

The relational operators are similar in SQL and PL/I, as the following table shows:

SQL Operators

PL/I Operators

=

=

<>, !=, ^=

^=

>

>

<

<

>=

>=

<=

<=
^<
^>

PL/I Versions

The Pro*PL/I Precompiler supports the standard implementation of PL/I for your operating system. See your Oracle installation or user's guide for more information.

Preprocessor

The Pro*PL/I Precompiler does not support PL/I preprocessor directives. Do not use preprocessor directives, even %INCLUDE, within EXEC SQL blocks. You can, of course, use whatever PL/I preprocessor facilities your compiler makes available in pure PL/I code. Code included by a PL/I preprocessor is not precompiled.

Quotation Marks and Apostrophes

In SQL, quotation marks are used to delimit identifiers containing special or lowercase characters, as in

EXEC SQL DELETE FROM "Emp2" WHERE DEPTNO = 30;

Both SQL and PL/I use apostrophes to delimit strings, as in the PL/I statements

Scope of Variables

Host variables used in embedded SQL statements follow the normal scoping rules of PL/I. Any host variable that you want to use in an embedded SQL statement must also be accessible to PL/I statements in the same block.

SQL Statement Terminator

End all embedded SQL statements with a semicolon, as shown in the following examples:

Statement Labels

Required Declarations and SQL Statements

This section describes the variable declarations and SQL statements that must be present in every Pro*PL/I source file.

The Declare Section

You must declare all variables that will be used in embedded SQL statements inside a SQL Declare Section, and the Declare Section must physically precede the embedded SQL statements that use the declared host variables. This section can be placed outside a procedure block or within a procedure block or a begin block. Each block that contains a SQL statement must have a Declare Section in scope, even if the statement does not contain host variables. In this case, the Declare Section is empty. A common solution is to place a Declare Section near the start of the main procedure block. The following example shows a SQL Declare Section in which two host variables are declared.

You must declare PL/I variables that are to be used as host variables inside a SQL Declare Section. You should not declare PL/I variables that are not to be used as host variables in a SQL Declare Section. Always use the standard PL/I declaration syntax to declare variables.

Using the INCLUDE Statement

The INCLUDE statement lets you copy files into your host program, as the following example shows:

You can INCLUDE any file. When you precompile your Pro*PL/I program, each EXEC SQL INCLUDE statement is replaced by a copy
of the file named in the statement.

If your system uses file extensions but you do not specify one, the Pro*PL/I Precompiler assumes the default extension for source files (usually PLI). The default extension is system dependent. Check the Oracle installation or user's guide for your system.

You can set a directory path for INCLUDEd files by specifying the precompiler option

INCLUDE=PATH

where path defaults to the current directory. The precompiler searches first in the current directory, then in the directory specified by INCLUDE, and finally in a directory for standard INCLUDE files. So, you need not specify a directory path for standard files such as the SQLCA and ORACA. However, you must use INCLUDE to specify a directory path for nonstandard files unless they are stored in the current directory.

You can specify more than one path on the command line, as follows:

INCLUDE=PATH1 INCLUDE=PATH2 ...

The precompiler searches first in the current directory, then in the directory named by PATH1, then in the directory named by PATH2, and finally in the directory for standard INCLUDE files.

The syntax for specifying a directory path is system specific. Check the Oracle installation or user's guide for your system.

Caution

Remember, the precompiler searches for a file in the current directory first--even if you specify a directory path. So, if the file you want to INCLUDE resides in another directory, make sure no file with the same name resides in the current directory.

Note: Don't confuse the SQL command INCLUDE with the PL/I directive %INCLUDE. You can use %INCLUDE to copy in the SQLCA, ORACA, or SQLDA because they contain no embedded SQL. But, for a file that contains embedded SQL, you must use the EXEC SQL INCLUDE statement to ensure that the file's contents are examined and processed by the precompiler. For improved readability, it is recommended that you use the SQL INCLUDE command to include all files that pertain to embedded SQL, even those that contain only PL/I statements.

Event and Error Handling

Release 1.6 provides forward and backward compatibility with regard to checking the outcome of executing SQL statements. The SQLCA data structure and SQLCODE status variable can be used in the same manner as in previous releases. The SQLSTATE status variable is introduced in release 1.6. There are restrictions on using SQLCA, SQLCODE, and SQLSTATE depending on how you set the MODE and DBMS options. For more information, see Chapter 2, "Error Handling and Diagnostics".

Host Variables

Host variables are the key to communication between your host program and Oracle. Typically, a host program inputs data to Oracle, and Oracle outputs data to the program. Oracle stores input data in database columns, and stores output data in program host variables.

Declaring Host Variables

You declare a host variable in the SQL Declare Section according to the rules of PL/I, specifying a PL/I datatype supported by Oracle. The PL/I datatype must be compatible with that of the host variable's source or target database column.

The following table describes the PL/I datatypes you can specify in the Declare Section:

PL/I Datatype

Description

CHARACTER (N)

string of length N

CHARACTER (N) VARYING

string of maximum length N

FIXED BINARY (15)

short signed integer

FIXED BINARY (31)

signed integer

FIXED DECIMAL (N)

decimal number of N digits

FIXED DECIMAL (P,S)

decimal with precision and scale

FLOAT BINARY (N)

floating point number

FLOAT DECIMAL (N)

float of maximum length N

You can also declare one-dimensional arrays of each of these types.

Note: Oracle supports only the PL/I datatypes in the table above.

Structures

You can declare structures in the SQL Declare Section, and can use individual structure elements as host variables. The elements of the structure must be of datatypes allowed in a Declare Section. You cannot reference the whole structure as a host variable. This means, for example, that if you have a structure containing three elements, you cannot SELECT three columns into the elements by referencing the top level of the structure. You must reference each element by name as a host variable.

You cannot use the LIKE attribute with structure declarations in the SQL Declare Section.

An Example

In the following example, you declare five host variables for use later in the Pro*PL/I program:

Special Requirements

You must explicitly declare host variables in the Declare Section of the procedure block that uses them in SQL statements. Thus, variables passed to a subroutine or function and used in SQL statements within the routine must be declared in the Declare Section. An example follows:

small signed integer
signed integer
fixed-point number
fixed-point number
floating-point number
floating-point number
string3variable-length string

DATE4LONG
RAW(X)1LONG RAW
ROWID5MLSLABEL6

CHARACTER (N)
CHARACTER (N) VARYING

string
variable-length string

Notes:
1.X ranges from 1 to 255. Y ranges from 1 to 2000. 1 is the default value.
2.P ranges from 2 to 38. S ranges from -84 to 127.
3.Strings can be converted to NUMBERs only if they contain convertible characters (`0' to `9', `.', `+', `-', `E', `e'). Note that the NLS settings in effect on your system might change the decimal point from `.' to `,'.
4.When converted as a string type, the default size of a DATE depends on the NLS settings in effect on your system. When converted as a binary type, the size is 7 bytes.
5.When converted as a string type, a ROWID requires between 18 and 256 bytes. When converted as a binary value, the length is system dependent.
6.Trusted Oracle7 only.

IndicatorVariables

You use indicator variables to provide information to Oracle about the status of a host variable, or to monitor the status of data that is returned from Oracle. An indicator variable is always associated with a host variable.

Declaring Indicator Variables

An indicator variable must be explicitly declared in the SQL Declare Section as a 2-byte signed integer (FIXED BINARY(15)) and must not be an Oracle reserved word. In the following example, you declare
two indicator variables (the names SAL_IND and COMM_IND are arbitrary):

Referencing Indicator Variables

You can use indicator variables in the VALUES, INTO, and SET clauses. In SQL statements, an indicator variable must be prefixed with a colon and appended to its associated host variable. In PL/I statements, an indicator variable must neither be prefixed with a colon nor appended to its associated host variable. An example follows:

Oracle Restrictions

When DBMS=V6, Oracle does not issue an error if you SELECT or FETCH a null into a host variable that is not associated with an indicator variable. However, when DBMS=V7, if you SELECT or FETCH a null into a host variable that has no indicator, Oracle issues the following error message:

ORA-01405: fetched column value is NULL

ANSI Requirements

When MODE=ORACLE, if you SELECT or FETCH a truncated column value into a host variable that is not associated with an indicator variable, Oracle issues the following error message:

ORA-01406: fetched column value was truncated

However, when MODE={ANSI | ANSI14 | ANSI13}, no error is generated.

Host Arrays

Host arrays can boost performance by letting you manipulate an entire collection of data items with a single SQL statement. With few exceptions, you can use host arrays wherever scalar host variables are allowed. And, you can associate an indicator array with any host array.

Declaring Host Arrays

You declare and dimension host arrays in the Declare Section. In the following example, you declare three host arrays and dimension them with 50 elements:

Referencing Host Arrays

If you use multiple host arrays in a single SQL statement, their sizes should be the same. This is not a requirement, however, because the Pro*PL/I Precompiler always uses the smallest array size for the SQL operation.

You do not need to process host arrays in a loop. Simply use the unsubscripted array names in your SQL statement. Oracle treats a SQL statement containing host arrays of dimension n like the same statement executed n times with n different scalar variables. For more information about using host arrays, see Chapter 8 of the Programmer's Guide to the Oracle Precompilers.

Using Indicator Arrays

You can use indicator arrays to assign nulls to input host arrays, and to detect nulls or truncated values in output host arrays. The following example shows how to INSERT using indicator arrays:

Oracle Restrictions

Mixing scalar host variables with host arrays in the VALUES, SET, INTO, or WHERE clause is not allowed. If any of the host variables is an array, all must be arrays.

Also, you cannot use host arrays with the CURRENT OF clause in an UPDATE or DELETE statement.

When DBMS=V6, no error is generated if you SELECT or FETCH nulls into a host array that is not associated with an indicator array. So, when doing array SELECTs and FETCHes, always use indicator arrays. That way, you can test for nulls in the associated output host array.

When DBMS=V7, if you SELECT or FETCH a null into a host variable that is not associated with an indicator variable, Oracle stops processing, sets sqlerrd[3] to the number of rows processed, and issues the following error message:

ORA-01405: fetched column values is NULL

ANSI Restrictions and Requirements

When MODE={ANSI | ANSI13 | ORACLE}, array SELECTs and FETCHes are allowed. You can flag the use of arrays by specifying the FIPS precompiler option.

When MODE=ORACLE, if you SELECT or FETCH a truncated column value into a host array that is not associated with an indicator array, Oracle stops processing, sets sqlerrd[3] to the number of rows processed, and issues the following error message:

ORA-01406: fetched column value was truncated

When MODE=ANSI13, Oracle stops processing and sets sqlerrd[3] to the number of rows processed but no error is generated.

When MODE=ANSI, Oracle does not consider truncation an error.

Pointers as Host Variables

You cannot use PL/I BASED variables in SQL statements. Also, PL/I pointers cannot be directly referenced in SQL statements. This restriction includes reference to structure elements using pointers. That is, you cannot declare a BASED structure in a Declare Section, allocate the structure, and then refer to the elements with respect to the pointer in a SQL statement.

The following code is accepted by the precompiler, but does not execute correctly (an Oracle error message is issued at runtime):

VARYINGReturning Nulls to a CHARACTER (N)

Oracle automatically sets the length of a CHARACTER (N) VARYING output host variable. If you SELECT or FETCH a null into a CHARACTER (N) VARYING variable, Oracle sets the length to zero. The variable itself remains unchanged.

Handling Character Data

This section explains how the Pro*PL/I Precompiler handles character host variables. There are two host variable character types:

CHARACTER (N) variables

CHARACTER (N) VARYING variables

Effects of the MODE Option

The MODE option, which you can specify on the command line, determines how the Pro*PL/I Precompiler treats data in character arrays and strings. The MODE option allows your program to take advantage of ANSI fixed-length strings, or to maintain compatibility with previous versions of Oracle and Pro*PL/I.

MODE=Oracle

MODE=ANSI

Note: The MODE option does not affect the way Pro*PL/I handles CHARACTER (N) VARYING host variables.

These choices are referred to in this section as Oracle mode and ANSI mode, respectively. Oracle is the default mode, and is in effect when the MODE option is not specified on the command line. When discussing character handling, MODE={ANSI13 | ANSI14} is effectively equivalent to Oracle mode.

The MODE option affects the way character data is treated on input from your host variables to the Oracle table.

On Input

When the mode is Oracle, the program interface strips trailing blanks up to the first non-blank character. After stripping the blanks, the value is sent to the database. If you are inserting into a fixed-length CHAR column, trailing blanks are then re-appended to the data by Oracle, up to the length of the database column. If you are inserting into a variable-length VARCHAR2 column, Oracle never appends blanks.

When the mode is ANSI, trailing blanks in the CHARACTER host variable are never stripped.

Be sure that the input host variable does not contain characters other than blanks after the data. For example, null characters are not stripped, and are inserted into the database. The easiest way to insure this in PL/I is to always use CHARACTER(N) host variables for character data. When values are read into these variables, or assigned to them, PL/I appends blanks to the data, up to the length of the variable. The following example illustrates this:

If you precompile this example in Oracle mode, the values 'MILLER' and 'SALES' are sent to the database, since the program interface strips the trailing blanks that PL/I appends to the CHARACTER host variables. If you precompile this example in ANSI mode, the trailing blanks that PL/I appends are not stripped, and the values 'MILLER ' (four trailing blanks) and 'SALES ' (three trailing blanks) are sent to the database.

In ANSI mode, if the JOB column in the EMP table is defined as CHAR(10), the resulting value in the JOB column is 'SALES ' (five trailing blanks). If the JOB column is defined as VARCHAR2(10), the resulting value in the column is 'SALES ' (three trailing blanks, since the host variable is a CHARACTER (8). This might not be what you want, so be careful when inserting data into VARCHAR2 columns using programs that were precompiled in ANSI mode.

On Output

The MODE option does not affect the way that character data are treated on output. When you use a CHARACTER (N) variable as an output host variable, the program interface always blank-pads it. In our example, when your program fetches the string 'MILLER' from the database, EMP_NAME contains the value 'MILLER ' (with 4 blanks). This character string can be used without change as input to another SQL statement.

The Oracle Datatypes

Oracle recognizes two kinds of datatypes: internal and external. Internal datatypes specify the formats used by Oracle to store column values in database tables, as well as the formats used to represent pseudocolumn values. External datatypes specify the formats used to store values in input and output host variables. For descriptions of the Oracle datatypes, see Chapter 3 of the Programmer's Guide to the Oracle Precompilers.

Internal Datatypes

For values stored in database columns, Oracle uses the following internal datatypes, which were chosen for their efficiency:

Name

Code

Description

VARCHAR2

1

2000-byte, variable-length
character string

NUMBER

2

fixed or floating point number

LONG

8

231-1 byte, variable-length
character string

ROWID

11

operating-system dependent

DATE

12

7-byte, fixed-length date/time value

RAW

23

255-byte, variable-length binary data

LONG RAW

24

231-1 byte, variable-length binary data

CHAR

96

255-byte, fixed-length character string

MLSLABEL

106

variable-length binary data, 2-5 bytes

These internal datatypes can be quite different from PL/I datatypes. For example, PL/I has no equivalent to the NUMBER datatype, which was specially designed for portability.

SQL Pseudocolumns and Functions

SQL recognizes the following pseudocolumns and parameterless functions, which return specific data items:

Pseudocolumn

Corresponding Internal Datatype

Code

NEXTVAL

NUMBER

2

CURRVAL

NUMBER

2

LEVEL

NUMBER

2

ROWNUM

NUMBER

2

ROWID

ROWID

11

ROWLABEL

MLSLABEL

106

Function

Corresponding Internal Datatype

Code

USER

VARCHAR2

1

UID

NUMBER

2

SYSDATE

DATE

12

Pseudocolumns are not actual columns in a table, but, like columns, their values must be SELECTed from a table.

You can reference SQL pseudocolumns and functions in SELECT, INSERT, UPDATE and DELETE statements.

External Datatypes

As the table below shows, the external datatypes include all the internal datatypes plus several datatypes found in popular host languages. For example, the INTEGER external datatype refers to a PL/I FIXED BINARY(31).

Name

Code

Description

VARCHAR2

1

variable-length character string

NUMBER

2

number

INTEGER

3

signed integer

FLOAT

4

floating point number

STRING

5

variable-length null-terminated character string

VARNUM

6

variable-length number

DECIMAL

7

COBOL or PL/I packed decimal

LONG

8

fixed-length character string

VARCHAR

9

variable-length character string

ROWID

11

binary value

DATE

12

fixed-length date/time value

VARRAW

15

variable-length binary data

RAW

23

fixed-length binary data

LONG RAW

24

fixed-length binary data

UNSIGNED

68

unsigned integer

DISPLAY

91

COBOL numeric character string

LONG VARCHAR

94

variable-length character string

LONG VARRAW

95

variable-length binary data

CHAR

1

96

variable-length character string,
if DBMS=V6
fixed-length character string,
if DBMS=V7

CHARZ

97

fixed-length null-terminated
character string

MLSLABEL

106

variable-length binary data

Datatype Conversion

At precompile time, an external datatype is assigned to each host variable in the Declare Section. For example, the precompiler assigns the Oracle FLOAT external datatype to host variables of type FLOAT DECIMAL.

At run time, the datatype code of every host variable used in a SQL statement is passed to Oracle. Oracle uses the codes to convert between internal and external datatypes.

Before assigning a SELECTed column (or pseudocolumn) value to an output host variable, Oracle must convert the internal datatype of the source column to the datatype of the host variable. Likewise, before assigning or comparing the value of an input host variable to a column, Oracle must convert the external datatype of the host variable to the internal datatype of the target column.

Conversions between internal and external datatypes follow the usual data conversion rules. For example, you can convert a CHAR value of '1234' to a FIXED BINARY(15) value. But, you cannot convert a CHAR value of '65543' (number too large) or '10F' (number not decimal) to a FIXED BINARY(15) value. Likewise, you cannot convert a CHARACTER(N) VARYING value that contains alphabetic characters to a NUMBER value.

For more information about datatype conversion, see Chapter 3 of the Programmer's Guide to the Oracle Precompilers.

Datatype Equivalencing

Datatype equivalencing lets you control the way Oracle interprets input data, and the way Oracle formats output data. On a variable-by-variable basis, you can equivalence supported PL/I datatypes to Oracle external datatypes.

Host Variable Equivalencing

By default, the Pro*PL/I Precompiler assigns a specific external datatype to every host variable. The following table shows the default assignments:

Host Datatype

External
Datatype

Code

CHARACTER (N)

VARCHAR2

1
when MODE!=ANSI

FIXED BINARY (15)

INTEGER

3
when MODE=ANSI

FIXED BINARY (31)

INTEGER

3

FLOAT BINARY (N)

FLOAT

4

FLOAT DECIMAL (P,S)

FLOAT

4

FIXED DECIMAL (N)

DECIMAL

7

FIXED DECIMAL (P,S)

DECIMAL

7

CHARACTER (N) VARYING

VARCHAR

9

Using the VAR statement, you can override the default assignments by equivalencing host variables to Oracle external datatypes in the Declare Section. The syntax you use is

Is an input or output host variable (or host array) declared earlier in the Declare Section.

type_name

Is the name of a valid external datatype.

length

Is an integer literal specifying a valid length in bytes.

precision, scale

Specify those quantities where required by the type.

Host variable equivalencing is useful in several ways. For example, you can use it when you want Oracle to store but not interpret data. Suppose you want to store a host array of 4-byte integers in a RAW database column. Simply equivalence the host array to the RAW external datatype, as follows:

With host arrays, the length you specify must match exactly the buffer size required to hold the array. So, you specify a length of 200, which is the buffer size required to hold 50 4-byte integers.

The following external datatypes cannot be used in the VAR command in Pro*PL/I:

NUMBER (use VARNUM instead)

UNSIGNED (not supported in Pro*Pl/I)

DISPLAY (COBOL only)

CHARZ (C only)

Embedding PL/SQL

The Pro*PL/I Precompiler treats a PL/SQL block like a single embedded SQL statement. So, you can place a PL/SQL block anywhere in a host program that you can place a SQL statement.

To embed a PL/SQL block in your host program, you simply declare the variables to be shared with PL/SQL, and bracket the PL/SQL block with the keywords EXEC SQL EXECUTE and END-EXEC.

Using Host Variables with PL/SQL

Inside a PL/SQL block, host variables are treated as global to the entire block, and can be used anywhere a PL/SQL variable is allowed. Like host variables in a SQL statement, host variables in a PL/SQL block must be prefixed with a colon. The colon sets host variables apart from PL/SQL variables and database objects.

Using Indicator Variables with PL/SQL

In a PL/SQL block, you cannot refer to an indicator variable by itself; it must be appended to its associated host variable. And, if you refer to a host variable with its indicator variable, you must always refer to it that way in the same block.

Handling Nulls

When entering a block, if an indicator variable has a value of -1, PL/SQL automatically assigns a NULL value to the host variable. When exiting the block, if a host variable has a NULL value, PL/SQL automatically assigns a value of -1 to the indicator variable.

Handling Truncated Values

PL/SQL does not raise an exception when a truncated string value is assigned to a host variable. However, if you use an indicator variable, PL/SQL sets it to the original length of the string.

SQLCHECK

You must use the SQLCHECK=SEMANTICS option when precompiling a program with an embedded PL/SQL block. You may also want to use the USERID option. See the Programmer's Guide to the Oracle Precompilers for more information.

Connecting to Oracle

Your host program must log in to Oracle before it can manipulate data. To log in, use the SQL connect statement

EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD;

where "USERNAME" and "PASSWORD" are PL/I character strings containing the user ID and the Oracle password. Or, you can use the SQL statement

EXEC SQL CONNECT :USER_PASSWORD;

where "USER_PASSWORD" is a PL/I character variable containing the user ID, a slash (/), and the password. The following examples show both ways of connecting to Oracle:

Automatic Logins

You can automatically log in to Oracle with the user ID

prefixusername

where username is the current operating system user or task name, prefixusername is a valid Oracle username, and prefix is a value determined by the Oracle initialization parameter OS_AUTHENT_PREFIX. For backward compatibility, prefix defaults to OPS$. For more information about operating system authentication, see the Oracle7 Server Administrator's Guide.

This automatically connects you as user prefixusername. For example, if your operating system username is RHILL, and OPS$RHILL is a valid Oracle username, connecting with '/' automatically logs you on to Oracle as user OPS$RHILL.

Concurrent Logins

Your application can use SQL*Net to concurrently access any combination of remote and local databases, or make multiple connections to the same database. In the following example, you connect to two non default databases concurrently: