Release 8.0
A54659-01
 Library |
 Product |
 Contents |
 Index |
| Programmer's Guide to the Oracle Pro*COBOL Precompiler Release 8.0 A54659-01 |
|
This chapter explains how embedded SQL programs do their work. You examine the special environment in which they operate and the impact of this environment on the design of your applications.
After covering the key concepts of embedded SQL programming and the steps you take in developing an application, this chapter uses a simple program to illustrate the main points.
Topics covered are:
This section lays the conceptual foundation on which later chapters build. It discusses the following subjects:
The term embedded SQL refers to SQL statements placed within an application program. Because the application program houses the SQL statements, it is called a host program, and the language in which it is written is called the host language. For example, with Pro*COBOL you can embed SQL statements in a COBOL host program.
For example, to manipulate and query Oracle data, you use the INSERT, UPDATE, DELETE, and SELECT statements. INSERT adds rows of data to database tables, UPDATE modifies rows, DELETE removes unwanted rows, and SELECT retrieves rows that meet your search criteria.
Only SQL statements-not SQL*Plus statements-are valid in an application program. (SQL*Plus has additional statements for setting environment parameters, editing, and report formatting.)
Embedded SQL includes all the interactive SQL statements plus others that allow you to transfer data between Oracle and a host program. There are two types of embedded SQL statements: executable and declarative.
Executable SQL statements generate calls to the database. They include almost all queries, DML (Data Manipulation Language), DDL (Data Definition Language), and DCL (Data Control Language) statements.
Declarative statements, on the other hand, do not result in calls to SQLLIB and do not operate on Oracle data. You use them to declare Oracle objects, communications areas, and SQL variables. They can be placed wherever host-language declarations can be placed.
Table 2-1 groups the various embedded SQL statements:.
In your application program, you can freely intermix SQL statements with host-language statements and use host-language variables in SQL statements. The only special requirement for building SQL statements into your host program is that you begin them with the words EXEC SQL and end them with the token END-EXEC. Pro*COBOL translates all executable EXEC SQL statements into calls to the runtime library SQLLIB.
Most embedded SQL statements differ from their interactive counterparts only through the adding of a new clause or the use of program variables. Compare the following interactive and embedded ROLLBACK statements:
ROLLBACK WORK; -- interactive * embedded
EXEC SQL
ROLLBACK WORK
END-EXEC.
For a summary of embedded SQL syntax, see the Oracle8 Server SQL Reference.
Most application programs are designed to process static SQL statements and fixed transactions. In this case, you know the makeup of each SQL statement and transaction before run time. That is, you know which SQL commands will be issued, which database tables might be changed, which columns will be updated, and so on. See Chapter 5, "Using Embedded SQL".
However, some applications are required to accept and process any valid SQL statement at run time. So, you might not know until then all the SQL commands, database tables, and columns involved.
Dynamic SQL is an advanced programming technique that lets your program accept or build SQL statements at run time and take explicit control over datatype conversion. See Chapter 11, "Using Dynamic SQL" and Chapter 12, "Using Dynamic SQL: Advanced Concepts".
Pro*COBOL treats a PL/SQL block like a single embedded SQL statement. So, you can place a PL/SQL block anywhere in an application program that you can place a SQL statement. To embed PL/SQL 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.
From embedded PL/SQL blocks, you can manipulate Oracle data flexibly and safely because PL/SQL supports all SQL data manipulation and transaction processing commands. For more information about PL/SQL, see Chapter 6, "Using Embedded PL/SQL".
A host variable is a scalar or table variable or group item declared in the host language and shared with Oracle, meaning that both your program and Oracle can reference its value. Host variables are the key to communication between Oracle and your program.
You use input host variables to pass data to the database. You use output host variables to pass data and status information from the database to your program.
Host variables can be used anywhere an expression can be used. But, in SQL statements, host variables must be prefixed with a colon, ':', to set them apart from database schema names.
You can associate any host variable with an optional indicator variable. An indicator variable is an integer variable that indicates the value or condition of its host variable. You use indicator variables to assign nulls to input host variables and to detect nulls or truncated values in output host variables. A null is a missing, unknown, or inapplicable value.
In SQL statements, an indicator variable must be prefixed with a colon and appended to its associated host variable (unless, to improve readability, you precede the indicator variable with the optional keyword INDICATOR).
Typically, a host program inputs data to the database, and the database outputs data to the program. Oracle inserts input data into database tables and selects output data into program host variables. To store a data item, Oracle must know its datatype, which specifies a storage format and valid range of values.
Oracle recognizes two kinds of datatypes: internal and external. Internal datatypes specify how Oracle stores data in database columns. Oracle also uses internal datatypes to represent database pseudo-columns, which return specific data items but are not actual columns in a table.
External datatypes specify how data is stored in host variables. When your host program inputs data to Oracle, if necessary, Oracle converts between the external datatype of the input host variable and the internal datatype of the database column. When Oracle outputs data to your host program, if necessary, Oracle converts between the internal datatype of the database column and the external datatype of the output host variable.
Pro*COBOL lets you define table host variables (called host tables) and operate on them with a single SQL statement. Using the table SELECT, FETCH, DELETE, INSERT, and UPDATE statements, you can query and manipulate large volumes of data with ease.
Pro*COBOL adds flexibility to your applications by letting you equivalence datatypes. That means you can customize the way Oracle interprets input data and formats output data.
On a variable-by-variable basis, you can equivalence supported host language datatypes to Oracle external datatypes. For more information, see "Datatype Equivalencing" on page 4-21
To process a SQL statement, Oracle opens a work area called a private SQL area. The private SQL area stores information needed to execute the SQL statement. An identifier called a cursor lets you name a SQL statement, access the information in its private SQL area, and, to some extent, control its processing.
For static SQL statements, there are two types of cursors: implicit and explicit. Oracle implicitly declares a cursor for all data definition and data manipulation statements, including SELECT statements (queries) that return only one row. However, for queries that return more than one row, to process beyond the first row, you must explicitly declare a cursor (or use host tables).
The set of rows retrieved is called the active set; its size depends on how many rows meet the query search condition. You use an explicit cursor to identify the row currently being processed, which is called the current row.
Imagine the set of rows being returned to a terminal screen. A screen cursor can point to the first row to be processed, then the next row, and so on. In the same way, an explicit cursor "points" to the current row in the active set, allowing your program to process the rows one at a time.
A transaction is a series of logically related SQL statements (two UPDATEs that credit one bank account and debit another, for example) that Oracle treats as a unit, so that all changes brought about by the statements are made permanent or undone at the same time. The current transaction consists of all data manipulation statements executed since the last data definition, COMMIT, or ROLLBACK statement was executed.
To help ensure the consistency of your database, Pro*COBOL lets you define transactions using the COMMIT, ROLLBACK, and SAVEPOINT statements. COMMIT makes permanent any changes made during the current transaction. ROLLBACK ends the current transaction and undoes any changes made since the transaction began. SAVEPOINT marks the current point in a transaction; used with ROLLBACK, it undoes part of a transaction.
When you execute an embedded SQL statement, it either succeeds or fails, and might result in an error or warning. You need a way to handle these results. Pro*COBOL provides these error handling mechanisms:
After executing a SQL statement, the Oracle Server returns a status code to a variable named SQLCODE or SQLSTATE. The status code indicates whether the SQL statement executed successfully or caused an error or warning condition.
The SQLCA is a data structure that defines program variables used by Oracle to pass runtime status information to the program. With the SQLCA, you can take different actions based on feedback from Oracle about work just attempted. For example, you can check to see if a DELETE statement succeeded and if so, how many rows were deleted.
With the WHENEVER statement, you can specify actions to be taken automatically when Oracle detects an error or warning condition. These actions include continuing with the next statement, calling a subroutine, branching to a labeled statement, or stopping.
When more information is needed about runtime errors than the SQLCA provides, you can use the ORACA. The ORACA is a data structure that handles Oracle communication. It contains cursor statistics, information about the current SQL statement, option settings, and system statistics.
Figure 2-1 walks you through the embedded SQL application development process.
As you can see, precompiling results in a source file that can be compiled normally. Although precompiling adds a step to the traditional development process, that step is well worth taking because it lets you write very flexible applications.
SQL statements begin with EXEC SQL and end with END-EXEC. A period or any other terminator can follows a SQL statement. Either of the following is allowed:
EXEC SQL ... END-EXEC, EXEC SQL ... END-EXEC.
The INCLUDE statement lets you copy files into your host program. It is similar to the COBOL COPY command. An example follows:
* copy in the SQLCA file
EXEC SQL INCLUDE SQLCA END-EXEC.
When you precompile your program, each EXEC SQL INCLUDE statement is replaced by a copy of the file named in the statement.
You can INCLUDE any file. If a file contains embedded SQL, you must INCLUDE it because only INCLUDEd files are precompiled.
If you do not specify a file extension, Pro*COBOL assumes the default extension, .cob.
You can set a directory path for INCLUDEd files by specifying the precompiler option
INCLUDE=<path>
where path defaults to the current directory. (In this context, a directory is an index of file locations.)
Pro*COBOL 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. You must still use INCLUDE to specify a directory path for nonstandard files unless they are stored in the current directory.
If your operating system is case-sensitive (UNIX, for example), be sure to specify the same upper/lower case filename under which the file is stored. The syntax for specifying a directory path is system-specific. Check your system-specific Oracle8 manuals.
The SQLCA is a data structure that provides for diagnostic checking and event handling. At run time, the SQLCA holds status information passed to your program by Oracle8. After executing a SQL statement, Oracle8 sets SQLCA variables to indicate the outcome, as illustrated in Figure 2-2.
Thus, you can check to see if an INSERT, UPDATE, or DELETE statement succeeded and if so, how many rows were affected. Or, if the statement failed, you can get more information about what happened.
When MODE={ANSI13|ORACLE}, you must declare the SQLCA by hard-coding it or by copying it into your program with the INCLUDE statement. The section "Using the SQL Communications Area" on page 9-19 shows you how to declare and use the SQLCA.
Oracle8 recognizes two kinds of datatypes: internal and external. Internal datatypes specify how Oracle8 stores data in database columns. Oracle8 also uses internal datatypes to represent database pseudo-columns. An external datatype specifies how data is stored in a host variable.
At precompile time, each host variable is associated with an external datatype code. At run time, the datatype code of every host variable used in a SQL statement is passed to Oracle8. Oracle8 uses the codes to convert between internal and external datatypes.
Note: You can override default datatype conversions by using dynamic SQL Method 4 or datatype equivalencing. For information about datatype equivalencing, see "Datatype Equivalencing" on page 4-21.
Every program variable used in a SQL statement must be declared according to the rules of the COBOL language. Normal rules of scope apply. COBOL variable names can be any length, but only the first 31 characters are significant for Pro*COBOL. Be aware that some COBOL compilers only recognize the first 30 characters of a variable name as significant. A host variable name must begin with a letter, and not contain consecutive or trailing underscores.
The external datatype of a host variable and the internal datatype of its source or target database column need not be the same, but they must be compatible. Table 4-6 on page 4-19 shows the compatible datatypes between which Oracle8 converts automatically when necessary.
You can use the VARCHAR pseudotype to declare variable-length character strings. (A pseudotype is a datatype not native to your host language.) Recall that VARCHAR variables have a 2-byte length field followed by a string field. For example, Pro*COBOL expands the VARCHAR declaration
01 ENAME PIC X(20) VARYING.
into the following COBOL group item with array and length members:
01 ENAME.
05 ENAME-LEN PIC S9(4) COMP.
05 ENAME-ARR PIC X(20).
To get the length of a VARCHAR, you simply refer to its length field. You need not use a string function or character-counting algorithm.
For more information about VARCHARs, see "VARCHAR Variables" on page 3-33.
The following guidelines apply to declaring and referencing host variables. A host variable must be
A host variable must not be
A host variable can be
You can associate every host variable with an optional indicator variable. An indicator variable must be defined as a signed 4-digit computational number and, in SQL statements, must be prefixed with a colon and must directly follows its host variable unless you use the keyword INDICATOR. For a detailed discussion, see "Indicator Variables" on page 3-27.
Most of the complete program examples in this guide use two sample database tables: DEPT and EMP. Their definitions follow:
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13))
CREATE TABLE EMP
(EMPNO NUMBER(4) primary key,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2))
Respectively, the DEPT and EMP tables contain the following rows of data:
DEPTNO DNAME LOC
------- ---------- ---------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------- --------- ------ --------- ------ ------ -------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
A good way to get acquainted with embedded SQL is to look at a program example.
This program logs on to the database, prompts the user for an employee number, queries the database table EMP for the employee's name, salary, and commission. The selected results are stored in host variables EMP-NAME, SALARY, and COMMISSION. The program uses the host indicator variable, COMM-IND to detect null values in column COMMISSION. See "Indicator Variables" on page 3-27.
The paragraph DISPLAY-INFO then displays the result.
The COBOL variables USERNAME, PASSWD, and EMP-NUMBER are declared using the VARYING clause, which allows you to use a variable-length string external Oracle datatype called VARCHAR. This datatype is explained in "VARCHAR Variables" on page 3-33.
The SQLCA Communications Area is included to handle errors. If an error occurs, paragraph SQL-ERROR is performed. See "Using the SQL Communications Area" on page 9-19.
The BEGIN DECLARE SECTION and END DECLARE SECTION statements used are optional, unless you set the Precompiler option DECLARE_SECTION to YES.
The program ends when the user enters a zero employee number.
IDENTIFICATION DIVISION.
PROGRAM-ID. QUERY.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(10) VARYING.
01 PASSWD PIC X(10) VARYING.
01 EMP-REC-VARS.
05 EMP-NAME PIC X(10) VARYING.
05 EMP-NUMBER PIC S9(4) COMP VALUE ZERO.
05 SALARY PIC S9(5)V99 COMP-3 VALUE ZERO.
05 COMMISSION PIC S9(5)V99 COMP-3 VALUE ZERO.
05 COMM-IND PIC S9(4) COMP VALUE ZERO.
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
01 DISPLAY-VARIABLES.
05 D-EMP-NAME PIC X(10).
05 D-SALARY PIC Z(4)9.99.
05 D-COMMISSION PIC Z(4)9.99.
01 D-TOTAL-QUERIED PIC 9(4) VALUE ZERO.
PROCEDURE DIVISION.
BEGIN-PGM.
EXEC SQL
WHENEVER SQLERROR DO PERFORM SQL-ERROR
END-EXEC.
PERFORM LOGON.
QUERY-LOOP.
DISPLAY " ".
DISPLAY "ENTER EMP NUMBER (0 TO QUIT): " WITH NO ADVANCING.
ACCEPT EMP-NUMBER.
IF (EMP-NUMBER = 0) PERFORM SIGN-OFF.
MOVE SPACES TO EMP-NAME-ARR.
EXEC SQL
WHENEVER NOT FOUND GOTO NO-EMP
END-EXEC.
EXEC SQL
SELECT ENAME, SAL, COMM
INTO :EMP-NAME, :SALARY, :COMMISSION:COMM-IND
FROM EMP
WHERE EMPNO = :EMP-NUMBER
END-EXEC.
PERFORM DISPLAY-INFO.
ADD 1 TO D-TOTAL-QUERIED.
GO TO QUERY-LOOP.
NO-EMP.
DISPLAY "NOT A VALID EMPLOYEE NUMBER - TRY AGAIN.".
GO TO QUERY-LOOP.
LOGON.
MOVE "SCOTT" TO USERNAME-ARR.
MOVE 5 TO USERNAME-LEN.
MOVE "TIGER" TO PASSWD-ARR.
MOVE 5 TO PASSWD-LEN.
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWD
END-EXEC.
DISPLAY " ".
DISPLAY "CONNECTED TO ORACLE AS USER: ", USERNAME-ARR.
DISPLAY-INFO.
DISPLAY " ".
DISPLAY "EMPLOYEE SALARY COMMISSION".
DISPLAY "-------- ------ ----------".
MOVE EMP-NAME-ARR TO D-EMP-NAME.
MOVE SALARY TO D-SALARY.
IF COMM-IND = -1
DISPLAY D-EMP-NAME, D-SALARY, " NULL"
ELSE
MOVE COMMISSION TO D-COMMISSION
DISPLAY D-EMP-NAME, D-SALARY, " ", D-COMMISSION
END-IF.
SIGN-OFF.
DISPLAY " ".
DISPLAY "TOTAL NUMBER QUERIED WAS ", D-TOTAL-QUERIED, ".".
DISPLAY " ".
DISPLAY "HAVE A GOOD DAY.".
DISPLAY " ".
EXEC SQL
COMMIT WORK RELEASE
END-EXEC.
STOP RUN.
SQL-ERROR.
EXEC SQL
WHENEVER SQLERROR CONTINUE
END-EXEC.
DISPLAY " ".
DISPLAY "ORACLE ERROR DETECTED:".
DISPLAY " ".
DISPLAY SQLERRMC.
EXEC SQL
ROLLBACK WORK RELEASE
END-EXEC.
STOP RUN.
