OraScript ORA-06502

[sinys]

When OraScript.SQL.Text have error and executing then ODAC send

Code: Select all

declare
CURSOR Cur IS
SELECT Line,Position,Text
FROM All_Errors
WHERE Name = :Name and Type = :Type
ORDER BY Sequence;
Rec User_Errors%ROWTYPE;
StartPos INTEGER;
EndPos INTEGER;
begin
:Res := '';
for Rec in Cur loop
if Rec.Line <> 0 or Rec.Position <> 0 then
:Res := :Res || RPad(Rec.Line || '/' || Rec.Position, 8);
end if;
StartPos := 1;
EndPos := 1;
while EndPos > 0 and StartPos <= Length(Rec.Text) loop
EndPos := InStr(Rec.Text, Chr(10), StartPos);
if EndPos > 0 then
if EndPos <> StartPos then
if StartPos > 1 then
:Res := :Res || RPad(' ', 10);
end if;
:Res := :Res || LTrim(SubStr(Rec.Text, StartPos, EndPos - StartPos)) || Chr(13);
end if;
else
if StartPos > 1 then
:Res := :Res || RPad(' ', 10);
end if;
:Res := :Res || LTrim(SubStr(Rec.Text, StartPos)) || Chr(13);
end if;
StartPos := EndPos + 1;
end loop;
end loop;
end;

that raise ORA-06502 for oracle 9 - 10.2.0.1
and please use sys.all_errors

[AlexP]

Hello,

We cannot reproduce the problem. Please describe the problem in more details.

P.S. Please specify the exact ODAC and Oracle server and client versions

[sinys]

