All geek questions in one place

Calling an Oracle function returning a type from C #

I use C # to call a function in an Oracle package that returns a type.

I spent the last couple of days researching this, so far the advice I came across:

  • use odp.net 11g data access driver.
  • make sure the return value is set to returnvalue
  • make sure the parameter is displayed as the first parameter added
  • Give the output parameter the name udttypename, which is the type of Oracle name.
  • To make sure that this udttypename is an uppercase (several similar cases that she asked about were resolved by this)

The following is an Oracle package (a package called prefs):

Type P_Details Is Record( var1 a.a_Type_Key%Type ,var2 Varchar2(1) ,var3 ab%Type ,var4 c.Type_Key%Type ,var5 d.Code%Type ,var6 d.Product_Path%Type ,var7 a.Channel_Key%Type ,var8 a.From_Date%Type ,var9 a.To_Date%Type); Type P_List Is Table Details; Function Get(p_1 In Number, p_2 In Varchar2, p_3 In Varchar2, p_4 In Date, p_5 In Out Varchar2) Return List;

The following is the C # code used to invoke the Oracle package.

 using (var connection = new OracleConnection(ConnectionString)) { using (var command = new OracleCommand { CommandType = CommandType.StoredProcedure, CommandText = "PACKAGENAME.FUNCTIONNAME", Connection = connection, BindByName = true }) { var output = new OracleParameter { UdtTypeName = "PREFS.PREFERENCE_LIST", ParameterName = "p_details", OracleDbType = OracleDbType.Object, Direction = ParameterDirection.ReturnValue }; command.Parameters.Add(output); command.Parameters.Add(new OracleParameter { ParameterName = "p_1", OracleDbType = OracleDbType.Decimal, Direction = ParameterDirection.Input, Value = details.RuleId }); command.Parameters.Add(new OracleParameter { ParameterName = "p_2", OracleDbType = OracleDbType.Decimal, Direction = ParameterDirection.Input, Value = details.CustomerDetails.CtiId }); command.Parameters.Add(new OracleParameter { ParameterName = "p_3", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input, Value = details.CustomerDetails.Surname }); command.Parameters.Add(new OracleParameter { ParameterName = "p_4", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input, Value = details.CustomerDetails.Postcode }); command.Parameters.Add(new OracleParameter { ParameterName = "p_5", OracleDbType = OracleDbType.Date, Direction = ParameterDirection.Input, Value = details.CustomerDetails.DateOfBirth }); command.Parameters.Add(new OracleParameter { ParameterName = "p_6", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.InputOutput }); connection.Open(); command.ExecuteNonQuery(); } }

Now i get an error

"OCI-22303: enter" PACKAGENAME.TYPENAME "not found"

For UDTTYPENAME I tried the following formats

  • TYPENAME
  • FUNCTIONNAME.TYPENAME
  • PACKAGENAME.TYPENAME
  • PACKAGENAME.TYPENAME
  • PACKAGENAME.FUNCTIONNAME.TYPENAME
  • SCHEMA.PACKAGENAME.TYPENAME

I would be grateful for any help and answer for this, since I have already run out of ideas.

+4
source share
1 answer

In fact, you simplify casting and preparation parameters for all your procedures (procedures, functions, etc.), as well as cursor type parameters with this small automation

