Devart Forums

Posted: **Thu 03 May 2012 12:12**

Hello,
i have got a stored procedure with a function that returns a table of integer.
Somehow i can't call this procedure from c# without getting a Devart.Data.Oracle.OracleException: ORA-06550 exeption.

We are using dotConnect for Oracle 6.60.268

Does anybody have a hint how to configure the return parameter ??
Or DO we have to use dotConnect 6.70 or 6.80

thanx for any help

Code: Select all

// ORACLE PACKAGE
PACKAGE my_package
  IS
    TYPE INT_TABTYPE IS TABLE OF INTEGER;
    FUNCTION  get_block(p_sequence_name IN VARCHAR2, p_increment IN INTEGER) RETURN  INT_TABTYPE;

END; --

// C# CODE

Code: Select all

        public decimal[] GetBlock(string sequenceName, int increment)
        {
            decimal[] result;
            OracleParameterCollection parameters;
            parameters = this.Parameters;
            parameters.Clear();
            OracleParameter parameter;

            parameter = new OracleParameter("RESULT", OracleDbType.Number);
            parameter.Direction = ParameterDirection.ReturnValue;
            parameter.Size = 50;
            parameter.ArrayLength = 20;
            parameter.IsNullable = true;
            parameters.Add(parameter);

            parameter = new OracleParameter("p_sequence_name", OracleDbType.VarChar);
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = sequenceName;
            parameters.Add(parameter);

            parameter = new OracleParameter("p_increment", OracleDbType.Integer);
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = increment;
            parameters.Add(parameter);

            ExecuteProcedure("GET_BLOCK", parameters);

            if ((Parameters["Result"].Value == System.DBNull.Value))
            {
                result = null;
            }
            else
            {
                result = ((decimal[])(Parameters["Result"].Value));
            }
            return result;
        }

Posted: **Tue 15 May 2012 06:51**

dotConnect for Oracle does not support the Nested table declared inside a package. The following two ways can be used as workarounds:

1. Change the type of INT_TABTYPE. Your package will look as follows:

Code: Select all

CREATE OR REPLACE PACKAGE SCOTT.my_package
  AS
    TYPE INT_TABTYPE IS TABLE OF INTEGER INDEX by binary_integer;
    FUNCTION  get_block(p_sequence_name IN VARCHAR2, p_increment IN INTEGER) 
RETURN  INT_TABTYPE;

END my_package;

2. You have to declare a table of a integer type outside the package:

Code: Select all

CREATE OR REPLACE TYPE SCOTT.INT_TABTYPE AS
    TABLE OF NUMBER(20);

And C# code for this case is:

Code: Select all

OraclePackage package = new OraclePackage();
package.Connection = conn;
package.PackageName = "my_package";

OracleParameterCollection parameters = new OracleParameterCollection();
OracleParameter parameter;

parameter = new OracleParameter("RESULT", OracleDbType.Table);
parameter.Direction = ParameterDirection.ReturnValue;
parameter.IsNullable = true;
parameter.ObjectTypeName = "INT_TABTYPE";
parameters.Add(parameter);

parameter = new OracleParameter("p_sequence_name", OracleDbType.VarChar);
parameter.Direction = ParameterDirection.Input;
parameter.Value = sequenceName;
parameters.Add(parameter);

parameter = new OracleParameter("p_increment", OracleDbType.Integer);
parameter.Direction = ParameterDirection.Input;
parameter.Value = increment;
parameters.Add(parameter);

package.ExecuteProcedure("get_block", parameters);

Note that data types

Code: Select all

TYPE INT_TABTYPE IS TABLE OF INTEGER INDEX by binary_integer;

and the one declared outside the package

Code: Select all

CREATE OR REPLACE TYPE SCOTT.INT_TABTYPE AS
    TABLE OF NUMBER(20);

are different types and must be implemented within the get_block function in different ways.

Posted: **Tue 15 May 2012 09:41**

Hello,
thanx for your help.

everything works now.

George

Devart Forums

TABLE OF INTEGER as result

TABLE OF INTEGER as result

Re: TABLE OF INTEGER as result

Re: TABLE OF INTEGER as result