Excel value as Input to Oracle database Connection data

[Anonymous]

Hello All,

Am new to Power query and this forum.

I have below requirement,

1. User will enter the OWNER, TABLE_NAME in one sheet of the current excel (any number of lines)

2. We need to fetch the matching records (with OWNER,TABLE_NAME joining) using query (SELECT OWNER,TABLE_NAME,COLUMN_NAME FROM DBA_TABLES) from Oracle database

I tried this using MERGE Queries and inner join with both the cloumns or combining CONCAT(OWNER,".",TABLE_NAME), it seems works. But it is taking more time, since the query from oracle database fetches more records.

Is there any option to construct the query using data from user inputs and send to Oracle database, so that it fill filter the data at the start and return only less data to Excel. My Oracle query will have millions of rows but if we apply the filter then the result would be tousands.

Something like below

= Oracle.Database("rmylocaldb", [Query="SELECT * FROM DBA_TAB_COLS WHERE OWNER_TNAME IN ("&Table.SelectColumns(Source,{"OWNER_TABLE"})])

Or anyother good option is there?

Thanks in Advance.

[v-juanli-msft]

Hi @Anonymous 

Two method :

1. create parameters for "owner" and "table name", then use parameters in the oracle connection string.

for example, please refer to:

https://community.powerbi.com/t5/Power-Query/Are-nested-parameters-supported/td-p/1159816

2.

create a excel file and enter values inside it, connect to the excel file with power bi, 

then connect to the oracle database and change the query to make it as a function.

invoke the query(connection to oracle) into the table which is from excel.

https://www.myonlinetraininghub.com/scrape-data-multiple-web-pages-power-query

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

which connector do you use?  "Native"  Oracle, ODBC, or something else?  Does that connector  support custom queries and/or query folding?

[Anonymous]

Am Connection Oracle Database (12c) from Excel as menu Data -> New query -> From Database -> From Oracle Database -> Here I Connect to my Database and Browse the view from the schema.

My Qeury Source is shown in Excel formula as

= Oracle.Database("my_dn_tns", [HierarchicalNavigation=true, Query="SELECT * FROM VIEW_TAB_COLS"])

[lbendlin]

Change your query to 

= Oracle.Database("my_dn_tns", [HierarchicalNavigation=true, Query="SELECT * FROM VIEW_TAB_COLS WHERE OWNER='" & <reference to owner cell> &"'"])

[Anonymous]

Thanks for the reply.

I change it as below

= Oracle.Database("my_dn_tns", [HierarchicalNavigation=true, Query="SELECT * FROM VIEW_TAB_COLS WHERE OWNER='" & Sheet3!A5 &"'"])

And getting error message as

Expression.Error: The section 'Sheet2' wasn't recognized. Make sure it's spelled correctly.

Am not sure How to refer the other sheets particular cell value here.

Also my requirement is to put a list of vaules

Something Like

= Oracle.Database("my_dn_tns", [HierarchicalNavigation=true, Query="SELECT * FROM VIEW_TAB_COLS WHERE OWNER IN ('" & Sheet3!A1:A15 &"')"])