Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    10

    Unanswered: Oracle Error Code = 1036

    We are using Oracle 8.1.7 and I keep getting the above error with the following query (executed via Cold Fusion 5.0):

    Oracle Error Code = 1036

    ORA-01036: illegal variable name/number

    SQL = "

    SELECT P.Disabled,
    P.SiteID,
    PC.EmailAddress,
    P.PersonID AS PID,
    PC.PrimaryEmail,
    P.Password

    FROM tbl_Person P,
    tbl_Person_Contact PC

    WHERE (P.PersonID = PC.PersonID)
    AND (P.SiteID = :1)
    AND (PC.PrimaryEmail = :2)
    AND (PC.EmailAddress = ':3')
    AND (P.Password = ':4')

    ORDER BY P.PersonID

    "

    Query Parameter Value(s) -

    Parameter #1 = 2
    Parameter #2 = 1
    Parameter #3 = test@something.com
    Parameter #4 = test

    From what I've read, the error could be caused by one or all of the following reasons:

    1) Use of "reserved word" in the column name of "Password".
    2) Not properly "binding" columns in the SQL statement with variables in the output.

    I have tried using brackets around Password in the above SQL statement, but the error persists. I have also tried removing the column alias of PID, but the error persists.

    Any suggestions would be greatly appreciated.

    Thanks in advance.

  2. #2
    Join Date
    Aug 2002
    Location
    UK
    Posts
    87
    Are you sure that
    PC.EmailAddress = ':3'
    is being substitued by test@something.com ??

    Shoud this be somthing like
    PC.EmailAddress = char(39) + :3 + char(39)
    to add the ansi ' before and after the variable :3

    clear as mud

  3. #3
    Join Date
    Jul 2002
    Posts
    10
    Originally posted by WingMan
    Are you sure that
    PC.EmailAddress = ':3'
    is being substitued by test@something.com ??

    Shoud this be somthing like
    PC.EmailAddress = char(39) + :3 + char(39)
    to add the ansi ' before and after the variable :3

    clear as mud
    Thank you very much for your reply! However, that is incorrect in terms of how Cold Fusion parameterizes variables within SQL statements. The parameterization is correct, but again, the error persists.

    I think it may have something to do with the column type for "Password", since it was set to RAW rather than VARCHAR. I am going to try to change the column type and also the SQL type in the Cold Fusion parameterization codes.

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •