MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Query Oracle database

with 120 comments

I caught a post on the OTN forum asking how to do this, and it happened to be something I’m working on for a new course that I’ll be teaching on data analytics. Ultimately, Microsoft Excel is the de facto tool of many accounts and financial analysts, protests notwithstanding.

This shows you how to query an Oracle 11g database from Excel 2007 (only available when you do a FULL install of Excel 2007). Actually, it should work on any current version of the Oracle database. The key to making this work is having the Oracle 10g Client software or an Oracle 11g database on the same machine. The Oracle client software allows you to resolve an Oracle Network Alias (found in the %ORACLE_HOME%\network\admin\tnsnames.ora file).

Here are the instructions with screen shots:

1. Click on the Data ribbon, and then click on the From Other Sources icon in the Get External Data section, as shown:

2. When you click on the From Other Sources icon, you’ll get the following drop down list. Click on the From Data Connection Wizard item.

3.The Data Connection Wizard provides a number of Microsoft data sources, but you click the Other/Advanced option to connect to an Oracle database.

4. The Other/Advanced selection launches the Data Link Properties dialog with the Provider tab open and active. Click the Oracle Provider for OLE DB choice. If you don’t have the Oracle Provider for OLE DB option, download the Oracle Data Access Components for Windows from Oracle site. The installation should also load it as a new data source, if not you can follow the same instructions provided for MySQL’s ODBC here. After clicking the Oracle Provider for OLE DB choice, you click the Next button.

5. The Next button brings you to the Connection tab. You should fill it out as noted below, and if you wish check the Allow saving password checkbox.

6. Before preceding, you should verify that the connection works. Click the Test Connection button to do that, and if successful you’ll see the following. Click the OK button in the Microsoft Data Link dialog to complete the test and the OK button in the Data Link Properties dialog.

7. After setting up the data connection in the Data Link Properties dialog, you’ll be taken to the Data Connection Wizard dialog. Here you can pick any table that you have access to within the PL/SQL schema (remember a schema in Oracle is a database in Microsoft SQL Server or MySQL). Click the Next button to continue.

8. After clicking the Next button, you’ll see the second Data Connection Wizard dialog. Click the Finish button to begin importing the data.

9. The Import Data dialog asks you how and where you want to import it. The following chooses to import it as an ordinary table. Click the OK button to import the data from a table. If you want to write a query against one or more tables, click here for the additional instructions.

10. Imports into Excel 2007 as a table are automatically created as a table. The following screen shot formats the display columns.

You have a number of very advantageous features in Excel 2007. They allow a number of options that resemble working within a database but honestly they’re much more difficult than working in SQL. I’d advise you create preformatted data by using views. Then, query the views through these steps.

Written by maclochlainn

November 7th, 2008 at 12:25 am

Posted in Microsoft Excel,Oracle,Oracle 11g,Oracle 12c,Oracle XE,sql

120 Responses to 'Query Oracle database'

