Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Cannot create trigger using OraOLEDB.Oracle from .Net

604519Sep 14 2009
Hi,
I have a problem creating a trigger using OraOLEDB.Oracle provider for OleDb.
The problem is that the command with create trigger statement executes fine but I cannot find the trigger in the database after that.
I tried executing the same create trigger statement using Microsoft's OracleCommand from Oracle.DataAccess and it all worked but I really need to be able to do it connecting from OleDb.
I am using the latest OleDb provider downloaded from Oracle.
The database is oracle 10 express and I am working on a Windows Vista.
Below is a sample code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using Oracle.DataAccess.Client;

namespace OracleTriggerRegisterTest
{
class Program
{
//OraOLEDB.Oracle
private static readonly string m_connString = @"Provider=OraOLEDB.Oracle;Data Source=XXX.XXX.X.XX/XE;User Id=XXXXX;Password=XXXX;";
private static readonly string m_connStringOracle = @"Data Source=XXX.XXX.X.XX/XE;User Id=XXXX;Password=XXXX;";

private static readonly string newLine = Environment.NewLine; //Also tried with " "
private static readonly string m_command = "CREATE OR REPLACE TRIGGER \"TGI_EMPLOYEES\"" + newLine
+ "AFTER INSERT" + newLine
+ "ON \"HR\".\"EMPLOYEES\"" + newLine
+ "REFERENCING NEW AS source_rec FOR EACH ROW" + newLine
+ "DECLARE" + newLine
+ " recCount int := 0;" + newLine
+ "BEGIN" + newLine
+ "SELECT NVL(MAX(\"IST_recId\"),0) + 1 INTO recCount FROM \"HR\".\"Employee_KEys\";" + newLine
+ "INSERT INTO \"HR\".\"Employee_KEys\" (" + newLine
+ "\"IST_SearchKey\", \"IST_recId\", \"IST_definitionId\"," + newLine
+ "\"FIRST_NAME\"," + newLine
+ "\"EMPLOYEE_ID\"" + newLine
+ ")" + newLine
+ "SELECT" + newLine
+ "B.COLUMN_VALUE, recCount, 1," + newLine
+ ":source_rec.\"FIRST_NAME\", :source_rec.\"EMPLOYEE_ID\"" + newLine
+ "FROM TABLE ( IST_GETKEYS ( 'Matcher', 'name1 ', rtrim(NVL( :source_rec.\"FIRST_NAME\", '')) )) B;" + newLine
+ "END;" + newLine; //Tried removing the ; tried adding newLine + /

static void Main(string[] args)
{
DoOleDB();
DodotNet();
}

private static void DoOleDB()
{
try
{
OleDbConnection conn = new OleDbConnection(m_connString);
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = m_command;
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
String msg = ex.Message;
}
}

private static void DodotNet()
{
try
{
OracleConnection conn = new OracleConnection(m_connStringOracle);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = m_command;
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
String msg = ex.Message;
}
}
}
}

I appreciate any help on this.

Regards,
Kos Mitev
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 12 2009
Added on Sep 14 2009
0 comments
2,256 views