ORA-06550: array bind type must match PL/SQL table row type

ORA-06550: array bind type must match PL/SQL table row type

Postby Mike Haro » Wed 10 Nov 2010 21:22

We have an Oracle Package that is set up to take Array values as the parameters. This package works fine when connecting in using the Direct=True method.

But when we connect in using Externally Managed method this same package produces the following error:

Failed in trying to run the package. ORA-06550: line 2, column 199:
PLS-00418: array bind type must match PL/SQL table row type
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored


We believe the one parameter causing the problem is an array of decimal values. In VB.Net we are creating the array being passed in as:

Code: Select all
Dim aPcts() as Decimal


The parameter binding is being created as:

Code: Select all
            Dim oParm As New OracleParameter()
            With oParm
                .ParameterName = "p_sku_percent"
                .Direction = ParameterDirection.Input
                .DbType = DbType.Double
                .Value = aPcts
                .ArrayLength = aPcts.GetLength(0)
            End With


In the package this is calling, the Spec has a definition of:

Code: Select all
TYPE percent_cc IS TABLE OF f21.ffct_offer_product_sku.sku_allocation_pct%TYPE
      INDEX BY BINARY_INTEGER;


The sku_allocation_pct field is defined as a: NUMBER (7,6)

The Package Body has the parameter defined as:
Code: Select all
p_sku_percent        f21.product_sku_mix_pkg.percent_cc


As I said, this all works fine when using the Direct method login but gets the error when using the Externally Managed login method.

If anyone can provide any help I would appreciate it.

We are using the Devart.Data.Oracle.dll version 5.35.62.0

Thank you



If needed, here is the full package spec if needed for any more clarification: (The parameters 8, 9, & 10 are Arrays of Strings, the 11th is an array of Integers, and the last is an array of Decimals).

Code: Select all
CREATE OR REPLACE PACKAGE F21.product_sku_mix_pkg
IS

   TYPE descriptor1_cc IS TABLE OF f21.ffct_offer_product_sku.descriptor1%TYPE
      INDEX BY BINARY_INTEGER;

   TYPE descriptor2_cc IS TABLE OF f21.ffct_offer_product_sku.descriptor2%TYPE
      INDEX BY BINARY_INTEGER;

   TYPE descriptor3_cc IS TABLE OF f21.ffct_offer_product_sku.descriptor2%TYPE
      INDEX BY BINARY_INTEGER;

   TYPE units_cc IS TABLE OF f21.ffct_offer_product_sku.units%TYPE
      INDEX BY BINARY_INTEGER;

   TYPE percent_cc IS TABLE OF f21.ffct_offer_product_sku.sku_allocation_pct%TYPE
      INDEX BY BINARY_INTEGER;

   PROCEDURE sku_mix (
      p_co_id              f21.ffct_offer_product_sku.company_id%TYPE,
      p_div_id             f21.ffct_offer_product_sku.division_id%TYPE,
      p_offer_id           f21.ffct_offer_product_sku.offer_id%TYPE,
      p_offer_yr           f21.ffct_offer_product_sku.offer_year%TYPE,
      p_offer_product_id   f21.ffct_offer_product_sku.offer_product_id%TYPE,
      p_work_ver_no        f21.ffct_offer_product_sku.version_no%TYPE,
      p_product_units      f21.ffct_offer_product_sku.units%TYPE,
      p_descriptor1        f21.product_sku_mix_pkg.descriptor1_cc,
      p_descriptor2        f21.product_sku_mix_pkg.descriptor2_cc,
      p_descriptor3        f21.product_sku_mix_pkg.descriptor3_cc,
      p_sku_units          f21.product_sku_mix_pkg.units_cc,
      p_sku_percent        f21.product_sku_mix_pkg.percent_cc
   );

END product_sku_mix_pkg;
Mike Haro
 
Posts: 1
Joined: Wed 10 Nov 2010 20:06

Postby StanislavK » Thu 11 Nov 2010 11:41

Thank you for your report, we have reproduced the issue. We will investigate it and inform you about the results.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby StanislavK » Tue 16 Nov 2010 17:05

Apparently, the problem is that the declared parameter type does not correspond to its value. Please try declaring the parameter with DbType.Decimal instead of Double:
Code: Select all
Dim oParm As New OracleParameter()
With oParm
   .ParameterName = "p_sku_percent"
   .Direction = ParameterDirection.Input
   .DbType = DbType.Decimal
   .Value = aPcts
   .ArrayLength = aPcts.GetLength(0)
End With

Also, you can define OracleCommand parameters automatically and only set their values using the ParameterCheck property:
Code: Select all
Dim cmd As New OracleCommand("product_sku_mix_pkg", connection)
cmd.CommandType = System.Data.CommandType.StoredProcedure
cmd.ParameterCheck = True
cmd.Prepare()
cmd.Parameters("p_sku_percent").Value = aPcts
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for Oracle