Page 1 of 1
OraScript ORA-06502
Posted: Thu 30 May 2013 14:39
by 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
Re: OraScript ORA-06502
Posted: Fri 31 May 2013 06:54
by 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
Re: OraScript ORA-06502
Posted: Sun 02 Jun 2013 08:50
by 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: буфер символьных строк слишком маленький ошибка числа или значения
Re: OraScript ORA-06502
Posted: Mon 03 Jun 2013 08:47
by 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.
Re: OraScript ORA-06502
Posted: Mon 03 Jun 2013 10:38
by 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)
Re: OraScript ORA-06502
Posted: Mon 03 Jun 2013 14:23
by 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.
Re: OraScript ORA-06502
Posted: Mon 03 Jun 2013 14:46
by sinys
AlexP wrote:
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.
AlexP wrote:
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.
AlexP wrote:
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.
Re: OraScript ORA-06502
Posted: Tue 04 Jun 2013 09:33
by 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.
Re: OraScript ORA-06502
Posted: Tue 04 Jun 2013 09:43
by sinys
AlexP wrote:I can now send you a fixed ODAC version. Please specify your e-mail.
Ok. I sent request to your email. I'm waiting.
Re: OraScript ORA-06502
Posted: Wed 05 Jun 2013 08:30
by sinys
Thanks
Re: OraScript ORA-06502
Posted: Wed 05 Jun 2013 10:11
by AlexP
Hello,
I have sent you a fixed ODAC version 9.0.1 with source code.
Re: OraScript ORA-06502
Posted: Wed 05 Jun 2013 13:08
by 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.
Re: OraScript ORA-06502
Posted: Wed 05 Jun 2013 14:38
by 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
Re: OraScript ORA-06502
Posted: Mon 24 Jun 2013 05:52
by sinys
Have you added support Stream to TOraXMLField already?
Or support Variant Array for case OraXMLField.Value := OraBLOBField.Value?
Re: OraScript ORA-06502
Posted: Wed 03 Jul 2013 13:15
by 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);