Call Oracle Stored Procedure

Question

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

Kirtan Patel · 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(); }

theLizard · Answer

Goto the article that explains it all here you may be better of. http://www.c-sharpcorner.com/UploadFile/john_charles/CallingOraclestoredproceduresfromMicrosoftdotNET06222007142805PM/CallingOraclestoredproceduresfromMicrosoftdotNET.aspx

Kirtan Patel · Answer

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();

Phil Saville · Answer

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?