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 helps you to understand and apply the basic techniques of embedded SQL programming. Topics are:
Oracle uses host variables to pass data and status information to your program; your program uses host variables to pass data to Oracle.
Depending on how they are used, host variables are called output or input host variables. Host variables in the INTO clause of a SELECT or FETCH statement are called output host variables because they hold column values output by Oracle. Oracle assigns the column values to corresponding output host variables in the INTO clause.
All other host variables in a SQL statement are called input host variables because your program inputs their values to Oracle. For example, you use input host variables in the VALUES clause of an INSERT statement and in the SET clause of an UPDATE statement. They are also used in the WHERE, HAVING, and FOR clauses. In fact, input host variables can appear in a SQL statement wherever a value or expression is allowed.
Attention: In an ORDER BY clause, you can use a host variable, but it is treated as a constant or literal, and hence the contents of the host variable have no effect. For example, the SQL statement
EXEC SQL SELECT ENAME, EMPNO INTO :NAME, :NUMBER
FROM EMP
ORDER BY :ORD
END-EXEC.
appears to contain an input host variable, ORD. However, the host variable in this case is treated as a constant, and regardless of the value of ord, no ordering is done.
You cannot use input host variables to supply SQL keywords or the names of database objects. Thus, you cannot use input host variables in data definition statements (sometimes called DDL) such as ALTER, CREATE, and DROP. In the following example, the DROP TABLE statement is invalid:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 TABLE-NAME PIC X(30) VARYING.
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
DISPLAY 'Table name? '.
ACCEPT TABLE-NAME.
EXEC SQL DROP TABLE :TABLE-NAME END-EXEC.
* -- host variable not allowed
Before Oracle executes a SQL statement containing input host variables, your program must assign values to them. Consider the following example:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 EMP-NUMBER PIC S9(4) COMP.
01 EMP-NAME PIC X(20) VARYING.
EXEC SQL END DECLARE SECTION END-EXEC.
...
* -- get values for input host variables
DISPLAY 'Employee number? '.
ACCEPT EMP-NUMBER.
DISPLAY 'Employee name? '.
ACCEPT EMP-NAME.
EXEC SQL INSERT INTO EMP (EMPNO, ENAME)
VALUES (:EMP-NUMBER, :EMP-NAME)
END-EXEC.
Notice that the input host variables in the VALUES clause of the INSERT statement are prefixed with colons.
You can associate any host variable with an optional indicator variable. Each time the host variable is used in a SQL statement, a result code is stored in its associated indicator variable. Thus, indicator variables let you monitor host variables.
You use indicator variables in the VALUES or SET clause to assign nulls to input host variables and in the INTO clause to detect nulls or truncated values in output host variables.
For input host variables, the values your program can assign to an indicator variable have the following meanings:
|
-1 |
Oracle will assign a null to the column, ignoring the value of the host variable. |
|
>= 0 |
Oracle will assigns the value of the host variable to the column. |
For output host variables, the values Oracle can assign to an indicator variable have the following meanings:
Remember, an indicator variable must be declared as a 2-byte integer and, in SQL statements, must be prefixed with a colon and appended to its host variable (unless you use the keyword INDICATOR).
You can use indicator variables to insert nulls. Before the insert, for each column you want to be null, set the appropriate indicator variable to -1, as shown in the following example:
MOVE -1 TO IND-COMM.
EXEC SQL INSERT INTO EMP (EMPNO, COMM)
VALUES (:EMP-NUMBER, :cOMMISSION:IND-COMM)
END-EXEC.
The indicator variable IND-COMM specifies that a null is to be stored in the COMM column.
You can hard-code the null instead, as follows:
EXEC SQL INSERT INTO EMP (EMPNO, COMM)
VALUES (:EMP-NUMBER, NULL)
END-EXEC.
While this is less flexible, it might be more readable.
Typically, you insert nulls conditionally, as the next example shows:
DISPLAY 'Enter employee number or 0 if not available: '
WITH NO ADVANCING.
ACCEPT EMP-NUMBER.
IF EMP-NUMBER = 0
MOVE -1 TO IND-EMPNUM
ELSE
MOVE 0 TO IND-EMPNUM
END-IF.
EXEC SQL INSERT INTO EMP (EMPNO, SAL)
VALUES (:EMP-NUMBER:IND-EMPNUM, :SALARY)
END-EXEC.
You can also use indicator variables to manipulate returned nulls, as the following example shows:
EXEC SQL SELECT ENAME, SAL, COMM
INTO :EMP-NAME, :SALARY, :COMMISSION:IND-COMM
FROM EMP
WHERE EMPNO = :EMP_NUMBER
END-EXEC.
IF IND-COMM = -1
MOVE SALARY TO PAY.
* -- commission is null; ignore it
ELSE
ADD SALARY TO COMMISSIO GIVING PAY.
END-IF.
Using the precompiler option UNSAFE_NULL=YES, you can select or fetch nulls into a host variable that lacks an indicator variable, as the following example shows:
* -- assume that commission is NULL
EXEC SQL SELECT ENAME, SAL, COMM
INTO :EMP-NAME, :SALARY, :COMMISSION
FROM EMP
WHERE EMPNO = :EMP-NUMBER
END-EXEC.
SQLCODE in the SQLCA is set to zero indicating that Oracle executed the statement without detecting an error or exception.
However, when UNSAFE_NULL=NO, if you select or fetch nulls into a host variable that lacks an indicator variable, Oracle issues the following error message:
ORA-01405: fetched column value is NULL
For more information, see "UNSAFE_NULL" on page 7-34.
You can use indicator variables in the WHERE clause to test for nulls, as the following example shows:
EXEC SQL SELECT ENAME, SAL
INTO :EMP-NAME, :SALARY
FROM EMP
WHERE :COMMISSION:IND-COMM IS NULL ...
However, you cannot use a relational operator to compare nulls with each other or with other values. For example, the following SELECT statement fails if the COMM column contains one or more nulls:
EXEC SQL SELECT ENAME, SAL
INTO :EMP-NAME, :SALARY
FROM EMP
WHERE COMM = :COMMISSION:IND-COMM
END-EXEC.
The next example shows how to compare values for equality when some of them might be nulls:
EXEC SQL SELECT ENAME, SAL
INTO :EMP_NAME, :SALARY
FROM EMP
WHERE (COMM = :COMMISSION) OR ((COMM IS NULL) AND
(:COMMISSION:IND-COMM IS NULL))
END-EXEC.
When DBMS=V6, if you select or fetch a truncated column value into a host variable that lacks an indicator variable, Oracle issues the following error message:
ORA-01406: fetched column value was truncated
However, when DBMS=V7 or V8, no error is generated.
Executable SQL statements let you query, manipulate, and control Oracle data and create, define, and maintain Oracle objects such as tables, views, and indexes. This chapter focuses on data manipulation statements (sometimes called DML) and cursor control statements. The following SQL statements let you query and manipulate Oracle data:
|
SELECT |
Returns rows from one or more tables. |
|
INSERT |
Adds new rows to a table. |
|
UPDATE |
Modifies rows in a table. |
|
DELETE |
Removes rows from a table. |
When executing a data manipulation statement such as INSERT, UPDATE, or DELETE, your only concern, besides setting the values of any input host variables, is whether the statement succeeds or fails. To find out, you simply check the SQLCA. (Executing any SQL statement sets the SQLCA variables.) You can check in the following two ways:
Alternatively, when MODE={ANSI|ANSI14}, you can check the status variable SQLSTATE or SQLCODE. For more information, see "Using Status Variables when MODE={ANSI|ANSI14}" on page 9-4.
When executing a SELECT statement (query), however, you must also deal with the rows of data it returns. Queries can be classified as follows:
Queries that return more than one row require an explicitly declared cursor or cursor variable (or the use of host arrays, which are discussed in Chapter 10, "Using Host Tables"). The following embedded SQL statements let you define and control an explicit cursor:
In the coming sections, first you learn how to code INSERT, UPDATE, DELETE, and single-row SELECT statements. Then, you progress to multi-row SELECT statements. For a detailed discussion of each statement and its clauses, see the Oracle8 Server SQL Reference.
Querying the database is a common SQL operation. To issue a query you use the SELECT statement. In the following example, you query the EMP table:
EXEC SQL SELECT ENAME, JOB, SAL + 2000
INTO :emp_name, :JOB-TITLE, :SALARY
FROM EMP
WHERE EMPNO = :EMP-NUMBER
END-EXEC.
The column names and expressions following the keyword SELECT make up the select list. The select list in our example contains three items. Under the conditions specified in the WHERE clause (and following clauses, if present), Oracle returns column values to the host variables in the INTO clause. The number of items in the select list should equal the number of host variables in the INTO clause, so there is a place to store every returned value.
In the simplest case, when a query returns one row, its form is that shown in the last example (in which EMPNO is a unique key). However, if a query can return more than one row, you must fetch the rows using a cursor or select them into a host array.
If a query is written to return only one row but might actually return several rows, the result depends on how you specify the option SELECT_ERROR. When SELECT_ERROR=YES (the default), Oracle issues the following error message if more than one row is returned:
ORA-01422: exact fetch returns more than requested number of rows
When SELECT_ERROR=NO, a row is returned and Oracle generates no error.
You can use all of the following standard SQL clauses in your SELECT statements: INTO, FROM, WHERE, CONNECT BY, START WITH, GROUP BY, HAVING, ORDER BY, and FOR UPDATE OF.
You use the INSERT statement to add rows to a table or view. In the following example, you add a row to the EMP table:
EXEC SQL INSERT INTO EMP (EMPNO, ENAME, SAL, DEPTNO)
VALUES (:EMP_NUMBER, :EMP-NAME, :SALARY, :DEPT-NUMBER)
END-EXEC.
Each column you specify in the column list must belong to the table named in the INTO clause. The VALUES clause specifies the row of values to be inserted. The values can be those of constants, host variables, SQL expressions, or pseudocolumns, such as USER and SYSDATE.
The number of values in the VALUES clause must equal the number of names in the column list. However, you can omit the column list if the VALUES clause contains a value for each column in the table in the same order they were defined by CREATE TABLE.
A subquery is a nested SELECT statement. Subqueries let you conduct multi-part searches. They can be used to
For example, to copy rows from one table to another, replace the VALUES clause in an INSERT statement with a subquery, as follows:
EXEC SQL INSERT INTO EMP2 (EMPNO, ENAME, SAL, DEPTNO)
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP
WHERE JOB = :JOB-TITLE
END-EXEC.
Notice how the INSERT statement uses the subquery to obtain intermediate results.
You use the UPDATE statement to change the values of specified columns in a table or view. In the following example, you update the SAL and COMM columns in the EMP table:
EXEC SQL UPDATE EMP
SET SAL = :SALARY, COMM = :COMMISSION
WHERE EMPNO = :EMP-NUMBER
END-EXEC.
You can use the optional WHERE clause to specify the conditions under which rows are updated. See "Using the WHERE Clause" on page 5-10.
The SET clause lists the names of one or more columns for which you must provide values. You can use a subquery to provide the values, as the following example shows:
EXEC SQL UPDATE EMP
SET SAL = (SELECT AVG(SAL)*1.1 FROM EMP WHERE DEPTNO = 20)
WHERE EMPNO = :EMP-NUMBER
END-EXEC.
You use the DELETE statement to remove rows from a table or view. In the following example, you delete all employees in a given department from the EMP table:
EXEC SQL DELETE FROM EMP
WHERE DEPTNO = :DEPT-NUMBER
END-EXEC.
You can use the optional WHERE clause to specify the condition under which rows are deleted.
You use the WHERE clause to select, update, or delete only those rows in a table or view that meet your search condition. The WHERE-clause search condition is a Boolean expression, which can include scalar host variables, host arrays (not in SELECT statements), and subqueries.
If you omit the WHERE clause, all rows in the table or view are processed. If you omit the WHERE clause in an UPDATE or DELETE statement, Oracle sets SQLWARN(5) in the SQLCA to 'W' to warn that all rows were processed.
When a query returns multiple rows, you can explicitly define a cursor to
A cursor identifies the current row in the set of rows returned by the query. This allows your program to process the rows one at a time. The following statements let you define and manipulate a cursor:
First you use the DECLARE statement to name the cursor and associate it with a query.
The OPEN statement executes the query and identifies all the rows that meet the query search condition. These rows form a set called the active set of the cursor. After opening the cursor, you can use it to retrieve the rows returned by its associated query.
Rows of the active set are retrieved one by one (unless you use host arrays). You use a FETCH statement to retrieve the current row in the active set. You can execute FETCH repeatedly until all rows have been retrieved.
When done fetching rows from the active set, you disable the cursor with a CLOSE statement, and the active set becomes undefined.
You use the DECLARE statement to define a cursor by giving it a name and associating it with a query, as the following example shows:
EXEC SQL DECLARE EMPCURSOR CURSOR FOR
SELECT ENAME, EMPNO, SAL
FROM EMP
WHERE DEPTNO = :DEPT_NUMBER
END-EXEC.
The cursor name is an identifier used by the precompiler, not a host or program variable, and should not be declared in a COBOL statement. Therefore, cursor names cannot be passed from one precompilation unit to another. Cursor names cannot be hyphenated. They can be any length, but only the first 31 characters are significant. For ANSI compatibility, use cursor names no longer than 18 characters.
The WITH HOLD clause can be used in a DECLARE CURSOR statement to hold the cursor open after a COMMIT or a ROLLBACK.
The precompiler option CLOSE_ON_COMMIT is provided for use in the command line or in a configuration file. Any cursor not declared with the WITH HOLD clause is closed after a COMMIT or ROLLBACK when CLOSE_ON_COMMIT=YES. See "WITH HOLD Clause in DECLARE CURSOR Statements" on page 8-5, and "CLOSE_ON_COMMIT" on page 7-13.
The SELECT statement associated with the cursor cannot include an INTO clause. Rather, the INTO clause and list of output host variables are part of the FETCH statement.
Because it is declarative, the DECLARE statement must physically (not just logically) precede all other SQL statements referencing the cursor. That is, forward references to the cursor are not allowed. In the following example, the OPEN statement is misplaced:
EXEC SQL OPEN EMPCURSOR END-EXEC.
* -- MISPLACED OPEN STATEMENT
EXEC SQL DECLARE EMPCURSOR CURSOR FOR
SELECT ENAME, EMPNO, SAL
FROM EMP
WHERE ENAME = :EMP-NAME
END-EXEC.
The cursor control statements (DECLARE, OPEN, FETCH, CLOSE) must all occur within the same precompiled unit. For example, you cannot declare a cursor in file A, then open it in file B.
Your host program can declare as many cursors as it needs. However, in a given file, every DECLARE statement must be unique. That is, you cannot declare two cursors with the same name in one precompilation unit, even across blocks or procedures, because the scope of a cursor is global within a file. If you will be using many cursors, you might want to specify the MAXOPENCURSORS option. For more information, see "MAXOPENCURSORS" on page 7-26.
Use the OPEN statement to execute the query and identify the active set. In the following example, a cursor named EMPCURSOR is opened.
EXEC SQL OPEN EMPCURSOR END-EXEC.
OPEN positions the cursor just before the first row of the active set. It also zeroes the rows-processed count kept by SQLERRD(3) in the SQLCA. However, none of the rows is actually retrieved at this point. That will be done by the FETCH statement.
Once you open a cursor, the query's input host variables are not reexamined until you reopen the cursor. Thus, the active set does not change. To change the active set, you must reopen the cursor.
Generally, you should close a cursor before reopening it. However, if you specify CLOSE_ON_COMMIT=YES, you need not close a cursor before reopening it. This can boost performance; for details, see Appendix D, "Performance Tuning"The amount of work done by OPEN depends on the values of three precompiler options: HOLD_CURSOR, RELEASE_CURSOR, and MAXOPENCURSORS. For more information, see "Using Pro*COBOL Options" on page 7-12.
You use the FETCH statement to retrieve rows from the active set and specify the output host variables that will contain the results. Recall that the SELECT statement associated with the cursor cannot include an INTO clause. Rather, the INTO clause and list of output host variables are part of the FETCH statement. In the following example, you fetch into three host variables:
EXEC SQL FETCH EMPCURSOR
INTO :EMP-NAME, :EMP-NUMBER, :SALARY
END-EXEC.
The cursor must have been previously declared and opened. The first time you execute FETCH, the cursor moves from before the first row in the active set to the first row. This row becomes the current row. Each subsequent execution of FETCH advances the cursor to the next row in the active set, changing the current row. The cursor can only move forward in the active set. To return to a row that has already been fetched, you must reopen the cursor, then begin again at the first row of the active set.
If you want to change the active set, you must assign new values to the input host variables in the query associated with the cursor, then reopen the cursor. When CLOSE_ON_COMMIT=NO, you must close the cursor before reopening it.
As the next example shows, you can fetch from the same cursor using different sets of output host variables. However, corresponding host variables in the INTO clause of each FETCH statement must have the same datatype.
EXEC SQL DECLARE EMPCURSOr CURSOR FOR
SELECT ENAME, SAL FROM EMP WHERE DEPTNO = 20
END-EXEC.
...
EXEC SQL OPEN EMPCURSOr END-EXEC.
EXEC SQL WHENEVER NOT FOUND DO ...
PERFORM
EXEC SQL FETCH EMPCURSOR INTO :EMP-NAME1, :SAL1 END-EXEC
EXEC SQL FETCH EMPCURSOR INTO :EMP-NAME2, :SAL2 END-EXEC
EXEC SQL FETCH EMPCURSOR INTO :EMP-NAME3, :SAL3 END-EXEC
...
END-PERFORM.
If the active set is empty or contains no more rows, FETCH returns the "no data found" Oracle warning code to SQLCODE in the SQLCA (or when MODE=ANSI, to the status variable SQLSTATE). The status of the output host variables is indeterminate. (In a typical program, the WHENEVER NOT FOUND statement detects this error.) To reuse the cursor, you must reopen it.
When finished fetching rows from the active set, you close the cursor to free the resources, such as storage, acquired by opening the cursor. When a cursor is closed, parse locks are released. What resources are freed depends on how you specify the options HOLD_CURSOR and RELEASE_CURSOR. In the following example, you close the cursor named EMPCURSOR:
EXEC SQL CLOSE EMPCURSOR END-EXEC.
You cannot fetch from a closed cursor because its active set becomes undefined. If necessary, you can reopen a cursor (with new values for the input host variables, for example).
When CLOSE_ON_COMMIT=NO, issuing a commit or rollback closes cursors referenced in a CURRENT OF clause. Other cursors are unaffected by a commit or rollback and if open, remain open. However, when CLOSE_ON_COMMIT=YES, issuing a commit or rollback closes all explicit cursors.
You use the CURRENT OF cursor_name clause in a DELETE or UPDATE statement to refer to the latest row fetched from the named cursor. The cursor must be open and positioned on a row. If no fetch has been done or if the cursor is not open, the CURRENT OF clause results in an error and processes no rows.
The FOR UPDATE OF clause is optional when you declare a cursor that is referenced in the CURRENT OF clause of an UPDATE or DELETE statement. The CURRENT OF clause signals the precompiler to add a FOR UPDATE clause if necessary. For more information, see"Mimicking the CURRENT OF Clause" on page 10-14.
In the following example, you use the CURRENT OF clause to refer to the latest row fetched from a cursor named EMPCURSOR:
EXEC SQL DECLARE EMPCURSOR CURSOR FOR
SELECT ENAME, SAL FROM EMP WHERE JOB = 'CLERK'
FOR UPDATE OF SAL
END-EXEC.
...
EXEC SQL OPEN EMPCURSOR END-EXEC.
EXEC SQL WHENEVER NOT FOUND DO ...
PERFORM
EXEC SQL FETCH EMPCURSOR INTO :EMP-NAME, :SALARY
END-EXEC
...
EXEC SQL UPDATE EMP SET SAL = :NEW-SALARY
WHERE CURRENT OF EMPCURSOR
END-EXEC
END-PERFORM.
An explicit FOR UPDATE OF or an implicit FOR UPDATE acquires exclusive row locks. All rows are locked at the open, not as they are fetched, and are released when you commit or rollback. If you try to fetch from a FOR UPDATE cursor after a commit, Oracle generates the following error:
ORA-01002: fetch out of sequence
You cannot use host arrays with the CURRENT OF clause. For an alternative, see "Mimicking the CURRENT OF Clause" on page 10-14. Also, you cannot reference multiple tables in an associated FOR UPDATE OF clause, which means that you cannot do joins with the CURRENT OF clause. Finally, you cannot use the CURRENT OF clause in dynamic SQL.
The following example shows the typical sequence of cursor control statements in an application program:
* -- Define a cursor.
EXEC SQL DECLARE EMPCURSOR CURSOR FOR
SELECT ENAME, JOB FROM EMP
WHERE EMPNO = :EMP-NUMBER
FOR UPDATE OF JOB
END-EXEC.
* -- Open the cursor and identify the active set.
EXEC SQL OPEN EMPCURSOR END-EXEC.
* -- Exit if the last row was already fetched.
EXEC SQL
WHENEVER NOT FOUND DO PERFORM NO-MORE
END-EXEC.
* -- Fetch and process data in a loop.
PERFORM
EXEC SQL FETCH EMPCURSOR INTO :EMP-NAME, :JOB-TITLE
END-EXEC
* -- host-language statements that operate on the fetched data
EXEC SQL UPDATE EMP
SET JOB = :NEW-JOB-TITLE
WHERE CURRENT OF EMPCURSOR
END-EXEC
END-PERFORM.
...
MO-MORE.
* -- Disable the cursor.
EXEC SQL CLOSE EMPCURSOR END-EXEC.
EXEC SQL COMMIT WORK RELEASE END-EXEC.
STOP RUN.
This program logs on to Oracle, declares and opens a cursor, fetches the names, salaries, and commissions of all salespeople, displays the results, then closes the cursor
All fetches except the final one return a row and, if no errors were detected during the fetch, a success status code. The final fetch fails and returns the "no data found" Oracle warning code to SQLCODE in the SQLCA. The cumulative number of rows actually fetched is found in SQLERRD(3) in the SQLCA.
IDENTIFICATION DIVISION.
PROGRAM-ID. CURSOR-OPS.
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 SALARY PIC S9(6)V99
DISPLAY SIGN LEADING SEPARATE.
05 COMMISSIO PIC S9(6)V99
DISPLAY SIGN LEADING SEPARATE.
EXEC SQL VAR SALARY IS DISPLAY(8,2) END-EXEC.
EXEC SQL VAR COMMISSION IS DISPLAY(8,2) END-EXEC.
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.
PROCEDURE DIVISION.
BEGIN-PGM.
EXEC SQL
WHENEVER SQLERROR DO PERFORM SQL-ERROR
END-EXEC.
PERFORM LOGON.
EXEC SQL
DECLARE SALESPEOPLE CURSOR FOR
SELECT ENAME, SAL, COMM FROM EMP
WHERE JOB LIKE 'SALES%'
END-EXEC.
EXEC SQL
OPEN SALESPEOPLE
END-EXEC.
DISPLAY "SALESPERSON SALARY COMMISSION".
DISPLAY "----------- ---------- ----------".
FETCH-LOOP.
EXEC SQL
WHENEVER NOT FOUND DO PERFORM SIGN-OFF
END-EXEC.
EXEC SQL
FETCH SALESPEOPLE
INTO :EMP-NAME, :SALARY, :COMMISSION
END-EXEC.
MOVE EMP-NAME-ARR TO D-EMP-NAME.
MOVE SALARY TO D-SALARY.
MOVE COMMISSION TO D-COMMISSION.
DISPLAY D-EMP-NAME, " ", D-SALARY, " ",
- D-COMMISSION.
MOVE SPACES TO EMP-NAME-ARR.
GO TO FETCH-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 " ".
SIGN-OFF.
EXEC SQL
CLOSE SALESPEOPLE
END-EXEC.
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.
After connecting to Oracle, this program creates a database table named IMAGE in the SCOTT account, then simulates the insertion of bitmap images of employee numbers into the table. Datatype equivalencing lets the program use the Oracle external datatype LONG RAW to represent the images. Later, when the user enters an employee number, the number's "bitmap" is selected from the IMAGE table and pseudo-displayed on the terminal screen.
IDENTIFICATION DIVISION.
PROGRAM-ID. DTY-EQUIV.
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-NUMBER PIC S9(4) COMP.
05 EMP-NAME PIC X(10) VARYING.
05 SALARY PIC S9(6)V99
DISPLAY SIGN LEADING SEPARATE.
05 COMMISSION PIC S9(6)V99
DISPLAY SIGN LEADING SEPARATE.
05 COMM-IND PIC S9(4) COMP.
EXEC SQL VAR SALARY IS DISPLAY(8,2) END-EXEC.
EXEC SQL VAR COMMISSION IS DISPLAY(8,2) END-EXEC.
01 BUFFER-VAR.
05 BUFFER PIC X(8192).
EXEC SQL VAR BUFFER IS LONG RAW END-EXEC.
01 SELECTION PIC S9(4) COMP.
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 REPLY PIC X(10).
01 INDX PIC S9(9) COMP.
01 PRT-QUOT PIC S9(9) COMP.
01 PRT-MOD PIC S9(9) COMP.
PROCEDURE DIVISION.
BEGIN-PGM.
EXEC SQL
WHENEVER SQLERROR DO PERFORM SQL-ERROR
END-EXEC.
PERFORM LOGON.
DISPLAY "OK TO DROP THE IMAGE TABLE? (Y/N) "
WITH NO ADVANCING.
ACCEPT REPLY.
IF (REPLY NOT = "Y") AND (REPLY NOT = "Y")
PERFORM SIGN-OFF.
EXEC SQL
WHENEVER SQLERROR CONTINUE
END-EXEC.
EXEC SQL
DROP TABLE IMAGE
END-EXEC.
DISPLAY " ".
IF (SQLCODE = 0) DISPLAY
"TABLE IMAGE DROPPED - CREATING NEW TABLE."
ELSE IF (SQLCODE = -942) DISPLAY
"TABLE IMAGE DOES NOT EXIST - CREATING NEW TABLE."
ELSE PERFORM SQL-ERROR.
EXEC SQL
WHENEVER SQLERROR DO PERFORM SQL-ERROR
END-EXEC.
EXEC SQL
CREATE TABLE IMAGE
(EMPNO NUMBER(4) NOT NULL, BITMAP LONG RAW)
END-EXEC.
EXEC SQL
DECLARE EMPCUR CURSOR FOR
SELECT EMPNO, ENAME FROM EMP
END-EXEC.
EXEC SQL
OPEN EMPCUR
END-EXEC.
DISPLAY " ".
DISPLAY "INSERTING BITMAPS INTO IMAGE FOR ALL EMPLOYEES.".
DISPLAY " ".
INSERT-LOOP.
EXEC SQL
WHENEVER NOT FOUND GOTO NOT-FOUND
END-EXEC.
EXEC SQL
FETCH EMPCUR INTO :EMP-NUMBER, :EMP-NAME
END-EXEC.
MOVE EMP-NAME-ARR TO D-EMP-NAME.
DISPLAY "EMPLOYEE ", D-EMP-NAME WITH NO ADVANCING.
PERFORM GET-IMAGE.
EXEC SQL
INSERT INTO IMAGE VALUES (:EMP-NUMBER, :BUFFER)
END-EXEC.
DISPLAY " IS DONE!".
MOVE SPACES TO EMP-NAME-ARR.
GO TO INSERT-LOOP.
NOT-FOUND.
EXEC SQL
CLOSE EMPCUR
END-EXEC.
EXEC SQL
COMMIT WORK
END-EXEC.
DISPLAY " ".
DISPLAY "DONE INSERTING BITMAPS. NEXT, DISPLAY SOME.".
DISP-LOOP.
MOVE 0 TO SELECTION.
DISPLAY " ".
DISPLAY "ENTER EMP NUMBER (0 TO QUIT): "
- WITH NO ADVANCING.
ACCEPT SELECTION.
IF (SELECTION = 0) PERFORM SIGN-OFF.
EXEC SQL
WHENEVER NOT FOUND GOTO NO-EMP
END-EXEC.
EXEC SQL
SELECT EMP.EMPNO, ENAME, SAL, COMM, BITMAP
INTO :EMP-NUMBER, :EMP-NAME, :SALARY,
:COMMISSION:COMM-IND, :BUFFER
FROM EMP, IMAGE
WHERE EMP.EMPNO = :SELECTION AND EMP.EMPNO = IMAGE.EMPNO
END-EXEC.
DISPLAY " ".
PERFORM SHOW-IMAGE.
MOVE EMP-NAME-ARR TO D-EMP-NAME.
MOVE SALARY TO D-SALARY.
MOVE COMMISSION TO D-COMMISSION.
DISPLAY "EMPLOYEE ", D-EMP-NAME, " HAS SALARY ", D-SALARY
WITH NO ADVANCING.
IF COMM-IND = -1
DISPLAY " AND NO COMMISSION."
ELSE
DISPLAY " AND COMMISSION ", D-COMMISSION
END-IF.
MOVE SPACES TO EMP-NAME-ARR.
GO TO DISP-LOOP.
NO-EMP.
DISPLAY "NOT A VALID EMPLOYEE NUMBER - TRY AGAIN.".
GO TO DISP-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 " ".
GET-IMAGE.
PERFORM MOVE-IMAGE
VARYING INDX FROM 1 BY 1 UNTIL INDX > 8192.
MOVE-IMAGE.
STRING '*' DELIMITED BY SIZE INTO BUFFER WITH POINTER
- INDX.
DIVIDE 256 INTO INDX GIVING PRT-QUOT REMAINDER
- PRT-MOD.
IF (PRT-MOD = 0) DISPLAY "." WITH NO ADVANCING.
SHOW-IMAGE.
PERFORM VARYING INDX FROM 1 BY 1 UNTIL INDX > 10
DISPLAY " **************************"
END-PERFORM.
DISPLAY " ".
SIGN-OFF.
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.
