Page 1 of 2
OraScript bug
Posted: Tue 15 May 2012 21:33
by sinys
ODAC 8.1.6 + Delphi XE2
I have bug in OraScript.
if I set
OraScript1.SQL.Text :=
'select 1 from dual;' + #13#10
'-----------------------' + #13#10
'select 2 from dual;';
OraScript parse as 2 command where second connamd = "----------------------- select 2 from dual;"
that is SELECT command begins with a comment, it's no good.
Why OraScript does not ignore comment lines?
Re: OraScript bug
Posted: Wed 16 May 2012 10:02
by AlexP
Hello,
When parsing operators, we don't delete any strings, data, etc. in OraScript. Commands are sent to the server "as is" with the account of the specified separator. Therefore you should delete unnecessary strings with comments from OraScript by yourself
Re: OraScript bug
Posted: Wed 16 May 2012 23:09
by sinys
I don't say delete, I'm say ignore.
Ok, Why work this
OraScript1.SQL.Text :=
'------------------' + #13#10
'select 1 from dual;'; // return result
and don't work this
OraScript1.SQL.Text :=
'------------------' + #13#10
'select 1 from dual;' + #13#10
'------------------'; // return error
I think this is strange.
From TOraScript description:
ability to use standart sql*plus tool syntax added
but sql*plus run second script without error
Re: OraScript bug
Posted: Thu 17 May 2012 11:02
by AlexP
Hello,
The code you provided above runs with no errors. If you enable the Debug mode in the OraScript edit window, enter your text and press Execute, you can see that the lasst comment line is ignored and not sent to the server, therefore the error doesn't occur. Please send the application demonstrating the problem to alexp*devart*com so that we could reproduce the error.
Re: OraScript bug
Posted: Thu 17 May 2012 19:19
by sinys
Excuse me, you are right.
I'm try parse memo from many sql scripts and execute each in OraQuery like this
Code: Select all
OraScript1.SQL.Text := Memo1.Text;
for i := 0 to OraScript1.Statements.Count - 1 do
begin
MyCollection.OraQuery[i].SQL.Text := OraScript1.Statements[i].SQL;
MyCollection.OraQuery[i].Open;
end;
How I can get clear SQL text without comments from OraScript? (without execute OraScript)
Re: OraScript bug
Posted: Fri 18 May 2012 11:32
by AlexP
Hello,
As I wrote above, you won't get a "clean" SQL statement without comments. The following text will be fully sent to the server.
Code: Select all
---------------------
select 1 from dual;
Therefore, independently on whether you execute the script or not for deleting comments, you should implement your parser.
Re: OraScript bug
Posted: Fri 18 May 2012 21:24
by sinys
I think I found another bug:
Delphi XE2 + ODAC 8.1.6
1) Create New VCL Forms Application Project
2) Add OraSession and connect to database
3) Add OraScript and set sql property "bla-bla;"
4) Add button with event
Code: Select all
procedure TForm2.Button1Click(Sender: TObject);
begin
try
OraScript1.Execute;
except
end;
end;
5) Run project and click button
6) I see exception with error ORA-00900, with ignored try...except block
Re: OraScript bug
Posted: Mon 21 May 2012 08:14
by AlexP
Hello,
Exceptions that can be raised during TOraScript execution are handled by the Application.HandleException method by default.
To change such behaviour, you should use TOraScript.OnError event handler in the following way:
procedure TMainForm.Button1Click(Sender: TObject);
begin
try
OraScript1.Execute;
except
end;
end;
procedure TMainForm.OraScriptError(Sender: TObject; E: Exception; SQL: String;
var Action: TErrorAction);
begin
Action := eaFail;
end;
In this case you will be able to handle exceptions on your own.
For more information, please read the ODAC documentation.
Re: OraScript bug
Posted: Mon 21 May 2012 14:34
by sinys
Thank you!
Re: OraScript bug
Posted: Thu 04 Oct 2012 17:06
by sinys
Delphi XE2, ODAC 8.5
Memo1.Text :=
Code: Select all
drop type Complex;
CREATE TYPE Complex AS OBJECT (
rpart REAL, -- "real" attribute
ipart REAL, -- "imaginary" attribute
MEMBER FUNCTION plus (x Complex) RETURN Complex, -- method
MEMBER FUNCTION less (x Complex) RETURN Complex,
MEMBER FUNCTION times (x Complex) RETURN Complex,
MEMBER FUNCTION divby (x Complex) RETURN Complex
);
/
CREATE TYPE BODY Complex AS
MEMBER FUNCTION plus (x Complex) RETURN Complex IS
BEGIN
RETURN Complex(rpart + x.rpart, ipart + x.ipart);
END plus;
MEMBER FUNCTION less (x Complex) RETURN Complex IS
BEGIN
RETURN Complex(rpart - x.rpart, ipart - x.ipart);
END less;
MEMBER FUNCTION times (x Complex) RETURN Complex IS
BEGIN
RETURN Complex(rpart * x.rpart - ipart * x.ipart,
rpart * x.ipart + ipart * x.rpart);
END times;
MEMBER FUNCTION divby (x Complex) RETURN Complex IS
z REAL := x.rpart**2 + x.ipart**2;
BEGIN
RETURN Complex((rpart * x.rpart + ipart * x.ipart) / z,
(ipart * x.rpart - rpart * x.ipart) / z);
END divby;
END;
/
Code: Select all
procedure TForm1.Button1Click(Sender: TObject);
var
i: Integer;
begin
OraScript1.SQL.Text := Memo1.Text;
for i := 0 to OraScript1.Statements.Count - 1 do
ShowMessage(OraScript1.Statements[i].SQL);
end;
Show 3 statement. 3 statement begin with '/ CREATE TYPE BODY...', first char "/".
Please fix it!
Re: OraScript bug
Posted: Mon 08 Oct 2012 09:33
by sinys
up!
Re: OraScript bug
Posted: Mon 08 Oct 2012 09:38
by AlexP
Hello,
If you use the "/" symbol as a separator, you should specify it in the OraScript.Delimiter property, and use it after each command
Re: OraScript bug
Posted: Wed 10 Oct 2012 21:09
by sinys
I think this is a mistake all the same, because the behavior is very strange.
My code works in SQL*plus, but does't work with OraScript.
Code: Select all
drop type Complex;
CREATE TYPE Complex AS OBJECT (
rpart REAL, -- "real" attribute
ipart REAL, -- "imaginary" attribute
MEMBER FUNCTION plus (x Complex) RETURN Complex, -- method
MEMBER FUNCTION less (x Complex) RETURN Complex,
MEMBER FUNCTION times (x Complex) RETURN Complex,
MEMBER FUNCTION divby (x Complex) RETURN Complex
);
/
CREATE TYPE BODY Complex AS
MEMBER FUNCTION plus (x Complex) RETURN Complex IS
BEGIN
RETURN Complex(rpart + x.rpart, ipart + x.ipart);
END plus;
MEMBER FUNCTION less (x Complex) RETURN Complex IS
BEGIN
RETURN Complex(rpart - x.rpart, ipart - x.ipart);
END less;
MEMBER FUNCTION times (x Complex) RETURN Complex IS
BEGIN
RETURN Complex(rpart * x.rpart - ipart * x.ipart,
rpart * x.ipart + ipart * x.rpart);
END times;
MEMBER FUNCTION divby (x Complex) RETURN Complex IS
z REAL := x.rpart**2 + x.ipart**2;
BEGIN
RETURN Complex((rpart * x.rpart + ipart * x.ipart) / z,
(ipart * x.rpart - rpart * x.ipart) / z);
END divby;
END;
/
select 1 from dual;
1)
(without ";" on end)
2)
Code: Select all
CREATE TYPE Complex AS OBJECT (
rpart REAL, -- "real" attribute
ipart REAL, -- "imaginary" attribute
MEMBER FUNCTION plus (x Complex) RETURN Complex, -- method
MEMBER FUNCTION less (x Complex) RETURN Complex,
MEMBER FUNCTION times (x Complex) RETURN Complex,
MEMBER FUNCTION divby (x Complex) RETURN Complex
)
(without ";" on end)
3)
Code: Select all
/
CREATE TYPE BODY Complex AS
MEMBER FUNCTION plus (x Complex) RETURN Complex IS
BEGIN
RETURN Complex(rpart + x.rpart, ipart + x.ipart);
END plus;
MEMBER FUNCTION less (x Complex) RETURN Complex IS
BEGIN
RETURN Complex(rpart - x.rpart, ipart - x.ipart);
END less;
MEMBER FUNCTION times (x Complex) RETURN Complex IS
BEGIN
RETURN Complex(rpart * x.rpart - ipart * x.ipart,
rpart * x.ipart + ipart * x.rpart);
END times;
MEMBER FUNCTION divby (x Complex) RETURN Complex IS
z REAL := x.rpart**2 + x.ipart**2;
BEGIN
RETURN Complex((rpart * x.rpart + ipart * x.ipart) / z,
(ipart * x.rpart - rpart * x.ipart) / z);
END divby;
END;
(with "/" in begin and with ";" on end)
4)
(without "/" in begin and without ";" on end)
TOraScript doesn't recognize the first char "/" as delimiter, but recognizes the second char "/" as delimiter.
Without the first char "/" this script does not work in SQL*plus, so I think it's a mistake.
Re: OraScript bug
Posted: Fri 12 Oct 2012 10:21
by AlexP
hello,
Semicolumn is not an element of a SQL-operator - it is an indication of its ending. When SQL*Plus reads a semicolumn, it knows that an operator ends, and sends it to the database. In PL/SQL, semicolumn is a syntax block element, but not an indication of operator ending. When user enters a DECLARE or BEGIN keyword, SQL*Plus understands that a user executes a PL/SQL block, but not a SQL-operator. However, SQL*Plus must know when the block must end. A slash is used for this, that is an equivalent of the RUN SQL*Plus command.
TOraScript behaviour is different from SQL*Plus's one. TOraScript has no inner operators (such as RUN, SET, etc.) as this component is not an interpreter. We just parse the entered text into operators according to the clause set in the Delimiter property, and then send the commands to the server in turn.
Re: OraScript bug
Posted: Sat 13 Oct 2012 01:04
by sinys
I understand that TOraScript has no inner operators such as RUN, SET, etc, but
Code: Select all
CREATE TYPE Complex AS OBJECT (
rpart REAL, -- "real" attribute
ipart REAL, -- "imaginary" attribute
MEMBER FUNCTION plus (x Complex) RETURN Complex, -- method
MEMBER FUNCTION less (x Complex) RETURN Complex,
MEMBER FUNCTION times (x Complex) RETURN Complex,
MEMBER FUNCTION divby (x Complex) RETURN Complex
);
/
basic DDL operator that doesn't work without "/" char.