How to create .udl files ?

Posted on 2002-06-15
Last Modified: 2009-05-28
How to create .udl files from Oracle 8i ?
Question by:shawvoel
  • 3
  • 2

Accepted Solution

M-Ali earned 30 total points
ID: 7080592
This note may help you:  

Doc ID:  Note:110404.1
Subject:  How to Set Up Generic Connectivity Using OLEDB on Windows NT


This document describes the steps to be performed when configuring Generic
Connectivity using OLEDB on Windows NT.

Installation Steps

1). Install the data dictionary tables and views for Heterogeneous Services.
Login as sys and run the script caths.sql. This script is located in
$ORACLE_HOME/rdbms/admin. The data dictionary tables and views may already
be installed on the server. Query to check the existence ie. SYS.HS_FDS_CLASS.

2). Create a Microsoft Data Link (.udl) and configure to connect to the target
datastore. Test the udl to verify connectivity to the target datastore.

3). Make sure the following entries are in the tnsnames.ora and listener.ora.


olemsql =
      (ADDRESS = (PROTOCOL = tcp)(host=winhost)(port=1521))
      (SID = olemsql)    <== sid needs to match listener and initHS_SID.ora(5)
  (HS=OK)                <== HS clause is placed in the description


        (ADDRESS = (PROTOCOL = TCP)(HOST = winhost)(PORT = 1521))

    (SID_DESC =
      (SID_NAME = olemsql)       <== matches sid in tnsnames.ora
      (ORACLE_HOME = E:\Ora816)
      (PROGRAM = hsolesql)       <== hsolesql is the agent executable

4). Start the listener.
Run "lsnrctl services" to verify that you now have a service handler for the
olemsql sid.

LSNRCTL> services
Connecting to (address=(protocol=tcp)(host=winhost)(port=1521))
Services Summary...
  olemsql          has 1 service handler(s)    
    DEDICATED SERVER established:0 refused:0
The command completed successfully

5).Create the Initialization file.

You must create and customize an initialization file for your generic
connectivity agent. Oracle supplies sample initialization files named
"initagent.ora", where agent might be hsodbc or hsoledb to indicate
which agent the sample file can be used for as in the following:


The sample files are stored in the $ORACLE_HOME\hs\admin directory.

To create an initialization file, copy the appropriate sample file and rename
the file to initHS_SID.ora. In this example, the sid noted in the listener and
tnsnames is olemsql so our file is called initolemsql.ora

6). Make sure the following noted entries are in the initolemsql.ora located
in $ORACLE_HOME/hs/admin. Use double backslashes to seperate the directory
path to the udl file.

# HS init parameters
HS_FDS_CONNECT_INFO ="UDLFILE=c:\\temp\\msql.udl"   <= data link location
HS_FDS_TRACE_LEVEL = 0                              <= trace levels 0 - 4
HS_FDS_TRACE_FILE_NAME = olemsql.trc                <= trace file name
# Environment variables required for the non-Oracle system
#set <envvar>=<value>

7). Create a database link to access target database. Be sure to use the
appropriate quotes as noted below. Replace user and password with a valid
userid and password on the target datastore.

SQL> create database link olemsql
SQL> connect to "user" identified by "password"
SQL> using  'olemsql';

8). To test, run a simple query of a known table on the target datastore.

SQL> select * from employee@olemsql;

     empid firstname       lastname        department job
---------- --------------- --------------- ---------- ---
     10000 William         Mays            Networking CDW
     10001 John            Doe             Sales      WNV
     10002 Julie           Reynolds        Relations  NPI
     10003 Peter           Piper           Relations  NPO
     10004 Nickolas        Kapusta         Telemarket PHN

5 rows selected.

Oracle8i Administrators Guide
Oracle8i Distributed Database Systems


 Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.  


Expert Comment

ID: 7080632
nice cut and paste
as always

Expert Comment

ID: 7081034
To UsamaMunir

If you care to read the entire document, it is clearly mentioned that the note is from metalink. True it is cut-and-paste as you put it, but then I dont claim to have authored that note. Unlike you, not all people have access to metalink to help them. If they get help like this, I dont see any harm. Your sarcasm is grossly misplaced.


Expert Comment

ID: 7084056
well well..i smell something here...u took me wrong my frnd, did i say anything which was not true?

Expert Comment

ID: 7085090
Please explain/prove your accusation, usamamunir.


Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question