Passing arrays to oracle stored procedure

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
cdima
Posts: 1
Joined: Thu 04 Oct 2012 21:20

Passing arrays to oracle stored procedure

Post by cdima » Thu 04 Oct 2012 21:30

Hello,

I am trying to pass a list of Guids from .Net to a stored procedure in Oracle.

In oracle, I have this:

Code: Select all

CREATE OR REPLACE TYPE t_guids AS TABLE OF RAW(16);
In .Net, I am trying to do this:

Code: Select all

                var ids1 = new Devart.Data.Oracle.OracleParameter()
                    {
                        ParameterName = "p_ID1",
                        OracleDbType = OracleDbType.Table,
                        OracleValue = new Guid[] { Guid.NewGuid() },
                        Direction = ParameterDirection.Input,
                        ObjectTypeName = "t_guids"
                    };
                var ids2 = new Devart.Data.Oracle.OracleParameter()
                  {
                      ParameterName = "p_ID2",
                      OracleDbType = OracleDbType.Table,
                      OracleValue = new Guid[] { Guid.NewGuid() },
                      Direction = ParameterDirection.Input,
                      ObjectTypeName = "t_guids"
                  };

                context.ExecuteStoreCommand("begin CONST2.setarray(:p_ID1, :p_ID2); end;", 
                    ids1, 
                    ids2);
Yet I always receive an error in converting the type from guid[] to t_guid, or a "index out of bounds" when converting. Please advise on how to pass an array of guids to a stored procedure.

Thanks.

Info:
Version Devart.Data.Oracle v7.1.58.0
Oracle 11.2

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Passing arrays to oracle stored procedure

Post by Shalex » Tue 16 Oct 2012 08:11

Sorry for the delay. Please pass a list of Guids from .Net to a stored procedure in Oracle in this way:

Code: Select all

        using (OracleConnection conn = new OracleConnection()) {
            conn.ConnectionString = "server=orcl1120;uid=shalex;pwd=shalex;";
            conn.Open();

            OracleTable tb = new OracleTable(OracleType.GetObjectType("T_GUIDS", conn));
            tb.Add(Guid.NewGuid().ToByteArray());
            tb.Add(Guid.NewGuid().ToByteArray());

            var ids1 = new Devart.Data.Oracle.OracleParameter() {
                ParameterName = "p_ID1",
                OracleDbType = OracleDbType.Table,
                OracleValue = tb
            };

            var ids2 = new Devart.Data.Oracle.OracleParameter() {
                ParameterName = "p_ID2",
                OracleDbType = OracleDbType.Table,
                OracleValue = tb
            };
            context.ExecuteStoreCommand("begin setarrayguids(:p_ID1, :p_ID2); end;",
                ids1,
                ids2);
        }

Post Reply