Passing array to Oracle procedure from c#

Question

I'm trying to pass an array to oracle procedure. I searched about it and firstly i created a type named 'dizi' (like here enter link description here). So it works in oracle developer. The problem is; i can't pass my c# array to procedure as a parameter. So how can i pass my array to my procedure?

Here is my code (When i execute, oracle error says: Not all variables bound)

public void InsertQuestion(List<string> area_list)
{
    quest_areas = area_list.ToArray();
    command.Connection = connect;
    connect.Open();

    var arry = command.Parameters.Add("Areas", OracleDbType.Varchar2);
    arry.Direction = ParameterDirection.Input;
    arry.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
    arry.Value = quest_areas;


    command.CommandText ="TESTPROCEDURE(:Areas)";
    command.CommandType = CommandType.StoredProcedure;
    command.ExecuteNonQuery();
    connect.Close();
}

You are already doing it command.Parameters.Add adding the DbParameter, internally OracleParameter — Mrinal Kamboj
– Mrinal Kamboj, Commented Oct 27, 2016 at 8:01

Andriy Tolstoy · Accepted Answer · 2016-10-31 14:28:01Z

16

Define an array type and a procedure:

CREATE or replace PACKAGE Testpackage AS 
  TYPE Areas_t is table of VARCHAR(100) index by BINARY_INTEGER;
  PROCEDURE TESTPROCEDURE(Areas IN Areas_t);       
END Testpackage;

C# routine:

public void InsertQuestion(IEnumerable<string> area_list)
{
    var connect = new OracleConnection("YOUR CONNECTION STRING");

    var command = new OracleCommand("BEGIN Testpackage.Testprocedure(:Areas); END;", connect);

    connect.Open();

    var arry = command.Parameters.Add("Areas", OracleDbType.Varchar2);

    arry.Direction = ParameterDirection.Input;
    arry.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
    arry.Value = area_list.ToArray();
    arry.Size = area_list.Count();
    arry.ArrayBindSize = area_list.Select(_ => _.Length).ToArray();
    arry.ArrayBindStatus = Enumerable.Repeat(OracleParameterStatus.Success, area_list.Count()).ToArray();

    command.ExecuteNonQuery();

    connect.Close();
}

answered Oct 31, 2016 at 14:28

Andriy Tolstoy

6,2002 gold badges35 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

XDS Over a year ago

Would you be kind enough to enrich this example so as to show how to use 'Areas' inside the implementation of TESTPROCEDURE? Thank you in advance.

Toolkit Over a year ago

seems like you don't need a bunch of parameters like Size,Direction, ArrayBindSize, ArrayBindStatus?

cdonner Over a year ago

Side note: IEnumerable.ToArray() requires System.Linq.

Collectives™ on Stack Overflow

Passing array to Oracle procedure from c#

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related