Query Paremeter Not Bound error - Dynamic Query from Oracle Database

[rileym94]

I have a SQL query against an Oracle Database where I'm trying to use a paremeter to create a dynamic query. The parameter is called in 3 separate locations.

Following this post, I created the parameter, edited the PowerQuery to include the declaration of the variable, and all seemed great... But I still got an error for "Not all variables bound"

I found another post from an Oracle user with a similar issue, and @v-qiuyu-msft said if it's in the where clause, replace the typical &Param with &Number.ToText(Param)&, but then I get an error of "Invalid host/bind variable name"

I have a sneaking suspicion that this is due to the exact way the parameter is being used in my query.

• I'm using a with() clause at the beginning of the query, which is where the references to the parameter are located.
• The parameter references are being called in functions in 2 of the 3 locations, and the functions are part of an inner join argument.
• To add more confusion, the parameter is a number stored as text.
  • This is how it's stored in the database, and I'd prefer to leave them as text references if at all possible

I feel like I've covered my bases, but if you have any questions, feel free to let me know. I would GREATLY appreciate any advice.

[v-chuncz-msft]

@rileym94 

You may try Text.From to convert type for string concatenation where needed.

[rileym94]

I thought I did it correctly, but I'm getting a new error now.

ORA-01747: invalid user.table.column, table.column, or column specification

Let me try providing a terribly watered down example...

Note: RegTerm is the name of the Parameter in Power BI

let
    RegTerm = RegTerm,
    Source = Oracle.Database("[xxxxx]", [HierarchicalNavigation=true, Query=
	"with aid as(
		#(lf)select 
		#(lf)t1.id
		#(lf), t1.year_code
		#(lf)from t1 
		#(lf)left join terms
		#(lf)    on term_code = &RegTerm 
		#(lf)inner join t2
		#(lf)    on t2.id = t1.id
		#(lf)    and t2.term_effective = user.func1(ID, Text.From(&RegTerm))
		#(lf)    and user.func2(ID,Text.From(&RegTerm)) = 'Y'
		[...]
		#(lf))
		#(lf)select *
		#(lf)from aid a"]),
    #"Changed Type" = Table.TransformColumnTypes(Source,[xxxx])
in
    #"Changed Type"