Best practice to pass parameter into Dataset query on Oracle data source (by ODBC)

Question

Hi guy,
I'm creating SSRS reports which get data from Oracle database by ODBC. Using Dataset properties, I can define query and want to use parameter in it. After research, I can you ":" to pass parameter. For example:
SELECT SNAPSHOT_DATE
FROM SBV_SUMMARY
WHERE 1= :pParam

Can you give me which is best practice to using parameter with Oracle by ODBC as discribed. Using ":" or something else better?
Thanks so much.

Answer 1

Hi @Linh Le Van ,
In this SQL statement, :pParam is a placeholder for the actual value provided to the statement by the application at runtime. The colon is used to distinguish the parameter name from the literal value. You can also include unnamed parameters by adding a question mark (?) to the query. Unnamed parameters are identified by position because they do not have unique names.
Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

WHERE 1= :pParam

Named parameters starting with colon like here :pParam are supported by Oracle clients & Ado.NET.
ODBC supports only the quotation mark ? as parameter placeholder, see Binding Parameters ODBC

Answer 3

Thanks all.
It's clear to understand on the issue which the report using SELECT statement, I need to use ? to the query and it will create parameter automatically by position of ? character then need to assign report parameter to each theses.

For example:
SELECT SNAPSHOT_DATE, 1 LOAD_NBR
FROM SBV_SUMMARY
WHERE SNAPSHOT_DATE = to_date(? , 'yyyymmdd') AND LOAD_JOB_NBR = ?