Delphi XE2, ODAC version 9.0.1 (on ODAC 8.6 it's work OK). Oracle 9.2.0.1 I'm try execute on server.

Please see my simple examle.

Instead of warnings like Oracle 11 on Oracle 9 I see Oracle error ORA-06502: PL\SQL: буфер символьных строк слишком маленький ошибка числа или значения

[AlexP]

Hello,

When an error occurs, we get its description with the OCIErrorGet method (from the Oci.dll library), therefore we don't affect the code and the text of the error. We have checked the ODAC versions 8.6.11, 8.6.12 and 9.0.1 - and the ORA-06502 error is returned indeed on Oraclе clients of lower than 11 versions, the ORA-24344 error and PLS errors - on Oracle Client 11. This is due to the behaviour of the clients, and we cannot affect this. To get more detailed information, you should contact Oracle developers.

[sinys]

function TOraSession.GetCompilationError(SQL: _string): _string;
Exists next text:

Code: Select all

      Self.SQL.Text :=
        'declare' + DALineSeparator +
          'CURSOR Cur IS' + DALineSeparator +
            'SELECT Line,Position,Text' + DALineSeparator +
            'FROM All_Errors' + DALineSeparator +
            'WHERE Name = :Name and Type = :Type' + DALineSeparator +
            'ORDER BY Sequence;' + DALineSeparator +
          'Rec User_Errors%ROWTYPE;' + DALineSeparator +
          'StartPos INTEGER;' + DALineSeparator +
          'EndPos INTEGER;' + DALineSeparator +
        'begin' + DALineSeparator +
          ':Res := '''';' + DALineSeparator +
          'for Rec in Cur loop' + DALineSeparator +
            'if Rec.Line <> 0 or Rec.Position <> 0 then' + DALineSeparator +
              ':Res := :Res || RPad(Rec.Line || ''/'' || Rec.Position, 8);' + DALineSeparator +
            'end if;' + DALineSeparator +
            'StartPos := 1;' + DALineSeparator +
            'EndPos := 1;' + DALineSeparator +
            'while EndPos > 0 and StartPos <= Length(Rec.Text) loop' + DALineSeparator +
              'EndPos := InStr(Rec.Text, Chr(10), StartPos);' + DALineSeparator +
              'if EndPos > 0 then' + DALineSeparator +
                'if EndPos <> StartPos then' + DALineSeparator +
                  'if StartPos > 1 then' + DALineSeparator +
                    ':Res := :Res || RPad('' '', 10);' + DALineSeparator +
                  'end if;' + DALineSeparator +
                  ':Res := :Res || LTrim(SubStr(Rec.Text, StartPos, EndPos - StartPos)) || Chr(13);' + DALineSeparator +
                'end if;' + DALineSeparator +
              'else' + DALineSeparator +
                'if StartPos > 1 then' + DALineSeparator +
                  ':Res := :Res || RPad('' '', 10);' + DALineSeparator +
                'end if;' + DALineSeparator +
                ':Res := :Res || LTrim(SubStr(Rec.Text, StartPos)) || Chr(13);' + DALineSeparator +
              'end if;' + DALineSeparator +
              'StartPos := EndPos + 1;' + DALineSeparator +
            'end loop;' + DALineSeparator +
          'end loop;' + DALineSeparator +
        'end;';

      Self.SQL['Res'] := '';
      Self.SQL['Name'] := ObjectName;
      Self.SQL['Type'] := _UpperCase(ObjectType);
      Self.SQL.Execute;

Please fix it:
1) Replace

Code: Select all

'FROM All_Errors' + DALineSeparator"

on

Code: Select all

'FROM sys.all_errors' + DALineSeparator"

2) Before set params values set params dataTypes, paramTypes and size (for fix problem with ORA-06502)

Code: Select all

  
...
          'end loop;' + DALineSeparator +
        'end;';

      Self.SQL['Res'].datatype := ftString;
      Self.SQL['Res'].ParamType := ptInputOutput;
      Self.SQL['Res'].size := 32767; 
      Self.SQL['Res'] := '';
      Self.SQL['Name'].datatype := ftString;
      Self.SQL['Name'].ParamType := ptInput;
      Self.SQL['Name'].size := 30;
      Self.SQL['Name'] := ObjectName;
      Self.SQL['Type'].datatype := ftString;
      Self.SQL['Type'].ParamType := ptInput;
      Self.SQL['Type'].size := 30;
      Self.SQL['Type'] := _UpperCase(ObjectType);
      Self.SQL.Execute;

3) You are always call GetCompilationError

Code: Select all

procedure TOraSQL.InternalExecute(Iters: integer);
begin
  try
    inherited;
  except
    on E: EOraError do begin
      if E.ErrorCode = 24344 then
        raise EOraError.Create(E.ErrorCode,
          E.Message + TOraSession(UsedConnection).GetCompilationError(SQL.Text), E.Component);
      raise EOraError.Create(E.ErrorCode, E.Message, E.Component);
    end
    else
      raise;
  end;
end;

but my application does not need this and at the same time very necessary speed of execution.
Please add the ability to avoid the query to all_errors. (don't call function GetCompilationError)

[AlexP]

Hello,

1) ALL_ERRORS is a public View on sys.ALL_ERRORS, therefore a scheme is unnecessary.
2) Thank you for the information, we have fixed this behaviour. Indeed, to avoid this error in lower client versions, the ptOutput type should be set explicitly to the Res parameter. Since SQL['Name'] returns parameter values, you cannot turn to the properties of this parameter. To set parameter properties, you should use the SQL.ParamByName method.
3) We can't not get the text of this error, since the first error ORA-24344 means more error messages on packages, procedures, triggers, etc. compilation errors, and we have to return the full error text to users.

[sinys]

1) ALL_ERRORS is a public View on sys.ALL_ERRORS, therefore a scheme is unnecessary.

Our customers have ALL_ERRORS table. All IDE for Oracle as rule used username sys for system views to avoid these problems.

2) Thank you for the information, we have fixed this behaviour. Indeed, to avoid this error in lower client versions, the ptOutput type should be set explicitly to the Res parameter. Since SQL['Name'] returns parameter values, you cannot turn to the properties of this parameter. To set parameter properties, you should use the SQL.ParamByName method.

This is what I meant. But please use not only ptOutput as well as dataType and size.

3) We can't not get the text of this error, since the first error ORA-24344 means more error messages on packages, procedures, triggers, etc. compilation errors, and we have to return the full error text to users.

I understand, but I want to handle the error itself more effective for my application.

It seems to have accumulated a few fixes for version 9.0.2. Please release it as soon as possible.

[AlexP]

Hello,

1) We have added scheme name when turning to system views, however, it is incorrect to create tables, fields, etc. identical with system names.
2) We have also added the out parameter type, however, we don't specify its size. since length of this parameter can be less than the maximum length, and this will lead to unnecessary memory allocation.
3) As I wrote before, we cannot refuse retrieving error message text, but you can handle errors by yourself in the onError event.

The new build is planned for the next month. I can now send you a fixed ODAC version. Please specify your e-mail.

[sinys]

I can now send you a fixed ODAC version. Please specify your e-mail.

Ok. I sent request to your email. I'm waiting.

[sinys]

Thanks

[AlexP]

Hello,

I have sent you a fixed ODAC version 9.0.1 with source code.

[sinys]

Code: Select all

  //dtXML            = 116; is moved to MemData

It is not good. Because old code does not supported.
ShowMessage(IntToStr(Integer(OraQuery1.FieldByName('XMLField').DataType)));
1) Old version show dtXML, new version show dtUndefined.
2) TBlobField(OraQuery1.FieldByName('XMLField')).SaveToStream(AStream);
raise exception "Field is not BLOB." but earlier this code to work.

[AlexP]

Hello,

1) Both old and new ODAC versions return type 108 (ftXML)
2) We have reproduced the problems with saving XML to Stream, and will try to fix them as soon as possible

[sinys]

Have you added support Stream to TOraXMLField already?
Or support Variant Array for case OraXMLField.Value := OraBLOBField.Value?

[AlexP]

Hello,

Now, to work with XML fields, you should use the following code:

Code: Select all

  if not TOraXMLField(OraQuery.FieldByName('F_XML')).IsNull then
    TOraXMLField(OraQuery.FieldByName('F_XML')).AsXML.SaveToStream(ms);