Getting good SQL error details in Pro*C?

From: Alan Polansky <alanp_at_tibinc.com>
Date: Fri, 3 Dec 1999 15:03:14 -0500
Message-ID: <s4g8faqagsp145_at_corp.supernews.com>

Using Pro*C , I am doing embedded SQL to insert rows in a C program.

When there is an an SQL INSERT error due to the content of the data (i.e. a field is larger than allowed by the DDL), the SQL/Pro*C/Oracle error handling does NOT indicate which field caused the error, only what the error was. Additionally, I cannot find a way to print out the actual data to be inserted, instead it just prints the SQL statement without the data to be inserted.

How can I get this information when an SQL INSERT error occurs???

Below is an example of an error message produced by my program, using Pro C error handling calls and some additional formatting by my program. I am displaying all of the available error information. As you can see, it is lacking in the neccessary details needed for me to quickly determine the exact cause of the error:

vInsertLogInput() failed. Could not add the log_input record.:Oracle error.Reason:ORA-01401: inserted value too large for column. At column 1 in the SQL statement. SQL statement: insert nto log_input
add_date,app_id,content_type,entry_type,src_agency,src_device_id,s_device_ty pe,src_group,src_interface_id,src_user_id,src_unit,msg_text_id,s eq_nbr,dst_agency,dst_device_id,dst_device_type,dst_group,dst_interface_id,d st_user_id,dst_unit,Version) values
(TO_DATE(:b0,'YYYYMMDDHH24MISS'),:b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,lo g_msg_text_seq.currval
,log_msg_seq.nextval,:b11,:b12,:b13,:b14,:b15,:b16,:b17,:b18). SQL function code 3.

Here is how the above message get produced in the C code:

1. In my code, I register an SQL error handler such as:

EXEC SQL WHENEVER SQLERROR DO sql_error( "vInsertLogInput() failed. Could not add the log_input record" );

2) I am using the following code in my sql_error() function to print out error information if an embedded SQL statement fails. ...
// Get Oracle error message.

sqlglm( ora_err_msg, &err_ret_len, &err_ret_len );

// Get SQL statement text.

sqlgls( sql_stmt, &sql_ret_len, &sql_func_code ); .
sprintf( err_msg, "Oracle error. Reason:%.*s. At column %ld in the SQL statement. SQL statement: %.*s. SQL function code %ld.\n", err_ret_len,

            ora_err_msg,
            sqlca.sqlerrd[ 4 ] + 1,    // change to be relative to 1.
sql_ret_len,
            sql_stmt,
            sql_func_code
            );

Received on Fri Dec 03 1999 - 21:03:14 CET