Skip Headers
Pro*PL/1® Supplement to the Oracle Precompilers Guide
10g Release 2 (10.2)

Part Number B14353-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

1 Writing a Pro*PL/1 Program

This chapter provides the basic information you need to develop a Pro*PL/1 application. You learn the following:

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/1 programs.

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.

SQL Operator PL/1 Operator
NOT ^ (prefix)
AND &
OR |&: |: ^ (infix)

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:

SQL Operator PL/1 Operator
=
=
<>, !=, ^= ^=
> >
<
<
>= >=
<=
<=^<^>

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.

Required Declarations and SQL Statements

This section describes the variable declarations and SQL statements that must be present in every Pro*PL/1 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.

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.

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

PL/1 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/1 datatypes in the preceeding table.

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

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;

You can use attribute factoring within a SQL Declare Section, as in

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

which is equivalent to

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

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:

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

Restrictions

A host variable cannot substitute for a column, table, or other Oracle object in a SQL statement, and must not be an Oracle reserved word.

Table 1-1 Compatible Internal Datatypes




CHAR(X)Foot 1 VARCHAR2(Y)Footref 1

CHARACTER (N) CHARACTER (N) VARYING FIXED BINARY (15) FIXED BINARY (31) FIXED DECIMAL (p,s) FIXED DECIMAL (N) FLOAT FLOAT DECIMAL (N)

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

NUMBER NUMBER (P,S)Foot 2 

FIXED BINARY (15) FIXED BINARY (31) FIXED DECIMAL (p,s) FIXED DECIMAL (N) FLOAT FLOAT DECIMAL (N) CHARACTER (N) CHARACTER (N) VARYING

small signed integer signed integer fixed-point number fixed-point number floating-point number floating-point number stringFoot 3 variable-length string

DATEFoot 4 LONG RAW(X)1LONG RAW ROWIDFoot 5 MLSLABELFoot 6 

CHARACTER (N) CHARACTER (N) VARYING

string variable-length string


Footnote 1 X ranges from 1 to 255. Y ranges from 1 to 2000. 1 is the default value.

Footnote 2 P ranges from 2 to 38. S ranges from -84 to 127.

Footnote 3 Strings can be converted to NUMBERs only if they contain convertible characters ('0' to '9', '.', '+', '-', 'E', 'e'). Note that the Globalization Support settings in effect on your system might change the decimal point from '.' to ','.

Footnote 4 When converted as a string type, the default size of a DATE depends on the Globalization Support settings in effect on your system. When converted as a binary type, the size is 7 bytes.

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

Footnote 6 Trusted Oracle database version 7 only.

Indicator Variables

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

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.

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:

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.

Pointers as Host Variables

You cannot use PL/1 BASED variables in SQL statements. Also, PL/1 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):

EXEC SQL BEGIN DECLARE SECTION;
DCL 1 EMP_STRUCT BASED,
 2 EMP_NAME CHAR(20),
 2 EMP_SAL FIXED DECIMAL(7,2);
DCL EMP_PTR POINTER;
EXEC SQL END DECLARE SECTION;
...
ALLOCATE EMP_STRUCT SET(EMP_PTR);
PUT SKIP LIST ('Enter employee name: ');
GET LIST (EMP_PTR->EMP_NAME);
EXEC SQL INSERT INTO EMP (ENAME, EMPNO, DEPTNO)
 VALUES (:EMP_PTR->EMP_NAME, 8000, 20):

You can, of course, use pointers in pure PL/1 code.

CHARACTER VARYING

The Oracle character datatypes can be directly converted to the PL/1 CHARACTER VARYING datatype. You declare CHARACTER VARYING as a host variable in the normal PL/1 style, as follows:

EXEC SQL BEGIN DECLARE SECTION;
 ..
. DCL EMP_NAME CHARACTER (20) VARYING,
 ...
EXEC SQL END DECLARE SECTION;

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/1 Precompiler handles character host variables. There are two host variable character types:

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.

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/1 datatypes. For example, PL/1 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
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 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).

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/1 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/1 datatypes to Oracle external datatypes.

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:

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

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)

Embedding PL/SQL

The Pro*PL/1 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/1 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/1 character variable containing the user ID, a slash (/), and the password. For more information about connecting to Oracle see Oracle Database Administrator's Guide.

The following examples show both ways of connecting to Oracle:

EXEC SQL BEGIN DECLARE SECTION;
 DCL USER_NAME CHAR(6) INIT('SCOTT'),
 PASSWORD CHAR(6) INIT('TIGER');
EXEC SQL END DECLARE SECTION;
...
EXEC SQL CONNECT :USER_NAME IDENTIFIED BY :PASSWORD;
DCL USER_PWD CHAR(14);
...
USER_PWD = 'SYSTEM/MANAGER';
EXEC SQL CONNECT :USER_PWD;

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.