Home » SQL & PL/SQL » SQL & PL/SQL » Error handling (oracle 9.x)
Error handling [message #386300] Fri, 13 February 2009 09:01 Go to next message
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 #386305 is a reply to message #386300] Fri, 13 February 2009 09:14 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Test each attribute individually for the appropriate info prior to building your statement.
Re: Error handling [message #386306 is a reply to message #386305] Fri, 13 February 2009 09:17 Go to previous messageGo to next message
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 Go to previous messageGo to next message
pablolee
Messages: 2834
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #386317 is a reply to message #386311] Fri, 13 February 2009 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

5) Get rid of this stuff and use external table.

Regards
Michel
Re: Error handling [message #386325 is a reply to message #386300] Fri, 13 February 2009 13:04 Go to previous messageGo to next message
cookiemonster
Messages: 12409
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.
Re: Error handling [message #386328 is a reply to message #386325] Fri, 13 February 2009 13:36 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7143933880166
Previous Topic: Performance issue while runningthe query
Next Topic: ORA-29279: SMTP permanent error: 553
Goto Forum:
  


Current Time: Tue Dec 06 16:09:41 CST 2016

Total time taken to generate the page: 0.24440 seconds