FIX: .NET Provider for Oracle Generates ORA-01458 When Calling an SP That Has More Than One Output Parameter (329163)

MORE INFORMATION

Steps to Reproduce the Behavior

1. Create the following Oracle package:

   CREATE OR REPLACE PACKAGE AA_TEST AS
      PROCEDURE Test1(
           val_in       IN    NUMBER,
           val_out1     OUT   NUMBER,
           val_out2     OUT   NUMBER
           );
   END;
   
   
   CREATE OR REPLACE PACKAGE BODY AA_TEST
   IS
      PROCEDURE Test1(
           val_in       IN    NUMBER,
           val_out1     OUT   NUMBER,
           val_out2     OUT   NUMBER
           )
       IS
       BEGIN
         val_out1 := val_in + 1 ;
         val_out2 := val_in + 2 ;
       END;
   
   END aa_test;
   					

2. Create a new Visual C# Windows Application project:
   1. Start Microsoft Visual Studio .NET.
   2. On the File menu, point to New, and then click Project.
   3. Click Visual C# Projects under Project Types, and then click Windows Application under Templates. By default, Form1 is added to the project.
   4. On the Project menu, click Add Reference, and then set a reference to the System.Data.OracleClient namespace.
   5. Drag a Button control from the toolbox to the form.
   6. Add the following code at the top of the Code window:

      using System.Data.OracleClient 
      						

   7. Add the following code to the Button1_Click event of Form1:

                   OracleConnection conn = new OracleConnection("Data Source=myOraSrv;User ID=scott;password=tiger");
                   try
                   {
                        conn.Open();
                        OracleCommand cmd =  new
                        OracleCommand("AA_TEST.Test1", conn); 
      
                        OracleParameter param1 = new OracleParameter("val_in", OracleType.Number);
                        param1.Direction = ParameterDirection.Input;
                        param1.Value = 80;
                        cmd.Parameters.Add(param1);
                        OracleParameter param2 = new OracleParameter("val_out1", OracleType.Number);
      
                        param2.Direction = ParameterDirection.Output;
                        cmd.Parameters.Add(param2);
      
                        cmd.Parameters.Add("val_out2",OracleType.Number).Direction =ParameterDirection.Output; 
                        cmd.CommandType =System.Data.CommandType.StoredProcedure;
                        cmd.ExecuteScalar();
                        MessageBox.Show (cmd.Parameters["val_out2"].Value.ToString());
                        }
                    catch(OracleException ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
      						
      

   8. Modify the "OracleConnection" string as appropriate for your environment.
   9. Press F5 to compile and to run the application.