Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
ayup
Helper I
Helper I

How to use parameter in Oracle-based SQL query

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?

1 ACCEPTED SOLUTION

Thanks for your response @kailas684

View solution in original post

7 REPLIES 7
theov
Advocate II
Advocate II

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
Community Champion
Community Champion

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

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.

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

 

 

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

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&"')

 

Thanks for your response @kailas684

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.