Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.5K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.2K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 402 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Unable to create Triggers when Oracle oleDB drivers are used

979724
Member Posts: 5
My 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?
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?
Tagged:
Answers
-
What version of OLEDB provider are you using? I tried this simple test using vbscript and 11203 oledb, and it worked for me..
'create table twocol(col1 number, col2 number); set con = createobject("adodb.connection") con.open "provider=oraoledb.oracle;user id=scott;password=tiger;data source=orcl" set cmd = createobject("adodb.command") cmd.activeconnection = con cmd.commandtext = "create or replace trigger twocoltrig " & _ "before insert on twocol " & _ "for each row " & _ "begin " & _ ":new.col2:=123;" & _ "end;" cmd.execute wscript.echo "done, no errors"
double check:
-------------------
SQL> select status from user_triggers where trigger_name='TWOCOLTRIG';
STATUS
--------
ENABLED
Edited by: gdarling on Jan 31, 2013 8:25 AM -
We are using 11.2.0.2 version of OLEDB.
-
when i executed the trigger
create or replace trigger twocoltrig
before insert on twocol
for each row
begin
:new.col2:=123;
end;
every thing went fine and the trigger was created in the database
After some investigation i changed my trigger to the following
Create Or Replace Trigger
MYTrigger
Before Update
On
MyTable
For Each Row
Declare
v_new_updatecount MyTable.UPDATECOUNT%TYPE;
Begin
GET_NEW_UPDATECOUNT(:Old.UPDATECOUNT, v_new_updatecount);
:New.UPDATECOUNT := v_new_updatecount;
End;
now too the trigger was created in the database.
so this has to do some thing with the referencing part of it and then using the new variables, though this is a valid syntax.
( Referencing
New As MyNew
Old As MyOld )
any suggestions on this?
Edited by: Murthy Gudivada on Jan 31, 2013 9:31 PM -
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
======================
for printer ink
Edited by: 986343 on Feb 5, 2013 2:39 PM -
This discussion has been closed.