How I can find position when sql raise error?

[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?

[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;

[sinys]

Thank you very much!

[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.

[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

[sinys]

How do you get the number 189? This number return from Oracle?
This number does not indicate a place with an error.

[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;