Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Getting good SQL error details in Pro C
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 into
log_input add_date,app_id,content_type,entry_type,src_agency,src_device_id,s rc_device_type,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,dst_user_id,dst_unit,Version) values
Here is how the above message get produced in the C code:
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 Thu Dec 02 1999 - 15:26:02 CST