Call Oracle Stored Procedure

Phil Saville

Hi, im new to using stored procedures and i have the following stored procedure...

CREATE OR REPLACE PROCEDURE DRL_PROCEDURE2(var_SOURCE_OBJECTID IN varchar2, var_NEW_OBJECTID OUT varchar2)
AS
BEGIN
SELECT MAX(NEW_OBJECTID)
INTO var_NEW_OBJECTID
FROM DRL_CONVERSION
WHERE SOURCE_OBJECTID = var_SOURCE_OBJECTID;
END;

I want to be able to call this procedure from my windows form application and display the result of the stored procedure into a label or a datagrid using C#...could someone please help me! Thanks in advance

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kirtan Patel17y ago
Accepted answer
   private void button1_Click(object sender, EventArgs e)
        {

            OdbcConnection con = new OdbcConnection("your Connection String");
            con.Open();

            OdbcCommand comm = new OdbcCommand("DRL_PROCEDURE2", con);
            comm.Parameters.AddWithValue("&var_SOURCE_OBJECTID", "Your Value");
            comm.Parameters.AddWithValue("&var_NEW_OBJECTID", "Your Value");

            comm.CommandType = CommandType.StoredProcedure;

            OdbcDataAdapter da = new OdbcDataAdapter(comm);
            DataTable dt = new DataTable();
            da.Fill(dt);

            dataGridView1.DataSource = dt;
            con.Close();

        }
0
theLizard17y ago
Goto the article that explains it all here, you may be better of.

http://www.c-sharpcorner.com/UploadFile/john_charles/CallingOraclestoredproceduresfromMicrosoftdotNET06222007142805PM/CallingOraclestoredproceduresfromMicrosoftdotNET.aspx
0
Kirtan Patel17y ago
Friend,

you guessed right that parameters in above post is for IN..

You can handle Output parameter as Below

            OdbcConnection con = new OdbcConnection("your Connection String");

            con.Open();

            OdbcCommand comm = new OdbcCommand("DRL_PROCEDURE2", con);

            comm.Parameters.AddWithValue("&var_SOURCE_OBJECTID", "Your Value");

            comm.Parameters.AddWithValue("&var_NEW_OBJECTID", "Your Value");

            comm.CommandType = CommandType.StoredProcedure;

           // Adding Output Parameter

            OdbcParameter perameter = new OdbcParameter("var_NEW_OBJECTID", OdbcType.Int);

            perameter.Direction = ParameterDirection.Output;

            comm.Parameters.Add(perameter);

            OdbcDataAdapter da = new OdbcDataAdapter(comm);

            DataTable dt = new DataTable();

            da.Fill(dt);

            //Get Value of Out Prameter

            int x = perameter.Value;

            dataGridView1.DataSource = dt;

            con.Close();

0
Phil Saville17y ago
hi, yea this may work but im not entirely sure what to put where you've said 'Your Value'. Im guessing for the IN parameter it would be the name of the field i get the SOURCE_OBJECTID from, but what would i put for the OUT parameter value...is it where the result of the query goes?
0