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. thanx…
    something i was looking for

    HEMANT KARNIK

    7 Nov 08 at 1:43 am

  2. One of the noticable enhancement in Excel 2007 is that you are no longer limited to 65536 rows and 256 columns !

    Laurent Schneider

    7 Nov 08 at 4:15 am

  3. tks a lot, this is what i was searching for

    Papitha

    15 Jan 09 at 9:51 pm

  4. Hi,

    do you know how to pass cell values to filter the returned rows from the query? Something like ‘select * from table where col1 = cellCoordinates’

    nuno

    19 Jan 09 at 10:51 am

  5. This is what i have been looking for however when i get to the data Link Properties page I do not have the option of selecting ‘Oracle provider for OLE DB’. How does one have this option show / install?

    John B

    21 Jan 09 at 5:14 pm

  6. Are you on MS Office 2007 or 2008? Did you choose typical or complete when you installed the MS Office Suite?

    maclochlainn

    21 Jan 09 at 11:17 pm

  7. Can I use pl script instead of simple “select * from Table” to get from DB calculated data in to Excel 2003/2007

    my script looks like

    declare
    …
    begin
    create temp table
    …
    end;
    /

    select … from tempTable;
    drop tempTable;

    Ivan S

    29 Apr 09 at 4:24 am

  8. Check this post out. You could write a function like this and then return the results into a view. That’ll make it cleanly to Excel.

    Hope this helps.

    maclochlainn

    30 Apr 09 at 11:50 pm

  9. Thanks very much.

    Can i use a query to do this? I need a query (PL/SQL) to import the data. If i can run the query using a batch file or something and it automatically imports the data into the Excel.

    And also how can I import into Oracle from Excel?

    Thanks

    Robert

    28 Aug 09 at 2:25 am

  10. I’m working on some examples like that for an advanced Excel class. I’ll post them as soon as they’re ready.

    maclochlainn

    28 Aug 09 at 7:59 am

  11. Can you get results from more than 1 table mapped to excel?

    AB

    28 Aug 09 at 2:38 pm

  12. And if you need the software in order to get the “Oracle Provider for OLE DB” provider use one of the folowing links:

    Installed as part of the developer tools:
    http://www.oracle.com/technology/software/tech/dotnet/utilsoft.html

    Listing of OLE DB Drivers (11g not included in list as of this posting):
    http://www.oracle.com/technology/software/tech/windows/ole_db/htdocs/utilsoft.htm

    gnash

    31 Aug 09 at 9:39 am

  13. Thanks gnash!

    gwyneth

    15 Oct 09 at 3:17 pm

  14. Whenever I try to connect to a large table, even as a PivotTable report, I get an error : “Could not connect to the database”. I tried connecting to a table with less records (10000) and it failed again. Next I tried connecting to a table with 1000 records and it succeeded. Next I tried connecting to a table with 10000 records and just 4 columns, it succeeded.
    In Excel 2007 is there a limit to the size (rows x columns) of the Oracle table that it connect to?
    Couldn’t find much info through google.

    Anish

    18 Nov 09 at 1:57 pm

  15. Is it possible to use an excel sheet which contains data, be used to query and pull back data/records in an Oracle data base.
    I have developed a Import data MS Query/Excel SQL which happily pulls back data as defined in the query or sql, but I really want my excel listing to be used as part of the query, effectively joining the excel sheet with oracle tables. Is this possible? Hope this makes sense!

    Thanks

    Perry

    27 Nov 09 at 10:58 am

  16. To all inquiring about building a query …

    #9 listed above, if you click ‘Properties’ button, it will take you to the Connection Properties screen. There will be 2 tabs (Usage and Definition) Click on Definition. In the middle you have “Command type” … drop down and select “SQL” … then enter you query in the ‘Command Text’ box located beneath.

    NOTE: It’s important that you do not connect to 1 specific table. Just ‘unselect’ the Connect to Specific Table checkbox and you’ll have access to all the tables. This helps when you’re creating a query using multiple tables.

    Hope this helps,

    V

    Vernon

    30 Nov 09 at 7:46 pm

  17. You want to use your Excel cell contents as parameters to a query against Oracle? That can be done in Visual Basic for Applications.

    maclochlainn

    30 Nov 09 at 11:54 pm

  18. Vernon, I really appreciate you’re comment. It got me to put it at the top of my heap. Here’s a set of screen shots to illustrate it.

    maclochlainn

    30 Nov 09 at 11:55 pm

  19. Sorry, I have used MS Query previously, however I am trying to set it up from scratch. When I test connection, I am getting an ORA-12154. TNS could not resolve the connect identifier specified.

    Can you please point me in the right direction?

    Arzu

    19 Jan 11 at 10:09 pm

  20. The ORA-12154 error may mean that the Data Source (Step #5) you’re using in Excel doesn’t match the one in your tnsnames.ora file. The screen shots were using a sample Oracle Database 11g database, which uses orcl. If you’re using Oracle XE, it would be xe.

    maclochlainn

    20 Jan 11 at 12:11 am

  21. […] I posted how you could query Oracle from Excel 2007 […]

    Query MySQL in Excel 2007

    15 Feb 11 at 1:49 am

  22. Hello Michael, I feel that I’m lucky enough to get to your website after searching on the internet on how to connect to oracle through excel for hours. This is a great step by step procedure. Thank you very much for all the effort to make the process much simpler.

    I have a question. Once we connect to a schema in the oracle database and save one of the tables in an Excel workbook, every time I try to refresh the data, I am asked to provide the password for the database connection. Is there a way that I can save the password and will not have to type it every time I refresh? Please reply. Thanks.

    Developer

    1 Mar 11 at 1:54 pm

  23. In step #5 above, enable the Allow Saving Password check box. It should do the trick for you.

    maclochlainn

    1 Mar 11 at 3:05 pm

  24. Appreciate your prompt response. My task is to connect to Oracle through Excel and save the table data in the workbook and send it to some one who doesn’t have Oracle client installed on their PC’s to access the workbook and refresh it when ever needed. What are the changes required in order to let them access the document (who do not have oracle client on their PC’s).

    Developer

    2 Mar 11 at 9:35 am

  25. You can post the spreadsheet and distribute it but the query part requires either the Oracle Client or Oracle Database on their client. Have you examined configuring the XDB server and writing the file to a download directory that can serve it back to your customer? A beginning example is in this blog post, which I didn’t test.

    maclochlainn

    2 Mar 11 at 8:39 pm

  26. Is there a benefit to using the “Oracle Provider for OLE DB” over an ODBC connection? I’ve run into the issue where trying to connect through the Oracle ODBC driver now takes a couple of hours to return the list of tables from my Oracle database (running 11g). I’ve determined that that’s due to the query Excel runs to retrieve that table list, and am looking for a quicker way around that.

    Once I have the table list, everything goes quickly and smoothly.

    Many thanks.

    David

    8 Mar 11 at 6:04 pm

  27. David,

    I’ve never tested the comparative speeds of the ODBC versus the OLE but I doubt there’s that much difference between the two as bridges between Excel and Oracle. However, they do use different query mechanics, I would suggest that you try queries as opposed to tables, which you can find in this other post. If you find any performance characteristics that you would like to share, please post them.

    Thanks, Michael

    maclochlainn

    8 Mar 11 at 9:55 pm

  28. In order to populate the list of tables as shown in figure #7 in this thread, I found out that Excel is running a query against Oracle in the background. When I was trying to use the ODBC connection, that query took two or more hours to execute. I figured out how to find the actual query text being run, and what I was asking you was whether that query was different if I used the Oracle provider for OLE DB.

    I tried using the OLE provider instead, and there was a failure after 5 hours trying to populate the list of tables (again, figure #7). I checked the query being run behind the scenes, like I did for ODBC), and while I didn’t compare the two queries character-for-character, they were awfully similar.

    Your other post didn’t help, because the issue I’m having is prior to figure #9.

    David

    13 Mar 11 at 4:34 pm

  29. David,

    Right, I should have caught that. I think the alternative would be a direct query using ADO in VBA. I’m actually working on examples for a class that another teacher will teach. When I get them done, I’ll post a note on my blog.

    Thanks,
    Michael

    maclochlainn

    13 Mar 11 at 9:49 pm

  30. I have setup the Connection precisely the same way other than on the definition in the command type i have SQL and in Command Text I have the actual sql code. My question is how can i setup a bind paramtere in the command text so that i can have the user of the spreadsheet put in the start and end dates and have the command text use those values instead of hard coding them into the text??

    Is it even possible?

    Tom Cusick

    16 Mar 11 at 11:49 am

  31. Unfortunately, you need to write this as a SUB and pass parameters via cell references or VBA form values. Here’s the best reference that I know, and it’s at MSDN. But here’s an untested copy of a FUNCTION that you could call in your SUBroutine:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

    Function ORAQUERY(strHost As String, strDatabase As String, strSQL As String, strUser As String, strPassword As String)
  Dim strConOracle, oConOracle, oRsOracle
  Dim StrResult As String
 
  StrResult = ""
 
  strConOracle = "Driver={Microsoft ODBC for Oracle}; " & _
         "CONNECTSTRING=(DESCRIPTION=" & _
         "(ADDRESS=(PROTOCOL=TCP)" & _
         "(HOST=" & strHost & ")(PORT=1521))" & _
         "(CONNECT_DATA=(SERVICE_NAME=" & strDatabase & "))); uid=" & strUser & " ;pwd=" & strPassword & ";"
  Set oConOracle = CreateObject("ADODB.Connection")
  Set oRsOracle = CreateObject("ADODB.Recordset")
  oConOracle.Open strConOracle
  Set oRsOracle = oConOracle.Execute(strSQL)
  Do While Not oRsOracle.EOF
      If StrResult <> "" Then
        StrResult = StrResult & Chr(10) & oRsOracle.Fields(0).Value
      Else
        StrResult = oRsOracle.Fields(0).Value
      End If
    oRsOracle.MoveNext
  Loop
  oConOracle.Close
  Set oRsOracle = Nothing
  Set oConOracle = Nothing
  ORAQUERY = StrResult
End Function

    If you install Oracle Client software on the same machine as Excel, you can replace the TNS connection strings with the network alias from the tnsnames.ora file.

    maclochlainn

    16 Mar 11 at 9:39 pm

  32. Tom, To prompt for a parameter, use a ? (tested with Excel sql). For example, instead of WHERE (`Sheet1$`.PROJECT_NAME=’foo’) use WHERE (`Sheet1$`.PROJECT_NAME=?) and you will get a popup for Parameter1. You will also get a selector button allowing you to choose a cell reference, and a couple of useful checkboxes. This may help Perry as well. To use an actual bind parameter against an Oracle function, you will need to investigate passing the OCI syntax via ODBC. I have read about it, but have never tried it. I always use ADODB. 🙂

    Alan B

    24 May 11 at 12:35 pm

  33. How insert data from spreadsheet Exel 2007 to Tables in Oracle 10G Express Edition?

    Alejandro

    11 Jul 11 at 10:13 am

  34. Is it possible to call an Oracle procedure that has out parameters using ADODB?

    I know the code published above lets me query tables etc, but how to use it to call procedures?

    Thanks in advance!

    Dangaya

    22 Aug 11 at 12:29 pm

  35. Yes, try this article.

    maclochlainn

    25 Aug 11 at 11:41 pm

  36. This is very helpful but I need to add that the oracle does not have to reside on the client machine. All you needed to add for the server end connection is the IP address/service name, e.g 173.15.0.34/addey (where addey is the oracle service name)

    It worked for me..

    Addey

    7 Oct 11 at 6:14 am

  37. Thanks for the most informative and easy to understand article about connecting Excel to Oracle.

    I wonder is it possible to export xls files to Oracle through ODBC? I mean instead of importing xls files through SQL loader of Oracle, how about exporting the Excel table directly to Oracle from Excel through ODBC. This is easy in Ms Access, but I couldn’t figure it out in Excel.

    Mehmet

    24 Oct 11 at 2:50 am

  38. Yes, it’s possible.

    maclochlainn

    24 Oct 11 at 12:32 pm

  39. Thank you for the informative article, i was desperately looking for some information in this regard. However, I do not have the option of selecting ‘Oracle provider for OLE DB’. i am using MS office 2007, but i don’t know if it was installed as ‘typical’ or ‘complete’ i would really appreciate if you could guide me about this problem.

    dixit

    9 Nov 11 at 11:21 am

  40. While you want to perform this for Oracle, the steps are pretty much the same for MySQL and they’re already document in this blog post.

    maclochlainn

    9 Nov 11 at 9:46 pm

  41. Simple and Clear
    Thank you.

    Nahed Nawar

    12 Nov 11 at 10:44 am

  42. Thanks for the prompt reply, I appreciate it. However, I want to know if the option ‘ORACLE provider for OLE DB’ can appear/be installed, as my database is in oracle only and I want to connect it with excel, can’t use MySQL. Is there some solution for this.

    dixit

    12 Nov 11 at 8:07 pm

  43. Yes, you need to download and install the ODAC product on your Windows 7 machine. It was too much for a comment, so it’s here.

    maclochlainn

    13 Nov 11 at 1:01 am

  44. […] return to set up an Excel query […]

    ODBC for Excel

    13 Nov 11 at 10:53 am

  45. At #6 I can´t make the connection to succeed. I get a message saying that the listener does know the service requested (my translation).
    I use ‘orcl’ as the data source, anda that’s the name in tnsnames.ora.
    When I use the server name, the error message is: “the SERVICE_NAME was not provided to the listener at the CONNECT_DATA” (my translation)
    Can you help me !

    Many thanks

    Paulo

    16 Nov 11 at 6:30 pm

  46. Very nice one…!

    Is there any way to do the other way around? That is, entering the datas in the excel sheet to the oracle database.

    It would be good if you can come out with it quickly.

    Thanks.

    Gopal

    17 Nov 11 at 3:12 am

  47. How can I get data from more tahn 1 queries..??
    For Ex..

    select * from table1;
    select * from table2;

    Thanks in Advance 🙂

    Gaurav

    16 Dec 11 at 2:43 am

  48. You can retrieve data from two tables by joining records through a primary and secondary key columns. The following post provides instructions for performing a query.

    Excel maps a single table to a single worksheet unless a query joins two tables.

    maclochlainn

    16 Dec 11 at 11:12 pm

  49. Hi, I connect to a database through Citrix.
    Hence I am unable to perform the above steps.
    I always get the data connection error.
    Any alternative for that ?

    Rahul

    15 Mar 12 at 2:12 am

  50. I was successfully able to run a complex query on excel installed on my machine. But when I try to do the same setup and run the same query on another users machine, i get a “command not prepared” error. Any idea why I am getting this error and how I can resolve it?

    Ananya

    19 Jun 12 at 3:43 pm

Leave a Reply