Hello !
I am able to read excel doc at my local drive through sqlplus(oracle).
Please help me to modify steps to access excel docs at network machine.

steps:->

1) start\settings\control\panel\administartive tolls\data source odbc\system dns\driver for microsoft excel (*.xls)\dns source at my local drive.

2)C:\oracle10g\product\10.2.0\db_1\hs\admin\initNITIN.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = TESTDS
HS_FDS_TRACE_LEVEL = 0
#HS_FDS_TRACE_FILE_NAME = aetna_xls.trc
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

3) adding one more listener in listner.ora

SID_LIST_HSODBC_LISTENER =
(SID_LIST=
(SID_DESC=
(SID_NAME=NITIN)
(ORACLE_HOME=C:\oracle10g\product\10.2.0\db_1)
(PROGRAM=hsodbc)
)
)

HSODBC_LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1555))
)
)

4) entry in tnsnames.ora
ABC.SCF.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1555))
)
(CONNECT_DATA =
(SID=NITIN)
)
(HS=OK)
)

5)lsnrctl stop listner
6) listner start HSODBC_LISTENER

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
System parameter file is C:\oracle10g\product\10.2.0\db_1\network\admin\listener
.ora
Log messages written to C:\oracle10g\product\10.2.0\db_1\network\log\hsodbc_list
ener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1555)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1555)))
STATUS of the LISTENER

---

Alias HSODBC_LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
Start Date 30-JUN-2007 12:30:25
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle10g\product\10.2.0\db_1\network\admin\listene
r.ora
Listener Log File C:\oracle10g\product\10.2.0\db_1\network\log\hsodbc_li
stener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1555)))
Services Summary...
Service "NITIN" has 1 instance(s).
Instance "NITIN", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

7)create database link test
connect to "ben"
identified by joe
using 'ABC.SCF.COM';

8)SQL> select * from all_objects@testin;

OWNER OBJECT_NAME S OBJECT_ID

---

------------------------------ -

---

DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ T S T G S

---

------------------

---

--------- - - - - -
Sheet1$ 0
0 TABLE 30-JUN-07 30-JUN-07

Sheet2$ 0
0 TABLE 30-JUN-07 30-JUN-07

Sheet3$ 0
0 TABLE 30-JUN-07 30-JUN-07

---

My queries are :
1)Is it possible to connect to a document at network
if yes then for , do i need to map a drive.
please explaqin how what changes required to make it possible to read a network excel documnet through oracle.

Thanks in Advance
--Ajay

Hi Justin ,

Thanks for your prompt reply ...

I have mapped network machine folder to X:\
now I can access this on my local machine by x:\
And dsn it pointing to document on x:\

then i tried to connect , but got an error.

SQL> select * from all_objects@testin;
select * from all_objects@testin
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][Microsoft][ODBC Excel Driver] '(unknown)' is
not a valid path. Make sure that the path name is spelled correctly and that
you are connected to the server on which the file resides. (SQL State: S1009;
SQL Code: -1023)
ORA-02063: preceding 2 lines from TESTIN

Do I need need any other changes in above confiiguration..

or create link statement need to be modified...

SQL> create database link testin
2 connect to "SCF\ben"
3 identified by "joe"
4 using 'ABC.SCF.COM';

please suggest

Hi,
since, i am not able to access mapped drive.
I tried to use utl_file to read or write from mapped drive connected as sys user.

And other alternative is to configure dns and listner, oracle_home\hs\admin\initSID at remote machine.
remote machine>lsnrctl start listner_new

Then used local tnsnames.ora to point to that machine listner which is using its local dns.

Is there any other better way???

Can we connect sqlserver through oracle locally/remotely???

or do we need to buy drivers for it .. any idea..

Thanks,
-Ajay