Release 1.8
Part Number A87540-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Pro*PL/I Supplement to the Oracle Precompilers Guide Release 1.8 Part Number A87540-01 |
|
This chapter provides the basic information you need to develop a Pro*PL/I application. You learn the following:
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.
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:
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.
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');
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 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.
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.
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.
The logical operators are different in SQL and PL/I, as the table below shows.
| SQL Operators | PL/I Operators |
|
NOT |
^ (prefix) |
|
AND |
& |
|
OR |
| |
PL/I logical operators are not allowed in SQL statements.
The relational operators are similar in SQL and PL/I, as the following table shows:
| SQL Operators | PL/I Operators |
|
= |
= |
|
<>, !=, ^= |
^= |
|
> |
> |
|
< |
< |
|
>= |
>= |
|
<= |
<= |
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.
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.
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
DCL NAME CHAR(20) VARYING; ... NAME = 'Pro*PL/I'; PUT SKIP LIST (NAME);
or the SQL statement
EXEC SQL SELECT ENAME, SAL FROM EMP WHERE JOB = 'MANAGER';
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.
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;
You can associate standard PL/I statement labels (for example, LABEL_NAME:) with SQL statements. The Pro*PL/I Precompiler can handle labels up to 31 characters long.
This section describes the variable declarations and SQL statements that must be present in every Pro*PL/I source file.
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
A Pro*PL/I source file can have multiple Declare Sections.
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.
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/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.
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.
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 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.
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:
You can also declare one-dimensional arrays of each of these types.
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.
In the following example, you declare five host variables for use later in the Pro*PL/I 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;
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;
A host variable must be prefixed with a colon (:) in SQL statements, but must not be prefixed with a colon in PL/I 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).
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.
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.
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;
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:
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.
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;
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
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 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.
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;
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;
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.
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.
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);
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
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.
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):
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/I code.
The Oracle character datatypes can be directly converted to the PL/I CHARACTER VARYING datatype. You declare CHARACTER VARYING as a host variable in the normal PL/I style, as follows:
EXEC SQL BEGIN DECLARE SECTION; .. . DCL EMP_NAME CHARACTER (20) VARYING, ... EXEC SQL END DECLARE SECTION;
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.
This section explains how the Pro*PL/I Precompiler handles character host variables. There are two host variable character types:
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.
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.
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:
/* 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/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.
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.
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.
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/I datatypes. For example, PL/I has no equivalent to the NUMBER datatype, which was specially designed for portability.
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.
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).
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 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.
By default, the Pro*PL/I 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 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/I:
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.
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.
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.
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.
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.
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.
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:
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;
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.
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.
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 non-default 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.
|
 Copyright © 1996-2001 Oracle Corporation. All Rights Reserved. |
|