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 <tablename>;
COMMIT;
Create Table <tablename>(<fields>);
COMMIT;
Alter Table <tablename> add primary key (<fields>);
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[i].ErrorCode <> 0 then
Result.Add(OScript.Commands[i].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