Subscribe to comments with RSS or TrackBack to 'Query Oracle database'.

  1. I’ve not encountered the error before, but it basically means it’s not packaged to send to the database correctly. Why don’t you save the query, close the Excel document, and then retry execution.

    maclochlainn

    19 Jun 12 at 11:48 pm

  2. I did try that as well.
    – SO I saved the excel workbook where I was able to run the query
    – reopened the same excel book on another user’s machine and got the “Command nor prepared” error

    Ananya

    25 Jun 12 at 3:54 pm

  3. My excel 2007 never bring the screen of step 7, I was able to successfully test the connection as per step 6.

    Any feedback will be helpful?

    Premyogi

    9 Jul 12 at 3:10 pm

  4. Can’t seem to get past step #5 above without “ORA-12154”. I am using “Data Source: orlc”, user name and password = “plsql. I looked at my tnsnames.ora file and it is correct. Any other tips?

    Arnold

    18 Jul 12 at 11:33 am

  5. Are you using a hostname in your tnsnames.ora file that’s recognized by the DNS server or that is qualified in your hosts file?

    Do you have a PLSQL user/schema in the database instance?

    maclochlainn

    18 Jul 12 at 11:57 am

  6. Yes for the host name. Not sure how to check the PLSQL user/schema in the database instance, would this be in the sqlnet.ora file? If so, it is configured as follows:

    NAMES.DEFAULT_DOMAIN = xx.com

    Arnold

    19 Jul 12 at 6:22 am

  7. Open a command shell and type the following at the DOS prompt:

    sqlplus plsql/plsql@orcl

    maclochlainn

    19 Jul 12 at 10:51 am

  8. “sqlplus” is not reconized as a command.I should mentioned that I am launching the command prompt as a user not admin (don’t have admin access)

    Arnold

    19 Jul 12 at 12:52 pm

  9. OK, well the others were from my iPhone. It appears you don’t have the Oracle client software installed. You need to ask your DBA which schema you’re allowed to connect to for queries. A schema is a private work area and most often thought of as a database. The example uses a schema named PLSQL, but if your Oracle instance doesn’t have a PLSQL user/schema the failure is normal.

    Discuss this with the DBA and find the USER name and PASSWORD for the schema where your data exists.

    maclochlainn

    19 Jul 12 at 2:56 pm

  10. hello maclochlainn

    which part of the TNSNAMES file will be used in the Data Source box? (step5)

    Ewan

    3 Oct 12 at 6:13 am

  11. It’s the TNS alias, which is orcl for the main sample database and xe.

    maclochlainn

    3 Oct 12 at 9:31 am

  12. Dear Maclochlainn,
    In Step 5, I also encounter error,ORA-12154 TNS could not resolve the connect identifier specified.
    In tnsnames.ora, the instance connection information should be correct, because if I open window command to test,using below to test
    C:\Users\ptian>sqlplus apps/apps@mc3yd213
    It can connect instance successfully.

    But in excel, I always have ORA-12154 error, do you have any idea?

    Thanks in advance
    Pan

    Pan

    28 Oct 12 at 8:38 am

  13. […] https://blog.mclaughlinsoftware.commicrosoft-excel/how-to-query-oracle-from-excel-2007/ […]

    Windows 7下通过Excel2007连接Oracle数据库并对表查询 | EvilCode 邪恶代码

    2 Nov 12 at 12:09 pm

  14. Have the same problem as Pan.
    Appreciate if you could find any reason for that.

    Thanks in advance!

    Gleb

    9 Jan 13 at 3:36 pm

  15. Pan & Gleb,

    The ORA-12154 error indicates that the network layer works and typically points to a problem finding the server or that the server is offline.

    maclochlainn

    16 Jan 13 at 12:00 am

  16. Hello,
    I am trying to connect oracle database to excel. My requirement is to import data(query data) from Oracle database to MS Excel 2010. This oracle database and all related tables are on server. the tnsnames.ora file is all maintained by third party and nothing is local on my machine. I am having a hard time connecting it through excel. Please let me know if any drivers are required to do this. And also would I be needing Oracle client software to do this?

    Priya

    8 Feb 13 at 2:21 pm

  17. A 32-bit application (Excel), a 64-bit Oracle 11.2.0.1 database. Which driver, 32-bit or 64-bit for ODBC?

    Mike Goins

    25 Feb 13 at 5:20 pm

  18. Either should work because the communication occurs over Oracle’s TNS.

    maclochlainn

    25 Feb 13 at 5:30 pm

  19. Created a connection using “Microsoft OLE DB provider for Oracle”. Connection test successful; however, not from Excel. Excel is 32-bit, the database and Oracle Client are 64-bit. Installing the Oracle client did NOT create an “Oracle provider for OLE DB” under data sources (it was installed from E:, not C:). OS is 64-bit Windows Server 2008. Error message something like “Oracle Client not installed” or “Oracle Client components could not be found”.

    Mike Goins

    25 Feb 13 at 5:43 pm

  20. “For those who don’t have an Oracle instance running locally, you can download the Oracle Data Access Components for Windows from the Oracle web site.” We DO have an Oracle instance running locally (64-bit), Excel is a 32-bit, on Windows Server 2008. No “Oracle provider for OLE DB” in the data sources. “Test connection” works from Data Sources, but not from Excel. Aggravating and interesting.

    Mike Goins

    25 Feb 13 at 5:56 pm

  21. Great catch, thanks!

    maclochlainn

    25 Feb 13 at 7:29 pm

  22. Thanks for the responses, Mac! I intend to fix this today, one way or another. Will let you know if I find anything useful. For now, getting the error in Excel “Unable to locate Oracle Client components…”.

    Mike Goins

    26 Feb 13 at 5:45 am

  23. maclochlainn,

    earlier in this thread, you responded to someone asking a question about the “Test Connection.” I have the same error as that person with the error ORA-12154. You told them to type in “xe” instead of “orcl”. I tried that but I still had the same error as before. What other methods would work?

    Thanks

    Andy

    13 Mar 13 at 9:49 am

  24. thanks a lot

    swapnil

    31 Aug 13 at 4:34 am

  25. Greetings! Very helpful advice within this post! It’s the little changes that will make the biggest changes.
    Thanks a lot for sharing!

    google.com

    10 Jan 14 at 9:24 pm

  26. After I saved as excel, I unlinked the table so I could use the information as is, with no impact upon the existing data. I wasn’t sure if there would be any issue with DB staying connected. Does this make sense? I was going to send the data to another for information only.

    Thank for your help!

    Allan Menezes

    18 Feb 14 at 1:31 pm

  27. Allan, That’s fine. It’s only a query, which means it won’t change the data. 🙂 As to sharing, that depends on your company/corporate policy, eh? 😉

    maclochlainn

    18 Feb 14 at 5:47 pm

  28. Hi, I’ve just setup OLE, on excel 2007, connecting to an oracle 10g database running on a separate server.

    Connection is successful, upto step 7.
    At that point, i see tables, views and synonyms for all users, not just the one I’ve logged in with.

    For info, ive setup the source in ODBC, and then am connecting using excel using the above, but in datasource put the name from ODBC.

    For both ODBC and OLEDB connections i am using a restricted userid.

    Any ideas how i can only bring back the tables from that user/schema?

    unclebob

    20 Feb 14 at 4:49 am

  29. Uncle Bob, You should be seeing the schema that you specified during your connection. At least that’s what I see. Which schema owns the tables and views that you see?

    maclochlainn

    20 Feb 14 at 8:05 pm

  30. Created connection using “PGNP Native OLEDB Provider for PostgresSQL” but i can’t able to view the result in excel. It’s showing error as “Cannot connect open DB connection”. Can anyone help on this?

    Mahi

    21 Feb 14 at 6:24 am

  31. I am using Excel 2007 and i can able to complete the steps up to 9 but i can’t get the select query result in excel as shown in step 10. i am getting error as “Cannot open DB Connection”. i am using “PGNP Natice OLEDB Provider for PostgresSQL” trail version to connect PostgreSQl in excel 2007. Can you please provide the answer to resolve this issue?

    Mahi

    22 Feb 14 at 3:16 am

  32. Mahi, I’ll try to test it for you but it won’t be until later in the week. Could you confirm where you downloaded the OLEDB?

    maclochlainn

    22 Feb 14 at 1:32 pm

  33. Maclochlainn, Ok. I have downloaded the OLEDB from here.

    Mahi

    24 Feb 14 at 9:36 am

  34. Mahi, You should download it from EnterpriseDB because they’re the authoratative source. The easy way is to just follow the instructions on my blog for Installing PostgreSQL with pgJDBC v9.3, psqlODBC (32 bit), and psqlODBC (64 bit).

    maclochlainn

    2 Mar 14 at 9:41 pm

  35. Hi all of you. What do you thing of that: http://vbatools.pl/pobieranie-danych-z-oracle/
    This is a complete solution for excel, with 50 last selects in memory, variables, with SQL viewer, and export as pivot table, data table or non format dates.
    Regards.

    OShon

    9 Mar 14 at 3:43 pm

  36. Hi,

    I did all in this forum, but still getting this error “Accessor is not a parameter accessor”, no error code or else…

    Im using Excel 2013 and Oracle 11G

    Thanks.

    Wiston

    6 May 14 at 2:34 pm

  37. Wiston, are you passing parameter values?

    maclochlainn

    7 May 14 at 2:33 am

  38. Hi Maclochlainn, Thanks too much for your great efforts.
    I have a problem in writing an oracle query. The query is a select statement for ex.( select … from … where condition1 and condition2)
    condition1 debends on excel sheet parameter so I used this mark (?) in it . the problem is that condition2 does not be executed. The output debends on the first condition only and neglect the second condition. Can you help me please.
    Thanks alot

    Hassan

    22 Jul 14 at 8:54 pm

  39. Hi
    I have data in Excel i want that data pull in Oracle Database & same for Delete same type of data from Oracle Database please tell me how to do this Excel Macro

    Abhijeet

    26 Mar 15 at 7:49 am

  40. Abhijeet,

    You want delete data from the server that meets the condition of a range of values? Does the data contain the surrogate or natural key? If so, you can use that range to write a dynamic array of values and use the WHERE clause like one of these examples.

    Using a single column as the surrogate key:

    WHERE surrogate_key_column IN
       ('excel_column_row1'
       ,'excel_column_row2'
       ,'excel_column_row3'
       , ...)

    Using a collection of columns as the natural key:

    WHERE (natural_key_column1, natural_key_column2) IN
       (('excel_column_A1','excel_column_A2')
       ,('excel_column_B1','excel_column_B2')
       , ... )

    Hope that helps. Unfortunately, I’m not in a position to write you an example because I’m too committed to other projects.

    maclochlainn

    27 Mar 15 at 10:16 pm

  41. Hi, I’m creating some reports in Excel from Oracle’s data. I’ve created 2 sheets and they go to oracle and report soma data from normal views there…. but there’s a thrid view and when i try to use this third, i get the error: cannot execute the query or cannot open the database table. after close this windows, i get the error: there’re problems getting the data. However, the sheet send a little message at the bottom: Connecting to data source…

    Could you help me? there’s some kind of log in order to review what’s the problem?

    Thanks a lot!

    CariS.

    11 May 15 at 5:09 pm

  42. CariS,

    Do you know if the third view uses any DB_LINKS? You can query the third view with the following:

    SET LONG 300000
