5 Replies Latest reply: Jun 8, 2009 11:36 AM by gdarling - oracle RSS

    . NET to call Oracle stored procedure, use an array of types of parameters

    user10133982
      . NET to call Oracle stored procedure, use an array of types of parameters
      Step1:(In the Oracle database define an array of types)

      CREATE OR REPLACE TYPE STRING_VARRAY AS VARRAY (1000) OF NVARCHAR2(255)

      Step2:
      CREATE OR REPLACE PROCEDURE Test
      (i_test in string_varray,o_result out int)
      IS
      BEGIN
      o_result:=i_test.count;
      EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
      NULL;
      WHEN OTHERS
      THEN
      o_result:=0;
      END arraytest;

      Step3:
      Use System.Data.OracleClient
      C# Code:
      OracleConnection conn = new OracleConnection("User Id=test;Password=test;Data Source=test");
      OracleCommand cmd = new OracleCommand("Test", conn);
      cmd.CommandType = CommandType.StoredProcedure;

      string[] str = new string[] { "11", "22" };

      OracleParameter p1 = new OracleParameter("i_test", OracleType.NVarChar);
      p1.Direction = ParameterDirection.Input;
      p1.Value = str;

      cmd.Parameters.Add(p1);

      OracleParameter p2 = new OracleParameter("o_result", OracleType.Int32);
      p2.Direction = ParameterDirection.Output;
      cmd.Parameters.Add(p2);

      int i = 0;
      try
      {
      conn.Open();
      cmd.ExecuteNonQuery();
      i =(int) p2.Value;

      }
      catch (Exception ex)
      {

      }
      finally
      {
      conn.Close();
      }

      Error:

      Execution Failed:ORA-06550:Line 1,Column 7:
      PLS-00306:Test parameters when calling the number or types of errors
      ORA-06550:Line 1,Column 7:
      PL/SQL:Statement ignored

      Edited by: user10133982 on Jun 4, 2009 7:13 AM
        • 1. Re: . NET to call Oracle stored procedure, use an array of types of parameters
          gdarling - oracle
          Hi,

          This forum is for Oracle's ODP (Oracle.DataAccess.Client), but I'm reasonably sure Microsoft's ODP doesnt support any sort of object/udt/varray/associative array/array binding. Oracle's provider suports them, and there are samples that install with it. You'd want to go wtih [1110621|http://www.oracle.com/technology/software/tech/windows/odpnet/index.html]

          Hope it helps,
          Greg
          • 2. Re: . NET to call Oracle stored procedure, use an array of types of parameters
            user10133982
            . NET to call Oracle stored procedure, use an array of types of parameters

            The use of ODP.net(Oracle 10g), the error is still the same

            Step1:(In the Oracle database define an array of types)

            CREATE OR REPLACE TYPE STRING_VARRAY AS VARRAY (1000) OF NVARCHAR2(255)

            Step2:
            CREATE OR REPLACE PROCEDURE Test
            (i_test in string_varray,o_result out int)
            IS
            BEGIN
            o_result:=i_test.count;
            EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
            NULL;
            WHEN OTHERS
            THEN
            o_result:=0;
            END arraytest;

            Step3:
            ODP.NET(Oracle 10g)
            OracleConnection conn = new OracleConnection("User Id=test;Password=test;Data Source=test");
            OracleCommand cmd = new OracleCommand("Test", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            string[] str = new string[2] { "11", "222" };
            cmd.ArrayBindCount=2;

            OracleParameter p1 = new OracleParameter("i_test", OracleDbType.NVarChar);
            p1.Direction = ParameterDirection.Input;
            p1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
            p1.Value = str;
            p1.ArrayBindSize=new int[2]{2,3};
            p1.ArrayBindStatus = new OracleParameterStatus[2]{
            OracleParameterStatus.Success,
            OracleParameterStatus.Success
            };

            cmd.Parameters.Add(p1);

            OracleParameter p2 = new OracleParameter("o_result", OracleDbType.Int32);
            p2.Direction = ParameterDirection.Output;
            P2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
            p2.Value=0;
            cmd.Parameters.Add(p2);

            int i = 0;
            try
            {
            conn.Open();
            cmd.ExecuteNonQuery();
            i =(int) p2.Value;

            }
            catch (Exception ex)
            {

            }
            finally
            {
            conn.Close();
            }

            Error:
            Execution Failed:ORA-06550:Line 1,Column 7:
            PLS-00306:Test parameters when calling the number or types of errors
            ORA-06550:Line 1,Column 7:
            PL/SQL:Statement ignored

            Edited by: user10133982 on Jun 5, 2009 7:48 AM
            • 3. Re: . NET to call Oracle stored procedure, use an array of types of parameters
              gdarling - oracle
              Hi,

              TYPE STRING_VARRAY AS VARRAY (1000) OF NVARCHAR2(255) is not an Associative Array.

              You can either switch the type your proc expects to an associative array and continue to use 10g odp, or use odp UDT type and continue to use the VARRAY which requires 11.1.6.20 odp or newer.

              Check your OracleHome\ODP.NET\samples\2.x\AssocArray for an example of Associative Arrays.
              If you have 1110620g ODP, check your OracleHome\ODP.NET\samples\2.x\UDT folder for samples.

              Yes you can use 11g odp with 10g database.

              Hope it helps,
              Greg
              • 4. Re: . NET to call Oracle stored procedure, use an array of types of parameters
                user10133982
                I Use ODTwithODAC1110621 ODP.net
                Run OracleHome\ODP.NET\samples\2.x\UDT\Varray Project(Default Reference Oracle 10g Oracle.DataAccess.dll)
                Build Error:

                error CS0246: The type or namespace name 'OracleUdtStatus' could not be found (are you missing a using directive or an assembly reference?)
                error CS0246: The type or namespace name 'IOracleCustomType' could not be found (are you missing a using directive or an assembly reference?)
                error CS0246: The type or namespace name 'OracleArrayMapping' could not be found (are you missing a using directive or an assembly reference?)
                error CS0246: The type or namespace name 'OracleArrayMappingAttribute' could not be found (are you missing a using directive or an assembly reference?)
                error CS0246: The type or namespace name 'OracleCustomTypeMapping' could not be found (are you missing a using directive or an assembly reference?)
                error CS0246: The type or namespace name 'OracleCustomTypeMappingAttribute' could not be found (are you missing a using directive or an assembly reference?)


                Reference ODTwithODAC1110621 in the Oracle.DataAccess.dll(Oracle 11g)
                Error:The type initializer for 'Oracle.DataAccess.Client.OracleConnection' threw an exception.
                • 5. Re: . NET to call Oracle stored procedure, use an array of types of parameters
                  gdarling - oracle
                  For UDT support, you need to USE 11g odp (reference it, as opposed to just installing it) as the functionality is new to 11.1.6.20. I'm not sure why you say "default reference 10g", the reference should default to 11g odp.

                  The typinitializer exception occurs when ODP cant find the right version of oci.dll, or cant find it at all. You could use a tool like Process Explorer or Process Monitor (http://sysinternals.com) to check the dlls that are being loaded.

                  You installed 11g odp into a seperate/new home, and you installed the whole thing (not just copied a couple dlls) right?

                  Greg