How to know whether SQL is successfully executed when programming using Pro*C?

Question

I recently programme using Pro*C to access Oracle database like this:

cmd[128];
dpc_set_sqlca(&sqlca);
strcpy(cmd,"EXECUTE IMMEDIATE");
dpc_exec_immediate(sql_string);
SQLCODE = sqlca.sqlcode;

How to know whether the SQL has successfully executed?

For example, how to know whether a table exists in oracle DB? How to know whether an INSERT statement is successfully executed?

Answer 1

First of all in Pro*C, SQL statements can be executed directly, by using the EXEC SQL directive, like

EXEC SQL INSERT INTO.... VALUES...

All other SQL statements can be executed in the same way. Every Pro*C uses SQLCA.H header file like EXEC SQL INCLUDE SQLCA.H; This line tells the Pro*C precompiler that the program has SQL statements that will need to be interpreted in it.

Dynamic sql can also be executed in Pro*C, like

char *cmd = "INSERT INTO emp VALUES(1234, 'jon', 3)";
EXEC SQL PREPARE q FROM :cmd;
EXEC SQL EXECUTE q;

Or

char cmd[255] = "TRUNCATE TABLE tab_name";
EXEC SQL EXECUTE IMMEDIATE :cmd;

**Note the : symbol, this denotes that the variable is a bind variable to the SQL engine.

Now coming to Exception handling. Below are some of the examples that you could use to handle SQL errors in a Pro*C-

  EXEC SQL INSERT INTO .... VALUES...;
  if SQL_ERROR_FOUND  //Exception Handling starts
  {
       sprintf(err_data,"INSERT/SELECT");                  
       //write error to log file, use SQLCODE to see what particular error happened
       return(-1);
  }

Cursor Open-

 EXEC SQL OPEN c_cursor;
 if SQL_ERROR_FOUND   //Exception Handling starts
 {
    sprintf(err_data,"CURSOR FETCH: cursor=c_item");
    //write error to log file
    return(-1);
 }
 else if NO_DATA_FOUND
 {//do something
 }

Or with dynamic SQL-

 EXEC SQL EXECUTE IMMEDIATE :cmd;
  if SQL_ERROR_FOUND
  {
        sprintf(err_data,"EXECUTE DYNAMIC STATEMENT %s", cmd); 
        //write error to log file 
        return(FATAL);
  }

And so on... Remember, the exception/error handling if..else should be just below the EXEC SQL <statement> for which you would like to handle the errors.

SQLCODE (sqlca.sqlcode) is the most commonly used error code. Declared as a long at the top of the program, preferably just after the inclusion of SQLCA.H as long SQLCODE;. SQLCODE is populated by Oracle after every SQL statement and holds the Oracle server error number. This code can be used for error handling and logging purposes too.