SELECT text
FROM   user_views
WHERE  view_name = 'UPPERCASE_VIEW_NAME';

    maclochlainn

    11 May 15 at 6:11 pm

  43. Gracias MacLochlainn, no, every view uses tables in the same db, in fact, if I use a materialized view instead a regular view, it works 🙁 but I need the data on line, no delayed …. thanks for your help…

    CariS.

    12 May 15 at 9:06 am

  44. CariS, Is it a large data source? If it works with materialized views, then it may be a timeout on the request. Have you tried writing a DDL trigger to monitor the connection and timeout?

    maclochlainn

    12 May 15 at 10:46 am

  45. Hi maclochlainn, no, I’m not, but let me work around this topic, and I’ll let you know, the original universe of data (rows) is the same, however, there is a join for obtain the exact date for begining and ending of the week, in order to clasify and count rows for week and indicating the dates between that data….we’ll see…. thanks!

    CariS.

    12 May 15 at 3:49 pm

  46. CariS, I’ve a hunch from your description that you can optimize the view. If you’ve done that incorrectly, it could take quite a while to resolve. What’s the explain plan look like for the query?

    maclochlainn

    14 May 15 at 11:35 pm

  47. Hi, i did till 5th step, when i test the connection i am getting as error as “Test connection failed because of an error in intializing provider.

     ORA-12541: TSN:no listener

    but I used CMD command and started TNS listener and its still running.

    I am using Windows 7, 64 bit but excel is 34 Bit. please help me out on this…

    vinod

    26 May 15 at 2:21 am

  48. Vinod,

    Did you select “Oracle Provider for OLE DB” in step #4? If so, did you follow the steps on configuring the Oracle ODBC driver?

    My guess is that the Oracle-ODBC driver isn’t working. Let me know what you find.

    maclochlainn

    26 May 15 at 8:20 pm

  49. Hi,
    This was very helpful for me. Thanks a lot for this guide!

    Aaron

    21 Oct 15 at 4:21 am

  50. Will you please guide me how can i display data from multiple tables of Oracle database 11g

    Hammad

    3 Nov 15 at 2:02 am

Leave a Reply