locked
ORA-00936 error - SSRS 2008 R2 parameterized report, Oracle Provider for OLEDB connection to the data source RRS feed

  • Question

  • Folks,

    I am trying to utilize a basic dynamic query in SSRS Query Designer for the parameterized report:

    SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID

    FROM HR.EMPLOYEES

    WHERE (DEPARTMENT_ID IN (@dept_parameter))

    VS throws the errors - SQL Execution Error Message: ORA-00936: missing expression, Error Source: OraOLEDB.

    Could anyone suggest a workaround?

    Thank you!

     

    Tuesday, July 12, 2011 8:19 PM

Answers

  • is there a reason why you are using OLE instead of a regular Oracle connection?

    to do parameters with OLE you use a '?' like so:

    select 1 from dual where 1 = ?

    but Oracle OLE parameters cannot be used as a multivalue parameter, you would have to do a workaround like doing an expression based query but that can get messy:

    ="select 1 from dual where 1 in (" & join(Parameters!pname.Value,",") & ")"

     

    but if you do a regular oracle connection you can simply do:

    SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID

    FROM HR.EMPLOYEES

    WHERE (DEPARTMENT_ID IN (:dept_parameter))


    • Proposed as answer by gk1393 Tuesday, July 12, 2011 9:06 PM
    • Marked as answer by Elvis Long Tuesday, July 19, 2011 9:55 AM
    Tuesday, July 12, 2011 9:03 PM

All replies

  • One suggestion at: http://stackoverflow.com/questions/249865/what-could-cause-an-ora-00936-missing-expression-with-the-following-sql

    "The problem was that the client was running the wrong client driver version, 9.2.0.1, and there are known problems with that version."  You might check your Oracle OLE DB driver version and (if necessary) update it.

    The second possibility is that the @dept_parameter is not being handled properly.  See Jamie Thomson at:

    http://consultingblogs.emc.com/jamiethomson/archive/2005/12/09/SSIS_3A00_-Using-dynamic-SQL-in-an-OLE-DB-Source-component.aspx

    FWIW,
    RLF

    Tuesday, July 12, 2011 8:40 PM
  • is there a reason why you are using OLE instead of a regular Oracle connection?

    to do parameters with OLE you use a '?' like so:

    select 1 from dual where 1 = ?

    but Oracle OLE parameters cannot be used as a multivalue parameter, you would have to do a workaround like doing an expression based query but that can get messy:

    ="select 1 from dual where 1 in (" & join(Parameters!pname.Value,",") & ")"

     

    but if you do a regular oracle connection you can simply do:

    SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID

    FROM HR.EMPLOYEES

    WHERE (DEPARTMENT_ID IN (:dept_parameter))


    • Proposed as answer by gk1393 Tuesday, July 12, 2011 9:06 PM
    • Marked as answer by Elvis Long Tuesday, July 19, 2011 9:55 AM
    Tuesday, July 12, 2011 9:03 PM
  • Kumar,

    What do you mean by a regular Oracle connection within BIDS?  - I tried to replace "@" by ":" within SSRS Query Designer but this extension is not supported by MS Reporting Services.  I am getting an error, when trying to run the report.

    Thank you,

    Yulia

     

     

    Wednesday, July 13, 2011 1:28 PM
  • Russels, thank you for the suggestion!  We do have the latest OLE DB Provider.
    Wednesday, July 13, 2011 1:31 PM
  • Yulia, in my earlier post I mentioned (in addition to driver version) the Jamie Thompson post about using Dynamic SQL.  In that way there are no @, ?, or : in the string.  Instead the SQL string is built to include the entire text of the query including the parameters.

    As I mentioned earlier, I am not an Oracle user, so I do not contest any Oracle advice from those who know. 

    FWIW,
    RLF


    Wednesday, July 13, 2011 3:42 PM
  • Kumar,

    What do you mean by a regular Oracle connection within BIDS?  - I tried to replace "@" by ":" within SSRS Query Designer but this extension is not supported by MS Reporting Services.  I am getting an error, when trying to run the report.

    Thank you,

    Yulia

     

     


    When you create a new data source in BIDS you should see a datasource type that says "Oracle" with this one you can use regular Oracle parameters.
    Wednesday, July 13, 2011 4:11 PM