1 Writing a Pro*PL/1 Program

Comments

You can place PL/1 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:

EXEC SQL SELECT ENAME, SAL /* name and salary */
 INTO :EMP_NAME, :SALARY -- output host variables
 FROM EMP
 WHERE DEPTNO = :DEPT_NUMBER;

You cannot nest comments.

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 Oracle database version 7 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/1 compiler normally converts variable names to upper case. Check your compiler documentation for the rules for forming PL/1 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/1. But, you might have to specify a lower value if your PL/1 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/1, 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/1, as the following table.

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

Operators, Relational

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

PL/1 Versions

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

Preprocessor

The Pro*PL/1 Precompiler does not support PL/1 preprocessor directives. Do not use preprocessor directives, even %INCLUDE, within EXEC SQL blocks. You can, of course, use whatever PL/1 preprocessor facilities your compiler makes available in pure PL/1 code. Code included by a PL/1 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/1 use apostrophes to delimit strings, as in the PL/1 statements

DCL NAME CHAR(20) VARYING; 
... 
NAME = 'Pro*PL/1'; 
PUT SKIP LIST (NAME);

or the SQL statement

EXEC SQL SELECT ENAME, SAL FROM EMP WHERE JOB = 'MANAGER';

Scope of Variables

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

SQL Statement Terminator

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

EXEC SQL DELETE FROM EMP WHERE DEPTNO = 20;
EXEC SQL SELECT ENAME, EMPNO, SAL, COMM 
 INTO :EMP_NAME, :EMP_NUMBER, :SALARY, :COMMISSION 
 FROM EMP 
 WHERE JOB LIKE 'SALES%' 
 AND COMM IS NOT NULL;

Statement Labels

You can associate standard PL/1 statement labels (for example, LABEL_NAME:) with SQL statements. The Pro*PL/1 Precompiler can handle labels up to 31 characters long.

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.

EXEC SQL BEGIN DECLARE SECTION;
 DCL EMP_NAME CHAR(20) VARYING,
 SALARY FLOAT(6);
EXEC SQL END DECLARE SECTION;

The only statements that are allowed in a Declare Section are

• host and indicator variable declarations

• EXEC SQL INCLUDE statements

• EXEC SQL VAR statements

• comments

A Pro*PL/1 source file can have multiple Declare Sections.

You must declare PL/1 variables that are to be used as host variables inside a SQL Declare Section. You should not declare PL/1 variables that are not to be used as host variables in a SQL Declare Section. Always use the standard PL/1 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:

/* copy in the SQL Communications Area (SQLCA) */ 
EXEC SQL INCLUDE SQLCA; 
... 
/* copy in the Oracle Communications Area (ORACA) */ 
EXEC SQL INCLUDE ORACA;

You can INCLUDE any file. When you precompile your Pro*PL/1 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/1 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.

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".

Declaring Host Variables

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

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

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

EXEC SQL BEGIN DECLARE SECTION;
 DCL USERNAME CHAR(10) VARYING INIT('SCOTT'), 
 PASSWORD CHAR(10) VARYING INIT('TIGER'), 
 EMP_NUMBER FIXED BINARY(31), 
 SALARY FIXED DECIMAL(7,2), 
 DEPTNO FIXED DECIMAL(2) INIT(10); 
EXEC SQL END DECLARE SECTION;

EXEC SQL BEGIN DECLARE SECTION; 
 DCL (VAR1, VAR2, VAR3) FIXED DECIMAL; 
EXEC SQL END DECLARE SECTION;

EXEC SQL BEGIN DECLARE SECTION; 
 DCL VAR1 FIXED DECIMAL, 
 VAR2 FIXED DECIMAL, 
 VAR3 FIXED DECIMAL; 
EXEC SQL END DECLARE SECTION; 

PROCEDURE TEST: OPTIONS(MAIN) 
 
 DCL EMPNO FIXED BIN(31), 
 EMP_NAME CHAR(10) VARYING, 
 SALARY FLOAT(6); 
