| Pro*Fortran Supplement to the Oracle Precompilers |  Library |
 Product |
 Contents |
 Index |
| Pro*Fortran Supplement to the Oracle Precompilers | Library |
Product |
Contents |
Index |
You learn how to declare and use the SQLSTA status variable and the SQLCOD status variable, and how to include the SQL Communications Area (SQLCA). You also learn how to declare and enable the Oracle Communications Area (ORACA).
![[*]](jump.gif)
.
When MODE={ORACLE|ANSI13}, you must declare the SQLCA status variable. SQLCOD and SQLSTA declarations are accepted (not recommended) but are not recognized as status variables. For more information, see "Using the SQL Communications Area" .
When MODE={ANSI|ANSI14}, you can use any one, two, or all three of the SQLCOD, SQLSTA, and SQLCA variables. To determine which variable (or variable combination) is best for your application, see "Using Status Variables when MODE={ANSI|ANSI14}" .
SQLCOD stores error codes and the "not found" condition. It is retained only for compatibility with SQL89 and is likely to be removed from future versions of the standard.
Unlike SQLCOD, SQLSTA stores error and warning codes and uses a standardized coding scheme. After executing a SQL statement, the Oracle server returns a status code to the SQLSTA variable currently in scope. The status code indicates whether a SQL statement executed successfully or raised an exception (error or warning condition). To promote interoperability (the ability of systems to exchange information easily), SQL92 predefines all the common SQL exceptions.
The ORACA is optional and can be declared regardless of the MODE setting. For more information about the ORACA status variable, see "Using the Oracle Communications Area" .
Your program can get the outcome of the most recent executable SQL statement by checking SQLCOD and/or SQLSTA explicitly with your own code after executable SQL and PL/SQL statements. Your program can also check SQLCA implicitly (with the WHENEVER SQLERROR and WHENEVER SQLWARNING statements) or it can check the SQLCA variables explicitly.
Note: When MODE={ORACLE|ANSI13}, you must declare the SQLCA status variable. For more information, see "Using the SQL Communications Area" .
SQLCOD is recognized as a status variable if and only if at least one of the following criteria is satisfied:
When ASSUME_SQLCODE=YES, and when SQLSTA and/or SQLCA are declared as a status variables, the precompiler presumes SQLCOD is declared whether or not it is declared in a Declare Section or of the proper type. This causes Releases 1.6.7 and later to act like Release 1.5 in this regard. For information about the precompiler option ASSUME_SQLCODE, see Chapter 6 in the Programmer's Guide to the Oracle Precompilers.
.
* Declare host and indicator variables.
EXEC SQL BEGIN DECLARE SECTION
...
EXEC SQL END DECLARE SECTION
* Declare status variable.
INTEGER*4 SQLCOD
If declared outside the Declare Section, SQLCOD is recognized as a status variable if and only if ASSUME_SQLCODE=YES. When MODE={ORACLE|ANSI13|ANSI14}, declarations of the SQLCOD variable are ignored.
Access to a local SQLCOD is limited by its scope within your program. After every SQL operation, Oracle returns a status code to the SQLCOD currently in scope. So, your program can learn the outcome of the most recent SQL operation by checking SQLCOD explicitly, or implicitly with the WHENEVER statement.
When you declare SQLCOD instead of the SQLCA in a particular compilation unit, the precompiler allocates an internal SQLCA for that unit. Your host program cannot access the internal SQLCA.
EXEC SQL BEGIN DECLARE SECTION
...
CHARACTER*5 SQLSTA
...
EXEC SQL END DECLARE SECTION
When MODE={ORACLE|ANSI13}, SQLSTA declarations are ignored. Declaring the SQLCA is optional.
When MODE={ORACLE|ANSI13}, the SQLCA is required; if the SQLCA is not declared, compile-time errors will occur. The SQLCA is optional when MODE={ANSI|ANSI14}, but you cannot use the WHENEVER SQLWARNING statement without the SQLCA. So, if you want to use the WHENEVER SQLWARNING statement, you must declare the SQLCA.
When MODE={ANSI|ANSI14}, you must declare either SQLSTA
(see "Declaring SQLSTA" ) or SQLCOD (see
"Declaring SQLCOD" ) or both. The SQLSTA status variable supports the SQLSTA status variable specified by the SQL92 standard. You can use the SQLSTA status variable with or without SQLCOD. For more information see Chapter 8 of the Programmer's Guide to the Oracle Precompilers.
Figure 2 - 1 shows all the variables in the SQLCA. However, SQLWN2, SQLWN5, SQLWN6, SQLWN7, and SQLEXT are not currently in use.
Figure 2 - 1. SQLCA Variable Declarations for Pro*FORTRAN
To ensure portability, LOGICAL variables are used in the SQLCA instead of CHARACTER variables. For a full description of the SQLCA, its fields, and the values its fields can store, see Chapter 8 of the Programmer's Guide to the Oracle Precompilers.
* Include the SQL Communications Area (SQLCA).
EXEC SQL INCLUDE SQLCA
Because it is a COMMON block, the SQLCA must be declared outside the Declare Section. Furthermore, the SQLCA must come before the CONNECT statement and the first executable FORTRAN statement.
You must declare the SQLCA in each subroutine and function that contains SQL statements. Every time a SQL statement in one of the subroutines or functions is executed, Oracle updates the SQLCA held in the COMMON block.
Ordinarily, only the order and datatypes of variables in a COMMON-list matter, not their names. However, you cannot rename the SQLCA variables because the precompiler generates code that refers to them. Thus, all declarations of the SQLCA must be identical.
When you precompile your program, the INCLUDE SQLCA statement is replaced by several variable declarations that allow Oracle to communicate with the program.
If your SQL statement does not cause a parse error, Oracle sets SQLERD(5) to zero. Oracle also sets SQLERD(5) to zero if a parse error begins at the first character, which occupies position zero. So, check SQLERD(5) only if SQLCDE is negative, which means that an error has occurred.
Handle SQL execution errors.
WRITE (*, 10000) SQLEMC
10000 FORMAT (1X, 70A1)
EXEC SQL WHENEVER SQLERROR CONTINUE
EXEC SQL ROLLBACK WORK RELEASE
...
At most, the first 70 characters of message text are stored. For messages longer than 70 characters, you must call the SQLGLM function, which is discussed next.
CALL SQLGLM (MSGBUF, BUFLEN, MSGLEN)
where:
MSGBUF
Is the buffer in which you want Oracle to store the error message. Oracle blank-pads to the end of this buffer.
BUFLEN
Is an integer variable that specifies the maximum length of MSGBUF in bytes.
MSGLEN
Is an integer variable in which Oracle stores the actual length of the error message.
The maximum length of an Oracle error message is 512 characters including the error code, nested messages, and message inserts such as table and column names. The maximum length of an error message returned by SQLGLM depends on the value you specify for BUFLEN. In the following example, you use SQLGLM to get an error message of up to 200 characters in length:
* Declare variables for function call.
LOGICAL*1 MSGBUF(200)
INTEGER*4 BUFLEN
INTEGER*4 MSGLEN
DATA BUFLEN /200/
EXEC SQL WHENEVER SQLERROR GO TO 9000
...
* Handle SQL execution errors.
9000 WRITE (*,9100)
9100 FORMAT (1X, ' >>> Oracle error detected', /)
* Get and display the full text of the error message.
CALL SQLGLM (MSGBUF, BUFLEN, MSGLEN)
WRITE (*, 9200) (MSGBUF(J), J = 1, MSGLEN)
9200 FORMAT (1X, 200A1, /)
...
In the example, SQLGLM is called only when a SQL error has occurred. Always make sure SQLCOD is negative before calling SQLGLM. If you call SQLGLM when SQLCOD is zero, you get the message text associated with a prior SQL statement.
With the WHENEVER statement you can specify actions to be taken when Oracle detects an error, warning condition, or "not found" condition. These actions include continuing with the next statement, calling a subroutine, branching to a labeled statement, or stopping.
Code the WHENEVER statement using the following syntax:
EXEC SQL WHENEVER <condition> <action>
You can have Oracle automatically check the SQLCA for any of the following conditions, which are described in the Programmer's Guide to the Oracle Precompilers:
When Oracle detects one of the preceding conditions, you can have your program take any of the following actions:
When using the WHENEVER ... DO statement, the usual rules for entering and exiting a subroutine apply. However, passing parameters to the subroutine is not allowed. Furthermore, the subroutine must not return a value.
In the following example, WHENEVER SQLERROR DO statements are used to handle specific errors:
EXEC SQL WHENEVER SQLERROR DO CALL INSERR
EXEC SQL INSERT INTO EMP (EMPNO, ENAME, DEPTNO)
VALUES (:MYEMPNO, :MYENAME, :MYDEPTNO)
EXEC SQL WHENEVER SQLERROR DO CALL DELERR
EXEC SQL DELETE FROM DEPT
WHERE DEPTNO = :MYDEPTNO
...
* Error-handling subroutines
SUBROUTINE INSERR
* Check for "duplicate key value" Oracle error.
IF (SQLCDE .EQ. -1) THEN
...
* Check for "value too large" Oracle error.
ELSE IF (SQLCDE .EQ. -1401) THEN
...
ELSE
...
END IF
...
SUBROUTINE DELERR
* Check for the number of rows processed.
IF (SQLERD(3) .EQ. 0) THEN
...
ELSE
...
END IF
...
Notice how the subroutines check variables in the SQLCA to determine a course of action. For more information about the WHENEVER conditions and actions, see Chapter 8 of the Programmer's Guide to the Oracle Precompilers.
A WHENEVER statement stays in effect until superseded by another WHENEVER statement checking for the same condition.
Suggestion: You might want to place WHENEVER statements at the beginning of each program unit that contains SQL statements. That way, SQL statements in one program unit will not reference WHENEVER actions in another program unit, causing errors at compile or run time.
* Improper use of WHENEVER
EXEC SQL WHENEVER NOT FOUND GOTO 7000
6000 EXEC SQL FETCH EMPCUR INTO :MYENAME, :MYSAL
...
GOTO 6000
7000 EXEC SQL DELETE FROM EMP WHERE EMPNO = :MYEMPNO
...
In the next example, you handle the NOT FOUND condition properly by resetting the GOTO target:
* Proper use of WHENEVER
EXEC SQL WHENEVER NOT FOUND GOTO 7000
6000 EXEC SQL FETCH EMPCUR INTO :MYENAME, :MYSAL
...
GOTO 6000
7000 EXEC SQL WHENEVER NOT FOUND GOTO 8000
EXEC SQL DELETE FROM EMP WHERE EMPNO = :MYEMPNO
...
8000 CONTINUE
Verify that all SQL statements governed by a WHENEVER ... GOTO statement can branch to the GOTO label. The following code results in a compilation error because the label 5000 in subroutine DELROW is not within the scope of the INSERT statement in subroutine INSROW:
SUBROUTINE DELROW
...
EXEC SQL WHENEVER SQLERROR GOTO 5000
EXEC SQL DELETE FROM EMP WHERE DEPTNO = :MYDEPTNO
...
5000 WRITE (*, 10000) SQLEMC
10000 FORMAT (1X, 70A1)
RETURN
END
SUBROUTINE INSROW
...
EXEC SQL INSERT INTO EMP (EMPNO, ENAME, DEPTNO)
VALUES (:MYEMPNO, :MYENAME, :MYDEPTNO)
...
Besides helping you to diagnose problems, the ORACA lets you monitor your program's use of Oracle resources such as the SQL Statement Executor and the cursor cache, an area of memory reserved for cursor management.
Figure 2 - 2. ORACA Variable Declarations for Pro*FORTRAN
To ensure portability, LOGICAL variables are used in the ORACA instead of CHARACTER variables. For a full description of the ORACA, its fields, and the values its fields can store, see Chapter 8 of the Programmer's Guide to the Oracle Precompilers.
* Include the Oracle Communications Area (ORACA).
EXEC SQL INCLUDE ORACA
Because it is a COMMON block, the ORACA must be declared outside the Declare Section. Furthermore, the ORACA must come before the CONNECT statement and the first executable FORTRAN statement.
You can redeclare the ORACA in any subroutine or function that contains SQL statements. Every time a SQL statement in the subroutine or function is executed, Oracle updates the ORACA held in COMMON.
Ordinarily, only the order and datatypes of variables in a COMMON-list matter, not their names. However, you cannot rename the ORACA variables because the precompiler generates code that refers to them. Thus, all declarations of the ORACA must be identical.
ORACA=YES
or inline with
* Enable the ORACA.
EXEC ORACLE OPTION (ORACA=YES)
Then, you must choose appropriate runtime options by setting flags in the ORACA. Enabling the ORACA is optional because it adds to runtime overhead. The default setting is ORACA=NO.
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |