Using MS Excel as a Source
bi_manJul 11 2011 — edited Jul 13 2011Hi 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?????