How to call oracle stored procedure with output in code behind

Development
1

I tried to use the code below to call stored procedure but failed.

using (Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString))
	        {	            
	            Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand();
	            cmd.Connection = conn;
	            cmd.CommandText = "xxtmtm_s0005.UPDATE_EBS_ITEM_STATUS";
	            cmd.CommandType = CommandType.StoredProcedure;
	            cmd.Parameters.Add("PRA_ITEM",OracleType.VarChar).Value = "6LH60083100";
	            cmd.Parameters.Add("PRA_NEW_STATUS",OracleType.VarChar).Value = "FAP_SS Y";
	            cmd.Parameters.Add("PRA_EMP_NO",OracleType.VarChar).Value = "EBSSUPP2";
	            cmd.Parameters.Add("PRA_RESULT",OracleType.VarChar).Direction = ParameterDirection.Output;
	            
	            conn.Open();
	            cmd.ExecuteNonQuery();
	            Oracle.ManagedDataAccess.Client.OracleDataReader rdr = cmd.ExecuteReader();
	            
	            if(rdr.HasRows)
	            {
	                while(rdr.Read())
	                {
	                    test = rdr["PRA_RESULT"].ToString();
	                }
	            }
	            
	            conn.Close();
	            conn.Dispose();
	        }

The error is shown as below:

image
image1231×168 8.83 KB

Is there any other method to call oracle stored procedure with code behind?

2

Hi @wing_hou,

For this code to work, you’ll need to put the assembly (.dll file) used for “Oracle.ManagedDataAccess” in wfgen/wfapps/webforms/bin folder located in your application server.

The code behind editor will try to look for assemblies in this folder.

Regards,
Eddy.

3

Hi @eddy.daouk ,

I have checked that “Oracle.ManagedDataAccess.dll” had already exist in the wfgen/wfapps/webforms/bin folder.
Is there any other assembly file that I need to put into that folder?

I found out that if I change the code like below, the script can found the context:

cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("PRA_RESULT",OracleType.VarChar).Direction = System.Data.ParameterDirection.Output;

But I have no idea what namespace should be put for the ‘OracleType’

cmd.Parameters.Add("PRA_ITEM",**OracleType.VarChar**).Value = "6LH60083100";
4

Should you not use OracleDbType which is part of the Oracle.DataAccess.Client namespace?

https://docs.oracle.com/database/121/ODPNT/OracleParameterClass.htm#ODPNT1771

https://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleDbTypeEnumerationType.htm

5

Hi, @kevin.lee

Thanks for your help.

After changing the code as below, it works.

using (Oracle.ManagedDataAccess.Client.OracleConnection conn2 = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString2))
{
	            Oracle.ManagedDataAccess.Client.OracleCommand cmd2 = new Oracle.ManagedDataAccess.Client.OracleCommand();
	            cmd2.Connection = conn2;
	            cmd2.CommandText = "XXTMTM_S0005.UPDATE_EBS_ITEM_STATUS";
	            cmd2.CommandType = System.Data.CommandType.StoredProcedure;
	            cmd2.Parameters.Add("PRA_ITEM_CODE", Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2,System.Data.ParameterDirection.Input).Value = "6LH60083100";
	            cmd2.Parameters.Add("PRA_NEW_STATUS", Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2,System.Data.ParameterDirection.Input).Value = "FAP_SS Y";
	            cmd2.Parameters.Add("PRA_EMP_NO", Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2,System.Data.ParameterDirection.Input).Value = "EBSSUPP2";
	            cmd2.Parameters.Add("PRA_RESULT", Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2,System.Data.ParameterDirection.Output).Size = 10;
	            
	            conn2.Open();
	            cmd2.ExecuteNonQuery();
	            output = ["PRA_RESULT"].Value;

	            conn2.Close();
	            conn2.Dispose();
}