..
. EMPNO = 7499; 
 CALL GET_SALARY(EMPNO, EMP_NAME, SALARY); 
..
. 
GET_SALARY: PROCEDURE(EMPNO, NAME, SALARY); 
EXEC SQL BEGIN DECLARE SECTION; 
 DCL EMPNO FIXED BIN(31), 
 NAME CHAR(10) VARYING, 
 SALARY FLOAT(6); 
EXEC SQL END DECLARE SECTION; 
 
 EXEC SQL SELECT ENAME, SAL 
 INTO :EMP_NAME, :SALARY 
 FROM EMP 
 WHERE EMPNO = :EMPNO; 
END GET_SALARY;

Referencing Host Variables

A host variable must be prefixed with a colon (:) in SQL statements, but must not be prefixed with a colon in PL/1 statements, as the following example shows:

EXEC SQL BEGIN DECLARE SECTION; 
 DCL (EMP_NUMBER, SAL) FIXED DECIMAL(7,2); 
EXEC SQL END DECLARE SECTION; 
PUT SKIP LIST ('Enter employee number: '); 
GET EDIT (EMP_NUMBER) (F(4)); 
EXEC SQL SELECT SAL 
 INTO :SAL 
 FROM EMP 
 WHERE EMPNO = :EMP_NUMBER;

Though it might be confusing, you can give a host variable the same name as an Oracle table or column, as the previous example showed (SAL).

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):

EXEC SQL BEGIN DECLARE SECTION; 
 DCL EMP_NAME CHAR(10) VARYING, 
 (SALARY, COMMISSION) FIXED DECIMAL(7,2), 
 /* indicator variables */ 
 (SAL_IND, COMM_IND) FIXED BINARY(15); 
EXEC SQL END DECLARE SECTION; 

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/1 statements, an indicator variable must neither be prefixed with a colon nor appended to its associated host variable. An example follows:

EXEC SQL SELECT sal INTO :SALARY :SAL_IND FROM emp 
  WHERE empno = :EMP_NUMBER;
IF SAL_IND = -1 THEN 
 PUT SKIP LIST('Salary is null.'); 

To improve readability, you can precede any indicator variable with the optional keyword INDICATOR. You must still prefix the indicator variable with a colon. The correct syntax is

:HOST_VARIABLE INDICATOR :INDICATOR_VARIABLE

which is equivalent to

:HOST_VARIABLE :INDICATOR_VARIABLE

You can use both forms of expression in your host program.

Restriction

Indicator variables cannot be used in the WHERE clause to search for nulls. For example, the following DELETE statement triggers an Oracle error at run time:

/* Set indicator variable. */
COMM_IND = -1;
EXEC SQL DELETE FROM emp WHERE comm = :COMMISSION :COMM_IND; 

The correct syntax follows:

EXEC SQL DELETE FROM emp WHERE comm IS NULL;

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.

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:

EXEC SQL BEGIN DECLARE SECTION;
 DCL EMP_NAME(50) CHAR(10),
 (EMP_NUMBER(50), SALARY(50)) FIXED DECIMAL(7,2);
EXEC SQL END DECLARE SECTION;

Restrictions

You cannot specify a lower dimension bound for host arrays. For example, the following declarations are invalid:

EXEC SQL BEGIN DECLARE SECTION;
 DCL EMP_NAME(26:50) CHAR(10), 
 (EMP_NUMBER(26:50), SALARY(26:50)) FIXED DECIMAL(7,2);
EXEC SQL END DECLARE SECTION;

Multidimensional host arrays are not allowed. Thus, the two-dimensional host array declared in the following example is invalid:

EXEC SQL BEGIN DECLARE SECTION;
 DCL HI_LO_SCORES(25,25) FIXED BIN(31); /* invalid */
EXEC SQL END DECLARE SECTION;

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/1 Precompiler always uses the smallest array size for the SQL operation.

DO J = 1 TO 10;
 EXEC SQL INSERT INTO EMP (EMPNO, SAL) 
 VALUES (:EMP_NUMBER(J), :SALARY(J)); /* invalid */ 
