Forum Stats

  • 3,817,447 Users
  • 2,259,334 Discussions
  • 7,893,776 Comments

Discussions

Categories

Unable to create Triggers when Oracle oleDB drivers are used

979724
979724 Member Posts: 5
edited Feb 5, 2013 5:43PM in Oracle Provider for OLE DB
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?
Tagged:
· Share on TwitterShare on Facebook

Answers

  • gdarling - oracle
    gdarling - oracle Member Posts: 2,034
    edited Jan 31, 2013 11:25AM
    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
    · Share on TwitterShare on Facebook
  • 979724
    979724 Member Posts: 5
    We are using 11.2.0.2 version of OLEDB.
    · Share on TwitterShare on Facebook
  • 979724
    979724 Member Posts: 5
    edited Feb 1, 2013 12:32AM
    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
    · Share on TwitterShare on Facebook
  • 989346
    989346 Member Posts: 3
    edited Feb 5, 2013 5:43PM
    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
    · Share on TwitterShare on Facebook
  • 989346
    989346 Member Posts: 3
    frinter ink
    · Share on TwitterShare on Facebook
This discussion has been closed.