5 Replies Latest reply: Feb 5, 2013 4:42 PM by 989346 RSS

    Unable to create Triggers when Oracle oleDB drivers are used

    979724
      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?
        • 1. Re: Unable to create Triggers when Oracle oleDB drivers are used
          gdarling - oracle
          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
          • 2. Re: Unable to create Triggers when Oracle oleDB drivers are used
            979724
            We are using 11.2.0.2 version of OLEDB.
            • 3. Re: Unable to create Triggers when Oracle oleDB drivers are used
              979724
              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
              • 4. Re: Unable to create Triggers when Oracle oleDB drivers are used
                989346
                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