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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Mike Haro
Posts: 1
Joined: Wed 10 Nov 2010 20:06

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

Post by 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;

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by 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

Post by 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

Post Reply