question

LinhLeVan-7818 avatar image
0 Votes"
LinhLeVan-7818 asked LinhLeVan-7818 answered

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

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.

sql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered

Hi @LinhLeVan-7818 ,
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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LinhLeVan-7818 avatar image
0 Votes"
LinhLeVan-7818 answered

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 = ?

108243-screenshot-2021-06-22-221220.jpg



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.