Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using MS Excel as a Source

bi_manJul 11 2011 — edited Jul 13 2011
Hi All,
I want to use excel as a source in OWB. By searching on the web I tried something but no luck it failed. I know that I’m doing something wrong somewhere, I don’t know where am going wrong.
Here I have posting all the system information and steps I followed please correct my mistake.

System Information
Windows Server 2003 R2
Standard x64 Edition
Service Pack 2
Oracle 11.1
Oracle Warehouse Builder 11.1
Microsoft Excel 2007

Steps
1. Created a DSN named odbc_excel. –actually there were no drivers found in when I followed the usual way to create ODBC connection. By suggestion on the web I ran C:\WINDOWS\SysWOW64 \odbcad32.exe and created the DSN.

2. Intit.ora file
Created a init.ora file named initexcel.ora in E:\app\administrator \product\11.1.0\db_1\hs\admin

HS_FDS_CONNECT_INFO = dm_odbc_excel
HS_FDS_TRACE_LEVEL = 1

3. Listener.ora

+(SID_DESC =+
+(SID_NAME = odbc_excel)+
+(ORACLE_HOME = E:\app\administrator\product\11.1.0\db_1)+
+(PROGRAM = hsodbc)+
+)+

4. Tnsnames.ora

odbc_excel =
+(DESCRIPTION =+
+(ADDRESS_LIST =+
+(ADDRESS = (PROTOCOL = TCP)(HOST = <server_name>)(PORT = 1521))+
+)+
+(CONNECT_DATA =+
+(SID = odbc_excel)+
+)+
+(HS = OK) +
+)+


After doing all this I restarted the listener and did a tnsping it was ok.

Then connected to sqlplus and created a database link as below, This also succeeded .
SQL> create database link odbc_excel using 'odbc_excel';


After creating the database link I tried below SELEct statement
SQL> select * from all_tables@odbc_excel;

At this point its throwing the error saying
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from ODBC_EXCEL

What could be the reason for this?? Is there any special things to consider when doing this in x64 bit environment?????
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 10 2011
Added on Jul 11 2011
11 comments
315 views