apply a database program (package, function or script )into oracle database from c#

Question

I am trying to apply a function by using odp.net into my oracle database but I cannot, woul you mind help me please? This program suppose to apply all the backend object for me into my database.( A file which is contain several ddl script[insert, create table or delete and alter table], apply function, procedure or package)

I am doing this activity for automate apply objects in our client side.

This software should work same as PLEdit software.

textbox1.text :

create or replace FUNCTION bfn_severity_temp (p_severity                CMMT_FIX_MASTER.N_severity%TYPE)
    RETURN VARCHAR2

    IS
   BEGIN
        IF p_severity = 1 THEN
            RETURN 'Mission Critical';
        ELSIF p_severity = 2 THEN
            RETURN 'High';
        ELSIF p_severity = 3 THEN
            RETURN 'Medium';
        ELSIF p_severity = 4 THEN
            RETURN 'High';
        END IF;
   END;

My c# code in as below:

try{
        OracleConnection oConn = new OracleConnection(ConnectionString);

            //OracleTransaction trans = oConn.BeginTransaction(IsolationLevel.ReadCommitted);

            OracleCommand oc = new OracleCommand();
            //oc.CommandType = CommandType.TableDirect;
            oc.CommandText = textBox1.Text;
            //oc.Transaction = trans;
            oc.Connection = oConn;
            int i  = 0;
            oConn.Open();
            i = oc.ExecuteNonQuery();
            oConn.Close();
            MessageBox.Show(i.ToString());
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            MessageBox.Show(ex.Message);
        }

what error do you get? I noticed you're discarding the exception on your try catch block... Maybe you could try adding a MessageBox.Show(ex.Message); — bastos.sergio
– bastos.sergio, Commented Jul 1, 2013 at 11:05
@Belogix well i is -1 and I am not receiving any exception, one more thing I cannot see my function in PLedit as valid or invalid object. — Amir
– Amir, Commented Jul 1, 2013 at 11:07
Does your connection string specify a valid user that has sufficient permission to perform the action you are trying? — Belogix
– Belogix, Commented Jul 1, 2013 at 11:09
yes I am using super user, as I said I can open connection and close connetion but problem is i result is -1 and I cannot see my function in list of applied function... — Amir
– Amir, Commented Jul 1, 2013 at 11:10

T.S. · Accepted Answer · 2013-07-20 02:26:17Z

1

Just remember, looking at DDL you creating the object in the schema defined by connection. And if the super-user you connecting as is "System", that is the place to look for your object.
Your object may be invalid after creation but will be compiled on the first use (if object is compilable) Or, you can add the following alter function <name> compile

answered Jul 20, 2013 at 2:26

T.S.

19.6k11 gold badges70 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

apply a database program (package, function or script )into oracle database from c#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related