END;

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:

EXEC SQL BEGIN DECLARE SECTION; 
 DCL EMP_NUMBER(50) FIXED BIN(31), 
 DEPT_NUMBER(50) FIXED BIN(31), 
 COMMISSION(50) REAL, 
 COMM_IND(50) FIXED BIN(15); 
EXEC SQL END DECLARE SECTION;

/* Populate the host and indicator arrays. To insert a 
 null, assign a -1 to the appropriate element in the 
 indicator array. */

EXEC SQL INSERT INTO emp (empno, deptno, comm) 
 VALUES (:EMP_NUMBER, :DEPT_NUMBER, :COMMISSION :COMM_IND);

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 Restrictionsd 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.

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.

Effects of the MODE Option

The MODE option, which you can specify on the command line, determines how the Pro*PL/1 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/1.

• MODE=Oracle

• MODE=ANSI

  Note:

  The MODE option does not affect the way Pro*PL/1 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/1 is to always use CHARACTER(N) host variables for character data. When values are read into these variables, or assigned to them, PL/1 appends blanks to the data, up to the length of the variable. The following example illustrates this:

/* Declare host variables */ 
EXEC SQL BEGIN DECLARE SECTION; 
 DCL EMP_NAME CHAR(10), 
 JOB_NAME CHAR(8) /* Note size */ 
EXEC SQL END DECLARE SECTION; 

PUT SKIP LIST('Enter employee name: '); 
/* Assume the user enters 'MILLER' */ 
GET EDIT(EMP_NAME) (A(10)); 
JOB_NAME = 'SALES'; 

EXEC SQL INSERT INTO emp (empno, ename, deptno, job) 
 VALUES (1234, :EMP_NAME, 20, :JOB_NAME)

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/1 appends to the CHARACTER host variables. If you precompile this example in ANSI mode, the trailing blanks that PL/1 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.

Internal Datatypes

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

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

External Datatypes

As the following table 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/1 FIXED BINARY(31).

Host Variable Equivalencing

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

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

EXEC SQL VAR host_variable
 IS type_name [ ( {length | precision,scale} ) ];

where:

• host_variable 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 and scale are specified 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:

EXEC SQL BEGIN DECLARE SECTION;
 DCL INT_ARRAY(50) FIXED BINARY(31);
...
/* Reset default external datatype to RAW */
 EXEC SQL VAR INT_ARRAY IS RAW(200);
EXEC SQL END DECLARE SECTION;

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/1:

• NUMBER (use VARNUM instead)

• UNSIGNED (not supported in Pro*PL/1)

• DISPLAY (COBOL only)

• CHARZ (C only)

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.

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.

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 Oracle database version 7 Server Administrator's Guide.

EXEC SQL BEGIN DECLARE SECTION;
 ..
. DCL OracleID CHAR(1) INIT('/');
EXEC SQL END DECLARE SECTION;
...
EXEC SQL CONNECT :OracleID;

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:

EXEC SQL BEGIN DECLARE SECTION;
 DCL USR CHAR(5),
 PWD CHAR(5),
 DBSTR1 CHAR(11),
 DBSTR2 CHAR(11);
EXEC SQL END DECLARE SECTION;

USR = 'SCOTT';
PWD = 'TIGER';
DBSTR1 = 'D:NODE1-Database1';
DBSTR2 = 'D:NODE1-Database2';

/* Give each database connection a unique name. */
EXEC SQL DECLARE DBNAM1 DATABASE;
EXEC SQL DECLARE DBNAM2 DATABASE;
/* Connect to the two nondefault databases. */ 
EXEC SQL CONNECT :USR IDENTIFIED BY :PWD
 AT DBNAM1 USING :DBSTR1;
EXEC SQL CONNECT :USR IDENTIFIED BY :PWD
 AT DBNAM2 USING :DBSTR2; 

DBNAM1 and DBNAM2 name the non default connections; they are identifiers used by the precompiler, not host variables.