Pro*C Error Handling
- C programming provides the in-built error handling mechanism which we use in our program. Error handling may be a mechanism that gives the status of our program. We'd like some mechanism that handles the error, so Pro*C contains the 2 error handling concepts which are given below:
Error Handling Concepts
- SQLCA (SQL Communication Area)
- Whenever statement
SQLCA
- SQLCA (SQL Communication Area) is a datastructure used by our program to see for errors.
- This datastructure contains some pre-defined variables used by Oracle. These variables contain the status information of the program, which is passed at the runtime.
The structure of sqlca is given below:
structsqlca {
charsqlcaid[8];
longsqlabc;
longsqlcode;
struct {
unsigned short sqlerrml;
charsqlerrmc[70];
} sqlerrm;
charsqlerrp[8];
longsqlerrd[6];
charsqlwarn[8];
charsqlext[8];
};
SQLCA Components
Sqlca Components
- The following are the components of SQLCA:
- sqlcaid: It's an array of char character which is initialized to "SQLCA", and wont to determine the SQL Communication area.
- sqlcabc: It's declared as an integer type to carry the length of the SQLCA structure in bytes.
- sqlcode: It's declared as an integer type that stores the status code of the foremost recently executed SQL statement. The status determines the result of the SQL statement, and therefore the outcome can are available the subsequent ways:
| Outcome | Description |
|---|---|
| 0 | The statement has been executed successfully with no error. |
| >0 | Some error occurs while executing the statement. For example, when we are using SELECT command with Where clause, then no such row found mentioned in the Where clause condition. |
| <0 | In this case, statement is not executed due to the database, system, application or network error. |
- sqlerrm: It's defined as a structure inside the sqlca.
- This field contains two components:
- sqlerrml: It's declared as an integer type that holds the length of the text message stored in sqlerrmc.
- sqlerrmc: It's declared as a string that holds the text message with reference to the error code stored during a sqlcode.
- sqlerrp: It's declared as a string but reserved for future use.
- sqlerrd: It's declared as an array of integers that holds six elements.
| Fields | Description |
|---|---|
| sqlerrp[0] | It is reserved for future use. |
| sqlerrp[1] | It is reserved for future use. |
| sqlerrp[2] | It contains the no. of rows which are processed by the SQL statement. |
| sqlerrp[3] | It is reserved for the future use. |
| sqlerrp[4] | It contains the character position at which the error begins in the most recently executed statement. |
| sqlerrp[5] | It is reserved for the future use. |
- sqlwarn: It's declared as an array of characters having eight elements. These elements are used as a warning flag in sqlca. If the flag is set , then Oracle assigns 'W' to the element.
| Fields | Description |
|---|---|
| sqlwarn[0] | It is set only when the value of another flag is set. |
| sqlwarn[1] | It is set when the Oracle assigns a truncated value to the output host variable. |
| sqlwarn[2] | It is set when the NULL column value is not considered while computing the SQL aggregate such MAX, MIN, AVG or SUM. |
| sqlwarn[3] | It is set when the no of columns retrieved by using the SELECT statement is not equal to the no. of host variables specified in a INTO clause. |
- sqlext: It's declared as a string which is reserved for future use.
Whenever statement
- The whenever statement is used for error handling. It performs implicit error checking and handling.
The syntax of the whenever statement is given below:
EXEC SQL WHENEVER <condition><action>;
- When the Whenever statement is executed, then the Oracle will automatically check the SQLCA for the condition mentioned within the whenever statement. If such condition is found within the sqlca, then the action given within the whenever statement are going to be performed.
- The condition are often NOT FOUND, SQLERROR, SQLWARNING, and therefore the action are often CONTINUE, GOTO label, STOP, DO routine.
SQL Conditions
- The condition are often of following types:
- SQLWARNING: If the oracle returns a warning, then the sqlwarn[0] are going to be set.
- SQLERROR: If the oracle returns a error , then the worth of sqlcode are going to be negative.
- NOT FOUND: If the Oracle isn't ready to find the row supported the condition mentioned within the WHERE clause of the SELECT statement, then the value of the sqlcode are going to be positive.
Actions
- If the Oracle finds any of the above condition, then the subsequent actions are often taken:
- CONTINUE: The program will continue execution with its next statement if possible.
- DO: In this action, the control are going to be transferred to the error handling function within the program. When the control is reached at the top of the error handling function, then the control are going to be transferred to the statement that comes after the failed SQL statement.
- DO BREAK: This break statement is especially utilized in our program, and may be used as an action in loops. When the condition present inside a WHENEVER statement is satisfied, then our program exits from the loop.
- DO CONTINUE: This continue statement is especially used in our program, and may be used as action in loops. When the condition inside a WHENEVER statement is satisfied, then our program continue with subsequent iteration inside the loop.
- GOTO: The program control transfers to the labeled statement.
- STOP: Our program stops running. When the WHENEVER condition is satisfied, then exit() function is named to prevent the execution of the program.