locked
Oracle parametered query : ORA-01008, not all variables bound RRS feed

  • Question

  • Hello everybody,

    The way my machine is installed at the office, it seams that to connect to an Oracle database, Oracle objects fail to be called, so from Access I have to use ADODB objects, this is why I ask my question here.

    In SQL Developper, I tried this query :

    SELECT NOM, CP, PRENOM, ID_INDIVIDU FROM INDIVIDU WHERE NOM=:LENOM;

    This works nice, whey I depress F9 I see a dialog box that asks me to insert LENOM as a value for the field NOM.

    The problem arises when trying to do that from an Access interface, in VBA.

    Dim Cmd As ADODB.Command Dim Param1 As ADODB.Parameter Dim strNom As String

    Dim strCon As String
    Dim NumLigne As Integer
    Dim NumLigne2 As Double
    Dim NumCol As Integer
    Set oraRs = New ADODB.Recordset
    strCon = 'correct string, otherwise I should have had

    'problems before dealing with parameterized queries,

    'shouldn't I ?

    Set Cn = New ADODB.Connection
    Cn.ConnectionString = strCon
    Cn.Open strCon, "USERNAME", "PASSWORD", &O0

    Set Cmd = New ADODB.Command strNom = "DUPOND" With Cmd .ActiveConnection = Cn .CommandType = adCmdText .CommandText = "SELECT NOM, CP, PRENOM, ID_INDIVIDU FROM INDIVIDU WHERE NOM=:LENOM;" Set Param1 = .CreateParameter("LENOM", adVarChar, adParamInput, 6, "DUPOND")

    .Parameters.Append Param1 End With

    Cmd.Parameters("P1").Value = "DUPOND" 'Nothing new in fact ...

    Set Rs = Cmd.Execute

    On the last line, I get "ORA-01008 : not all variables bound".

    So, if the ":" column prefixes a parameter name in SQL Developper, why should not it in VBA ?

    In fact, Cmd does have one parameter, called LENOM, with the value "DUPONT", but it seems it does not arrive to Oracle.

    I tried with different names and with no name for the parameter, I tried the query ending with NOM=':P1' as somebody suggested that in a forum, but it seems I still did not try what had to be tried.

    Any suggestions ?



    • Edited by Gloops Tuesday, July 31, 2012 4:18 PM
    Tuesday, July 31, 2012 4:04 PM

All replies

  • I use ADO with a different language, but it should be similar. Concerning ADO and Oracle sql syntax there are some translations for how to define a prepared Statement. The Sql Developper program might have used the right settings automatically or by chance. Using ADO from programming language usually takes some settings. There might be different ADO drivers for the Oracle Database. Depending on which you use it takes a different syntax for the variable.

    =:LENOM

    Is the standard syntax for Oracle, but not the standard in ADO. Only the ADO drivers for Oracle use it. Other Databases prefer to declare the variable by =?

    There might be an ADO driver installed that expects the usual question mark as standard syntax. Then the driver translates it back to Oracle syntax and then it fits. But giving the Oracle syntax to any oracle driver wouldn't fit with every driver.

    This would be the first place to look at. Find out what is returned by the Param() method of ADOConnection class and which subclasses (drivers) overwrite that method.

    Friday, October 4, 2013 11:51 PM
  • Hello,

    The context I was working on when asking the question is not available any more, but I shall have to adapt what I am doing now to an Oracle database, so maybe a question can be at its place here (despite I now rather develop in Csharp), but that can be in a few months.

    I am now on a web site developed on Visual Studio, that calls a MySql database, and the query only works, when I replace the parameters by their values explicitly by code : you see, still a parameters problem. And the parameters are preceded by question marks in the query code, as they must in a MySql database -other queries work OK.

    Do you want my searching the URL about the relevant question ?

    Saturday, October 5, 2013 9:15 PM
  • Thanks, I don't need your researches. And sorry, my explanations wouldn't fit for your programming. Although the same name is used for programming technologies with the same purpose, ADODB is here quite different from the ADODB for PHP which I use. We might have to solve the same problems, but each one in a different language and probably in a different way.

    Sunday, October 6, 2013 9:15 AM