strange behavior of TOraScript

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
cis-wurzen
Posts: 75
Joined: Tue 04 Jan 2005 10:26

strange behavior of TOraScript

Post by cis-wurzen » Wed 08 Apr 2020 19:41

Sample code:

Code: Select all

{$APPTYPE CONSOLE}

program a;

uses
  SysUtils, Ora, OraScript, OraError, DAScript;

type
  TEventDummy = class(TObject)
    procedure ScriptError(Sender: TObject; E: Exception; SQL: string; var Action: TErrorAction);
    procedure ScriptBeforeExecute(Sender: TObject; var SQL: string; var Omit: Boolean);
  end;

{ TEventDummy }

procedure TEventDummy.ScriptBeforeExecute(Sender: TObject; var SQL: string; var Omit: Boolean);
begin
  Writeln('------------------');
  Writeln('BEFORE-EXEC: ' + SQL);
  Writeln('------------------');
end;

procedure TEventDummy.ScriptError(Sender: TObject; E: Exception; SQL: string; var Action: TErrorAction);
begin
  Writeln('------------------');
  Writeln('ERROR: ' + E.Message);
  Writeln('SQL: ' + SQL);
  Writeln('------------------');
  Action := eaContinue;
end;

var
  Session: TOraSession;
  Script: TOraScript;
  Event: TEventDummy;

begin
  Writeln(ODACVersion);

  Session := TOraSession.Create(nil);
  Script  := TOraScript.Create(nil);

  Event := TEventDummy.Create;

  Script.Session := Session;
  Script.OnError := Event.ScriptError;
  Script.BeforeExecute := Event.ScriptBeforeExecute;

  Session.ConnectString := 'any/valid@login';

  Script.SQL.Add('DROP TABLE FOO;');
  Script.SQL.Add('DROP TABLE BAR;');
  Script.SQL.Add('DROP JAVA SOURCE BAZ;');
  Script.SQL.Add('DROP TABLE FOOBAR;');
  Script.SQL.Add('DROP TABLE BARFOO;');
  Script.SQL.Add('-- add any number of lines here');
  Script.SQL.Add('-- all lines captured in last exec!');
  Script.SQL.Add('DROP TABLE BAZBAZ;');

  Script.Execute;
end.
Output:

Code: Select all

11.1.3
------------------
BEFORE-EXEC: DROP TABLE FOO
------------------
------------------
ERROR: ORA-00942: table or view does not exist (as expected)

SQL: DROP TABLE FOO
------------------
------------------
BEFORE-EXEC: DROP TABLE BAR
------------------
------------------
ERROR: ORA-00942: table or view does not exist (as expected)

SQL: DROP TABLE BAR
------------------
------------------
BEFORE-EXEC: DROP JAVA SOURCE BAZ;
DROP TABLE FOOBAR;
DROP TABLE BARFOO;
-- add any number of lines here
-- all lines captured in last exec!
DROP TABLE BAZBAZ;
------------------
------------------
ERROR: ORA-00933: SQL command not properly ended ([b][u]not[/u][/b] expected)

SQL: DROP JAVA SOURCE BAZ;
DROP TABLE FOOBAR;
DROP TABLE BARFOO;
-- add any number of lines here
-- all lines captured in last exec!
DROP TABLE BAZBAZ;
------------------
If "DROP JAVA SOURCE" is in line, every following line will included in active SQL.
Obviously Oracle answers with ORA-00933

cis-wurzen
Posts: 75
Joined: Tue 04 Jan 2005 10:26

Re: strange behavior of TOraScript

Post by cis-wurzen » Thu 09 Apr 2020 15:43

Workaround:

Remove "DROP JAVA SOURCE" lines from script.

Send each "DROP JAVA SOURCE" as single line script without the ending semicolon.

With semicolon ORA-00933 is raised again.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: strange behavior of TOraScript

Post by MaximG » Mon 13 Apr 2020 10:12

We've reproduced the issue and fixed it. The fix will be included in the next build of our product.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: strange behavior of TOraScript

Post by MaximG » Mon 19 Jul 2021 11:44

Kindly note that have released a new version of ODAC that contains the fix: https://www.devart.com/odac/download.html

Post Reply