Connect Excel to Oracle Database

How to connect an Oracle Database to Microsoft Excel. https://www.elance.com/s/edtemb/

---

Closed Caption:

hi this is that when tempo and today I'm
going to show you how to create a simple
Microsoft Excel report to using data
from an oracle database so the first
thing we need to do is create the report
template such as this this is just a
simple sheet with a refresh button that
actually has a macro that will just
simply refresh the data from the
database our whenever it's clicked ok so
the first thing we want to do is create
a connection to the database and how we
do that is by going to the data tab
clicking on from other sources and
selecting the data connection wizard
once that comes up we're going to go
ahead and select other / advanced and in
the next screen we're going to select
Microsoft all day DB provider for oracle
hit okay and it's going to ask you for
your database login information
basically username your password and
your host name right where it says
server there
okay i'm going to go ahead and add to
that
so after you've connected to the
database you're going to get a pop-up
it's going to show all the objects in
your database that you can transfer to
this excel spreadsheet and unfortunately
this really it doesn't seem to be away
rather to search for one particular
object so you're just going to have to
scroll down to find that that particular
object that you're looking for in this
case i created a view to manipulate data
from several tables within the database
it's simply just a view showing average
salary based on web based on each
particular location
ok see you should be able to scroll down
to it fairly easily here and there it is
I'm gonna hit next and i'm going to go
ahead and hit finish and this is just a
pop-up telling you that i already have
this connection so i'm going to go ahead
and hit yes and I have the option to
create a table pivottable report of a
chart ins and just to create a
connection to the database for that
actually getting any data from so i'm
going to go ahead and create a table
okay as you can see it's just a simple
table that's showing the department name
the city and the average salary in that
particular city for that particular
Department ok alright so that's how you
get connected the next thing i want to
show you is how you can use this
somewhat dynamically without going
through all those other steps if there
are any changes to the database
so as you can see there we have our
department sending a drone number 33 now
suppose we need to make some cuts in the
business or you know some departments
weren't necessary anymore
we'll assume that some front-end
processes going to take care of that and
I have gone ahead and written the SQL
code to go ahead and delete those
departments from the department's table
which is part of the the view with the
data that will show you know Excel
spreadsheet
ok so we're just going to go ahead and
run that that SQL code as you can see
those three departments were deleted
going to go ahead and commit the
transaction
ok transaction committed and when I come
back and hit refresh those departments
should disappear from the sheet so here
we go
ok and as you can see now we have our
departments ending at number at bro
number 30 as opposed to the number 33
before you can simply save the
spreadsheet and you can share it with
end-users anywhere in your organization
and should be able to update it with
updo with updates from the database as
long as you can connect this particular
spreadsheet to the database typically
you need to be on the same machine to do
that
ok that concludes this video and if you
need any assistance with simple or
complex reports such as these
contact me at the link provided below