TOracleScript doesn't commit(?)

[David Keith]

I have written some code to use a TOracleScript component to execute multiple DDL commands against an Oracle 11g instance.

I generate the commands, by reading the catalog info from M$Access and building the DDL for each object.

I have event handlers for all of the output events of the TOracleScript component. Every command in the script executes with no errors.

The command pattern is very simple:

Drop table

;
COMMIT;
Create Table

();
COMMIT;
Alter Table

add primary key ();
commit;

Finally, at the end of the script I have a '/'.

When I check the database schema there are no tables created!!

If I copy & paste the script into Oracle SQL Developer and execute the script, all commands execute properly with no errors and the objects are created.

What am I doing wrong?

Code:

private
...
function GetDDLScript: TStrings;
...
procedure SetDDLScript(AScript: TStrings);
public
...
function ExecDDL: TStrings;
...
property DDLScript: TStrings read GetDDLScript write SetDDLScript;
...
end;

implementation

{$R *.dfm}

constructor TdmDDLOra.Create(TNSServiceName: String; Username,Password: String);
var
sErr: String;
begin
inherited Create(Nil);
osess.LogonDatabase := TNSServiceName;
osess.LogonUsername := Username;
osess.LogonPassword := Password;
try
osess.LogOn;
except
on E:EOracleError do
begin
if not osess.Connected then
begin
sErr := E.Message + #13 +
'Failed to establish connection with ' + TNSServiceName +
' using supplied credentials.' + #13 +
'Please confirm that you are using a valid TNS Service Name, Username and Password.';
MessageDlg(sErr,mtError,[mbOk],0);
Status(sErr);
end;
end;
end;
end;
...

procedure TdmDDLOra.osessAfterLogOn(Sender: TOracleSession);
begin
Status('Successfully logged on to ' + osess.LogonDatabase + ' using username ' + osess.LogonUsername + '.');
end;

procedure TdmDDLOra.osessChange(Sender: TOracleSession);
begin
if osess.Connected then
Status('Successfully logged on to ' + osess.LogonDatabase + ' using username ' + osess.LogonUsername + '.')
else
Status('Disconnected ' + osess.LogonUsername + ' from the ' + osess.LogonDatabase + '.');
end;

...

procedure TdmDDLOra.OScriptAfterCommand(Sender: TOracleScript; var Handled: Boolean);
begin
Status('Finished executing command: ' + Sender.CurrentCommand.Text);
end;

procedure TdmDDLOra.OScriptCommand(Sender: TOracleScript; var Handled: Boolean);
begin
try
Status('Executing command: ' + Sender.CurrentCommand.Text);
Handled := True;
except
Handled := False;
end;
end;

procedure TdmDDLOra.OScriptError(Sender: TOracleScript);
begin
Status('An error occurred while executing command: ' + Sender.CurrentCommand.Text + #13 +
IntToStr(Sender.CurrentCommand.ErrorCode) + ': ' + Sender.CurrentCommand.ErrorMessage);
end;

function TdmDDLOra.GetDDLScript: TStrings;
begin
Result := OScript.Lines;
end;

procedure TdmDDLOra.SetDDLScript(AScript: TStrings);
begin
OScript.Lines.Clear;
OScript.Lines := AScript;
end;

function TdmDDLOra.ExecDDL: TStrings;
var
i: Integer;
begin
if (OScript.Commands.Count > 0) then
if OScript.Execute then
begin
OSess.Commit;
Result := OScript.Output;
for i := 0 to Pred(OScript.Commands.Count) do
if OScript.Commands .ErrorCode 0 then
Result.Add(OScript.Commands.ErrorMessage);
end;
end;
...
end.

At runtime I check the commands and there are about 294 of them listed, I never get an error code etc.

Help!

Thanks.

David Keith

 

[David Keith]

Found it. Wrong role, DDL was executing as SYSDBA.

Now I'm trying to figure out how to use TOracleDirectPathLoader to get data from a TDataset into Oracle. So far I'm getting lots of interesting errors, like data in table is bad/wrong, data in one column overwrites data in another column.

Code:

