Error handling [message #386300] |
Fri, 13 February 2009 09:01  |
rroooott
Messages: 11 Registered: February 2009
|
Junior Member |
|
|
Hello everybody!
I am writing a procedure in which I generate an insert statement, with several attributes (about 25). The question is that I read the valuese from a CSV file saved from excel.
If the user compiling Excel file makes an error (e.g. he writes a string instead of an integer), I would like to tell him the attribute from which it did arise the error. Oracle returns the "SQLERRM" but it does not return the attribute, it only writes "ORA-01722: numero non valido", and I can't write, handlig that exception, the attribute in which it is written a string instead of a number.
Can somebody tell me how to get the attribute of the error?
Thank you, regards
|
|
|
|
Re: Error handling [message #386306 is a reply to message #386305] |
Fri, 13 February 2009 09:17   |
rroooott
Messages: 11 Registered: February 2009
|
Junior Member |
|
|
Yes, it is certainly a solution, but for my situation is quite difficult to implement, because the user that compile the excel file could potentially change the order of the attributes (that is, of the colums). So, I can't know if, for example, in the third position there must be an integer. So, I must try to perform the query before making this control.
But is it possible that Oracle cannot tell to the programmer what is the name of the attribute with the error?? It seems so strange to me!!
|
|
|
Re: Error handling [message #386307 is a reply to message #386306] |
Fri, 13 February 2009 09:25   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Rather than to-ing and fro-ing before we get anywhere at all, how about you give a fuller picture of what is going on? A cut down snippet of code, (instead of 25 columns, maybe 3 columns) and tell us exactly what it is that you are trying to / want to do.
|
|
|
Re: Error handling [message #386308 is a reply to message #386306] |
Fri, 13 February 2009 09:28   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Depends on your code. At some point Oracle seems to know that that attribute should be an integer, and this attribute should be a string.
You could read EVERYTHING into varchar2 variables first, then convert the values into the types in which you need them.
At that point you could include an exception block that includes the attribute and the value that creates the problem in the raised error message.
|
|
|
Re: Error handling [message #386311 is a reply to message #386307] |
Fri, 13 February 2009 09:34   |
rroooott
Messages: 11 Registered: February 2009
|
Junior Member |
|
|
- I read the csv file. In the first line they are present the names of the attributes, in a unspecified order. In all the other rows the values.
- I create the insert statement for each row of the csv file, like
insert into table (attr1, attr2, ... , attrn) values (...)
In this way the order of the attributes in the csv file is not important.
- then I have something like (in the loop that reads the lines of the csv files, so that the program executes correct queries and print the error for the uncorrect):
....
BEGIN
EXECUTE IMMEDIATE insert_string;
COMMIT;
....
- then I catch some exception, but I can't catch all the possible exception!, so at the end I have
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('error in row ' || row_n);
DBMS_OUTPUT.put_line(SQLERRM);
UTL_FILE.put_line(error_file, SQLERRM);
I'd like to print, in add to the row of the error (csv file input file has thousands of lines), the column of the error.
Thank you for your attention
|
|
|
Re: Error handling [message #386313 is a reply to message #386311] |
Fri, 13 February 2009 09:44   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
1) Remove the WHEN OTHERS completely.
If you don't expect a specific exception, and don't have some way of handling it, let it raise.
2) Don't use EXECUTE IMMEDIATE
just do selects / inserts etc.. with variables that already have the right data type.
3) Put the CONVERSIONS between datatypes in exceptions blocks, so that you can raise specific exceptions for the specific row attribute
4) And READ THE FORUM GUIDE on how to format code in your post.
|
|
|
|
Re: Error handling [message #386325 is a reply to message #386300] |
Fri, 13 February 2009 13:04   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Quote: |
the user that compile the excel file could potentially change the order of the attributes (that is, of the colums)
|
Surely you have a much bigger problem in this case. Aren't you just sticking random data in the database - what if they swap two integer columns around?
Checking data types isn't going to catch that.
|
|
|
|