Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Passing array to Oracle procedure from c#

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();
}
like image 743
Flardryn Avatar asked Oct 27 '16 07:10

Flardryn


1 Answers

  1. 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; 
    
  2. 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();
    }
    
like image 166
Andriy Tolstoy Avatar answered Sep 18 '22 03:09

Andriy Tolstoy