Oracle, Web, Script, SQLserver, Tips & Trick

October 22, 2010

Connection Excel to Oracle Using ODBC

Filed under: odbc,oracle — sikathabis @ 4:03 am
Tags:

I experienced lots of pain getting HS working, so I’ve put together a worked example,
in the hope it will spare you some of that pain. I’m using Excel – if it works for Excel
then it should just be a matter of chaging the DSN (and database link username/password)
to connect to another database.

OK, I’ll assume you already have a spread sheet handy. We’ll start by setting up an ODBC Data Source Name.

Open the ODBC Data Source Administrator [ Start -> Programs -> Administrative Tools -> Data Sources (ODBC) ]
Click on the tab page labeled “System DSN”Click on the button labeled “Add”Select the Excel driver,
and press the button marked “Finish”In the box labeled ‘Data Source Name’, enter the word ‘SPONGE’.
Click on the button labeled ‘Select Workbook’Select the excel workbook you saved earlier.
Click OK, and exit the ODBC Administrator

This has created a Data Source Name (called ‘SPONGE’) that points to your excel workbook.

Now we’ll move onto the Oracle side of things.

First, we need to tell Hetrogeneous Services about SPONGE.The HS initialisation files are stored in ORACLE_HOME\hs\admin.
In there, you will find a file named ‘inithsodbc.ora’. Make a copy of it called ‘initFRUIT.ora’, and edit the file.

HS_FDS_CONNECT_INFO = SPONGE HS_FDS_TRACE_LEVEL = 0

Save the file.

This has created a link between the SID ‘FRUIT’ to the DSN ‘SPONGE’.

Now edit your LISTENER.ORA file. In the SID_LIST, add the following entry :-

(SID_DESC= (SID_NAME=FRUIT) (ORACLE_HOME=C:\Oracle\Ora9i) # change this to YOUR oracle home (PROGRAM=hsodbc) )

This instructs the listener that to service this sid, use ‘hsodbc’. You’ll need to stop and start the listener to get it to pick up the changes, or perhaps you just need to use the ‘reload’ option – old habits die hard.

Next we need to define a service, so that NET8 knows how to connect to that sid. Add the following entry to your TNSNAMES.ORA on your database server.

JELLY = # you may need to add a domain name suffix – ask your DBA (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=TCP) # edit to point to your LISTENER (HOST=localhost) # edit to point to your LISTENER (PORT=1521) # edit to point to your LISTENER ) ) (CONNECT_DATA=(SID=FRUIT)) (HS=OK) )

Finally, we can log in to oracle. Create a database link to refer to the JELLY service. For excel, we seem to need to specify the Windows 2000 account name (‘tangt’ on machine ‘home’ becomes “HOME\tangt”) and password :-

CREATE DATABASE LINK custard CONNECT TO “home\tangt” IDENTIFIED BY taktangspassword USING ‘jelly’;

Now try this :-

SELECT table_name FROM all_tables@custard;

TABLE_NAME——————————Sheet1$Sheet2$Sheet3$CDSTRACKS

(Got an error instead? Skip to the error section below)

You can see that each worksheet is available as a table (with a ‘$’ appended for good measure), as well as any NAMED RANGES you might have set up. Now try doing some queries against it.

Q: What is a ‘named range’?A: It’s an Excel thing, and beyond the scope of this article. Have a look in the excel help pages.

Q: Does it support ‘unnamed ranges’? What about the [] and “ notations?A: As far as I can tell, they are not supported.

Q: Should I use the worksheet names, or use named ranges?A: Thats up to you. It depends on how you use Excel, whether you have multiple sets of data on a single worksheet, whether you have lots of extraneous text which will need stripping out etc…

Q: How does it know what the columns are called?A: The column names are in the first row.

Q: How does it determine the datatype?A: I would imagine it uses the formatting information to make a guess. Perhaps theres an answer on MSDN.

Q: Why did I pick such odd names for the link, service, sid and dsn?A: Well, it’s not because I’m a trifle mad (BOOM! BOOM!, ahem, I’m sorry, so very sorry) – its so that you can see how the database link name (‘CUSTARD’) relates to the tns service name (‘JELLY’) in the tnsnames.ora, and how that relates to a ‘SID’ (‘FRUIT’) in both the listener.ora and in the hetrogeneous services parameter file (‘initFRUIT.ora’), and how that links to the DSN (‘SPONGE’), which gets you to the Excel file.

Oh, by the way, the instructions above are for a Windows 2000 system. Things might be different on your system, especially if you are trying to do this on an *nix system.

You might also consider downloading the latest version of Microsofts Data Access Components pack – which includes more recent ODBC drivers. I’m running 2.7, but I think the latest is 2.8. Have a look on MSDN.

Takmeister

————————-ORA-28500: connection from ORACLE to a non-Oracle system returned this message:[Generic Connectivity Using ODBC][Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL State: 00000; SQL Code: 0)ORA-02063: preceding 2 lines from CUSTARD

The DSN name specified by “HS_FDS_CONNECT_INFO = SPONGE” in your %ORACLE_HOME%\hs\admin\initSID.ora could not be found.

Check your iniSID.ora file and the ODBC manager.

————————-ORA-02085

You have GLOBAL_NAMES set to TRUE. I have not managed to get HS working with global names, so turn it off with this command :- alter session set global_names = false;

————————-ORA-28545: error diagnosed by Net8 when connecting to an agentNCRO: Failed to make RSLV connectionORA-02063: preceding 2 lines from JELLY

Could be that listener is not running, or has not been restarted.Could be that the PROGRAM in listener.ora is not ‘hsodbc’Could be that the SID in tnsnames.ora is incorrect

————————-ORA-28500: connection from ORACLE to a non-Oracle system returned this message:[Generic Connectivity Using ODBC][H006] The init parameter is not set. Pleaseset it in init.ora file.ORA-02063: preceding 2 lines from JELLY

Could be that the initSID.ora file is not named correctly. Match the SID to that in the listener.ora and tnsnames.ora files.

————————-ORA-12154: TNS:could not resolve service name

The TNS Service name in your tnsnames.ora file does not match that specified in the ‘using’ clause of your database link.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

Teknologi Pendidikan

Just another WordPress.com weblog

KATAKAMI

Jurnalisme Yang Dapat Dipercaya

Tonk Kosonk Baonk Bunyinya

Penampungan Serapah Tanpa Sumpah

Qey050489's Blog

Ayo Bangkit sobat..!!!!!

B. S. Totoraharjo

Orang Bodoh Yang Tak Kunjung Pandai

culturekitchen foodlab

New Food and Art in Yogyakarta

Wahyu Catur Wibowo

Musafir Dunia Maya

SQL.... Still Learning

My Reference on SQL Server

neinframe

Street Photography is like a box of a chocolates, you're never know what you're gonna get - Thomas Leuthard -

Keluarga Andrivito

-- Andrivito's Journal --

DRK™

This site is about Latest NEWS, Articles, Videos, Books, Course, Presentations, Tips & Tricks related to I.T Pro's

Gaurav Saxena

|| तत् त्वम् असि ||

Be a good Moeslim

Menuju Pribadi Muslim Sejati yang Bertaqwa

TuanSUFI

Manajemen Sholat, renungan spiritual, Teknologi Informasi

Pelangi di khatulistiwa....

Mari berbagi kebaikan, pengalaman dan senyum denganku

angkasa13

Ceritanya saya, Pikirannya saya, Hatinya saya

sikathabis

sikathabis semua informasi disini

%d bloggers like this: