Re: Which column caused ORA-01438?

An enhancement (315222) requesting such functionality has been filed by oracle.

If you are interested in doing some research, i have a way. Turn on SQL*NET trace at SUPPORT level from client and give the insert statement.
open the sqlnet trace and scroll upto the packet dump for nspsend: which contains the INSERT statement. Ignore that, after this you will get a packet dump for nsprecv: followed by nspsend: followed by nsprecv: ignore all these and after these you will find nsprecv: packet dump which contains the ORA-01438 message. That dump will look something like this
[02-JUN-2004 12:39:40:050] nsprecv: packet dump
[02-JUN-2004 12:39:40:050] nsprecv: 00 B1 00 00 06 00 00 00
|........|
[02-JUN-2004 12:39:40:050] nsprecv: 00 00 04 01 00 00 00 01
|........|
[02-JUN-2004 12:39:40:050] nsprecv: 00 00 00 00 9E 05 00 00
|........|
[02-JUN-2004 12:39:40:050] nsprecv: 00 00 01 00 36 00 02 00
|....6...|
[02-JUN-2004 12:39:40:050] nsprecv: 00 00 00 00 FF FF FF FF
|........|
[02-JUN-2004 12:39:40:050] nsprecv: 00 00 00 00 00 00 00 00
|........|
[02-JUN-2004 12:39:40:050] nsprecv: 00 00 00 00 00 00 00 00
|........|
[02-JUN-2004 12:39:40:050] nsprecv: 00 0D 00 00 00 00 00 00
|........|
[02-JUN-2004 12:39:40:050] nsprecv: 36 01 00 00 00 00 00 00
|6.......|
[02-JUN-2004 12:39:40:050] nsprecv: D4 A9 16 00 00 00 00 00
|........|
[02-JUN-2004 12:39:40:050] nsprecv: 00 00 00 00 00 00 00 00
|........|
[02-JUN-2004 12:39:40:050] nsprecv: 00 00 00 00 00 00 00 00
|........|
[02-JUN-2004 12:39:40:050] nsprecv: 00 00 00 00 00 00 00 00
|........|
[02-JUN-2004 12:39:40:050] nsprecv: 48 4F 52 41 2D 30 31 34
|HORA-014|
[02-JUN-2004 12:39:40:050] nsprecv: 33 38 3A 20 76 61 6C 75
|38:.valu|
[02-JUN-2004 12:39:40:050] nsprecv: 65 20 6C 61 72 67 65 72
|e.larger|
[02-JUN-2004 12:39:40:050] nsprecv: 20 74 68 61 6E 20 73 70
|.than.sp|
[02-JUN-2004 12:39:40:050] nsprecv: 65 63 69 66 69 65 64 20
|ecified.|
[02-JUN-2004 12:39:40:050] nsprecv: 70 72 65 63 69 73 69 6F
|precisio|
[02-JUN-2004 12:39:40:050] nsprecv: 6E 20 61 6C 6C 6F 77 73
|n.allows|
[02-JUN-2004 12:39:40:050] nsprecv: 20 66 6F 72 20 74 68 69
|.for.thi|
[02-JUN-2004 12:39:40:050] nsprecv: 73 20 63 6F 6C 75 6D 6E
|s.column|
[02-JUN-2004 12:39:40:050] nsprecv: 0A |.
|
[02-JUN-2004 12:39:40:050] nsprecv: normal exit

The 4th line from the first, (i had pasted it below) will contain info about the column which caused the ORA-01438.
[02-JUN-2004 12:39:40:050] nsprecv: 00 00 01 00 36 00 02 00
|....6...|
The fields 36 00 give info about the column. But it is very hard to interpret. (The first column starts at 15, but the next columns depend on the values that you gave in the insert statement).
for ex- if you have a table like this
X(A Number(10), B number(2))
and you gave an insert like this
INSERT INTO X VALUES(123,123)
The column values will look like this
19 00
This means column 2 is o.k. and since you gave 3 digist for the column A, it adds 3+1 to 15 and raises the error at 19. If you have time research and enjoy.
regards
Srivenu Received on Wed Jun 02 2004 - 02:20:49 CDT