none
Unable to get Out array parameter using odp.net in Oracle Procedure RRS feed

  • Question

  • Hi there,

     

    I am using odp.net to access Oracle in my .net application.

     

    The problem is:

    I need to fetch an out array (Varchar2) parameter from a procedure.

     

    When i add the parameter, i do not know what size the array will be. Neither do i know what wil be ArrayBindSize of each array element.

    Hence, i do not set the size and arraybindsize for the array out parameter.

     

    But when i execute the statement using odp.net, i get an error stating size of the array not set.

     

    How to handle this problem.

     

     

    Any suggestion will be helpful 

    • Moved by KareninstructorMVP Friday, April 17, 2015 12:23 PM Moved from VB.NET
    • Moved by Fred Bao Saturday, April 25, 2015 8:11 AM Oracle Related
    Friday, April 17, 2015 5:29 AM

All replies

  • Are you using an associative array?

    http://weblogs.asp.net/ricardoperes/odp-net-associative-arrays

    Another option would be to use a ref cursor instead, since you don't know the number of items that will be returned.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, April 17, 2015 1:22 PM
  • Hi Paul, I am aware of it. But, we don't want to go with refCursor due to some reason.

    According to me, if ODP.net is allowing Out array parameter, then there might be some work around to handle this issue.

    Regards,

    Pratik Nirgun

    Monday, April 20, 2015 4:59 AM
  • So did you try using an Associative Array, as in the example?

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Monday, April 20, 2015 1:01 PM
  • Yes I did,

    But the problem is, I need to set ArrayBindSize and Size for the out parameter.

    Now, since it is an out parameter, i do not know the arrayBindSize and the size factors.

    Regards,

    Pratik Nirgun


    Wednesday, April 22, 2015 8:14 AM
  • My problem is same as mentioned in this ticket.

    We cannot predict the arraybindsize and size of and out array parameter. Also, I feel that, it will not be good to set arraybindsize and size to maximum.

    How to cope with such issue?

    Regards,

    Pratik Nirgun

    Wednesday, April 22, 2015 11:09 AM
  • My problem is same as mentioned in this ticket.

    We cannot predict the arraybindsize and size of and out array parameter. Also, I feel that, it will not be good to set arraybindsize and size to maximum.

    How to cope with such issue?

    Regards,

    Pratik Nirgun

    Wednesday, April 22, 2015 11:09 AM
  • I'm not aware of a workaround, other than to specify the ArrayBindSize as the maximum number of characters the column in the table (that you are assigning to the array) is defined for. So, for example, if the column is defined as VARCHAR(20) then 20 would be the ArrayBindSize for that element of the array.

    Unfortunately, this is a limitation when using an associative array and variable length data, which is why I suggested using a ref cursor.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, April 22, 2015 12:04 PM
  • Hello nirgunpratik,

    For issues related with Oracle/ODP.NET, I suggest you could ask it on Oracle/ODP.NET forum:

    https://community.oracle.com/welcome, there are Oracle experts will help you.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Saturday, April 25, 2015 8:13 AM