TABLE OF INTEGER as result

TABLE OF INTEGER as result

Postby gkubesch » 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;
        }
gkubesch
 
Posts: 4
Joined: Thu 03 May 2012 12:00

Re: TABLE OF INTEGER as result

Postby Pinturiccio » 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.
Pinturiccio
Devart Team
 
Posts: 1893
Joined: Wed 02 Nov 2011 09:44

Re: TABLE OF INTEGER as result

Postby gkubesch » Tue 15 May 2012 09:41

Hello,
thanx for your help.

:D
everything works now.

George
gkubesch
 
Posts: 4
Joined: Thu 03 May 2012 12:00


Return to dotConnect for Oracle