Power BI Desktop Bind Variables in Oracle Queries

[Mottor]

Hi,

I have found how to use parameter in query, like this:

let
Source = Oracle.Database("SYNCDWP", [Query="SELECT MGROUP,COL2,COL3 #(lf) FROM MY_BIG_TABLE#(lf)WHERE MGROUP = '"&PAR_GROUP&"'", HierarchicalNavigation=true])
in
Source

The Power BI replaces the parameter PAR_GROUP with the value and executes the query.

1. First problem is, that  for every new parameter value I have to give permission for executing.

2. The second is, that is not bind variable used in Oracle, but constant. In this way, Oracle will parse the query again and again

for every new parameter value and there will be a different execution plan as in the case of bind variable using.

Did I use the parameters in the wrong way?

Is there a way to use real bind variables with Oracle queries?

Where can I find more about the language (and functions) used in Advanced Editor?

Best Regards

[Eric_Zhang · Accepted solution]

---

@Mottor wrote:

Hi,

I have found how to use parameter in query, like this:

let
Source = Oracle.Database("SYNCDWP", [Query="SELECT MGROUP,COL2,COL3 #(lf) FROM MY_BIG_TABLE#(lf)WHERE MGROUP = '"&PAR_GROUP&"'", HierarchicalNavigation=true])
in
Source

The Power BI replaces the parameter PAR_GROUP with the value and executes the query.

1. First problem is, that  for every new parameter value I have to give permission for executing.

2. The second is, that is not bind variable used in Oracle, but constant. In this way, Oracle will parse the query again and again

for every new parameter value and there will be a different execution plan as in the case of bind variable using.

Did I use the parameters in the wrong way?

Is there a way to use real bind variables with Oracle queries?

Where can I find more about the language (and functions) used in Advanced Editor?

Best Regards

---

@Mottor

1. "File->Options and Settings->Options->Security", uncheck "Require user approval for new native database queries".
2. Power BI so far seems not to support bind variables in Oracle, what is the concern of parsing a new execution plan every time? Does the parse consume too much time? As per the query in the original post, the query is quite simple, I don't have much knowledge on Oracle execution plan, however for that query, parsing execution plan won't take too much time in SQL Server.
   If the parsing indeeds affect performance, try a function or stored procedure instead of that query, I guess that the execution plan for functions and stored procedures may be cached as they're in SQL Server?

[Mottor]

Hi @Eric_Zhang,

1. Thank you

2. The 40 queries which I have are two pages each and have up to 7 parameters. I don't want to post them here. Parsing takes time when query is complex. But is not too much or critical. But when constants are used instead of bind  variables the query is seen as new and it is cached with the execution plan. Does not mather. I will use stored procedures, like you suggested. Thank you