procedure TdmDDLOra.LoadData;
var
i,Row,iVal,n: Integer;
fVal: Double;
pVal: PAnsiString;
v: Variant;
s: AnsiString;
dt: TDirectPathColumnType;
begin
odpl.TableName := FTable;
// Get the default columns for the record_data table
odpl.GetDefaultColumns(False);
// Prepare the odpl
odpl.Prepare;
// Process all data in batches of records
Row := 0;
while not FData.Eof do
begin
// Copy one record to the array
for I := 0 to Pred(FData.FieldCount) do
begin
dt := GetDataType(FData.Fields);
if (dt in [dpInteger,dpFloat]) then
begin
if (FData.Fields.Required and FData.Fields.IsNull) then
begin
FData.Edit;
if (Length(FData.Fields.DefaultExpression) > 0) then
FData.Fields.AsString := FData.Fields.DefaultExpression
else
FData.Fields.Value := 0;
FData.Post;
end;
if (dt = dpInteger) then
begin
iVal := FData.Fields.Value;
odpl.Columns.SetData(Row, @iVal, 0);
end;
if (dt = dpFloat) then
begin
fVal := FData.Fields.Value;
odpl.Columns.SetData(Row, @fVal, 0);
end;
end;
if (dt in [dpString,dpBinary]) then
begin
if (FData.Fields.Required and FData.Fields.IsNull) then
begin
FData.Edit;
if (Length(FData.Fields.DefaultExpression) > 0) then
FData.Fields.AsString := FData.Fields.DefaultExpression
else
FData.Fields.Value := '';
FData.Post;
end;
if (dt = dpString) then
begin
s := FData.Fields.AsString;
pVal := PAnsiString(s);
odpl.Columns.SetData(Row, pVal,Length(s));

end;
if (dt = dpBinary) then
begin
v := FData.Fields.Value;
odpl.Columns.SetData(Row, @v, 0);
end;
end;
end;
Inc(Row);
// The array is filled, or we have preocessed all records:
// load this batch of records
if (Row = odpl.MaxRows) or (Row = FData.RecordCount) then
begin
try
odpl.Load(Row);
except
// In case of an error: show where things went wrong

// and abort the load operation
on E:EOracleError do
begin
MessageDlg(E.Message + #13#10 + 'Row = ' + IntToStr(odpl.LastRow) + #13#10 +
'Col = ' + IntToStr(odpl.LastColumn),mtError,[mbOK],0);
odpl.Abort;
raise;
end;
end;
Row := 0;
end;
FData.Next;
end;
// Commit the loaded data
odpl.Finish;
end;

Any assistance would be appreciated.

David Keith
Wellsoft Corporation

 

[Marco Kalter]

The problem here is that the memory at the pointer passed to SetData procedure must remain valid until you call Load. You cannot assign a value to a local variable, pass it to SetData, and then reuse that variable for other data. You will get the mix-up you describe.

Create an array, list or other structure to hold all the values you want to pass to the Load procedure.

 

[David Keith]

I tried using FDataset.Fields[0].Value, but it wouldn't work with that. Do I have to create a TList descendant? If I use a record, won't I have the same problem?

Would it be feasible to load one row at a time? Or would that kill performance or otherwise not work?

another problem that I noticed with the data that gets put into oracle is that when the data gets put there in a semi-readable format it has carriage return characters (little squares) between each legible letter.

also, how do I handle binary objects? Is a pointer to a variant sufficient? Or will that scramble the contents? Is there a particular type that is best used to represent binary data?

What about declaring a TIntegerField/TStringField/TFloatField/TBlobField instance for each data value and using that?

Thanks.

David Keith

 

[Marco Kalter]

I tried using FDataset.Fields[0].Value, but it wouldn't work with that. Do I have to create a TList descendant? If I use a record, won't I have the same problem?

Property values cannot be used either. The TList descendant will work, because it will hold all the data until you Load it.

Would it be feasible to load one row at a time? Or would that kill performance or otherwise not work?

That will defeat the performance purpose of the Direct Path Loader.

also, how do I handle binary objects? Is a pointer to a variant sufficient? Or will that scramble the contents? Is there a particular type that is best used to represent binary data?

You cannot point directly to Variants. You will have to point to the binary data itself.

What about declaring a TIntegerField/TStringField/TFloatField/TBlobField instance for each data value and using that?

That will not work. Use a TList or TCollection.

Note: if you find it easier to work with Variants without the memory requirements of the TDirectPathLoader, use a TOracleQuery and Array DML instead.

 

[David Keith]

I'm using D2009, this wouldn't have anything to do with Unicode would it?

