I suspect Joel would have kittens if he knew I would use the appreciation day to talk about Excel instead of APEX but courtesy of an AskTom question I stumbled across this cool little tool for accessing data from your autonomous database via Excel.
You might be thinking: “Hold on, we’ve been accessing Oracle databases from Excel for years” but typically this means fluffing around with Microsoft Query plus an appropriate ODBC driver plus an appropriate Oracle client, and if you got any of that wrong then you were left with a mystery to work out where the broken piece of the plumbing was.
By comparison, using this tool is a trivial click-and-done style operation and requires no special software over and above Excel and the tool itself (which is just an Excel add-in). There is an official blog post about the tool here, but I thought I’d share my concise version here to show you how simple it is to set up.
- Head into Database Actions on your Autonomous database and download the Add-in.
- Unzip the downloaded file and run the install as Administrator
And …. that’s it! You’re done. Now you’re ready to query a cloud database straight from Excel.
- Fire up Excel, go to Addins and choose your Autonomous Database from the Shared Folder tab
- Now you have have a ribbon entry for your database
- Sign in as whatever account you want to use
- Enter your SQL query and hit Execute
and boom! Cloud data right there in your Excel workbook!
Of course, if Joel had his way you would then immediately throw this away and do it properly in APEX and I also think that’s pretty good advice for most situations. But we all know that people do love their Excel.
This tool has removed another obstacle to migrating databases to Cloud, because as long as users have data, they’ll want to use Excel to play with it. This tool makes it very easy to access that data, and there are a lot more options to it than what I’ve presented above.