OraScript bug

[AlexP]

Hello,

Could you, please, explain in greater detail what you mean by saying "basic DDL operator that doesn't work without "/" char"?
The "CREATE" operator you have provided does not require specifying the "/" character at the end, it works fine without it.

[sinys]

From your site
http://www.devart.com/odac/features.html

Script execution:
Ability to use standard SQL*Plus tool syntax in scripts

Ok, I run SQL*Plus and write:

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

And it doesn't work without "/" char.

Wrere I generated scripts from IDE for Oracle they do it with "/" char for this situation.

[AlexP]

hello,

The problem with incorrect parsing of SQL expressions occurs only when calling the Statements property.
If this property is not used, scripts are parsed and executed without errors, you can check this by turning on the the Debug property of TOraScript.
We'll try to fix this problem by the next build

[sinys]

Thank you, AlexP. I will wait.

Like a new bug: commands "conn", "connect" & "disconnect" not call OraScript.AfterExecute event.

[AlexP]

hello,

As I wrote earlier, TOraScript is not an analogue of SQL Plus, therefore such commands as Connect and Disconnect are executed in the context of TOraSession that is connected with TOraScript, and to intercept these events, you should use the following TOraSession events: AfterConnect, AfterDisconnect.

[AlexP]

Hello,

We have fixed the problem with statement parsing in TOraScript, this fix will be added to the next build

[sinys]

AlexP,
It is excelent news!
I have full source licence, so could I get fixed version here or to my e-mail?

[AlexP]

Hello,

To fix this problem, you should replace the following code in the DAScript.pas module, in the TDAScriptProcessor.ExecuteNext method

Code: Select all

  if (Trim(s) <> '') or GetReady(Code) then begin
  // Execution
    Omit := (FDelimiterState = dsSet) or Omit;
    if FDelimiterState = dsSet then begin
    // DelimiterState := dsNone;
      FCurrDelimiter := NewDelimiter;
    end;
    ExecuteStatement(s, StatementType, Omit, BreakExecution);
    if not Omit and BreakExecution then
      break;
    if Assigned(FParser) then begin //if not BreakExec
      FOwner.FStmtOffset := FParser.CurrPos;
      FOwner.FStartPos := FParser.CurrPos;
      FOwner.FStartLine := FParser.CurrLine;
      FOwner.FStartOffset := FParser.CurrCol;
    end;
    Result := True;
    break;
  end;

with

Code: Select all

  if (Trim(s) <> '') or GetReady(Code) then begin
  // Execution
    Omit := (FDelimiterState = dsSet) or Omit;
    if FDelimiterState = dsSet then begin
    // DelimiterState := dsNone;
      FCurrDelimiter := NewDelimiter;
    end;
    ExecuteStatement(s, StatementType, Omit, BreakExecution);
    if not Omit and BreakExecution then
      break;
    Result := True;
  end;

  if Assigned(FParser) then begin //if not BreakExec
    FOwner.FStmtOffset := FParser.CurrPos;
    FOwner.FStartPos := FParser.CurrPos;
    FOwner.FStartLine := FParser.CurrLine;
    FOwner.FStartOffset := FParser.CurrCol;
  end;

  if Result then
    break;

[sinys]

AlexP,
Thank you!

Next problems:
1) How can I show SQL statements using StartPos&EndPos? (preferably including delimiters)
I'm trying to use
ShowMessage(Copy(OraScript1.SQL.Text, OraScript1.Statements.StartPos, OraScript1.Statements.EndPos - OraScript1.Statements.StartPos + 1));
for my example with cycle, but statements ends differently:
a) As not complete statement OraScript1.Statements.SQL
b) Without delimiters

2) When I'm trying to execute the statement:

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

Attention: In the end "space" + "/" instead of only "/" without "space".

Code: Select all

procedure TForm1.Button2Click(Sender: TObject);
begin
  OraScript1.SQL.Text := Memo1.Text;
  OraScript1.Execute;
end;

procedure TForm1.OraScript1Error(Sender: TObject; E: Exception; SQL: string;
  var Action: TErrorAction);
begin
  Action := eaFail;
end;

I got the error "No errors" instead of the expected "ORA-24344: success with compilation error". Why is this happening?

[AlexP]

Hello,

Thank you for the information.
We've reproduced the problem.
We will notify you as soon as we have any results.

[AlexP]

Hello,

We have fixed the problem with spaces before the "/" symbol in TOraScript, this fix will be included to the next product version.

To display SQL statements in OraScript Statements correctly, you should use the following code:

Code: Select all

ShowMessage(
        Copy(OraScript.SQL.Text,
             OraScript.Statements[i].StartPos + 1,
             OraScript.Statements[i].EndPos - OraScript.Statements.StartPos[i] + 1)
                  );

or (that is more correct)

Code: Select all

ShowMessage(OraScript.Statements[i].SQL);