title: Calling Oracle function that returns type from C#
body: <p>I am using c# to try and call a function in an Oracle package that returns a Type.</p>
  
  <p>I've spent the last couple of days researching this, so far the advice i've come across is:</p>
  
  <ul>
  <li>to use the odp.net 11g Data Access driver.</li>
  <li>ensure the parameter output direction is set to returnvalue</li>
  <li>ensure the parameter output is first parameter added</li>
  <li>Give the output parameter a udttypename which is the Oracle type
  name.</li>
  <li>To make sure this udttypename is Upper Case (A few similar cases asked on her were resolved by this)</li>
  </ul>
  
  <p>Below is the Oracle package (package is called prefs):</p>
  
  <pre><code>Type P_Details Is Record(
     var1    a.a_Type_Key%Type
    ,var2    Varchar2(1)
    ,var3    a.b%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;
  </code></pre>
  
  <p>Below is the C# code used for calling the Oracle package.</p>
  
  <pre><code>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();
      }
  }
  </code></pre>
  
  <p>I'm now getting an error </p>
  
  <blockquote>
    <p>"OCI-22303: type "PACKAGENAME.TYPENAME" not found"</p>
  </blockquote>
  
  <p>For the UDTTYPENAME i've tried the following formats</p>
  
  <ul>
  <li>TYPENAME</li>
  <li>FUNCTIONNAME.TYPENAME</li>
  <li>PACKAGENAME.TYPENAME</li>
  <li>PACKAGENAME.TYPENAME</li>
  <li>PACKAGENAME.FUNCTIONNAME.TYPENAME</li>
  <li>SCHEMA.PACKAGENAME.TYPENAME</li>
  </ul>
  
  <p>I'd appreciate any help and response for this as i've now run out of ideas.</p>