How to get constraint errors from OCIErrorGet?

Question

Our C ++ program uses Oracle and OCI to work with the database. Sometimes a user causes a constraint violation that we detect, and then display an error message from OCIErrorGet. OCIErrorGet returns strings as follows:

ORA-02292: integrity constraint (MYSCHEMA.CC_MYCONSTRAINT) violated - child record found ORA-06512: at line 5 

I am looking for the cleanest way to extract "MYSCHEMA.CC_MYCONSTRAINT" from an Oracle error. Knowing the name of the restriction, I could show the best error message (our code could find a very meaningful error message if it had access to the name of the restriction).

I could use regex or something else and assume that the Oracle message will never change, but that seems a bit fragile to me. Or I could look for specific ORA codes, and then grab any text between parentheses. But I was hoping that the OCI would be a cleaner / more reliable way if the constraint fails to determine the actual name of the failed constraint without resorting to the tightly-bound string manipulations.

Any ideas?

Answer 1

According to Oracle Docs, string searching is exactly what you need to do:

Recognizing Variables in Messages

To help you find and correct errors, Oracle embeds object names, numbers, and character strings in some messages. These built-in variables are represented by a string, number, or character, as appropriate. For example:

 ORA-00020: maximum number of processes (number) exceeded 

The previous message might look like this:

 ORA-00020: maximum number of processes (50) exceeded 

Oracle makes a big conclusion in its docs that strings will be constantly updated in the "Message Accuracy" section. This is a pretty strong suggestion that they intend to do a string search.

In addition, according to this site , the Oracle Error structure also pretty much implies that they intend to perform a string search, since this structure has nothing else for you:

 array(4) { ["code"]=>int(942) ["message"]=>string(40) "ORA-00942: table or view does not exist" ["offset"]=>int(14) ["sqltext"]=>string(32) "select * from non_existing_table" } 

This output shows the following information:

• The $ erris variable is an array with four elements.

• The first element is accessible using the key code, and its value is number 942.

• The second value is available using the key message, and the value is the string "ORA-00942: table or view does not exist."

• The third value is accessible using key offset, and its value is the number 14. This is a character before the file name non-existent table.

• The fourth element is a problematic SQL message that causes the error in the first place.

I agree with you; it would be great if there was a better way to get the name of the constraint you are breaking, but string matching seems to be the intended way.