a) define the following type and routine in a generic package (let's call it utils).

 Type recRoutineSchema is Record (ColumnName varchar2(64),DataType Varchar2(20), ColumnOrder number, Direction varchar2(10), sSize nUMBER); Type tblRoutineSchema is table of recRoutineSchema; function ftRoutineSchema(pkg varchar2,Routine varchar2) return tblRoutineSchema PIPELINED is x recRoutineSchema; pkN varchar2(100); rtN varchar2(100); Begin FOR Y in ( Select Argument_Name ColumnName ,Data_type DataType ,Position ColumnOrder ,In_out Direction ,Data_length SSize from user_ARGUMENTS where package_Name=Upper(pkg) and object_name=Upper(Routine) order by position ) LOOP PIPE ROW(Y); END LOOP; Return; End;

b) and the C # method for calling the function above to retrieve and configure the parameters of the procedure / function you are calling

 public void SetupParams(string RoutineName, OracleCommand cmd, IDictionary<string, string> prms, bool keepConnectionOpen = true) { Debug.WriteLine("Setting parameters for " + RoutineName); if (cmd != null) cmd.Parameters.Clear(); string pname = ""; string[] s = RoutineName.Split('.'); DataTable tblParams = Select(String.Format("Select * from Table(pkgUtils.ftRoutineSchema('{0}','{1}')) ", s[0], s[1])); cmd.CommandText=RoutineName; foreach (DataRow dr in tblParams.Rows) { using (OracleParameter p = new OracleParameter()) { pname = dr["COLUMnNAME"].ToString() == "" ? "returnvalue" : pname = dr["COLUMnNAME"].ToString().ToLower(); if (prms.Keys.Contains(pname)) p.Value = prms[pname]; string direction = dr["Direction"].ToString().ToLower(); string sptype = (string)dr["DataType"]; string[] sx = dr["DataType"].ToString().Split(new char[] { '(', ',', ')' }); direction = pname == "returnvalue" ? "rc" : direction; p.ParameterName = pname; #region case type switch switch (sx[0].ToLower()) { case "number": // p.DbType = OracleDbType.Decimal; p.OracleDbType = OracleDbType.Decimal; break; case "varchar2": p.DbType = DbType.String; p.Size = 65536; // p.Size = prms[pname].Length; // p.Size = int.Parse(sx[1]); break; case "ref cursor": p.OracleDbType = OracleDbType.RefCursor; // direction = "rc"; // force return value break; case "datetime": p.DbType = DbType.DateTime; break; case "ntext": case "text": p.DbType = DbType.String; p.Size = 65536; break; default: break; } //------------------------------------------------------------------------------- switch (direction) { case "in": p.Direction = ParameterDirection.Input; break; case "out": p.Direction = ParameterDirection.Output; break; case "in/out": p.Direction = ParameterDirection.InputOutput; break; case "rc": p.Direction = ParameterDirection.ReturnValue; break; default: break; } #endregion cmd.Parameters.Add(p); ; } } }

from). Now you can easily call any / proc function as follows. This procedure actually returns two refcursor parameters to populate the dataset.

  private void btnDumpExcel_Click(object sender, EventArgs e) { IDictionary<string, string> p = new Dictionary<string, string>(); p.Add("pcomno", "020"); p.Add("pcpls", "221"); p.Add("pUploaderName", "Anthony Peiris"); try { pGroupDs = O.execProc2DatSet("priceWorx.prSnapshotDiscounts", p, false, false); Excel.MakeWorkBook(ref pGroupDs, ref O, "1"); } catch (Exception ex) { Debug.WriteLine(ex); Debugger.Break(); } //Excel.MakeWorkBook(ref ds, ref O, "1"); }

Here is the O.execProc2DataSet method

 public DataSet execProc2DatSet(string storedProcedureName, IDictionary<string, string> prms, bool propagateDbInfo, bool leaveConnectionOpen = false) { // initPackage(storedProcedureName.Substring(0,storedProcedureName.IndexOf('.'))); try { using (OracleCommand cmd = new OracleCommand("", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = storedProcedureName; //dep = new OracleDependency(cmd); //dep.OnChange += new OnChangeEventHandler(dep_OnChange); if (prms != null) SetupParams(storedProcedureName, cmd, prms, true); using (OracleDataAdapter da = new OracleDataAdapter(cmd)) { if (conn.State != ConnectionState.Open) { conn.Open(); cmd.Connection = conn; } using (DataSet ds = new DataSet()) { da.Fill(ds); return ds; } } } } catch (Exception ex) { Debug.WriteLine(ex); Debugger.Break(); return null; } finally { if (!leaveConnectionOpen) conn.Close(); } }

This approach allows you to change the parameters of the Proce / function without agreeing on which parameters can change from the last moment, since the parameter settings are now fully automatic. NTN

+3
source

Source: https://habr.com/ru/post/1403032/

More articles:


All Articles