Pro*COBOL Supplement to the Oracle Precompilers | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
SELECT ENAME, JOB, SAL + COMM FROM EMP WHERE DEPTNO = 20
Placeholders are dummy bind (input) variables that hold places in a SQL statement for actual bind variables. You do not declare placeholders and can name them anything you like. Placeholders for bind variables are most often used in the SET, VALUES, and WHERE clauses. For example, the following dynamic SQL statements each contain two placeholders.
INSERT INTO EMP (EMPNO, DEPTNO) VALUES (:E, :D)
DELETE FROM DEPT WHERE DEPTNO = :DNUM AND LOC = :DLOC
Placeholders cannot reference table or column names.
* Dynamic SQL statement...
STMT = 'DELETE FROM EMP WHERE DEPTNO = 30'
However, most dynamic SQL statements contain select-list items or placeholders for bind variables, as shown in the following UPDATE statement:
* Dynamic SQL statement with placeholders...
STMT = 'UPDATE EMP SET COMM = :C WHERE EMPNO = :E'
To execute a dynamic SQL statement that contains select-list items and/or placeholders for bind variables, Oracle needs information about the program variables that will hold output or input values. Specifically, Oracle needs the following information:
Descriptions of select-list items are stored in a select SQLDA, and descriptions of placeholders for bind variables are stored in a bind SQLDA.
The values of select-list items are stored in output buffers; the values of bind variables are stored in input buffers. You use the library routine SQLADR to store the addresses of these data buffers in a select or bind SQLDA, so that Oracle knows where to write output values and read input values.
How do values get stored in these data variables? Output values are FETCHed using a cursor, and input values are filled in by your program, typically from information entered interactively by the user.
The DESCRIBE BIND VARIABLES statement examines each placeholder to determine its name and length, then stores this information in an input buffer and bind SQLDA for your use. For example, you might use placeholder names to prompt the user for the values of bind variables.
A select descriptor stores descriptions of select-list items and the addresses of output buffers that hold the names and values of select-list items.
Note: The name of a select-list item can be a column name, a column alias, or the text of an expression such as SAL + COMM.
A bind descriptor stores descriptions of bind variables and indicator variables, and the addresses of input buffers where the names and values of bind variables and indicator variables are stored.
Remember, some descriptor variables contain addresses, not values. So, you must declare data buffers to hold the values. You decide the sizes of the required input and output buffers. Because COBOL does not support pointers, you must use the library subroutine SQLADR to get the addresses of input and output buffers. You learn how to call SQLADR in the section "Using SQLADR" .
Note: For byte-swapped platforms, use COMP5 instead of COMP when declaring a SQLDA.
Figure 4 - 1. Sample Pro*COBOL SQLDA Descriptors and Data Buffers
You might want to store the SQLDAs in files (named SELDSC and BNDDSC, for example), then copy the files into your program with the INCLUDE statement as follows:
EXEC SQL INCLUDE SELDSC END-EXEC.
EXEC SQL INCLUDE BNDDSC END-EXEC.
Figure 4 - 2 shows whether variables are set by SQLADR calls, DESCRIBE commands, FETCH commands, or program assignments.
Figure 4 - 2. How Variables Are Set
Before issuing a DESCRIBE command, you must set this variable to the dimension of the descriptor tables. After the DESCRIBE, you must reset it to the actual number of variables DESCRIBEd, which is stored in SQLDFND.
SQLDFND is set by DESCRIBE. If SQLDFND is negative, the DESCRIBE command found too many select-list items or placeholders for the size of the descriptor. For example, if you set SQLDNUM to 10 but DESCRIBE finds 11 select-list items or placeholders, SQLDFND is set to -11. If this happens, you cannot process the SQL statement without reallocating the descriptor.
After the DESCRIBE, you must set SQLDNUM equal to SQLDFND.
You must set the elements of SELDV or BNDDV using SQLADR.
EXEC SQL FETCH ... USING DESCRIPTOR ...
directs Oracle to store FETCHed select-list values in the data buffers addressed by SELDV(1) through SELDV(SQLDNUM). Thus, Oracle stores the Jth select-list value in SEL-DV(J).
EXEC SQL OPEN ... USING DESCRIPTOR ...
directs Oracle to execute the dynamic SQL statement using the bind-variable values addressed by BNDDV(1) through BNDDV(SQLDNUM). (Typically, the values are entered by the user.) Oracle finds the Jth bind-variable value in BND-DV(J).
The use of format strings is optional. If you want a conversion format for the Jth select-list item or bind variable, set SELDFMT(J) or BNDDFMT(J) using SQLADR, then store the packed-decimal format ("07.+02" for example) in SEL-DFMT or BND-DFMT. Otherwise, set SELDFMT(J) or BNDDFMT(J) to zero.
The format of the length differs among Oracle datatypes. For CHAR select-list items, DESCRIBE SELECT LIST sets SELDVLN(J) to the maximum length in bytes of the select-list item. For NUMBER select-list items, scale and precision are returned respectively in the low and next-higher bytes of the variable. You can use the library routine SQLPRC to extract precision and scale values from SELDVLN. See the section "Extracting Precision and Scale" .
You must reset SELDVLN(J) to the required length of the data buffer before the FETCH. For example, when coercing a NUMBER to a COBOL character string, set SELDVLN(J) to the precision of the number plus two for the sign and decimal point. When coercing a NUMBER to a COBOL floating point number, set SELDVLN(J) to the length of the appropriate floating point type on your system. For more information about the lengths of coerced datatypes, see the section "Converting Data" .
PROCEDURE DIVISION.
...
PERFORM GET-INPUT-VAR
VARYING J FROM 1 BY 1 UNTIL J > SQLDNUM IN BNDDSC.
...
GET-INPUT-VAR.
DISPLAY "Enter value of ", BND-DH-VNAME(J).
ACCEPT INPUT-STRING.
UNSTRING INPUT-STRING DELIMITED BY " "
INTO BND-DV(J) COUNT IN BNDDVLN(J).
Because Oracle accesses a data buffer indirectly, using the address in SELDV(J) or BNDDV(J), it does not know the length of the value in that buffer. If you want to change the length Oracle uses for the Jth select-list or bind-variable value, reset SELDVLN(J) or BNDDVLN(J) to the length you need. Each input or output buffer can have a different length.
The use of format strings is optional. If you want a conversion format for the Jth select-list item or bind variable, set SELDFMTL(J) before the FETCH or BNDDFMTL(J) before the OPEN to the length of the packed-decimal format stored in SEL-DFMT or BND-DFMT. Otherwise, set SELDFMTL(J) or BNDDFMTL(J) to zero.
If the value of SELDFMTL(J) or BNDDFMTL(J) is zero, SELDFMT(J) or BNDDFMT(J) is not used.
Before FETCHing, you might want to reset some datatypes because the internal format of Oracle datatypes can be difficult to handle. For display purposes, it is usually a good idea to coerce the datatype of select-list values to VARCHAR2. For calculations, you might want to coerce numbers from Oracle to COBOL format. See "Coercing Datatypes" .
The high bit of SELDVTYP(J) is set to indicate the null/not null
status of the Jth select-list column. You must always clear this bit before issuing an OPEN or FETCH command. You use the library routine SQLNUL to retrieve the datatype code and clear the null/not null bit. For more information, see "Handling Null/Not Null Datatypes" .
You should change the Oracle NUMBER internal datatype to an external datatype compatible with that of the COBOL data buffer addressed by SELDV(J).
To change the datatype of the Jth select-list or bind-variable value, reset SELDVTYP(J) or BNDDVTYP(J) to the datatype you want.
EXEC SQL FETCH ... USING DESCRIPTOR ...
if the Jth returned select-list value is null, the buffer addressed by SELDI(J) is set to -1. Otherwise, it is set to zero (the value is not null) or a positive integer (the value was truncated).
EXEC SQL OPEN ... USING DESCRIPTOR ...
the buffer addressed by BNDDI(J) determines whether the Jth bind variable is null. If the value of an indicator variable is -1, its associated bind variable is null.
DESCRIBE directs Oracle to store the name of the Jth select-list item or placeholder in the data buffer addressed by SELDH-VNAME(J) or BNDDH-VNAME(J). Thus, Oracle stores the Jth select-list or placeholder name in SEL-DH-VNAME(J) or BND-DH-VNAME(J).
You must set the elements of SELDH-MAX-VNAMEL or BNDDH-MAX-VNAMEL before issuing the DESCRIBE command. Each select-list or placeholder name buffer can have a different length.
You can associate indicator-variable values with select-list items and bind variables. However, you can associate indicator-variable names only with bind variables. So, you can use this table only with bind descriptors. You must set the elements of BNDDI-VNAME using SQLADR before issuing the DESCRIBE command.
DESCRIBE BIND VARIABLES directs Oracle to store any indicator-variable names in the data buffers addressed by BNDDI-VNAME(1) through BNDDI-VNAME(SQLDNUM). Thus, Oracle stores the Jth indicator-variable name in BND-DI-VNAME(J).
You can associate indicator-variable names only with bind variables. So, you can use this table only with bind descriptors.
You must set the elements BNDDI-MAX-VNAMEL(1) through BNDDI-MAX-VNAMEL(SQLDNUM) before issuing the DESCRIBE command. Each indicator-variable name buffer can have a different length.
DESCRIBE BIND VARIABLES sets the table of actual lengths to the number of characters in each indicator-variable name.
CALL "SQLADR" USING BUFFER, ADDRESS.
where:
BUFFER
Is a data buffer that stores the value or name of a select-list item, bind variable, or indicator variable.
ADDRESS
Is an integer variable that returns the address of the data buffer.
A call to SQLADR stores the address of BUFFER in ADDRESS. In the next example, you use SQLADR to initialize the select descriptor tables SELDV, SELDH-VNAME, and SELDI. Their elements address data buffers for select-list values, select-list names, and indicator values.
PROCEDURE DIVISION.
...
PERFORM INIT-SELDSC
VARYING J FROM 1 BY 1 UNTIL J > SQLDNUM IN SELDSC.
...
INIT-SELDSC.
CALL "SQLADR" USING SEL-DV(J), SELDV(J).
CALL "SQLADR" USING SEL-DH-VNAME(J), SELDH-VNAME(J).
CALL "SQLADR" USING SEL-DI(J), SELDI(J).
...
However, Method 4 lets you control data conversion and formatting. You specify conversions by setting datatype codes in the datatype descriptor table.
When you issue a DESCRIBE SELECT LIST command, Oracle returns the internal datatype code for each select-list item to the SELDVTYP (datatype) descriptor table. For example, the datatype code for the Jth select-list item is returned to SELDVTYP(J).
Table 4 - 1 shows the Oracle internal datatypes and their codes.
Oracle Internal Datatype | Code |
VARCHAR2 | 1 |
NUMBER | 2 |
LONG | 8 |
ROWID | 11 |
DATE | 12 |
RAW | 23 |
LONG RAW | 24 |
CHAR | 96 |
MLSLABEL | 105 |
The DESCRIBE BIND VARIABLES command sets the BNDDVTYP table of datatype codes to zeros. So, you must reset the codes before issuing the OPEN command. The codes tell Oracle which external datatypes to expect for the various bind variables. For the Jth bind variable, reset BNDDVTYP(J) to the external datatype you want.
The following table shows the Oracle external datatypes and their codes, as well as the corresponding COBOL datatypes:
Name | Code | COBOL Datatype |
VARCHAR2 | 1 | PIC X(n) when MODE != ANSI |
NUMBER | 2 | PIC X(n) |
INTEGER | 3 | PIC S9(n) COMP PIC S9(n) COMP5 (COMP5 for byte-swapped platforms) |
FLOAT | 4 | COMP-1 COMP-2 |
STRING (1) | 5 | PIC X(n) |
VARNUM | 6 | PIC X(n) |
DECIMAL | 7 | PIC S9(n)V9(n) COMP-3 |
LONG | 8 | PIC X(n) |
VARCHAR (2) | 9 | PIC X(n) VARYING PIC N(n) VARYING |
ROWID | 11 | PIC X(n) |
DATE | 12 | PIC X(n) |
VARRAW (2) | 15 | PIC X(n) |
RAW | 23 | PIC X(n) |
LONG RAW | 24 | PIC X(n) |
UNSIGNED | 68 | (not supported) |
DISPLAY | 91 | PIC S9...9V9...9 DISPLAY SIGN LEADING SEPARATE PIC S9(n)V9(n) DISPLAY SIGN LEADING SEPARATE |
LONG VARCHAR (2) | 94 | PIC X(n) |
LONG VARRAW (2) | 95 | PIC X(n) |
CHARF | 96 | PIC X(n) when MODE = ANSI PIC N(n) when MODE = ANSI |
CHARZ (1) | 97 | PIC X(n) |
CURSOR | 102 | SQL-CURSOR |
MLSLABEL | 106 | PIC X(n) |
Notes:
For example, you might want to reset NUMBER values to FLOAT values, which correspond to PIC S9(n)V9(n) COMP-1 values in COBOL. Oracle does any necessary conversion between internal and external datatypes at FETCH time. So, be sure to reset the datatypes after the DESCRIBE SELECT LIST but before the FETCH.
For a bind descriptor, DESCRIBE BIND VARIABLES does not return the datatypes of bind variables, only their number and names. Therefore, you must explicitly set the BNDDVTYP table of datatype codes to tell Oracle the external datatype of each bind variable. Oracle does any necessary conversion between external and internal datatypes at OPEN time.
When you reset datatype codes in the SELDVTYP or BNDDVTYP descriptor table, you are "coercing datatypes." For example, to coerce the Jth select-list value to VARCHAR2, use the following statement:
* Coerce select-list value to VARCHAR2.
MOVE 1 TO SELDVTYP(J).
When coercing a NUMBER select-list value to VARCHAR2 for display purposes, you must also extract the precision and scale bytes of the value and use them to compute a maximum display length. Then, before the FETCH, you must reset the appropriate element of the SELDVLN (length) descriptor table to tell Oracle the buffer length to use. To specify the length of the Jth select-list value, set SELDVLN(J) to the length you need.
For example, if DESCRIBE SELECT LIST finds that the Jth select-list item is of type NUMBER, and you want to store the returned value in a COBOL variable declared as PIC S9(n)V9(n) COMP-1, simply set SELDVTYP(J) to 4 and SELDVLN(J) to the length of COMP-1 numbers on your system.
Similarly, when you DESCRIBE a NUMBER select-list item, Oracle returns the datatype code 2 to the SELDVTYP table. Unless you reset the code before the FETCH, the numeric value is returned in its internal format, which is probably not what you want. So, change the code from 2 to 1 (VARCHAR2), 3 (INTEGER), 4 (FLOAT), or some other appropriate datatype.
CALL "SQLPRC" USING LENGTH, PRECISION, SCALE.
where:
LENGTH
Is an integer variable that stores the length of an Oracle NUMBER value. The scale and precision of the value are stored in the low and next-higher bytes, respectively.
PRECISION
Is an integer variable that returns the precision of the NUMBER value. Precision is the number of significant digits. It is set to zero if the select-list item refers to a NUMBER of unspecified size. In this case, because the size is unspecified, you might want to assume the maximum precision, 38.
SCALE
Is an integer variable that returns the scale of the NUMBER value. Scale specifies where rounding will occur. For example, a scale of 2 means the value is rounded to the nearest hundredth (3.456 becomes 3.46); a scale of -3 means the number is rounded to the nearest thousand (3456 becomes 3000).
The following example shows how SQLPRC is used to compute maximum display lengths for NUMBER values that will be coerced to VARCHAR2:
WORKING-STORAGE SECTION.
01 PRECISION PIC S9(9) COMP.
01 SCALE PIC S9(9) COMP.
01 DISPLAY-LENGTH PIC S9(9) COMP.
01 MAX-LENGTH PIC S9(9) COMP VALUE 80.
...
PROCEDURE DIVISION.
...
PERFORM ADJUST-LENGTH
VARYING J FROM 1 BY 1 UNTIL J > SQLDNUM IN SELDSC.
ADJUST-LENGTH.
* If datatype is NUMBER, extract precision and scale.
IF SELDVTYP(J) = 2
CALL "SQLPRC" USING SELDVLN(J), PRECISION, SCALE.
MOVE 0 TO DISPLAY-LENGTH.
* Precision is set to zero if the select-list item
* refers to a NUMBER of unspecified size. We allow for
* a maximum precision of 10.
IF SELDVTYP(J) = 2 AND PRECISION = 0
MOVE 10 TO DISPLAY-LENGTH.
* Allow for possible decimal point and sign.
IF SELDVTYP(J) = 2 AND PRECISION > 0
ADD 2 TO PRECISION
MOVE PRECISION TO DISPLAY-LENGTH.
...
Notice that the first parameter in the subroutine call is the Jth element in the table of select-list lengths.
The SQLPRC procedure, defined in the SQLLIB runtime library, returns zero as the precision and scale values for certain SQL datatypes. The SQLPR2 procedure is similar to SQLPRC in that it has the same syntax and returns the same binary values, except for the datatypes shown in Table 4 - 4.
SQL Datatype | Binary Precision | Binary Scale |
FLOAT | 126 | -127 |
FLOAT(n) | n (range is 1 .. 126) | -127 |
REAL | 63 | -127 |
DOUBLE PRECISION | 126 | -127 |
Before using the datatype in an OPEN or FETCH statement, if the null status bit is set, you must clear it. Never set the bit.
You can use the library routine SQLNUL to find out if a column allows nulls, and to clear the datatype's null status bit. You call SQLNUL using the syntax
CALL "SQLNUL" USING VALUE-TYPE, TYPE-CODE, NULL-STATUS.
where:
VALUE-TYPE
Is a 2-byte integer variable that stores the datatype code of a select-list column.
TYPE-CODE
Is a 2-byte integer variable that returns the datatype code of the select-list column with the high-order bit cleared.
NULL-STATUS
Is an integer variable that returns the null status of the select-list column. 1 means the column allows nulls; 0 means it does not.
The following example shows how to use SQLNUL:
WORKING-STORAGE SECTION.
...
* Declare variable for subroutine call.
01 NULL-STATUS PIC S9(9) COMP.
...
PROCEDURE DIVISION.
MAIN.
EXEC SQL WHENEVER SQLERROR GOTO SQL-ERROR END-EXEC.
...
PERFORM HANDLE-NULLS
VARYING J FROM 1 BY 1 UNTIL J > SQLDNUM IN SELDSC.
...
HANDLE-NULLS.
* Find out if column is NOT NULL, and clear high-order bit.
CALL "SQLNUL" USING SELDVTYP(J), SELDVTYP(J), NULL-STATUS.
* If NULL-STATUS = 1, nulls are allowed.
Notice that the first and second parameters in the subroutine call are the same. Respectively, they are the datatype variable before and after its null status bit is cleared.
To process the dynamic query, our example program takes the following steps:
EXEC SQL
PREPARE <statement_name>
FROM {:<host_string>|<string_literal>}
END-EXEC.
EXEC SQL
DECLARE <cursor_name> CURSOR FOR <statement_name>
END-EXEC.
EXEC SQL
DESCRIBE BIND VARIABLES FOR <statement_name>
INTO <bind_descriptor_name>
END-EXEC.
EXEC SQL
OPEN <cursor_name>
[USING DESCRIPTOR <bind_descriptor_name>]
END-EXEC.
EXEC SQL
DESCRIBE [SELECT LIST FOR] <statement_name>
INTO <select_descriptor_name>
END-EXEC.
EXEC SQL
FETCH <cursor_name> USING DESCRIPTOR <select_descriptor_name>
END-EXEC.
EXEC SQL
CLOSE <cursor_name>
END-EXEC.
If the number of select-list items in a dynamic query is known, you can omit DESCRIBE SELECT LIST and use the following Method 3 FETCH statement:
EXEC SQL FETCH <cursor_name> INTO <host_variable_list> END-EXEC.
Or, if the number of placeholders for bind variables in a dynamic SQL statement is known, you can omit DESCRIBE BIND VARIABLES and use the following Method 3 OPEN statement:
EXEC SQL OPEN <cursor_name> [USING <host_variable_list>] END-EXEC.
Next, you see how these statements allow your host program to accept and process a dynamic SQL statement using descriptors.
Note: Several figures accompany the following discussion. To avoid cluttering the figures, it was necessary to confine descriptor tables to 3 elements and to limit the maximum length of names and values to 5 and 10 characters, respectively.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
...
01 SELECT-STMT PIC X(120).
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE SELDSC END-EXEC.
EXEC SQL INCLUDE BNDDSC END-EXEC.
For reference, the INCLUDEd declaration of SELDSC follows:
WORKING-STORAGE SECTION.
...
01 SELDSC.
05 SQLDNUM PIC S9(9) COMP.
05 SQLDFND PIC S9(9) COMP.
05 SELDVAR OCCURS 3 TIMES.
10 SELDV PIC S9(9) COMP.
10 SELDFMT PIC S9(9) COMP.
10 SELDVLN PIC S9(9) COMP.
10 SELDFMTL PIC S9(4) COMP.
10 SELDVTYP PIC S9(4) COMP.
10 SELDI PIC S9(9) COMP.
10 SELDH-VNAME PIC S9(9) COMP.
10 SELDH-MAX-VNAMEL PIC S9(4) COMP.
10 SELDH-CUR-VNAMEL PIC S9(4) COMP.
10 SELDI-VNAME PIC S9(9) COMP.
10 SELDI-MAX-VNAMEL PIC S9(4) COMP.
10 SELDI-CUR-VNAMEL PIC S9(4) COMP.
10 SELDFCLP PIC S9(9) COMP.
10 SELDFCRCP PIC S9(9) COMP.
01 XSELDI.
05 SEL-DI OCCURS 3 TIMES PIC S9(9) COMP.
01 XSELDIVNAME.
05 SEL-DI-VNAME OCCURS 3 TIMES PIC X(5).
01 XSELDV.
05 SEL-DV OCCURS 3 TIMES PIC X(10).
01 XSELDHVNAME.
05 SEL-DH-VNAME OCCURS 3 TIMES PIC X(5).
MOVE 3 TO SQLDNUM IN SELDSC.
MOVE 3 TO SQLDNUM IN BNDDSC.
In our example, you store the maximum lengths of name buffers in the SELDH-MAX-VNAMEL, BNDDH-MAX-VNAMEL, and BNDDI-MAX-VNAMEL tables, and use SQLADR to store the addresses of value and name buffers in the SELDV, SELDI, BNDDV, BNDDI, SELDH-VNAME, BNDDH-VNAME, and BNDDI-VNAME tables.
PROCEDURE DIVISION.
...
PERFORM INIT-SELDSC
VARYING J FROM 1 BY 1 UNTIL J > SQLDNUM IN SELDSC.
PERFORM INIT-BNDDSC
VARYING J FROM 1 BY 1 UNTIL J > SQLDNUM IN BNDDSC.
...
INIT-SELDSC.
MOVE SPACES TO SEL-DV(J).
MOVE SPACES TO SEL-DH-VNAME(J).
MOVE 5 TO SELDH-MAX-VNAMEL(J).
CALL "SQLADR" USING SEL-DV(J), SELDV(J).
CALL "SQLADR" USING SEL-DH-VNAME(J), SELDH-VNAME(J).
CALL "SQLADR" USING SEL-DI(J), SELDI(J).
...
INIT-BNDDSC.
MOVE SPACES TO BND-DV(J).
MOVE SPACES TO BND-DH-VNAME(J).
MOVE SPACES TO BND-DI-VNAME(J).
MOVE 5 TO BNDDH-MAX-VNAMEL(J).
MOVE 5 TO BNDDI-MAX-VNAMEL(J).
CALL "SQLADR" USING BND-DV(J), BNDDV(J).
CALL "SQLADR" USING BND-DH-VNAME(J), BNDDH-VNAME(J).
CALL "SQLADR" USING BND-DI(J), BNDDI(J).
CALL "SQLADR" USING BND-DI-VNAME(J), BNDDI-VNAME(J).
...
Figure 4 - 3 and Figure 4 - 4 represent the resulting descriptors.
Figure 4 - 3. Initialized Select Descriptor
Figure 4 - 4. Initialized Bind Descriptor
DISPLAY "Enter a SELECT statement: " WITH NO ADVANCING.
ACCEPT SELECT-STMT.
We assume the user entered the following string:
SELECT ENAME, EMPNO, COMM FROM EMP WHERE COMM < :BONUS
EXEC SQL PREPARE SQL-STMT FROM :SELECT-STMT END-EXEC.
To declare a cursor for static queries, you use the following syntax:
EXEC SQL DECLARE cursor_name CURSOR FOR SELECT ...
To declare a cursor for dynamic queries, the statement name given to the dynamic query by PREPARE is substituted for the static query. In our example, DECLARE CURSOR defines a cursor named EMP-CURSOR and associates it with SQL-STMT, as follows:
EXEC SQL DECLARE EMP-CURSOR CURSOR FOR SQL-STMT END-EXEC.
Note: You must declare a cursor for all dynamic SQL statements, not just queries. With non-queries, OPENing the cursor executes the dynamic SQL statement.
EXEC SQL
DESCRIBE BIND VARIABLES FOR SQL-STMT
INTO BNDDSC
END-EXEC.
Note that BNDDSC must not be prefixed with a colon.
The DESCRIBE BIND VARIABLES statement must follow the PREPARE statement but precede the OPEN statement.
Figure 4 - 5 shows the bind descriptor in our example after the DESCRIBE. Notice that DESCRIBE has set SQLDFND to the actual number of placeholders found in the processed SQL statement.
Figure 4 - 5. Bind Descriptor after the DESCRIBE
IF SQLDFND IN BNDDSC < 0
DISPLAY "Too many bind variables".
GOTO ROLL-BACK
ELSE
MOVE SQLDFND IN BNDDSC TO SQLDNUM IN BNDDSC.
In our example, a value must be assigned to the bind variable that replaces the placeholder BONUS in the query WHERE clause. Prompt the user for the value, then process it, as follows:
PROCEDURE DIVISION.
...
PERFORM GET-INPUT-VAR
VARYING J FROM 1 BY 1 UNTIL J > SQLDNUM IN BNDDSC.
...
GET-INPUT-VAR.
...
* Replace the 0 DESCRIBEd into the datatype table
* with a 1 to avoid an "invalid datatype" Oracle error.
MOVE 1 TO BNDDVTYP(J).
* Get value of bind variable.
DISPLAY "Enter value of ", BND-DH-VNAME(J).
ACCEPT INPUT-STRING.
UNSTRING INPUT-STRING DELIMITED BY " "
INTO BND-DV(J) COUNT IN BNDDVLN(J).
Assuming that the user supplied a value of 625 for BONUS, Figure 4 - 6 shows the resulting bind descriptor.
Figure 4 - 6. Bind Descriptor after Assigning Values
In our example, OPEN associates EMP-CURSOR with BNDDSC as follows:
EXEC SQL
OPEN EMP-CUR USING DESCRIPTOR BNDDSC
END-EXEC.
Remember, BNDDSC must not be prefixed with a colon.
Then, OPEN executes the SQL statement. With queries, OPEN also identifies the active set and positions the cursor at the first row.
DESCRIBE SELECT LIST puts descriptions of select-list items into a select descriptor. In our example, DESCRIBE readies SELDSC as follows:
EXEC SQL
DESCRIBE SELECT LIST FOR SQL-STMT INTO SELDSC
END-EXEC.
Accessing the Oracle data dictionary, DESCRIBE sets the length and datatype of each select-list value.
Figure 4 - 7 shows the select descriptor in our example after the DESCRIBE. Notice that DESCRIBE has set SQLDFND to the actual number of items found in the query select list. If the SQL statement is not a query, SQLDFND is set to zero.
Also notice that the NUMBER lengths are not usable yet. For columns defined as NUMBER, you must use the library subroutine SQLPRC to extract precision and scale. See the section "Coercing Datatypes" .
Figure 4 - 7. Select Descriptor after the DESCRIBE
MOVE SQLDFND IN SELDSC TO SQLDNUM IN SELDSC.
PROCEDURE DIVISION.
...
PERFORM COERCE-COLUMN-TYPE
VARYING J FROM 1 BY 1 UNTIL J > SQLDNUM IN SELDSC.
...
COERCE-COLUMN-TYPE.
* Clear NULL bit.
CALL "SQLNUL" USING SELDVTYP(J), SELDVTYP(J), NULL-STATUS.
* If datatype is DATE, lengthen to 9 characters.
IF SELDVTYP(J) = 12
MOVE 9 TO SELDVLN(J).
* If datatype is NUMBER, extract precision and scale.
MOVE 0 TO DISPLAY-LENGTH.
IF SELDVTYP(J) = 2 AND PRECISION = 0
MOVE 10 TO DISPLAY-LENGTH.
IF SELDVTYP(J) = 2 AND PRECISION > 0
ADD 2 TO PRECISION
MOVE PRECISION TO DISPLAY-LENGTH.
IF SELDVTYP(J) = 2
IF DISPLAY-LENGTH > MAX-LENGTH
DISPLAY "Column value too large for data buffer."
GO TO END-PROGRAM
ELSE
MOVE DISPLAY-LENGTH TO SELDVLN(J).
* Coerce datatypes to VARCHAR2.
MOVE 1 TO SELDVTYP(J).
Figure 4 - 8 shows the resulting select descriptor. Notice that the NUMBER lengths are now usable and that all the datatypes are VARCHAR2. The lengths in SELDVLN(2) and SELDVLN(3) are 6 and 9 because we increased the DESCRIBEd lengths of 4 and 7 by 2 to allow for a possible sign and decimal point.
Figure 4 - 8. Select Descriptor before the FETCH
EXEC SQL
FETCH EMP-CURSOR USING DESCRIPTOR SELDSC
END-EXEC.
Figure 4 - 9 shows the select descriptor in our example after the FETCH. Notice that Oracle has stored the select-list and indicator values in the data buffers addressed by the elements of SELDV and SELDI.
For output buffers of datatype 1, Oracle, using the lengths stored in SELDVLN, left-justifies CHAR or VARCHAR2 data, and right-justifies NUMBER data.
The value "MARTIN" was retrieved from a VARCHAR2(10) column in the EMP table. Using the length in SELDVLN(1), Oracle left-justifies the value in a 10-byte field, filling the buffer.
The value 7654 was retrieved from a NUMBER(4) column and coerced to "7654." However, the length in SELDVLN(2) was increased by two to allow for a possible sign and decimal point, so Oracle right-justifies the value in a 6-byte field.
The value 482.50 was retrieved from a NUMBER(7,2) column and coerced to "482.50." Again, the length in SELDVLN(3) was increased by two, so Oracle right-justifies the value in a 9-byte field.
EXEC SQL
CLOSE EMP-CURSOR
END-EXEC.
Figure 4 - 9. Select Descriptor after the FETCH
Set descriptor entries for the Jth select-list item or bind variable, but instead of addressing a single data buffer, SELDVLN(J) or BNDDVLN(J) addresses a table of data buffers. Then use a FOR clause in the EXECUTE or FETCH statement, as appropriate, to tell Oracle the number of table elements you want to process.
This procedure is necessary, because Oracle has no other way of knowing the size of your host table.
In the example below, two input host tables are used to INSERT data into the EMP table. Note that EXECUTE can be used for non-queries with Method 4.
IDENTIFICATION DIVISION.
PROGRAM-ID. DYN4INS.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 BNDDSC.
02 SQLDNUM PIC S9(9) COMP VALUE 2.
02 SQLDFND PIC S9(9) COMP.
02 BNDDVAR OCCURS 2 TIMES.
03 BNDDV PIC S9(9) COMP.
03 BNDDFMT PIC S9(9) COMP.
03 BNDDVLN PIC S9(9) COMP.
03 BNDDFMTL PIC S9(4) COMP.
03 BNDDVTYP PIC S9(4) COMP.
03 BNDDI PIC S9(9) COMP.
03 BNDDH-VNAME PIC S9(9) COMP.
03 BNDDH-MAX-VNAMEL PIC S9(4) COMP.
03 BNDDH-CUR-VNAMEL PIC S9(4) COMP.
03 BNDDI-VNAME PIC S9(9) COMP.
03 BNDDI-MAX-VNAMEL PIC S9(4) COMP.
03 BNDDI-CUR-VNAMEL PIC S9(4) COMP.
03 BNDDFCLP PIC S9(9) COMP.
03 BNDDFCRCP PIC S9(9) COMP.
01 XBNDDI.
03 BND-DI OCCURS 2 TIMES PIC S9(4) COMP.
01 XBNDDIVNAME.
03 BND-DI-VNAME OCCURS 2 TIMES PIC X(80).
01 XBNDDV.
* Since you know what the SQL statement will be, you can set
* up a two-dimensional table with a maximum of 2 columns and
* 8 rows. Each element can be up to 10 characters long. (You
* can alter these values according to your needs.)
03 BND-COLUMN OCCURS 2 TIMES.
05 BND-ELEMENT OCCURS 8 TIMES PIC X(10).
01 XBNDDHVNAME.
03 BND-DH-VNAME OCCURS 2 TIMES PIC X(80).
01 COLUMN-INDEX PIC 999.
01 ROW-INDEX PIC 999.
01 DUMMY-INTEGER PIC 9999.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(20).
01 PASSWD PIC X(20).
01 DYN-STATEMENT PIC X(80).
01 NUMBER-OF-ROWS PIC S9(4) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
PROCEDURE DIVISION.
START-MAIN.
EXEC SQL WHENEVER SQLERROR GOTO SQL-ERROR END-EXEC.
MOVE "SCOTT" TO USERNAME.
MOVE "TIGER" TO PASSWD.
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWD
END-EXEC.
DISPLAY "Connected to Oracle".
* Initialize bind and select descriptors.
PERFORM INIT-BNDDSC THRU INIT-BNDDSC-EXIT
VARYING COLUMN-INDEX FROM 1 BY 1
UNTIL COLUMN-INDEX > 2.
* Set up the SQL statement.
MOVE SPACES TO DYN-STATEMENT.
MOVE "INSERT INTO EMP(EMPNO, DEPTNO) VALUES(:E,:D)"
TO DYN-STATEMENT.
* Prepare the SQL statement.
EXEC SQL
PREPARE S1 FROM :DYN-STATEMENT
END-EXEC.
* Describe the bind variables.
EXEC SQL
DESCRIBE BIND VARIABLES FOR S1 INTO BNDDSC
END-EXEC.
PERFORM Z-BIND-TYPE THRU Z-BIND-TYPE-EXIT
VARYING COLUMN-INDEX FROM 1 BY 1
UNTIL COLUMN-INDEX > 2.
IF SQLDFND IN BNDDSC < 0
DISPLAY "TOO MANY BIND VARIABLES."
GO TO SQL-ERROR
ELSE
DISPLAY "BIND VARS = " WITH NO ADVANCING
MOVE SQLDFND IN BNDDSC TO DUMMY-INTEGER
DISPLAY DUMMY-INTEGER
MOVE SQLDFND IN BNDDSC TO SQLDNUM IN BNDDSC.
MOVE 8 TO NUMBER-OF-ROWS.
PERFORM GET-ALL-VALUES THRU GET-ALL-VALUES-EXIT
VARYING ROW-INDEX FROM 1 BY 1
UNTIL ROW-INDEX > NUMBER-OF-ROWS.
* Execute the SQL statement.
EXEC SQL FOR :NUMBER-OF-ROWS
EXECUTE S1 USING DESCRIPTOR BNDDSC
END-EXEC.
DISPLAY "INSERTED " WITH NO ADVANCING.
MOVE SQLERRD(3) TO DUMMY-INTEGER.
DISPLAY DUMMY-INTEGER WITH NO ADVANCING.
DISPLAY " ROWS.".
GO TO END-SQL.
SQL-ERROR.
* Display any SQL error message and code.
DISPLAY SQLERRMC.
EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
STOP RUN.
END-SQL.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
EXEC SQL COMMIT WORK RELEASE END-EXEC.
STOP RUN.
INIT-BNDDSC.
* Start of COBOL PERFORM procedures, initialize the bind
* descriptor.
MOVE 80 TO BNDDH-MAX-VNAMEL(COLUMN-INDEX).
CALL "SQLADR" USING
BND-DH-VNAME(COLUMN-INDEX)
BNDDH-VNAME(COLUMN-INDEX).
MOVE 80 TO BNDDI-MAX-VNAMEL(COLUMN-INDEX).
CALL "SQLADR" USING
BND-DI-VNAME(COLUMN-INDEX)
BNDDI-VNAME (COLUMN-INDEX).
MOVE 10 TO BNDDVLN(COLUMN-INDEX).
CALL "SQLADR" USING
BND-ELEMENT(COLUMN-INDEX,1)
BNDDV(COLUMN-INDEX).
MOVE ZERO TO BNDDI(COLUMN-INDEX).
CALL "SQLADR" USING
BND-DI(COLUMN-INDEX)
BNDDI(COLUMN-INDEX).
MOVE ZERO TO BNDDFMT(COLUMN-INDEX).
MOVE ZERO TO BNDDFMTL(COLUMN-INDEX).
MOVE ZERO TO BNDDFCLP(COLUMN-INDEX).
MOVE ZERO TO BNDDFCRCP(COLUMN-INDEX).
INIT-BNDDSC-EXIT.
EXIT.
Z-BIND-TYPE.
* Replace the 0s DESCRIBEd into the datatype table with 1s to
* avoid an "invalid datatype" Oracle error.
MOVE 1 TO BNDDVTYP(COLUMN-INDEX).
Z-BIND-TYPE-EXIT.
EXIT.
GET-ALL-VALUES.
* Get the bind variables for each row.
DISPLAY "ENTER VALUES FOR ROW NUMBER ",ROW-INDEX.
PERFORM GET-BIND-VARS
VARYING COLUMN-INDEX FROM 1 BY 1
UNTIL COLUMN-INDEX > SQLDFND IN BNDDSC.
GET-ALL-VALUES-EXIT.
EXIT.
GET-BIND-VARS.
* Get the value of each bind variable.
DISPLAY " ENTER VALUE FOR ",BND-DH-VNAME(COLUMN-INDEX)
WITH NO ADVANCING.
ACCEPT BND-ELEMENT(COLUMN-INDEX,ROW-INDEX).
GET-BIND-VARS-EXIT.
EXIT.
IDENTIFICATION DIVISION.
PROGRAM-ID. DYNSQL4.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 BNDDSC.
02 SQLDNUM PIC S9(9) COMP VALUE 20.
02 SQLDFND PIC S9(9) COMP.
02 BNDDVAR OCCURS 20 TIMES.
03 BNDDV PIC S9(9) COMP.
03 BNDDFMT PIC S9(9) COMP.
03 BNDDVLN PIC S9(9) COMP.
03 BNDDFMTL PIC S9(4) COMP.
03 BNDDVTYP PIC S9(4) COMP.
03 BNDDI PIC S9(9) COMP.
03 BNDDH-VNAME PIC S9(9) COMP.
03 BNDDH-MAX-VNAMEL PIC S9(4) COMP.
03 BNDDH-CUR-VNAMEL PIC S9(4) COMP.
03 BNDDI-VNAME PIC S9(9) COMP.
03 BNDDI-MAX-VNAMEL PIC S9(4) COMP.
03 BNDDI-CUR-VNAMEL PIC S9(4) COMP.
03 BNDDFCLP PIC S9(9) COMP.
03 BNDDFCRCP PIC S9(9) COMP.
01 XBNDDI.
03 BND-DI OCCURS 20 TIMES PIC S9(4) COMP.
01 XBNDDIVNAME.
03 BND-DI-VNAME OCCURS 20 TIMES PIC X(80).
01 XBNDDV.
03 BND-DV OCCURS 20 TIMES PIC X(80).
01 XBNDDHVNAME.
03 BND-DH-VNAME OCCURS 20 TIMES PIC X(80).
01 SELDSC.
02 SQLDNUM PIC S9(9) COMP VALUE 20.
02 SQLDFND PIC S9(9) COMP.
02 SELDVAR OCCURS 20 TIMES.
03 SELDV PIC S9(9) COMP.
03 SELDFMT PIC S9(9) COMP.
03 SELDVLN PIC S9(9) COMP.
03 SELDFMTL PIC S9(4) COMP.
03 SELDVTYP PIC S9(4) COMP.
03 SELDI PIC S9(9) COMP.
03 SELDH-VNAME PIC S9(9) COMP.
03 SELDH-MAX-VNAMEL PIC S9(4) COMP.
03 SELDH-CUR-VNAMEL PIC S9(4) COMP.
03 SELDI-VNAME PIC S9(9) COMP.
03 SELDI-MAX-VNAMEL PIC S9(4) COMP.
03 SELDI-CUR-VNAMEL PIC S9(4) COMP.
03 SELDFCLP PIC S9(9) COMP.
03 SELDFCRCP PIC S9(9) COMP.
01 XSELDI.
03 SEL-DI OCCURS 20 TIMES PIC S9(4) COMP.
01 XSELDIVNAME.
03 SEL-DI-VNAME OCCURS 20 TIMES PIC X(80).
01 XSELDV.
03 SEL-DV OCCURS 20 TIMES PIC X(80).
01 XSELDHVNAME.
03 SEL-DH-VNAME OCCURS 20 TIMES PIC X(80).
01 TABLE-INDEX PIC 9(3).
01 VAR-COUNT PIC 9(2).
01 ROW-COUNT PIC 9(4).
01 NO-MORE-DATA PIC X(1) VALUE "N".
01 NULLS-ALLOWED PIC S9(9) COMP.
01 PRECISION PIC S9(9) COMP.
01 SCALE PIC S9(9) COMP.
01 DISPLAY-LENGTH PIC S9(9) COMP.
01 MAX-LENGTH PIC S9(9) COMP VALUE 80.
01 COLUMN-NAME PIC X(30).
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(20).
01 PASSWD PIC X(20).
01 DYN-STATEMENT PIC X(80).
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
PROCEDURE DIVISION.
START-MAIN.
EXEC SQL
WHENEVER SQLERROR GOTO SQL-ERROR
END-EXEC.
DISPLAY "USERNAME: " WITH NO ADVANCING.
ACCEPT USERNAME.
DISPLAY "PASSWORD: " WITH NO ADVANCING.
ACCEPT PASSWD.
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWD
END-EXEC.
DISPLAY "CONNECTED TO ORACLE AS USER: ", USERNAME.
* Initialize the bind and select descriptors.
PERFORM INIT-BNDDSC
VARYING TABLE-INDEX FROM 1 BY 1
UNTIL TABLE-INDEX > 20.
PERFORM INIT-SELDSC
VARYING TABLE-INDEX FROM 1 BY 1
UNTIL TABLE-INDEX > 20.
* Get a SQL statement from the operator.
DISPLAY "ENTER SQL STATEMENT WITHOUT TERMINATOR:".
DISPLAY ">" WITH NO ADVANCING.
ACCEPT DYN-STATEMENT.
DISPLAY " ".
* Prepare the SQL statement and declare a cursor.
EXEC SQL
PREPARE S1 FROM :DYN-STATEMENT
END-EXEC.
EXEC SQL
DECLARE C1 CURSOR FOR S1
END-EXEC.
* Describe any bind variables.
EXEC SQL
DESCRIBE BIND VARIABLES FOR S1 INTO BNDDSC
END-EXEC.
IF SQLDFND IN BNDDSC < 0
DISPLAY "TOO MANY BIND VARIABLES."
GO TO END-SQL
ELSE
DISPLAY "NUMBER OF BIND VARIABLES: " WITH NO ADVANCING
MOVE SQLDFND IN BNDDSC TO VAR-COUNT
DISPLAY VAR-COUNT
MOVE SQLDFND IN BNDDSC TO SQLDNUM IN BNDDSC
END-IF.
* Replace the 0s described into the datatype fields of the bind
* descriptor with 1s to avoid an Oracle "invalid datatype"
* error.
MOVE 1 TO TABLE-INDEX.
FIX-BIND-TYPE.
MOVE 1 TO BNDDVTYP(TABLE-INDEX).
ADD 1 TO TABLE-INDEX.
IF TABLE-INDEX <= 20
GO TO FIX-BIND-TYPE.
* Let the user fill in the bind variables.
IF SQLDFND IN BNDDSC = 0
GO TO DESCRIBE-ITEMS.
MOVE 1 TO TABLE-INDEX.
GET-BIND-VAR.
DISPLAY "ENTER VALUE FOR ", BND-DH-VNAME(TABLE-INDEX).
ACCEPT BND-DV(TABLE-INDEX).
ADD 1 TO TABLE-INDEX.
IF TABLE-INDEX <= SQLDFND IN BNDDSC
GO TO GET-BIND-VAR.
DESCRIBE-ITEMS.
* Open the cursor and describe the select-list items.
EXEC SQL
OPEN C1 USING DESCRIPTOR BNDDSC
END-EXEC.
EXEC SQL
DESCRIBE SELECT LIST FOR S1 INTO SELDSC
END-EXEC.
IF SQLDFND IN SELDSC < 0
DISPLAY "TOO MANY SELECT-LIST ITEMS."
GO TO END-SQL
ELSE
DISPLAY "NUMBER OF SELECT-LIST ITEMS: "
WITH NO ADVANCING
MOVE SQLDFND IN SELDSC TO VAR-COUNT
DISPLAY VAR-COUNT
DISPLAY " "
MOVE SQLDFND IN SELDSC TO SQLDNUM IN SELDSC
END-IF.
* Coerce the datatype of all select-list items to VARCHAR2.
IF SQLDNUM IN SELDSC > 0
PERFORM COERCE-COLUMN-TYPE
VARYING TABLE-INDEX FROM 1 BY 1
UNTIL TABLE-INDEX > SQLDNUM IN SELDSC
DISPLAY " ".
* Fetch each row and print each select-list value.
IF SQLDNUM IN SELDSC > 0
PERFORM FETCH-ROWS UNTIL NO-MORE-DATA = "Y".
DISPLAY " "
DISPLAY "NUMBER OF ROWS PROCESSED: " WITH NO ADVANCING.
MOVE SQLERRD(3) TO ROW-COUNT.
DISPLAY ROW-COUNT.
* Clean up and terminate.
EXEC SQL
CLOSE C1
END-EXEC.
EXEC SQL
COMMIT WORK RELEASE
END-EXEC.
DISPLAY " ".
DISPLAY "HAVE A GOOD DAY!".
DISPLAY " ".
STOP RUN.
SQL-ERROR.
* Display Oracle error message and code.
DISPLAY " ".
DISPLAY SQLERRMC.
END-SQL.
EXEC SQL
WHENEVER SQLERROR CONTINUE
END-EXEC.
EXEC SQL
ROLLBACK WORK RELEASE
END-EXEC.
STOP RUN.
* PERFORMed subroutines begin here:
INIT-BNDDSC.
* Initialize the bind descriptor.
MOVE SPACES TO BND-DH-VNAME(TABLE-INDEX).
MOVE 80 TO BNDDH-MAX-VNAMEL(TABLE-INDEX).
CALL "SQLADR" USING
BND-DH-VNAME(TABLE-INDEX)
BNDDH-VNAME(TABLE-INDEX).
MOVE SPACES TO BND-DI-VNAME(TABLE-INDEX).
MOVE 80 TO BNDDI-MAX-VNAMEL(TABLE-INDEX).
CALL "SQLADR" USING
BND-DI-VNAME(TABLE-INDEX)
BNDDI-VNAME (TABLE-INDEX).
MOVE SPACES TO BND-DV(TABLE-INDEX).
MOVE 80 TO BNDDVLN(TABLE-INDEX).
CALL "SQLADR" USING
BND-DV(TABLE-INDEX)
BNDDV(TABLE-INDEX).
MOVE ZERO TO BND-DI(TABLE-INDEX).
CALL "SQLADR" USING
BND-DI(TABLE-INDEX)
BNDDI(TABLE-INDEX).
MOVE ZERO TO BNDDFMT(TABLE-INDEX).
MOVE ZERO TO BNDDFMTL(TABLE-INDEX).
MOVE ZERO TO BNDDFCLP(TABLE-INDEX).
MOVE ZERO TO BNDDFCRCP(TABLE-INDEX).
EXIT.
INIT-SELDSC.
* Initialize the select descriptor.
MOVE SPACES TO SEL-DH-VNAME(TABLE-INDEX).
MOVE 80 TO SELDH-MAX-VNAMEL(TABLE-INDEX).
CALL "SQLADR" USING
SEL-DH-VNAME(TABLE-INDEX)
SELDH-VNAME(TABLE-INDEX).
MOVE SPACES TO SEL-DI-VNAME(TABLE-INDEX).
MOVE 80 TO SELDI-MAX-VNAMEL(TABLE-INDEX).
CALL "SQLADR" USING
SEL-DI-VNAME(TABLE-INDEX)
SELDI-VNAME (TABLE-INDEX).
MOVE SPACES TO SEL-DV(TABLE-INDEX).
MOVE 80 TO SELDVLN(TABLE-INDEX).
CALL "SQLADR" USING
SEL-DV(TABLE-INDEX)
SELDV(TABLE-INDEX).
MOVE ZERO TO SEL-DI(TABLE-INDEX).
CALL "SQLADR" USING
SEL-DI(TABLE-INDEX)
SELDI(TABLE-INDEX).
MOVE ZERO TO SELDFMT(TABLE-INDEX).
MOVE ZERO TO SELDFMTL(TABLE-INDEX).
MOVE ZERO TO SELDFCLP(TABLE-INDEX).
MOVE ZERO TO SELDFCRCP(TABLE-INDEX).
EXIT.
COERCE-COLUMN-TYPE.
* Coerce select-list datatypes to VARCHAR2.
CALL "SQLNUL" USING
SELDVTYP(TABLE-INDEX)
SELDVTYP(TABLE-INDEX)
NULLS-ALLOWED.
* If datatype is date, lengthen to 9 characters.
IF SELDVTYP(TABLE-INDEX) = 12
MOVE 9 TO SELDVLN(TABLE-INDEX).
* IF DATATYPE IS NUMBER, SET LENGTH TO PRECISION.
IF SELDVTYP(TABLE-INDEX) = 2
CALL "SQLPRC" USING SELDVLN(TABLE-INDEX) PRECISION SCALE.
MOVE 0 TO DISPLAY-LENGTH.
IF SELDVTYP(TABLE-INDEX) = 2 AND PRECISION = 0
MOVE 40 TO DISPLAY-LENGTH.
IF SELDVTYP(TABLE-INDEX) = 2 AND PRECISION > 0
ADD 2 TO PRECISION
MOVE PRECISION TO DISPLAY-LENGTH.
IF SELDVTYP(TABLE-INDEX) = 2
IF DISPLAY-LENGTH > MAX-LENGTH
DISPLAY "COLUMN VALUE TOO LARGE FOR DATA BUFFER."
GO TO END-SQL
ELSE
MOVE DISPLAY-LENGTH TO SELDVLN(TABLE-INDEX).
* Coerce datatypes to VARCHAR2.
MOVE 1 TO SELDVTYP(TABLE-INDEX).
* Display column heading.
MOVE SEL-DH-VNAME(TABLE-INDEX) TO COLUMN-NAME.
DISPLAY COLUMN-NAME(1:SELDVLN(TABLE-INDEX)), " "
WITH NO ADVANCING.
EXIT.
FETCH-ROWS.
* Fetch a row and print the select-list value.
EXEC SQL
FETCH C1 USING DESCRIPTOR SELDSC
END-EXEC.
IF SQLCODE NOT = 0
MOVE "Y" TO NO-MORE-DATA.
IF SQLCODE = 0
PERFORM PRINT-COLUMN-VALUES
VARYING TABLE-INDEX FROM 1 BY 1
UNTIL TABLE-INDEX > SQLDNUM IN SELDSC
DISPLAY " ".
EXIT.
PRINT-COLUMN-VALUES.
* Print a select-list value.
DISPLAY SEL-DV(TABLE-INDEX)(1:SELDVLN(TABLE-INDEX)), " "
WITH NO ADVANCING.
EXIT.
![]() ![]() Prev Next |
![]() Copyright © 1997 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |