Add the following lines to the file (in the same area as the other SID_DESC). The SID_NAME has the name of the file created in the \hs\admin\ directory (excluding init at the start and. Go to the directory \Network\Admin\ and open the file listener.ora. Next the Oracle listener needs to be configured. For the Excel file I also added “HS_AUTOREGISTER = TRUE” and “HS_DB_NAME = hsodbc” to the file.ģ. Open the renamed file and alter the: HS_FDS_CONNECT_INFO to contain the name of the just created ODBC Data Source. Ensure that the file name always starts with “iniths”. Go to the directory \hs\admin\ and make a copy of the file inithsodbc.ora, rename it e.g. Next the Hetrogeneous Srvice needs to be configured. Give the Data Source a name e.g DEMO_EXCEL and select the excel file.Ģ. I will be using an Excel file in this example but you could do the same for an Access database, SQL Server or even MySQL. Start-up the ODBC Data Source Administrator tool (Control Panel > Administrative Tools), select the System DSN tab and click the add button. The first step is to create a System DSN in the Windows ODBC Data Source. Here are the 5 basic steps required to create a database link to non-Oracle sources:ġ. The following Oracle documentation has alot of information on this subject, alot more than I though there was to know, for instance that there are Heterogeneous Service (HS) data dictionary views. If one searches the web using “Heterogeneous Service” you will find many links but when I started my search I had never heard of this service. After searching the web I was able to find bits and pieces and eventually all the pieces fitted together to allow me to connect to Excel and Access by using Oracle Heterogeneous Service, which has been available since Oracle 8i. One method was to export the data from Access to Oracle via ODBC (at that stage I had not heard of Workbench ) but I was sure that there must be a way of doing the same but then from Oracle’s side. Recently I had to extract data from 5 Access databases and 4 Excel files and merge the data into an Oracle table.
0 Comments
Leave a Reply. |