How to use parameter in Oracle-based SQL query

[ayup]

Hoping someone can help me with this...

I've read the "Deep Dive into Query Parameters and Power BI Templates" article about the use of parameters in Power BI Desktop .  Further to this, I've been trying - unsuccessfully - to make use of a parameter in a simple test SQL query that accesses an Oracle database.

Let's say I have a simple query:

SELECT col1, col2

FROM  mytable

WHERE col3 = 'mytestvalue'

I then create a parameter called "myparam", which I'd like to use instead of the literal value:

SELECT col1, col2

FROM   mytable

WHERE col3 = &myparam

I've tried various ways of expressing the parameter, e.g. &myparam& / :myparam / "&myparam&" but none have worked - I just get different Oracle errors raised, e.g. ORA-904: invalid identifier, ORA-1008 not all variables bound.

How should I express the parameter to get this to work?

[theov]

This will help on thi subject. For creating a parameter from Orcale source or using a parameter in Orcale query.

https://www.youtube.com/watch?v=Qk9yLKtl8t8

[ankitpatira]

@ayup Check out approach shown here and here to make use of query parameters.

[ayup]

Hi ankitpatira

Thanks for your response.

Unfortunately, the examples given in these articles don't seem to address the specific scenario that I'm encountering when trying to use the parameter as a filter when accessing data from an Oracle database.

[kailas684]

I know, it is too late; just to close the loop and share solution for others who are looking for working solution.

Step#1: Create Parameter pID [text parameter and keep default value]

Step#2: On Dataset that is pulled on Query Editor, right click and go to Advanced Editor. Edit below code as per your database and table name.

let
    pID=ID,
    Source =
    Oracle.Database("your database name",
    [HierarchicalNavigation=true,
    Query="SELECT * FROM MYtableName#(lf) where DA_LOAN_ID='"&pID&"'"])   
in
    Source

[Cairocalling]

Thank you, kailas684. You're a star! I spent hours yesterday trying to get this to work. This morning I set my parameter type to text and used '"&Parameter&"' and it finally worked. I can now bring back 1 record instead of 20,000

[SohaibS]

Thanks Kailas684, this sort of worked for me. My requirement is to pass a list to the sql query from the parameter list. 

Insead of this where DA_LOAN_ID='"&pID&"'

I want something that is where DA_LOAD_ID IN ('"&ParameterList&"')

[ayup · Accepted solution]

Thanks for your response @kailas684