0

I've created a procedure for test this problem and it works right in oracle developer. There is a typed named "dizi" (array and varchar2). And procedure has input parameter. I'm trying to pass an array to this to this procedure as a parameter in c#. I've searched a lot but i couldn't solve the problem. The error is: "Not all veriables bound"

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

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

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

Here is my procedure (it is just for test but i'll use something like that)

CREATE OR REPLACE PROCEDURE TESTPROCEDURE (t_in IN dizi)
IS
BEGIN
 FOR i IN 1..t_in.count LOOP
 dbms_output.put_line(t_in(i));
    END LOOP;
    END;
Flardryn
  • 457
  • 1
  • 10
  • 25

1 Answers1

0

I've got code that successfully passes array down to oracle sprocs. Takes a slightly different approach to yours. Not entirely sure how much is relevant, but in case it helps my code:

  • uses the correct name parameter name (t_in in your case)
  • doesn't bother setting the size of the parameter
  • creates an object array that is the correct length and copies the contents across into it (ie from quest_areas in your case)
  • then sets this object array as the Value for the command parameter
  • doesn't use bind variables when calling the proc, rather just uses the proc name by itself as the CommandText.

That said, I suspect your problem might be around your use of a bind variable when calling the procedure. What happens if you just set 'TESTPROCEDURE' as the CommandText?

Or go the other way and put change it into a proper anonymous PLSQL block 'begin TESTPROCEDURE(:area_array); end;' and change the CommandType to CommandType.Text (as just suggested by Wernfried while I was typing...)

Update

public void InsertQuestion(List<string> area_list)
    {
        var input_array = area_list.Select(s => (object)s).ToArray();
        command = new OracleCommand();
        command.Connection = connect;           
        connect.Open();

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

        command.CommandText ="TESTPROCEDURE";
        command.CommandType = CommandType.StoredProcedure;
        command.ExecuteNonQuery();
        connect.Close();
    }
Ian of Oz
  • 116
  • 7
  • Before your advice i tryed using begin-end; and also tryed using just TESTPROCEDURE. But i've change my parameter name as t_in and "BEGIN TESTPROCEDURE(:t_in); END;" So it doesn't work but my error has changed: wrong number of types of arguments in call to 'TESTPROCEDURE' – Flardryn Oct 27 '16 at 13:04
  • In that case, try my updated answer - a fuller version of what I was suggesting to match the approach I've successfully taken. – Ian of Oz Oct 27 '16 at 13:20
  • Unfortunately it still doesn't work (wrong number or types of arguments in call to 'TESTPROCEDURE') :( I think we've used all bullets, maybe i should change my all structure – Flardryn Oct 27 '16 at 13:47
  • One more thing to try: change the parameter name to match the parameter name in the procedure: var arry = command.Parameters.Add("t_in",OracleDbType.Varchar2); – Ian of Oz Oct 27 '16 at 13:58
  • I had tryed before my comment :) I think i give up, i'll change my way. Thank u for your time and effort – Flardryn Oct 27 '16 at 14:10