Power Query to Oracle using OLE DB not working

[danny-uk]

I am trying to get Power Query (Excel) to Oracle 12c working. In excel using ODBC and MS Query I can connect to Oracle fine. You cannot use ODBC with Power Query you have to use Microsoft OLE DB for Oracle. I downloaded 64-bit ODAC 12,2c Release 1 for windows x64. I installed it and followed the setup instructions. Then in Power Query I go to get data and choose Oracle Provider for OLE DB, in Data Link Properties page I fill in details and test the connection, which is a success. Instead of showing the user database like it does in Excel, loads of Schema tables like OPS$Username, SYS… You cannot view the fields in tables. Any idea why I cannot see the user database using OLE DB but I can using ODBC?   

Regards

Danny

[Anonymous]

Hi @danny-uk ,

Did you mean you could see and use other tables except Users table?  Do you have access to it in Oracle database?

Please provide more details.

Best Regards,
Eyelyn Qin

[danny-uk]

Hi Eyelyn

I have a username and password, the ip address and database name. When connecting with the Data Link wizard using OLD DB I put Data Source as ipaddress/database name, then put the user and password in. The wizard then displays tables that are not the tables in ipaddress/database that was specified. I am assuming it is an Oracle system database as some of the tables are like OPS$Username.

Regards

Danny