Can't pass an array to oracle procedure

Question

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;

Try command.CommandType = CommandType.Text; command.CommandText ="BEGIN TESTPROCEDURE(:area_array); END;"; — Wernfried Domscheit
– Wernfried Domscheit, Commented Oct 27, 2016 at 12:40
I think you cannot use BindByName = true; when you do CommandType = CommandType.StoredProcedure;. — Wernfried Domscheit
– Wernfried Domscheit, Commented Oct 27, 2016 at 12:52
Possible duplicate of Passing array to Oracle procedure from c# — Andriy Tolstoy
– Andriy Tolstoy, Commented Nov 1, 2016 at 9:07

Ian of Oz · Accepted Answer · 2016-10-27 13:19:47Z

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();
    }

edited Oct 27, 2016 at 13:19

answered Oct 27, 2016 at 12:43

Ian of Oz

1167 bronze badges

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

5 Comments

Flardryn Over a year ago

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'

Ian of Oz Over a year ago

In that case, try my updated answer - a fuller version of what I was suggesting to match the approach I've successfully taken.

Flardryn Over a year ago

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

Ian of Oz Over a year ago

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);

Flardryn Over a year ago

I had tryed before my comment :) I think i give up, i'll change my way. Thank u for your time and effort

Collectives™ on Stack Overflow

Can't pass an array to oracle procedure

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related