How to pass parameter in SSRS for Oracle Query?

Question

I am using SQL Server Reporting Services (SSRS), and I am using Report Builder to create a report from Oracle database. in my report I have a parameter called @ID. and I need to pass this parameter in the Query Designer in the Report Builder Dataset for Oracle Query in SSRS as below,

SELECT CustID FROM Customers where CustID=@ID

But when run this query I got this error

An error occurred while executing the query
ORA-00936 Missing Expression
ORA-00936 Missing Expression

I also tried to preview the report by clicking on the RUN button, but it didn't work , and I got this error

An error occurred during local report processing.
An error has occurred during report processing.
Query execution failed for data source
ORA-00936:Missing Expression[]

What am I missing? and How can I pass parameter in SSRS report for Oracle Query?

Answer 1

How to pass parameter for Oracle Query in SSRS?

First, you should be aware of there are some syntax differences between the Oracle Query and the SQL Server Query when you pass a parameter in SSRS report query.

In your case, to can pass a parameter in Oracle Query in SSRS, you just need to replace @ with :

EX: SELECT CustID FROM Customers where CustID=:ID

By the way, even if you have replaced @ with :, the Oracle Query will not work in Query Designer as expected and you will get the same error ORA-00936 Missing Expression, Instead, you should click on the RUN button to see the result.

Comments

Brilliant, Works like charm, Thanks Mohamed!