Unable to create Triggers when Oracle oleDB drivers are used
979724Jan 31 2013 — edited Feb 5 2013My Delphi application creates procedures and triggers in to MSSQL and Oracle database. We use ADO as the data access layer and Oracle.ole DB(oraoleDB.oracle.1) as provider when we insert procedures into an Oracle DB. Some of the triggers/Procedure are executed successfully but some do not. However there is no error via the ADO components when the command is executed. But they are simply not created in the database. Below is the sample code:
procedure CreateTriggers
var
qry : TADOCommand;
begin
qry := TADOCommand.Create(self);
try
qry.Connection := MyconnectionString
// something like this
//(Provider=OraOLEDB.Oracle;Data Source=xxx;User ID=xxx;Password=xx;
//Extended properties="NDatatype=true")
qry.ParamCheck := false;
qry.CommandText := MyMemo.Text;
qry.Execute;
finally
qry.free
end;
end;
The text in the memo/Trigger is something like this:
Create Or Replace Trigger
MYTrigger
Before Update
On
MyTable
Referencing
New As MyNew
Old As MyOld
For Each Row
Declare
v_new_updatecount MyTable.UPDATECOUNT%TYPE;
Begin
GET_NEW_UPDATECOUNT(:MyOld.UPDATECOUNT, v_new_updatecount);
:MyNew.UPDATECOUNT := v_new_updatecount;
End;
This is only a problem with Oracle and when i use OLEDDB drivers. When MSDORA is used then there are no issues
When i remove the colons from the trigger then , the trigger is executed successfully.
Any suggestions what is going wrong?