Here is my latest iteration, the data that gets put into the database is scrambled and on the third table going in I get the following error:

ORA-01438: value larger than specified precision allowed for this column

Row = 3
Col = 4

The column definition in Oracle in question has a definition of NUMBER(1,0), the number that I'm trying to put in is a 1 digit integer. I assume the error occurs because the pointer operation is somehow scrambling the data so the length/type is off.

Can you please provide a better example? Or perhaps point out something that you can see that I'm doing wrong?

Thanks.

David Keith

Code:

procedure TdmDDLOra.LoadData;
var
i,Row: Integer;
s: AnsiString;
dt: TDirectPathColumnType;
ayWStr: array of WideString;
ayFloat: Array of Double;
ayInt: array of Integer;
ayStream: array of TMemoryStream;
begin
odpl.TableName := FTable;
// Get the default columns for the record_data table
odpl.GetDefaultColumns(False);
// Prepare the odpl
odpl.Prepare;
// Process all data in batches of records
Row := 0;
try
while not FData.Eof do
begin
// Copy one record to the array
for I := 0 to Pred(FData.FieldCount) do
begin
dt := GetDataType(FData.Fields);
if (FData.Fields.Required and FData.Fields.IsNull) then
begin
FData.Edit;
if (Length(FData.Fields.DefaultExpression) > 0) then
FData.Fields.AsString := FData.Fields.DefaultExpression
else
FData.Fields.Value := 0;
FData.Post;
end;
if (dt = dpInteger) then
begin
SetLength(ayInt,Length(ayInt) + 1);
ayInt[Pred(Length(ayInt))] := FData.Fields.AsInteger;
odpl.Columns.SetData(Row, @ayInt[Pred(Length(ayInt))], 0);
end;
if (dt = dpFloat) then
begin
SetLength(ayFloat,Length(ayFloat) + 1);
ayFloat[Pred(Length(ayFloat))] := FData.Fields.AsFloat;
odpl.Columns.SetData(Row, @ayFloat[Pred(Length(ayFloat))], 0);
end;
if (dt = dpString) then
begin
SetLength(ayWStr,Length(ayWStr) + 1);
ayWStr[Pred(Length(ayWStr))] := FData.Fields.AsString;
odpl.Columns.SetData(Row, @ayWStr[Pred(Length(ayWStr))], Length(ayWStr[Pred(Length(ayWStr))]));
end;
if (dt = dpBinary) then
begin
SetLength(ayFloat,Length(ayFloat) + 1);
ayStream[Pred(Length(ayStream))] := TMemoryStream.Create;
TBlobField(FData.Fields).SaveToStream(ayStream[Pred(Length(ayStream))]);
ayStream[Pred(Length(ayStream))].Position := 0;
odpl.Columns.SetData(Row, ayStream[Pred(Length(ayFloat))].Memory, ayStream[Pred(Length(ayStream))].Size);
end;
end;
Inc(Row);
// The array is filled, or we have preocessed all records:
// load this batch of records
if (Row = odpl.MaxRows) or (Row = FData.RecordCount) then
begin
try
odpl.Load(Row);
SetLength(ayStream,0);
SetLength(ayFloat,0);
SetLength(ayWStr,0);
SetLength(ayInt,0);
except
// In case of an error: show where things went wrong
// and abort the load operation
on E:EOracleError do
begin
MessageDlg(E.Message + #13#10 + 'Row = ' + IntToStr(odpl.LastRow) + #13#10 +
'Col = ' + IntToStr(odpl.LastColumn),mtError,[mbOK],0);
odpl.Abort;
raise;
end;
end;
Row := 0;
end;
FData.Next;
end;
// Commit the loaded data
odpl.Finish;
finally
for i := 0 to Pred(Length(ayStream)) do
FreeAndNil(ayStream[Pred(Length(ayStream))]);
SetLength(ayStream,0);
SetLength(ayFloat,0);
SetLength(ayWStr,0);
SetLength(ayInt,0);
odpl.Columns.Clear;
end;
end;

 

[Marco Kalter]

The only error I see is that for strings you need to pass a pointer to the first character, not a pointer to the Delphi AnsiString structure. So instead of this:

SetData(@MyStringVar, Length(MyStringVar);

You would need to do this:

SetData(@MyStringVar[1], Length(MyStringVar);