none
DataSet designer in Visual Studio with oracle ORA-01008: not all variables bound error message RRS feed

  • Question

  • Hi,

    I have one problem, I tried to make a dataset in Visual Studio 2010, using oracle database, and some complex query(with procedures, package functions etc) and with parameters, like "select field1, field2, mypackage.myfunction(idfield3,to_date(:param1,'dd.mm.rrrr')) from mytable" and when I try to execute data, it show me an error with the message:ORA-01008: not all variables bound. Now I did some research and I found that Microsoft data provider for oracle has some issues with oracle parameter binding, anyway I installed devart's component "dotConnect for Oracle", and I managed to pass parameters in sql queries, but I couldn't make it to work using package functions, and more complex queries like "select field1,field2,(select myfield from mytable2 where myfield2=u.field1) from mytable1 u".

    It seems like Microsoft Dataset Designer has some limitations in parsing oracle statements and complex queries. I always get an error:

    Error in SELECT clause: expression near '('.
    Missing FROM clause.
    Error in SELECT clause: expression near ','.
    Unable to parse query text.

     

    and after that again error: ORA-01008: not all variables bound.

    Does anyone know what could be the problem, and how can it be solved?

    Thanks.

    • Moved by VMazurModerator Monday, August 16, 2010 10:22 AM (From:ADO.NET Managed Providers)
    Thursday, August 12, 2010 9:01 PM

Answers

All replies

  • Hello,

     

    Welcome to MSDN forums!

     

    I think the problem is more related to the third party data provider for Oracle database, which is off topic in the current forum queue.

     

    BTW, Microsoft provider for Oracle is deprecated in VS2010 and .NET 4.0, http://blogs.msdn.com/b/adonet/archive/2009/06/15/system-data-oracleclient-update.aspx.   You can also consider Oracle provider for .NET, http://www.oracle.com/technology/tech/windows/odpnet/index.html.  

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com

     

    (This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.)


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, August 13, 2010 8:25 AM
    Moderator
  • Hi,

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, August 18, 2010 1:19 AM
    Moderator
  • I believe I am having similar problems using Oracle's ODAC with Visual Studio's Data Designer.

    I suspect that the problem is on Oracle's side.

    When I attempt to use a "TableAdapter" for a query of the form

      " select ... where .... and x_low <= :x_val and x_high >= :x_val and acolumn in ( :aval, 'xxx' )"

    I get an "unbound variable" exception, but if I do

      "select ... where ... and ( :x_val between x_low and x_high ) and acolumn in ( :aval, 'xxx' )"

    it works.

    I peeked at the code generated by the "Data Designer" and it seemed to be putting the

    correct number of parameters in the "Parameters" array on the adapters, so I suspect Oracle

    is the problem.

     

    I've also seen Visual Studio's Designer "hang" for 10 or 15 minutes after pushing the "finish" button.

    There is a "devenv.exe" and another process which seem to consume one full CPU.  I've taken to restarting visual studio each time I edit a query :-).

     

    Good luck.

    Wednesday, October 27, 2010 11:18 PM
  • Hi

    I used ? instead of :parameter_name and it works

    please test it !

    Saturday, August 6, 2011 9:17 AM