Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Greetings,
I am working on a paginated report in Power BI Report Builder, and getting the attached error "A data source used by this report returned an error. An exception encountered while accessing the target data source ERROR [07001] [Oracle][ODBC][Ora]ORA-01008: not all variables bound", screenshot shown below.
I am using the ODBC(Preview) data source in Report Builder, with a simplified query structure like:
SELECT
*
FROM <table_1>
WHERE <field_1> BETWEEN :p_variable_1 AND :p_variable_2
AND (<field_2>= :p_variable_3 or <field_3>= :p_variable_3 or <field_4>= :p_variable_3 or <field_5>= :p_variable_3);
If I remove the parameters and hard code the values, the query works fine.
I am also able to run the query in SQL Developer without issues, and if I change the Data Source to "Oracle Database" the query also runs without issues.
I had initally tried Publshing the paginated report with the Data Source sourse as Oracle Database, which runs on my local machine, but when published to the server I get "A data source used by this report returned an error. An exception encountered while creating the target data source Unable to find the requested .Net Framework Data Provider. It may not be installed." (We have .NET Framework 4.5). It went on to specify Additional steps are required to view this report, which brought me to instructions on setting up and using ODBC, which I took to mean I can only use Paginated Reports with an ODBC Data Source. If that assumption is incorrect I am open to correction, whatever works.
TL;DR - Trying to publish a paginated report in Power BI Report Builder using ODBC but encountering "ERROR [07001] [Oracle][ODBC][Ora]ORA-01008: not all variables bound". When using Oracle DB as the Data Source yields "Unable to find the requested .Net Framework Data Provider. It may not be installed".
Any help would be appreciated with getting this running.
.
Solved! Go to Solution.
Hi
instead of using :Parametername, use ? (Question Mark). Usually ODBC look for ? wherever you need to define the parameters.
SELECT
*
FROM <table_1>
WHERE <field_1> BETWEEN ? AND ?
AND (<field_2>=? or <field_3>= ? or <field_4>=? or <field_5>= ?);
Map the ? with appropriate variables in Parameters section of the dataset.
Thanks
Hari
Hi
instead of using :Parametername, use ? (Question Mark). Usually ODBC look for ? wherever you need to define the parameters.
SELECT
*
FROM <table_1>
WHERE <field_1> BETWEEN ? AND ?
AND (<field_2>=? or <field_3>= ? or <field_4>=? or <field_5>= ?);
Map the ? with appropriate variables in Parameters section of the dataset.
Thanks
Hari
Hi Hari,
Bless you, that worked.