Page 1 of 1
How I can find position when sql raise error?
Posted: Mon 06 Aug 2012 06:29
by sinys
For example:
Code: Select all
select
*
from
table1,
table2, -- this table not exist
table3
When I execute this SQL statement I get ORA-00942 error.
How I can find position in sql text when sql raise error?
Re: How I can find position when sql raise error?
Posted: Mon 06 Aug 2012 13:51
by AlexP
Hello,
To define the position of the error, you can use the OraQuery.ErrorOffset property, that returns the position in SQL, where the error occured. And if the initial query is known, you can retrieve the string and the position in this string, for example, in the following way:
Code: Select all
procedure GetErrorPos(SQLText: TStrings; ErrOffset: Integer; out Line, Position: integer);
var
i, len: integer;
begin
len := 0;
for i:= 0 to SQLText.Count - 1 do
if length(SQLText[i]) + len >= ErrOffset then
begin
Line := i;
position := len + length(SQLText[i]) - ErrOffset;
exit;
end
else
len := len + length(SQLText[i]);
end;
procedure TForm1.FormCreate(Sender: TObject);
var
l,p: integer;
begin
try
OraQuery1.Open;
except
on e: EOraError do
begin
GetErrorPos(OraQuery1.SQL, OraQuery1.ErrorOffset, l, p);
ShowMessage(Format('ErrorCode: %d, ErrorMsg: %s, Line: %d, Position: %d',[e.ErrorCode,e.Message,l,p]));
end;
end;
end;
Re: How I can find position when sql raise error?
Posted: Tue 07 Aug 2012 05:34
by sinys
Thank you very much!
Re: How I can find position when sql raise error?
Posted: Wed 10 Oct 2012 20:45
by sinys
I'm trying to use your code for this
Code: Select all
select
s.tablespace_name as "Tablespace",
to_char(sum(s.bytes), 'fm999g999g999g999g990') as "Space (bytes)"
from
dba_free_space s
group by s.tablespace_name
order by sum(s.bytes), s.
ErrorOffset return 189
GetErrorPos doesn't work correct because it is ignore #13#10 (end of line).
And value 189 is strange, because it does not show the place where it was expected.
Re: How I can find position when sql raise error?
Posted: Fri 12 Oct 2012 09:26
by AlexP
hello,
The mentioned GetErrorPos method is a simple example for demonstration of work with the ErrorOffset property, and it doesn't take into account all possible variants when parsing a SQL statement. For real applications, you have to write your own SQL statement parsing and error position displaying method
Re: How I can find position when sql raise error?
Posted: Sat 13 Oct 2012 01:08
by sinys
How do you get the number 189? This number return from Oracle?
This number does not indicate a place with an error.
Re: How I can find position when sql raise error?
Posted: Mon 15 Oct 2012 09:37
by AlexP
hello,
The error position is returned by the server, and as I have written to you before, to detect the row and the place in the row where the error occurs correctly, you should modify the example method, e.g. as follows:
Code: Select all
procedure GetErrorPos(SQLText: TStrings; ErrOffset: Integer; out Line, Position: integer);
var
i: integer;
begin
Position := ErrOffset;
for i:= 0 to SQLText.Count - 1 do
if length(SQLText) + 2 >= Position then
begin
Line := i + 1;
inc(Position);
exit;
end
else
Position := Position - length(SQLText) - 2;
end;