Query Oracle database
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. 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.
Subscribe via RSS
thanx…
something i was looking for
HEMANT KARNIK
7 Nov 08 at 1:43 am
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
tks a lot, this is what i was searching for
Papitha
15 Jan 09 at 9:51 pm
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
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
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
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
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
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
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
Can you get results from more than 1 table mapped to excel?
AB
28 Aug 09 at 2:38 pm
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
Thanks gnash!
gwyneth
15 Oct 09 at 3:17 pm
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
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
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
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
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