SQL WHENEVER SQLERROR

The SQL WHENEVER statement can be written anywhere in the program. The selected action remains in effect until the next time it's re-defined.

SQL WHENEVER provides a mechanism for error handling in a program. The sql whenever statement causes the program to take corrective acction when pro*c/oracle returns an error.

The syntax is

SQL WHENEVER SQLERROR  ;

The actions that can be taken are
1)CONTINUE in which case the program continues to execute the next instruction of the program.

SQL WHENEVER SQLERROR GOTO l2;
2) GOTO label: In which the program jumps to that label.These labels are what you define in you C program.
 
3)SQL WHENEVER SQLERROR DO sql_error();
In this case you specify a function name with or without arguments.So when oracle returns an error the code calls this function and inside the function you can call exit to quit the program Or return to try and execute the next statement in the program.

4) SQL WHENEVER SQLERROR STOP;
This statement would cause the program to exit immediately.

Source Code Example:
/*

* method1.pc
*
* Example using method 1 dynamic sql statements
*/
#include
#include
#include
#include
#include
#include
#include

/* Define constants for VARCHAR lengths. */
#define UNAME_LEN 20
#define PWD_LEN 40
EXEC SQL DECLARE DB_NAME DATABASE;
/* Declare variables.No declare section is needed if MODE=ORACLE.*/

EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
/* VARCHAR is an Oracle-supplied struct */
varchar password[40];
varchar loc[40];
/* varchar can be in lower case also. */
/* Include the SQL Communications Area.
You can use #include or EXEC SQL INCLUDE. */
EXEC SQL END DECLARE SECTION;

/* Declare error handling function. */
void sql_error();
int total_queried=0;
main()
{
char temp_char[32];
char upd_sql[250]="update t_viol_tx_event set etc_account_id=12 where lane_tx_id=3990950";
/* Connect to ORACLE--
*/
strncpy((char *) username.arr, "int", 20);
/* Set the length component of the VARCHAR. */
username.len = strlen((char *) username.arr);
/* Copy the password. */
strncpy((char *) password.arr, "int",40);
password.len = strlen((char *) password.arr);
strncpy((char*)loc.arr,"OLTPDEV",40);
loc.len=strlen((char*)loc.arr);

/* Register sql_error() as the error handler. */
EXEC SQL WHENEVER SQLERROR DO sql_error();
/* Connect to ORACLE. Program will call sql_error()
* if an error occurs when connecting to the default database.
*/

EXEC SQL CONNECT :username IDENTIFIED BY :password
at DB_NAME using :loc;
/* printf("\nConnected to ORACLE as user: %s\n", username.arr);*/
EXEC ORACLE OPTION (CHAR_MAP=CHARZ);
EXEC SQL AT DB_NAME EXECUTE IMMEDIATE :upd_sql;
/* Disconnect from ORACLE. */
EXEC SQL at DB_NAME COMMIT WORK RELEASE;
exit(0);
}

void sql_error()
{
char err_msg[128];
size_t buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE; //you can change the action
buf_len=sizeof(err_msg);
sqlglm(err_msg,&buf_len,&msg_len);
printf("\n%s\n",err_msg);
EXEC SQL at DB_NAME ROLLBACK RELEASE;
exit(1); // you can use return
}