Search code examples
oracle-databasepowerquery

Using parameters in Value.NativeQuery() for Oracle database from Microsoft Power Query

I can issue SQL against Oracle from Power Query like this:

=Oracle.Database("database_name", [Query="SELECT name FROM employees WHERE name = 'Mike'"])

If I want to use a parameter in the SQL, I can do this:

=Oracle.Database("database_name", [Query="SELECT name FROM employees WHERE name = '" & Parameter & "'"])

But the problem with this is when I want to run this query anywhere else, say Oracle's SQL Developer tool, I have to replace all the string concatenations and plug in the replacement values to get the query to run. This example isn't bad, but we have some queries that are quite a bit longer and have quite a few parameters, and replacing the parameters with the values is a pain.

I could build my query string in a separate step before the Oracle.Database() step and get the result of all the parameter substitutions in the Power Query editor, but that only helps when I'm in the editor. Sometimes I just have the M code, so I still have to do all the manual substitutions.

So today I was pleased to find Value.NativeQuery() had proper support for parameters. Here's a SQL Server example:

Value.NativeQuery(Source, 
"SELECT [FirstNane], [LastName] FROM [dba].[DimEmployee] 
WHERE [FirstName] = @FirstN", 
[FirstN = myParam])

That looks great, but it doesn't work for Oracle. It appears for Oracle, you use & instead of @, and instead of passing parameters in a record ala [], you need to pass a list ala {}. But it's still not working:

=Value.NativeQuery(Source,"SELECT name FROM employees WHERE name = '&name'",{name="Mike"})

Result: Expression.Error: The name 'name' wasn't recognized. Make sure it's spelled correctly.

The "name" that is unrecognized is from the list. If I change it to {nm="Mike"} it says 'nm' wasn't recognized. Anyone know the proper syntax for Oracle for parameters in Value.NativeQuery()?

Solution

  • Based on Chris Webb's BI Blog, it looks like you need to either use a record or a simple list.

    Have you tried passing a record like this?

    =Value.NativeQuery(
    Source,
    "SELECT name FROM employees
     WHERE name = :name",
    [name = "Mike"]
)

    Or a list like this?

    =Value.NativeQuery(
    Source,
    "SELECT name FROM employees
     WHERE name = ?",
    {"Mike"}
)

    In Power Query, {name="Mike"} is interpreted as a list with a single element that is true/false depending on if name equals "Mike" and throws an error because you have not defined name.

    Edit: Based on testing the above, OP recommends the following (see comment below):

    =Value.NativeQuery(
    Source,
    "SELECT * FROM employees
     WHERE name LIKE :var1 AND id = :var2",
    {"Mike%", 817}
)