How to trace failed UpdateSQL query

, ,
1

Hi All,

We have a UpdateSQL JDBC query where in there is one input field and there are lots of fields that are to be updated.
When the query fails because of any null values passed/data error we could not able to track as for which column/columns it is failing. Please let me know how to tarce/debug this. Its pretty urgent.
Thanks in advance.

Regards,
Datta

2

Put the service in try catch block.
In catch block call a flow service getLastError.
In the output of get last error you can see the reason why it failed.

3

Hi,

its better to set fields as mandatory to avoid passing of null values to the service.

4

Hi All,

Let me rephrase my question, in error notification email, we will get some thing like ex:

Error: [ADA
.1.316] Cannot execute the SQL statement “UPDATE ABC_Schema.XYZ_Table SET col1 = ?,col2 = sysdate WHERE col3 = ? AND col4 = ? AND col5 = ? AND col6 = ?”."(72000/1008) ORA-01008: not all variables bound "ORA-01008: not all variables bound

The above example is for one or more columns data is not passed.
From this how can can we infer as which column(s) exactly the data is not been set?
Please help me in tracing these types of ORA errors. I am hoping there should be some work around to trace such types of errors.
Thanks in advance.

Regards,
Datta

5

Hi All,

I found a way…the count of question marks (?) should match variables for values that you specify. This is very cumbersome if there are more number of columns or if it is a stored procedure. But I need a simple solution.
Thanks in advance!

Regards,
Datta