Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, harsh.

Asked: June 25, 2002 - 9:22 pm UTC

Last updated: November 06, 2017 - 11:18 am UTC

Version: 8.1.6

Viewed 100K+ times! This question is

You Asked

How to connect SqlServer through oracle using generic connectivity.
what changes require in ORacle_home\hs (folder).I mean in inithsodbc,listener,tnsnames after i make System Dsn in odbc

and Tom said...

https://docs.oracle.com/cd/E11882_01/server.112/e11050/toc.htm

describes the architecture and the setup necessary.

Rating

  (141 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Not useful

harsh, June 27, 2002 - 9:20 pm UTC

Sir,Please describe with example because I read that docmentation but cannot be able to apply.

Tom Kyte
June 28, 2002 - 6:37 am UTC

Sorry, if you cannot follow the directions for setup and then provide some useful infromation beyond "cannot be able to apply".

The setup is all there.

That chapter has an example (no more or less then I would be able to provide in any case).

You provide no information about what your problem is, error message, erroneous behavior, etc. I cannot provide anything useful back to you either.

Read n tried ..

Charanjiv, July 23, 2002 - 7:06 am UTC

I went through the URL and related chapters/pages butstill getting the error

scott@817> select authid from authors@sqlsvr ;
select authid from authors@sqlsvr
*
ERROR at line 1:
ORA-28509: unable to establish a connection to non-Oracle system
ORA-02063: preceding line from SQLSVR

Looking the error up in the error manual i have checked the following

1. The USING clause in the DB link and TNSNAMES is the same as in the TNSNAMES file

CREATE DATABASE LINK SQLSVR
USING 'HSORA'

<tnsnames>
HSORA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = BP)(PORT = 1433))
)
(CONNECT_DATA =
(SID = HSORA)
)
(HS = OK)
)

2. protocol-specific information in the service name definition is the same as in the <LISTENER>

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = tsora)
(ORACLE_HOME = C:\Oracle\Ora81)
(SID_NAME = tsora)
)
(SID_DESC =
(PROGRAM = extproc)
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\Oracle\Ora81)
)
(SID_DESC =
(PROGRAM = hsodbc)
(SID_NAME = hsora)
(ORACLE_HOME = C:\Oracle\Ora81)
)
)

LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 8.1.7.0.0 - Production
System parameter file is C:\Oracle\Ora81\network\admin\listener.ora
Log messages written to C:\Oracle\Ora81\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc
)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=258_stock)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=258_stock)(PORT=2481))(P
ROTOCOL_STACK=(PRESENTATION=GIOP)(SESSION=RAW)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 8.1.7.0.0 - Produc
tion
Start Date 23-JUL-2002 12:57:47
Uptime 0 days 0 hr. 0 min. 2 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File C:\Oracle\Ora81\network\admin\listener.ora
Listener Log File C:\Oracle\Ora81\network\log\listener.log
Services Summary...
PLSExtProc has 1 service handler(s)
hsora has 1 service handler(s)
tsora has 1 service handler(s)
The command completed successfully


3.SID=name in the service name definition (in the TNSNAMES.ORA file) must match the value in the LISTENER.ORA file for the responding listener.
Was this the Service (hsora)?

The system DSN is called HSORA. Microsoft SQL Server ODBC Driver (Version 03.80.0194)

Running connectivity tests...

Attempting connection
Connection established
Verifying option settings
Disconnecting from server

TESTS COMPLETED SUCCESSFULLY!

I have run the HS script C:\Oracle\Ora81\RDBMS\ADMIN\caths.sql

The init file for HS has been modified and named appropriately
INITHSORA
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = hsora
HS_FDS_TRACE_LEVEL = OFF

The SQL Server is on another machine. I am running this test from the Oracle Server (8.1.7) and has the SQL server 2000 Client installed on it.

I am still trying ot find where i went wrong. in case you can help pl let me know

A reader, July 23, 2002 - 8:22 am UTC

Hi TOM,

All the time if some one ask question regarding how to connect to non-oracle database you just give us links.

I think that is not enough.Almost every big company has at least one database other then oracle.So it is importent for us if you explain with real examples (No body knows how to use PL/SQL in this case)

I would appreciate if you explain with an example.

Thanks,

Tom Kyte
July 23, 2002 - 10:44 am UTC

No -- I cannot give you an example. The reason is just really simple.

Connecting to a non-oracle data source is NO DIFFERENT THEN CONNECTING TO ANOTHER ORACLE DATA SOURCE.

You follow the setup, make sure the stuff is working and use dblinks. It is NOT any different then a normal DBLINK.

There is no "example" to be given. Oh wait -- here is one:

create database link foo connect to user identified by pw using 'sqlserver';
select * from t@foo;



It is Just a database link. You have to configure the configuration files and that is something you need to discover how to do in your environment, given your odbc drivers, etc etc etc (and something that the documentation actually does walk you through step by step)


Configuration and setup -- support is excellent at helping you there. I personally (believe it or not) do not have sqlserver installed on my machines. In fact, I don't have any machines that are physically capable of running sqlserver since they only run on one OS.



A reader, July 23, 2002 - 12:29 pm UTC

Tom,

We are using oracle 8.1.7. Is PL/SQL gateway is available
in this release? If so, Can you give me link to that documents

Thanks,

Tom Kyte
July 23, 2002 - 8:57 pm UTC

plsql gateway -- do you mean mod_plsql?

got it ..

Charanjiv, July 24, 2002 - 4:56 am UTC

two things which i was doing wrong
1. in SQLNET instead of
SQLNET.AUTHENTICATION_SERVICES= (NTS)
use
SQLNET.AUTHENTICATION_SERVICES=(NONE)
look out for changes in connecting from localhost

2. In the hsodbc definition in tnsnames.ora the host needs to be the Oracle host where I am trying to link from, not the SQL Server host that I am trying to connect to

I also noticed one thing .. after i execute the script for HS my DBMS_HS_PASSTHROUGH package is no longer to be found !! ..but now i can select data from SQL Server

A reader, July 24, 2002 - 8:25 am UTC

Mr. Chiranjiv,

Can you list the step you perform to connect to non-oracle database in correct order(With description)?

Thanks,


as i did it

Charanjiv, July 24, 2002 - 9:28 am UTC

as tom said .. it is important that you do read thru the URL so that you can diy

here are the steps i followed

1. initodbc.ora - modify it to incule the DSN you have created

HS_FDS_CONNECT_INFO=FILEDSN=file_dsn
When connecting using a System DSN, specify the value using:
HS_FDS_CONNECT_INFO=system_dsn

HS_FDS_TRACE_LEVEL = <trace_level> value of on or off

2. </code> http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76960/hs_admin.htm#15089 <code>

set up the LISTENER and TNSNAMES
as i said in my earlier post the tnsnames host is the localhost.
the listener setup sample is provided in the oracle_home\hs\admin directory. So is the tnsnames sample

3.CREATE DATABASE LINK link_name
CONNECT TO user IDENTIFIED BY password
USING 'non_oracle_system';

again ref to my post above for the precautions you have to take

4. run hte caths.sql as sys or internal

thats it. !!!



Heterogenius Services

Vish, July 30, 2002 - 6:01 am UTC

I have been trying to get this working on my oracle 9i rel 2.0 datawarehouse database. Its a shame oracle does'nt have a simple method to get this done ( I was heckled by MS Access users with the ease with which they can set it up :( )
I have a access database and another UNIDATA database for which I 'm trying to access using Heterogenious services.
My initavantiaccess.ora file in HSadmin folder and Tnsnames.ora in network/admin folder are in place.

hsinitavantiaccess.ora
--

HS_FDS_CONNECT_INFO = avantiaccess
HS_FDS_TRACE_LEVEL = OFF


--

Networkadmin nsnames.ora
--
avantiaccess =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=STRATSOURCING)(PORT=1521))
(CONNECT_DATA=(SID=avantiaccess))
(HS=OK)
)


--
Listener.ora
--
(SID_DESC=
(SID_NAME=avantiaccess)
(ORACLE_HOME=C:OracleOra81)
(PROGRAM=hsodbc.exe)
)

When I query I get this message :(
------
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 12564
ORA-02063: preceding 2 lines from AVANTIACCESS

------------
I could tnsping the service name.


I also created a db link. , I get this bug when I query... any idea ? Have i missed anything ?

My email id is vishkr73@yahoo.co.uk. A help in this would be highly appreciated. I really really struggling here


Cheers
Vish


What product licenses do we need to do this?

Cynthia, September 30, 2002 - 3:57 pm UTC

Is it possible to set this up using the products that
are included in the basic Oracle Server Enterprise Edition
install CD?
Or does this require purchase or download of additional
tools?

Tom Kyte
October 01, 2002 - 9:33 am UTC

The generic connectivity is part of...

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76962/ch4.htm#74437 <code>

should not require anything else. There are more advanced transparent gateways available with 9iAS that provide broader functionality above and beyond this capability.

Doc ID 114820.1 at metalink.oracle.com

Saminathan Seerangan, April 15, 2003 - 9:29 am UTC

look for Doc ID: Note:114820.1 at </code> http://metalink.oracle.com <code>for more detailed information



Tom Kyte
April 15, 2003 - 10:07 am UTC

actually for "less detailed".

The documentation has quite a bit more information - and all of the setup steps...

THNX VERY VERY VERY MUTCH SIR

Aladdin Khalifa, September 16, 2003 - 3:39 am UTC

first i wanna thank you sir for ur effort in response us
and i realy found these material very very helpful to me
in connecting to sql server an ms access , but when i tried to connect to borland interbase database and paradox databse
i found a bad news that oracle didnt recogmize the names of the tables that in these datbases , so i tried to found them from the data dictionary view that its names where found in the hs_class_dd and then i selected then rows from these views i found a view called dictionary that stored the table names of the non oracle database tables , so when i tried to select from these tables
he gave me exceptoin ora-00942, though these name of table alrady exist in the dictionary table
so plz what should i do

Tom Kyte
September 16, 2003 - 8:29 am UTC

is the table name in lower/mixed case?

try quoting it

select * from "t"

is very different from

select * from t


the quoted "t" will query a lowercase tablename, the unquoted t is T (uppercase)

thnks

Aladdin, September 17, 2003 - 3:37 am UTC

thanks sir , but i realy tried these before i write my previous review , but it doesnt make any diffencee , so plz
try to help me as soon as u can

Tom Kyte
September 17, 2003 - 7:10 am UTC

sorry -- don't know, i don't run windows, i have none of those products to test with.

Microsoft Access

Abhi, December 15, 2003 - 3:05 am UTC

hello Sir,

I have two tables with simple structure
like EMP and DEPT [ Using 8.1.5 on win2000 ]

How can I write a Microsoft Access db file for that tables
through ORACLE or D2k?

Thanks
Abhi

Tom Kyte
December 15, 2003 - 8:03 am UTC

see the link above? it describes the generic connectivity that lets us access an odbc data source.

d2k can code directly using odbc.

Compatiable SQL for Oracle and SQL Server

A reader, December 30, 2003 - 8:48 pm UTC

Hi Tom,

Unfortunately our application has to support Oracle and SQL Server at the same time. One trouble we had in SQL Server is that it doesn't support Oracle hierarchy query like "CONNECT BY".

I came up with a function which returns the list of child IDs for given root ID, which works in both databases.

For example, get_children(20) may returns a string of '21,23,30,220,22'.

Obviously this has some limitation such as the list cannot be too long. However it's not the concern right now.

So at least now we can use the same code (writen in C#) for both databases, which works like this:

1. get the list of child IDs for the given root ID.
2. compose the whole SQL statement such as 'SELECT * FROM orgs WHERE orgid IN (' + <list of IDs> + ')'

As compared to the original query in Oracle, which may look like
'SELECT * FROM orgs WHERE orgid IN (
SELECT orgid FROM orgassociations
START WITH parentorgid = 20
CONNECT BY PRIOR orgid = parentorgid)'.

What I want to ask you:

1. Does this solution look workable to you?
2. Will Oracle "CONNECT BY" become ANSI standards sometime?
3. Is there a document that I can refer to for the "minimal intersect of SQL syntax supported by all major RDBMS vendors"?

Thanks.




Tom Kyte
December 31, 2003 - 9:23 am UTC

sorry -- i don't do "database independence"

all you get from that is an application that runs pretty bad on one database and horrible on all of the rest. whichever you develop on -- you run pretty bad on and everyone else is hack.

You've basically totally crippled both products -- totally.

My solution would be to hide all of this stuff in stored procedures, have 0 lines of code in the proprietary language C# that start with SELECT, INSERT, UPDATE, DELETE -- only call stored procedures that return result sets

and maximize the $$$'s you and your customers spend on the underly databases to get the best possible performance from them. This works -- it works well.

doen't matter if connect by is ansi or not. ansi is soooo huge these days that no vendor will ever be 100% compliant.

just speaking from the voice of "been there done that". before Oracle I did just database things using sqlserver, informix, db2, ingress, oracle and others. database independence -- nope -- doesn't happen unless you write your own database like SAP or Peoplesoft do with their middle tier caches -- and even then they can only support a small handful of databases using simple keyed reads.





ODBC or OLE DB

Stewart Bryson, April 27, 2004 - 11:20 am UTC

Thanks for the info Tom.

Do you, or any of your readers, have a recommendation as to whether I should use ODBC or OLE DB through HS?

I'll be doing mostly selects from the SQLServer database, so what I'm most concerned with is performance. I don't typically use Windows so I'm not very informed on the microsoft standards

link missing

Jeremy Quaid, May 25, 2004 - 11:46 am UTC

Hi Tom
the link:
</code> http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76960/hs_genco.htm#173 <code>

is unavailable. (I do have an account with OTN but I'm getting a doc not found error)
J.

Tom Kyte
May 25, 2004 - 12:01 pm UTC

it went straight there for me. anyone else having a problem with it?

A reader, May 25, 2004 - 12:20 pm UTC

Link works dandy!

Worked example for using Excel through ODBC

Tak Tang, May 25, 2004 - 3:18 pm UTC

I experienced lots of pain getting HS working, so I've put together a worked example, in the hope it will spare you some of that pain. I'm using Excel - if it works for Excel then it should just be a matter of chaging the DSN (and database link username/password) to connect to another database.

OK, I'll assume you already have a spread sheet handy. We'll start by setting up an ODBC Data Source Name.

Open the ODBC Data Source Administrator
[ Start -> Programs -> Administrative Tools -> Data Sources (ODBC) ]
Click on the tab page labeled "System DSN"
Click on the button labeled "Add"
Select the Excel driver, and press the button marked "Finish"
In the box labeled 'Data Source Name', enter the word 'SPONGE'.
Click on the button labeled 'Select Workbook'
Select the excel workbook you saved earlier.
Click OK, and exit the ODBC Administrator

This has created a Data Source Name (called 'SPONGE') that points to your excel workbook.

Now we'll move onto the Oracle side of things.

First, we need to tell Hetrogeneous Services about SPONGE.
The HS initialisation files are stored in ORACLE_HOME\hs\admin. In there, you will find a file named 'inithsodbc.ora'. Make a copy of it called 'initFRUIT.ora', and edit the file.

HS_FDS_CONNECT_INFO = SPONGE
HS_FDS_TRACE_LEVEL = 0

Save the file.

This has created a link between the SID 'FRUIT' to the DSN 'SPONGE'.

Now edit your LISTENER.ORA file. In the SID_LIST, add the following entry :-

(SID_DESC=
(SID_NAME=FRUIT)
(ORACLE_HOME=C:\Oracle\Ora9i) # change this to YOUR oracle home
(PROGRAM=hsodbc)
)


This instructs the listener that to service this sid, use 'hsodbc'. You'll need to stop and start the listener to get it to pick up the changes, or perhaps you just need to use the 'reload' option - old habits die hard.

Next we need to define a service, so that NET8 knows how to connect to that sid. Add the following entry to your TNSNAMES.ORA on your database server.


JELLY = # you may need to add a domain name suffix - ask your DBA
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=TCP) # edit to point to your LISTENER
(HOST=localhost) # edit to point to your LISTENER
(PORT=1521) # edit to point to your LISTENER
)
)
(CONNECT_DATA=(SID=FRUIT))
(HS=OK)
)


Finally, we can log in to oracle. Create a database link to refer to the JELLY service. For excel, we seem to need to specify the Windows 2000 account name ('tangt' on machine 'home' becomes "HOME\tangt") and password :-

CREATE DATABASE LINK custard
CONNECT TO "home\tangt"
IDENTIFIED BY taktangspassword
USING 'jelly';

Now try this :-

SELECT table_name
FROM all_tables@custard;


TABLE_NAME
------------------------------
Sheet1$
Sheet2$
Sheet3$
CDS
TRACKS


(Got an error instead? Skip to the error section below)

You can see that each worksheet is available as a table (with a '$' appended for good measure), as well as any NAMED RANGES you might have set up. Now try doing some queries against it.

Q: What is a 'named range'?
A: It's an Excel thing, and beyond the scope of this article. Have a look in the excel help pages.

Q: Does it support 'unnamed ranges'? What about the [] and `` notations?
A: As far as I can tell, they are not supported.

Q: Should I use the worksheet names, or use named ranges?
A: Thats up to you. It depends on how you use Excel, whether you have multiple sets of data on a single worksheet, whether you have lots of extraneous text which will need stripping out etc...

Q: How does it know what the columns are called?
A: The column names are in the first row.

Q: How does it determine the datatype?
A: I would imagine it uses the formatting information to make a guess. Perhaps theres an answer on MSDN.

Q: Why did I pick such odd names for the link, service, sid and dsn?
A: Well, it's not because I'm a trifle mad (BOOM! BOOM!, ahem, I'm sorry, so very sorry) - its so that you can see how the database link name ('CUSTARD') relates to the tns service name ('JELLY') in the tnsnames.ora, and how that relates to a 'SID' ('FRUIT') in both the listener.ora and in the hetrogeneous services parameter file ('initFRUIT.ora'), and how that links to the DSN ('SPONGE'), which gets you to the Excel file.

Oh, by the way, the instructions above are for a Windows 2000 system. Things might be different on your system, especially if you are trying to do this on an *nix system.

You might also consider downloading the latest version of Microsofts Data Access Components pack - which includes more recent ODBC drivers. I'm running 2.7, but I think the latest is 2.8. Have a look on MSDN.

Takmeister


-------------------------
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL State: 00000; SQL Code: 0)
ORA-02063: preceding 2 lines from CUSTARD

The DSN name specified by "HS_FDS_CONNECT_INFO = SPONGE" in your %ORACLE_HOME%\hs\admin\initSID.ora could not be found.

Check your iniSID.ora file and the ODBC manager.

-------------------------
ORA-02085

You have GLOBAL_NAMES set to TRUE. I have not managed to get HS working with global names, so turn it off with this command :-
alter session set global_names = false;

-------------------------
ORA-28545: error diagnosed by Net8 when connecting to an agent
NCRO: Failed to make RSLV connection
ORA-02063: preceding 2 lines from JELLY

Could be that listener is not running, or has not been restarted.
Could be that the PROGRAM in listener.ora is not 'hsodbc'
Could be that the SID in tnsnames.ora is incorrect

-------------------------
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][H006] The init parameter <HS_FDS_CONNECT_INFO> is not set. Please
set it in init<orasid>.ora file.
ORA-02063: preceding 2 lines from JELLY

Could be that the initSID.ora file is not named correctly. Match the SID to that in the listener.ora and tnsnames.ora files.

-------------------------
ORA-12154: TNS:could not resolve service name

The TNS Service name in your tnsnames.ora file does not match that specified in the 'using' clause of your database link.


Tom Kyte
May 25, 2004 - 3:54 pm UTC



I'm impressed, thank you very much, this comes up pretty often.

If it comes into being, would you mind if we printed this in the magazine? (not promising, but it looks like it would make a pretty good article actually)

IF Oracle Server On Linux ,then forget about HS Services ?

pasko, May 25, 2004 - 4:00 pm UTC

Hi Tom,

somebody told me that , Oracle does not support HS for Linux ; so if the Oracle Database Server is on Linux Box and you want to connect SQL Server , then it is just won't happen :)

Is that true ?
Do you know of any Free ODBC Manager for Linux?

Thanks in advance.





Tom Kyte
May 25, 2004 - 4:14 pm UTC

[tkyte@tkyte-pc bin]$ ls hs*
hsalloci hsallociO hsdepxa hsdepxaO hsodbc hsodbcO hsots hsotsO
[tkyte@tkyte-pc bin]$ hsodbc


Oracle Corporation --- TUESDAY MAY 25 2004 15:59:10.651

Heterogeneous Agent Release 10.1.0.2.0 - Production Built with
Driver for ODBC



10g, new addition


Yes, it works very fine

j, May 25, 2004 - 4:33 pm UTC

but on my 9iR2 the first row of the "named range" is *NOT* selected ...

(do u know why?)
thanks.

Tom Kyte
May 25, 2004 - 4:55 pm UTC

seems like it would be expecting the column headings to be there according the above note.

Reprinting the article

Tak Tang, May 26, 2004 - 3:53 am UTC

Tom,

Of course you can reprint it! Distribute it as widely as possible. Adapt it as part of the oracle manual if you like. I'm flattered that you think so highly of it. HS is such a great technology. You can get it up and running in under 5 minutes, without writing a *single* line of code. And the ODBC/OLE side is completely free.

I produced the errors by starting with a working setup, and gradually misspelling things to see what error message came out. Oh, and I notice that I've missed out the 't' in 'initSID.ora' under the first error.

Takmeister


To Tak

A reader, July 02, 2004 - 6:56 pm UTC

Great! Do you know if a similar method can be used when my Oracle server is on Unix?

I am using a software called Sharity (</code> http://www.obdev.at <code> to access my Windows-land files from my Solaris server so as far as Oracle is concerned, the .xls file is local to it.

Thanks

Tom Kyte
July 03, 2004 - 10:08 am UTC

the heterogenous services (ODBC connectivity) is available on aix, hp, sun and starting with 10g, linux.

Access to xls

A reader, July 04, 2004 - 10:04 am UTC

Right, I know that HS is available, but the missing piece is a ODBC driver for Excel. As far as I know, there is no freely available Unix/ODBC driver for Excel.

I tried all the usual places like </code> http://www.unixodbc.org <code>but didnt see anything.

Is anyone out there able to 'select * from' a XLS file when Oracle is running on Unix?

Thanks

Tom Kyte
July 04, 2004 - 11:34 am UTC

time to goto askbill.microsoft.com

sorry -- we can load the xls spreadsheet into the database, we can even turn it into a open systems format (html) using TEXT -- but beyond that, I just don't work with that stuff -- I turn xls into csv and load it into something useful (a table) and skip the xls after that.

working with MS specific file formats on non-windows OS's is "not easy".


A quick search on google for

java api excel

found as the first hit:

</code> http://www.andykhan.com/jexcelapi/ <code>

which looks promising, you could easily load that jar up and create a java stored procedure.

Tak Tang, July 04, 2004 - 3:57 pm UTC

To Reader (July 2nd 2004)

'Sharity' looks like something that lets a unix/linux machine mount a windows share. If you have a windows machine as a server, install an oracle listener on it (second option - 'management/integration'; select CUSTOM and deselect everything except for net services/listner; if the install does not create the hs subdirectory, you can extract it manually from disk 2). Modify the listener.ora, and hs\admin files as described in my worked example.

On the unix machine hosting oracle server, modify the tnsnames.ora as described, but change the 'ADDRESS=' portion to point to the listener on your windows machine.

This uses SQL*Net as the communication protocol, and launches ODBC on the windows machine natively.

Do not be tempted to use some kind of odbc-odbc bridge - this is exactly what Generic Connectivity does.

--

To Reader (July 4th 2004)

My example above used Excel because it is ubiquitous. In my (limited) experience, the performance of the Excel ODBC driver is terrible and would only use it to simplify one-off data loads. There also seem to be some concurrency issues which I did not manage to resolve.

If you only want to extract data, try one of the various ODBC extraction tools, such as 'Database Fishing Tool' from sourceforge; or my own quick-and-dirty contibution 'dumpodbc.js' (which does not require ODBC DSN for Excel/Access) </code> http://www.orafaq.net/scripts/win/dumpodbc.txt <code>(posted there because its more than 1000 'words').

If read/writeing Excel files (in spite of the performance and concurrency issues) is mission critical, you have two options :-

1) Deploy a windows server to host the excel files, and install a listener on it. This lets you use Microsoft's ODBC drivers. (Variation: use some kind of PC/Windows emulation on Unix/Linux)

2) Put together a buisiness case to your boss for licensing third party ODBC drivers, but make sure you evaluate their trial version first. If your budget is tight, you'll have to write your own, but consider the cost of your time to write it vs the cost of buying one in. I might have a go at it myself if I can find time.


Takmeister


To Tak

A reader, July 04, 2004 - 6:46 pm UTC

"the performance of the Excel ODBC driver is terrible ..."

"Deploy a windows server to host the excel files, and install a listener on it. This lets you use Microsoft's ODBC drivers"

Let me understand what you are saying. You are saying that the performance of Microsoft's ODBC drivers is terrible? But surely I can use other Windows-land freely available ODBC drivers for Excel? Or are you saying all of them perform horribly?

I mean, Excel can handle only about 64000 rows only anyway, right? Even the worst ODBC driver should be able to load this in a minute, right?!
"If you only want to extract data, try one of the various ODBC extraction tools"

Thanks, but I was thinking more about ongoing real-time interface between Oracle and Excel. For one-time loads, I can just save the Excel as a CSV (or better a | separated file, because , is very common in user data) and load using SQL Loader/external tables

Performance of Microsofts Excel ODBC Drivers

Tak Tang, July 05, 2004 - 12:18 pm UTC

To Reader,

Sorry - that was a vague and completely unsubstantiated opinion - I probably shouldn't have mentioned it. Tom would never be so careless. It might just be something wierd about my setup. Try it out and see if it performs well enough for you - its cheap and easy to try.

The concurrency problem I had was that the Excel file gets locked as soon as you access it, even if you set it to 'read only', until you terminate the session (perhaps by connecting as another user?) so you can not access it from two different sessions, nor from Excel and Oracle at the same time. I notice the Hetrogeneous Services manual mentions setting up Multi Threaded Agents. That might be worth looking into.

I also got HS working with Ole DB, if you're interested.

Takmeister


Thanks

A reader, July 05, 2004 - 6:11 pm UTC

Tom and Tak, Thanks for your ideas and comments.

I dislike Excel i.e. proprietary file formats as much as you do. But the thing about Excel files is that they are like weeds, no one likes them but you have to deal with them anyway. IMHO more companies have lot of critical information locked up inside .xls files than they would admit.

I even have users referring to .xls files as "databases"! And why not! After all they have rows and columns and guess what tables have! Over the years, they accumulate tabs/workbooks with elaborate cross-checking, lookups, etc, all the things that you typically associate with database-based applications.

Given all this, having the ability to access (even just for reading) .xls files as effortlessly as flat files (the external tables feature in 9i) is immensely useful.

Thanks

Tom Kyte
July 05, 2004 - 8:26 pm UTC

htmldb is our attempt to help there -- cut and paste the spreadsheet into a table and make it shareable, secure, backed-up and accessible.....




HTMLDB

A reader, July 05, 2004 - 8:55 pm UTC

Well, HTMLDB is much more than just cut-n-paste xls into a table! Dont downplay htmldb.

For one-time load of XLS into the database, there are many options, the simplest (other than HTMLDB) being just save as CSV and load using SQLLDR/external tables.

The problem, as always, is the culture around use of technology. In other words, the users!

Even after loading the xls into the database and providing a way to maintain it right in the database, users have a tendency to keep using their darn spreadsheets and then coming back after 3 months complaining that the database is "out of sync"! :-(

God, how I hate Excel spreadsheets, Access "databases" and the like!

Almost there

don thiel, October 13, 2004 - 12:35 pm UTC

First, Tak Tang's article was great! I was able to get my link between 9i and SQL Server working.

However, referencing the SQL Server table data on a column level has eluded me.

SQL> select count(*)
  2  from SQL_SERVER_TABLE@custard;

  COUNT(*)
----------
   1836925 

works fine.

SQL> select *
  2  from SQL_SERVER_TABLE@custard;

will list all of the rows as normal.

SQL> desc SQL_SERVER_TABLE@custard
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 Account_no                           NOT NULL VARCHAR2(20)
 Individual_ID                             NOT NULL NUMBER(10)
 Date_added                                NOT NULL DATE
 Presence_of_Email                                  CHAR(1)
 Last_Update                               NOT NULL DATE   

works as expected

However:

  1  select *
  2  from SQL_SERVER_TABLE@custard
  3* where Presence_of_Email = '1'
where Presence_of_Email = '1'
      *
ERROR at line 3:
ORA-00904: "PRESENCE_OF_EMAIL": invalid identifier

Is there some special snytax needed when refering to a linked table?
 

Tom Kyte
October 13, 2004 - 2:56 pm UTC

select "Account_no" from .....

use quoted identifiers -- they've got in in mixed case over there.

Yes!

don thiel, October 13, 2004 - 3:48 pm UTC

Yes, that worked, I'm constantly amazed how other vendors make easy things so cryptic.

Thanks, Tak Tang and Tom

VKumar, November 05, 2004 - 6:07 am UTC

Thanks Tak Tang. Your article was excellent. The connectivity between Oracle and SQL server is working fine.

I was struggling for more than a week to establish a connection between Oracle 9i and SQL Server 2000. I got all the errors stated in the errors section of the article.

The way the article was put up - how-to-do (establishing connectivity) and what-to-do (resolving errors) - is really good.

Thanks Tom for a superb site.

Followed Tak Tang Process but not working

A reader, January 28, 2005 - 3:31 pm UTC

Hi Tak,
I have followed the steps given by you to access excel from oracle exactly same way you put without any change but it is not working.
It is giving following error.

ERROR at line 2:
ORA-12154: TNS:could not resolve the connect identifier specified

I have verified it atleast 10 times but no luck ?
I have also gone through the oracle error messages doc for above error ORA-12154 to verify other things but not working.
any idea ?
Thanks
Kalpesh

Tom Kyte
January 28, 2005 - 7:18 pm UTC

the tnsnames on the machine that needs the tnsnames (the database machine) is not correct.

the tnsnames.ora on your client - not meaningful, the DATABASE is trying to resolve the tns entry.

[tkyte@localhost tkyte]$ oerr ora 12154
12154, 00000, "TNS:could not resolve service name"
// *Cause: The service name specified is not defined correctly in the
// TNSNAMES.ORA file.
// *Action: Make the following checks and correct the error:
// - Verify that a TNSNAMES.ORA file exists and is in the proper
// place and accessible. See the operating system specific manual
// for details on the required name and location.
// - Check to see that the service name exists in one of the
// TNSNAMES.ORA files and add it if necessary.
// - Make sure there are no syntax errors anywhere in the file.
// Particularly look for unmatched parentheses or stray characters.
// Any error in a TNSNAMES.ORA file makes it unusable. See
// Chapter 4 in the SQL*Net V2 Administrator's Guide. If
// possible, regenerate the configuration files using the Oracle
// Network Manager.


Followed Tak Tang Process but not working

A reader, January 31, 2005 - 11:48 am UTC

Hi,
I am still having the issue.
Let me put some details.
My oracle DB and excel file is on same WIN NT machine. Oracle DB is 10g.
I have changed all tnsnames.ora which are in following directories.
C:\Oracle\product\10.1.0\Client_1\network\ADMIN
C:\Oracle\product\10.1.0\db_1\hs\admin
C:\Oracle\product\10.1.0\db_1\NETWORK\ADMIN
The entry in tnsnames.ora is:

EXCEL1 =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=STG4LC361)(PORT=1521) )
(CONNECT_DATA=(SID=EXCEL1))
(HS=OK)
)

Thanks



Tom Kyte
January 31, 2005 - 11:57 am UTC

do you have something in your sqlnet.ora

Followed Tak Tang Process but not working

A reader, January 31, 2005 - 12:28 pm UTC

Yes,
I do have following lines in sqlnet.ora which is
C:\Oralce\product\10.1.0\db_1\NETWORK\ADMIN

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Let me know if you need more information.
Thanks
Kalpesh

Tom Kyte
January 31, 2005 - 12:33 pm UTC

\Oralce\pr
^^^^^^^

??

and show us a CUT AND PASTE of your create database link and subsequent attempt to use it.

and you can always put the

(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=STG4LC361)(PORT=1521) )
(CONNECT_DATA=(SID=EXCEL1))
(HS=OK)
)

right in the create database, skipping the tnsnames resolution all together (that would show us if the file itself just isn't formatted correctly -- could be a bad entry above excel1)

Followed Tak Tang Process but not working

A reader, January 31, 2005 - 1:15 pm UTC

Hi,
Here is the information you wanted.

SQL> create public database link excel1
  2  using 'EXCEL1';

Database link created.

SQL> select table_name from all_tables@excel1;
select table_name from all_tables@excel1
                                  *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


Thank You 

Tom Kyte
January 31, 2005 - 1:42 pm UTC

and did you try my suggestion, to remove the tns resolution from the loop -- for you are not resolving that name *apparently*


A reader, January 31, 2005 - 2:16 pm UTC

Hi,
I didn't get "remove the tns resolution from the loop".
Could you explain with some steps.

Thanks


Tom Kyte
January 31, 2005 - 2:24 pm UTC

(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=STG4LC361)(PORT=1521) )
(CONNECT_DATA=(SID=EXCEL1))
(HS=OK)
)

put that where you have using 'excel1'

... using '(description=(.........)'



It Worked

A reader, January 31, 2005 - 2:45 pm UTC

Hi Tom,
It worked. Here is the detail.

SQL> create database link
  2  excel1 using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=STG4LC361)(PORT=152
1))(CONNECT_DATA=(SID=EXCEL1))(HS=OK)
  3  )';

Database link created.

SQL> select table_name from all_tables@excel1;

TABLE_NAME
------------------------------
Sheet1$
Sheet2$
Sheet3$

Thank You
 

Tom Kyte
January 31, 2005 - 2:48 pm UTC

that means your tnsnames.ora file is really "not correct", something is wrong in it. it is the one in your database directory.

Connect to Ingres on Linux from Oracle 10g on XP

A reader, February 01, 2005 - 5:04 pm UTC

Hi Tom,
I am using Oracle 10g on Win XP. I am trying to access Ingres DB on Linux from Oracle. BUT it is not working and giving following errors.

"ORA-28545: error diagnosed by Net8 when connecting to an agent NCRO: Failed to make RSLV connection ORA-02063: preceding 2 lines from INGRES"

Here are the details about my setup.

- I did setup ODBC from XP to ingres and tested. It was successful

- My initINGRES.ora in $ORACLE_HOME/hs/admin is having following lines.
HS_FDS_CONNECT_INFO = INGRES
HS_FDS_TRACE_LEVEL = OFF

- Following is entry in listener.ora.
(SID_DESC =
(SID_NAME = INGRES)
(ORACLE_HOME = C:\oracle\product\10.1.0\db_1)
(PROGRAM = hsodbc)

- Following is my create dblink command from oracle to ingres.

CREATE DATABASE LINK "INGRES" CONNECT TO "USERNAME" IDENTIFIED by "PASSWORD" USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LINUX_IP)(PORT=1521))(CONNECT_DATA=(SID=INGRES))(HS=OK))'

Here USERNAME and PASSWORD is to connect to LINUX_IP where Ingres Database is.

I am unable to figure out what is wrong ?
Could you help me and let me know if you need more information.

Thank You
A Reader



Tom Kyte
February 02, 2005 - 4:50 am UTC

please utilize support for an issue like this.

A reader, February 02, 2005 - 11:55 am UTC

Hi Tom,
I do not have oracle support. I am trying this with free download of Oracle 10g on my PC.

Thank You

Tom Kyte
February 03, 2005 - 12:51 am UTC

sorry, I don't have ingress. I won't be able to set up and test this myself.

i don't use windows
i've never configured odbc.

HS configuration in Unix

Ravi Monni, February 08, 2005 - 5:09 pm UTC

Hi,

HS configuration for ODBC under Unix necessitates setting the following parameters. [This is HS configuration for Oracle in Solaris to connect to Teradata using ODBC].

# inittddev.ora
HS_FDS_CONNECT_INFO=tddev
HS_FDS_TRACE_LEVEL=0
HS_FDS_SHAREABLE_NAME=/usr/odbc/lib/libodbc.so

set ODBCINI=/u01/app/orahome/.odbc.ini
# end of inittddev.ora

Setting HS_FDS_SHAREABLE_NAME is mandatory for HS in Unix. Please check the manual page </code> http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10764/apa.htm#634607 <code>

Regards
Ravi

ODBC and MSAccess

denni50, February 25, 2005 - 3:00 pm UTC

a generous thanks to Tak Tang for posting your ODBC Connectivity instructions.

I printed out your instructions and applied them to an MSAccess DB and, with a little tweaking, got it working in no time.

SQL> select db_link,host,created from user_db_links;

DB_LINK
-----------------------------------------------------
HOST
-----------------------------------------------------
CREATED
---------
MYACSS
ACSSDB
20-FEB-05

SQL> select table_name from user_tables@myacss;

TABLE_NAME
------------------------------
Combo_ph2
Combo_phone1

2 rows selected.

SQL> select * from Combo_ph2@myacss;

        ID Phone
---------- -----------------------------------------------------------------
    119962 215 257-9652
    109946 207 498-2163
     97303 814 486-0754
     99408 814 225-4520
     94179 530 758-0046
     83252 540 371-3665
     69862 304 436-2596
    972970 706 782-1557
    972368 205 487-3509
   1055934 360 592-2633
   1053383 843 744-7098


For anyone wanting to connect to MSAccess just follow Tak's instructions except select the Microsoft Access.mdb Driver..when you click FINISH go back to the System DSN screen..where your data source file is listed in the panel..and hit the Configure button.

You'll need to "SELECT" the drive\folder where your .mdb file resides...example browse 'G:\mydbFiles\mydb.mdb'...that will get posted to the default directory in the ADVANCED tab(click ADVANCED..you should see the directory path)..and you're good to go.

I got mine setup on Windows 2000 Server.

thanks Tak!







 

Tom Kyte
February 25, 2005 - 6:42 pm UTC

I was very appreciative of his work as well -- at the point in time of the posting, I had no windows machines at all (I do now). His setup and contribution has been used time and time again.

HEAR! HEAR!

denni50, February 26, 2005 - 10:58 am UTC

Global World News Flash!

The earth has just ceased rotating on it's axis...
Tom Kyte has a "WINDOZ" machine..(hark! the angels sing).

Across the globe Bill Gates announces.."Tom, YOU'RE HIRED!!!"

(chuckle!) :~)




Following Tak's article

whizkid, April 18, 2005 - 7:38 am UTC

I was just trying to connect to the excel file from my oracle db using Tak's steps.. but getting an error.. going thro the process that i followed.. here the database and excel file both are on the same machine..

1) created a system dsn pointing to an excel file
2) added entry in the listener.ora file & restarted the listener..

LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 9.2.0.5.0 - Production
System parameter file is F:\Oracle\Ora92\network\admin\listener.ora
Log messages written to F:\Oracle\Ora92\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dgvishalshah.domain-in.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dgvishalshah)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 9.2.0.5.0 - Production
Start Date 18-APR-2005 16:45:41
Uptime 0 days 0 hr. 0 min. 2 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File F:\Oracle\Ora92\network\admin\listener.ora
Listener Log File F:\Oracle\Ora92\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dgvishalshah.domain-in.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
Services Summary...
Service "CP" has 1 instance(s).
Instance "CP", status UNKNOWN, has 1 handler(s) for this service...
Service "FRUIT" has 1 instance(s).
Instance "FRUIT", status UNKNOWN, has 1 handler(s) for this service...
Service "PC1" has 1 instance(s).
Instance "PC1", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "SUNDB" has 1 instance(s).
Instance "SUNDB", status UNKNOWN, has 1 handler(s) for this service...
Service "TEST" has 1 instance(s).
Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
Service "ora9i" has 1 instance(s).
Instance "ora9i", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

3) added the following in tnsnames.ora
JELLY =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=dgvishalshah)
(PORT=1521)
)
)
(CONNECT_DATA=(SID=FRUIT))
(HS=OK)
)

C:\>tnsping jelly

TNS Ping Utility for 32-bit Windows: Version 9.2.0.5.0 - Production on 18-APR-2005 16:57:55

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:
F:\Oracle\Ora92\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=TCP) (HOST=dgvishalshah) (PORT=1521))) (CONNECT_DATA=(SID=FRUIT)) (HS=OK))
OK (50 msec)

4) created the db link....

ora9i >create database link custard
2 connect to "dgvishalshah\vishal" identified by pass using 'jelly';

Database link created.

Elapsed: 00:00:00.01

but when i try to access the sheets in the workbook it gives the error...

ora9i >select table_name from all_tables@custard;
select table_name from all_tables@custard
*
ERROR at line 1:
ORA-28522: error initializing heterogeneous capabilities
ORA-28522: error initializing heterogeneous capabilities
ORA-28559: FDS_CLASS_NAME is ODBC9.2.0.5.0_128, FDS_INST_NAME is FRUIT
ORA-02063: preceding 3 lines from CUSTARD
ORA-00604: error occurred at recursive SQL level 1
ORA-24274: no row exists in the HS$_BASE_CAPS table for these parameters
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_HS_UTL", line 431
ORA-06512: at "SYS.DBMS_HS_CHK", line 51
ORA-06512: at "SYS.DBMS_HS_UTL", line 48
ORA-06512: at "SYS.DBMS_HS", line 38
ORA-06512: at line 1

database is 9.2.0.5 on Windows 2000 Advanced server.

Tom Kyte
April 18, 2005 - 8:42 am UTC

An extrace from metalink note Note 234517.1 (do you have metalink access?  great place to search...) says this:



ORA-24274/ORA-28522:
           SQL> select * from all_catalog@<db_link>;
           select * from all_catalog@<db_link>
                          *
           ERROR at line 1:
           ORA-28522: error initializing heterogeneous capabilities
           ORA-28522: error initializing heterogeneous capabilities
           ORA-28559: FDS_CLASS_NAME is <GTW>9.2.0.5.0_128, FDS_INST_NAME is <link>
           ORA-02063: preceding 3 lines from %s
           ORA-00604: error occurred at recursive SQL level 1
           ORA-24274: no row exists in the HS$_BASE_CAPS table for these parameters
           ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
           ORA-06512: at "SYS.DBMS_HS_UTL", line 431
           ORA-06512: at "SYS.DBMS_HS_CHK", line 51
           ORA-06512: at "SYS.DBMS_HS_UTL", line 48
           ORA-06512: at "SYS.DBMS_HS", line 38
           ORA-06512: at line 1

  Resolution:
           
           This kind of error might occure, if the gateway release
           is 9.2.0.5 and the Oracle database is an older database release.
           To fix this problem, the hs catalog needs to be updated:
           Connect to the Oracle database witrh the db link
           SQL> connect system/manager
           Connected.
           and execute the following code:
           SQL> exec dbms_hs.replace_base_caps(531, 531, 'First/Last function');
            PL/SQL procedure successfully completed.
           
           Now the repository is up to date. 

Thanks Tom!

whizkid, April 18, 2005 - 10:07 am UTC

Thanks a ton, Tom. I'm ashamed of wasting your time :( I do have access to metalink and had searched for this error and remember hitting the note that you pointed out but because it mentioned "Oracle Transparent Gateway for IBM DRDA" under the product, didnt look inside it. Sorry once again for troubling you..

Tom Kyte
April 18, 2005 - 10:23 am UTC

did it work?

Yes!

whizkid, April 18, 2005 - 10:33 am UTC

Yes it did work.. but noticed a strange thing.. it worked only if i execute the procedure from system. Not from sys. Seemed strange to me though. Any clues why?

C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.5.0 - Production on Mon Apr 18 19:33:03 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

ora9i >exec dbms_hs.replace_base_caps(531, 531, 'First/Last function');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
ora9i >select table_name from all_tables@custard;
select table_name from all_tables@custard
*
ERROR at line 1:
ORA-28522: error initializing heterogeneous capabilities
ORA-28522: error initializing heterogeneous capabilities
ORA-28559: FDS_CLASS_NAME is ODBC9.2.0.5.0_128, FDS_INST_NAME is FRUIT
ORA-02063: preceding 3 lines from CUSTARD
ORA-00604: error occurred at recursive SQL level 1
ORA-24274: no row exists in the HS$_BASE_CAPS table for these parameters
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_HS_UTL", line 431
ORA-06512: at "SYS.DBMS_HS_CHK", line 51
ORA-06512: at "SYS.DBMS_HS_UTL", line 48
ORA-06512: at "SYS.DBMS_HS", line 38
ORA-06512: at line 1

ora9i >conn system/manager
Connected.
ora9i >exec dbms_hs.replace_base_caps(531, 531, 'First/Last function');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

ora9i >select table_name from all_tables@custard;

TABLE_NAME
------------------------------------------------------------
'premium register$'

1 row selected.

Elapsed: 00:00:00.00
ora9i >

It did work.. no complaints..

Tom Kyte
April 18, 2005 - 10:38 am UTC

no idea, but I suggest using "as sysdba" to basically startup and shutdown and not much more.

sys is magic, it is very very special. things work differently for sys.

connectivity from oracle to msde

A reader, April 19, 2005 - 4:59 pm UTC

Hi,

I want to know if we can use heterogenous connectivity to connect from oracle to msde. Has anyone tried this? Please let me know.

Thanks.

Tom Kyte
April 19, 2005 - 7:49 pm UTC

if it has odbc, should work

Saved my project

Rick, April 26, 2005 - 7:25 am UTC

I'm using all this to create a link between Oracle and OSI Software's PI Server.

It works great... I'm really a n00b when it comes to these things, but the example works like a charm.

It just saved my project :P

Connect to SQL Server

Yulianto, May 10, 2005 - 3:53 am UTC

Dear sir, 
i've found that your articles are very useful for my final project. I've create a database link to SQL Server 2000 Developer Edition.
I'am using default database northwind at SQL server.
But when I :
SQL> desc employees@sqlsvr
I found this error :
ERROR :
ORA-02084 : database name is missing a component

Beside that i also found that my oracle can't identify some database fields that use 'nvarchar' dan 'nchar' as their data types. I can only select 'int' and 'char' datatypes.
Are there something wrong with my link or my HS setting? 

Thanks before.   

Tom Kyte
May 10, 2005 - 8:55 am UTC

did you set the appropriate nls settings?
</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96544/apa.htm#631820 <code>

Would HS work for MySQL Database

Orlando Reyes, May 12, 2005 - 11:52 am UTC

Hi Tom,

At a client I worked for last year we used Data Direct to setup the framework to connect to AS/400 machines running DB2, it worked fine most of the time. Also on a document note on Metalink (238771.1) it explains hot to setup HSODBC on AIX and all the time it makes reference to third party vendors who provide ODBC drivers and/or framework (including Data Direct).

Question is, do we really need an external driver vendor to get HS to work from Oracle? I don’t get that feeling from reading the Oracle Manuals, but I have never installed HS along. Also, on this thread I don’t see anyone asking/mentioning an external vendor, but I need to be 100% before I start installing the HS services and present a solution to my superiors.

Finally, would HS work for the MySql DB without using an extra component? Please don’t ask me why or how this is being used, it is there and we need to deal with connecting it to Oracle somehow.

I need to get HS setup on an AIX server to connect to MySql on a Windows machine.

Thanks a lot for your help as always and have a great day.

Orlando


Tom Kyte
May 12, 2005 - 1:30 pm UTC

I don't know what you mean by "Question is, do we really need an external driver vendor to get HS to work from
Oracle?"

can you clarify?

you would need an odbc driver to mysql to use HS to access mysql.

Need External drivers outside Oracle

Orlando Reyes, May 12, 2005 - 3:19 pm UTC

Tom,

What I mean is do I need to get the ODBC drivers for Unix from and third party vendor or are they provided by Oracle when I install the HS services?

I might be confused, because when I install Oracle on Windows, I get the ODBC Administration interface under my Oracle folders and I was kind of thinking that some of the drivers were installed by Oracle. Is that the case or is Oracle just giving me an interface to manage the drivers that come with Windows (I know you don’t use windows much)? And what would be the case for AIX? I guess I need to find out if AIX comes with any ODBC framework already and if not that is when Data Direct needs to come into play.

Any comments will help.

Thanks,

Orlando


Tom Kyte
May 12, 2005 - 3:48 pm UTC

you need to get odbc drivers for whatever data source you want to access.

we do not ship drivers to other databases.

successfully done

riyaz, May 13, 2005 - 8:10 am UTC

Successfully done the same with 6 steps: (tks Tom)


Server

1. ODBC should be created.

ex: sqlserver7
SQL server should be loaded, need not be in oracle server itself.

(used system dsn)

2.
Go to (in oracle server)

\ora90\hs\admin

copy file inithsodbc.ora as initSQLSERVER7.ora

Inside:
=======
# 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 = sqlserver7 <<dsn name>>
HS_FDS_TRACE_LEVEL = ON


#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>


3. \ora90\network\admin

SQLNET.ORA

SQLNET.AUTHENTICATION_SERVICES= (NONE) <<changed as NONE instead of NTS>>

4. tnsnames.ora

Add the below

SQLSERVER7.ITSERVER =
(DESCRIPTION=
(ADDRESS=(PROTOCOL = TCP)(HOST = it-server)(PORT=1521)
)
(CONNECT_DATA=
(SID = sqlserver7))
(HS= OK)
)


5. Listener.ora

Ad sqlserver7 as sid_name


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:\oracle\ora90)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = c:\oracle\ora90)
(SID_NAME = orcl)
)
(SID_DESC =
(PROGRAM = hsodbc) <<-- constant >>
(ORACLE_HOME = c:\oracle\ora90)
(SID_NAME = sqlserver7) <<--- sqlserver>>
)
)


6.Create datebase link (ex: scott/tiger)

>sql prompt

CREATE public DATABASE LINK rizsql
CONNECT TO sarah IDENTIFIED BY sarah
USING 'sqlserver7'

riz/riz --> SQL server uname/pwd

<< selecting table test1 of sqlserver, user - riz >>

system@ORCL> select * from test1@rizsql
2 /

slno amount
---------- ----------
3 1000
1 2000
2 19000
5 5000





one correction in the above

riyaz, May 13, 2005 - 8:12 am UTC

One correction

CREATE public DATABASE LINK rizsql
CONNECT TO riz IDENTIFIED BY riz
USING 'sqlserver7'



Oracle to Foxpro

Yulianto, May 13, 2005 - 10:47 am UTC

Dear sir,

I've created a link from oracle 9.0 to Visual Foxpro 8.0
Professional.
However i found that when i open the link in SQL* plus, my foxpro database become read-only.
It also happen when i open my foxpro. The link in SQL* plus will show some error message like "cannot open database file c:\aa.dbf ...."
How can i modify my foxpro database without closing my SQL*plus window?

thank you...

Tom Kyte
May 13, 2005 - 10:53 am UTC

sorry, never have touched foxpro myself. maybe if I had the entire real error message...

greats!!

sachin, May 18, 2005 - 11:36 am UTC

dear tom ,

today i have read this articles,this is very useful for me, actually i m facing this type of problems. so i got help from this .
thanks!!

sachin

foxpro error

Yulianto, May 20, 2005 - 4:51 am UTC

the error was :
file access is denied d:\test.dbc

i've found from foxpro manual that the the database file
became write-protected.
However i need to access it from my oracle.

Tom Kyte
May 20, 2005 - 8:00 am UTC

sorry, don't do "foxpro", you'll need to fix that at the foxpro level.

other link maybe

yulianto, May 24, 2005 - 11:27 pm UTC

ok,no problem..

can you recommend other link or forum about connectivity to
foxpro

thanks before...

Tom Kyte
May 25, 2005 - 7:41 am UTC

</code> http://asktom.oracle.com/Misc/success.html <code>

for a list of some other good forums out there.

Replication in HS

Yulianto, July 05, 2005 - 10:15 pm UTC

Dear tom,

is it possible to use snaphot replication in HS-based database link?
I have tried to replicate some data from SQL Server 2000 through my link but it failed.
Are there other ways to replicate data and automatically refresh the replica without using snapshot?

Thanks before...

Tom Kyte
July 06, 2005 - 7:38 am UTC

full refreshes should work, but since they do not have materialized view logs, incremental refreshes would not be.

you say "it failed"
that is "very vague"

error

Yulianto, July 06, 2005 - 10:01 pm UTC

Dear Tom,

Sorry for the vague explanation.

I tried to create this snapshot :

Create Snapshot a
Pctfree 5 pctused 60
tablespace users
storage (initial 50k next 50k pctincrease 50)
refresh complete
start with sysdate
next sysdate + 1/1440
as select nrp,nama from biomhs@baak.stts;

the "baak.ssts" is a dblink to SQLServer. However i found this kind of error.

ERROR at line 5:
ORA-03113: end-of-file on communication channel

Any idea about it?

Thanks.

Tom Kyte
July 07, 2005 - 9:01 am UTC

please contact support for such an issue.

Servers : Running One Database on two Servers.

VIKAS SANGAR, August 24, 2005 - 1:37 am UTC

Dear Mr. Kyte

I have a query which, i would like to put in front of you.
I have oracle 9iR2DB installed on two machines(Servers)...

One with Fedora 2 Linux (which is our actual production server (we all work by connecting our oracle client to this Linux server)
Other one, Windows 2003 Server(actually used to do some R&D, Exp /Imp, backups etc) , I have imported the Users (Schema) of production database actually being run on Linux server onto this Windows Server.

Now, what I intend to do is, to configure oracle on my machines (servers) in such a way that what ever work creation, deletion,insertion etc, that are made to my production database on Linux Server actually gets saved/incorporated on to my Windows server along with the Linux server (as it usually does).

-->Can you pls tell me, if this is possible and feasible?
If yes how can this be achieved?
-->What are the points and considerations I must take into
account before applying this.
-->Can you provide me with the link to any documnetation or
resource which provides with detailed information on how
to achieve the above task?

Thanx a ton with regards...
Vikas.



Tom Kyte
August 24, 2005 - 9:10 am UTC

ugh, it is called replication - you have two choices, advandced replication and streams. Your windows box will become a production machine by doing this by definition.

I would question the usefulness. if you want a failover/copy -- use homogenous operating systems and data guard.

limitations of generic connectivity

Dan, August 26, 2005 - 12:40 pm UTC

This thread has really veered off topic since I last saw it.

I've connected my oracle 9.2 db to FoxPro 8 data using OLEDB (looks like ODBC is desupported since FoxPro 8?), and am running into some limitations, hoping for suggestions of a workaround/solution.

1. The FoxPro data files have to be on the same box as Oracle.
I tried using syntax like \\remote_computer\C$\data in the dialog I got when clicking on the .udl file. "Test Connection" worked fine, but oracle gave errors:
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using OLEDB_SQL](IDBINITIALIZE:INITIALIZE:rc=-2147217887):Extended = Invalid path or file name.
ORA-02063: preceding 2 lines from IMAP

I also tried using a mapped drive but that failed with the same error.

2. Can't reference memo data. Oracle is treating these as longs, though the actual data we have could be cast into smaller, varchar2(4000) or less. But now any query I write that calls these causes me to get disconnected:
ERROR:
ORA-03113: end-of-file on communication channel

Thanks.

Tom Kyte
August 26, 2005 - 1:57 pm UTC

1) you'd have to make the share available to the service, windows isn't like unix with nfs, it is slightly more convoluted.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:241814624807 <code>

2) please contact support for 3113 related issues.

In response to previous HS questions

melissa, September 12, 2005 - 2:31 pm UTC

I got my isql and tsql tests to work correctly however i am trying to set up HS so I can connect from within Oracle 8i to SQL Server 7.0 .
Here are my files what am i doing wrong?
ODBC.ini

[OCARSQL]
Driver = /usr/local/lib/libtdsodbc.so
Description = SQL Server 7.0
Trace = Yes
Servername = OCARSQL
Database = fdb

ODBCinst.ini

[TDS]
Description = v0.52 with protocol v4.2
Driver = /usr/local/lib/libtdsodbc.so
FileUsage = 1

FREEtds.conf

[OCARSQL]
host = ocarsql.host
port = 1433
tds version = 7.0


initOCARSQL.ora

HS_FDS_CONNECT_INFO = ocarsql
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/local/lib/libtdsodbc.so

#
# ODBC specific environment variables
#
set ODBCINI = /etc/odbc.ini


tnsnames.ora


OCARSQL=
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS =(PROTOCOL = TCP)(HOST = enbossdba)(PORT = 1521)
)
(CONNECT_DATA=
(SID = OCARSQL)
)
(HS = OK)
)


listener.ora


(SID_LIST =
(SID_DESC =
(PROGRAM = hsodbc)
(SID_NAME = OCARSQL)
(ORACLE_HOME = /u01/app/oracle/product/8.1.7)
)
)



When i try to restart my listener i get this:
[oracle@enbossdba admin]$ lsnrctl start

LSNRCTL for Linux: Version 8.1.7.4.0 - Production on 12-SEP-2005 14:02:23

(c) Copyright 1998 Oracle Corporation. All rights reserved.

Starting /u01/app/oracle/product/8.1.7/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 8.1.7.4.0 - Production
System parameter file is /u01/app/oracle/product/8.1.7/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/8.1.7/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=enbossdba\)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=enbossdba\)(PORT=2481))(PROTOCOL_STACK=(PRESENTATION=GIOP)(SESSION=RAW)))
TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
NL-00303: syntax error in NV string


Is it possible?

A reader, September 26, 2005 - 9:59 am UTC

Is it possible to use a hs agent with Oracle8i on Linux ? I cannot find my hsodbc files.

Tom Kyte
September 26, 2005 - 10:58 am UTC

no, that feature was added in 10g to the linux port. it was not there in 8i on linux

TNSPING... cha-ching

Duke Ganote, September 28, 2005 - 11:16 am UTC

Using TNSPING to test the connection between setting up the database link was very useful to me recently. I was struggling to connect to an Informix database. I knew the connection was good using TNSPING (this was a step I'd never bothered with before). But I was still having a problem, so I knew it had to be an issue with the database link.

The problem was (drum roll, please) because the username and password were case-sensitive and I had to double-quote & lower-case them in the database link! (Similarly, the Informix tables had lower-case column names although the table names were in upper case, similar to the possibility you noted of lowercase table names, back at </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4406709207206#12421855676182 <code>

NVARCHAR and BIT data_types on SQL Server

Duke Ganote, October 04, 2005 - 12:51 pm UTC

Yulianto from Surabaya, Indonesia wrote: "oracle can't identify some database fields that use 'nvarchar' dan 'nchar' as their data types" (May 10, 2005).

I just thumped against that. 9i doesn't do type conversions for NVARCHAR and BIT ODBC data types (see metalink Notes 241346.1 and 252548.1) -- although 10g does. Since SQL Server 2000 supports our custom application and our production server is Oracle 9i, I just created views on the SS2000 database like:

CREATE VIEW dbo.Policy_VW as
select CAST ( some_bit AS VARCHAR ) AS some_bit_column
, CAST ( some_nvarchar AS VARCHAR ) as some_nvarchar
from dbo.Policy

I possibly could have used DBMS_HS_PASSTHROUGH if I wanted to write some code and didn't have access to the SS2000 database.

"Connectivity to SQL Server"

Oracle User, December 21, 2005 - 3:51 pm UTC

Any one with experience on Transparent Gateway, please share your knowledge to me, is Transparent Gateway really fast than generic ODBC access?



Tom Kyte
December 21, 2005 - 8:00 pm UTC

If the limits of the generic ODBC access are acceptable to you, that is what you should use (the transparent gateways in some cases use odbc themselves, they just add "more stuff")

detailed setup process

jon, December 22, 2005 - 10:19 am UTC

In case anyone is still interested in this topic,here's a document I worked out several months ago.

It's on linux ,using 10g,unixodbc,freetds to connect sql server,wish it can help

</code> http://www.guruwired.com/blog/?page_id=6 <code>


any comment is welcome


Tom Kyte
December 22, 2005 - 11:09 am UTC

thanks much!

No hs or hsodbc

ARU, January 23, 2006 - 9:55 pm UTC

Hi Tom,

I have gone through the docs and this thread and was ready to start the steps mentioned in the docs, but noticed that on my UNIX server there is no hs directory in $ORACLE_HOME or any hsodbc in the $ORACLE_HOME/bin directory.
The original install of oracle on the server was done some time back by someone else. We are using 8.1.6 for the time being. (Upgrading soon).
How do I proceed from here? Is'ent the hs and hsodbc dir and executables a prerequisite?

$ uname -a
OSF1 hlz1trd1.datacentre.nzmp.co.nz V5.1 732 alpha

Also how would I find out if the agent is alredy installed on some other m/c if I wanted to check?

Thanks,
Regards,
ARU.

Tom Kyte
January 23, 2006 - 11:17 pm UTC

It is not available on every platform and this is one it is not on.

listerner verification

Karthikeyan, January 25, 2006 - 11:01 am UTC

hi tom thank for your help you make..

I want to make things to be clear that i am on the right way.

for heterogenous connectivity these are the steps i follow:

1).Modify the heterogeneous init file for our DSN name.
ex init<sid123>.ora....here the sid is for our convention may be anything.

filename:initfruit.ora
content:
HS_FDS_CONNECT_INFO = MYSQL_SERVER
HS_FDS_TRACE_LEVEL = off


2).use the above sid123 in listener and tnsname files refering oracle to take care of the given sid as the service for my DSN.

listener:

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = skarthikeyan)(PORT = 1521))
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = testdb)
      (ORACLE_HOME = C:\ORACLE\ORA92)
      (SID_NAME = testdb)
    )
    (SID_DESC =
          (PROGRAM = HSODBC.exe)
          (SID_NAME = FRUIT)
          (ORACLE_HOME = C:\ORACLE\ORA92)
    )
  )

tnsname.ora:
MYSQL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1521))
    )
    (CONNECT_DATA=
        (SID = FRUIT)
    )
    (HS = OK)
  )

3).give a net service name for our identification let be <dbms123>.

4).create a link in our database for the above created dbms123.
SQL> CREATE DATABASE LINK HSTEST
  2    CONNECT TO "192.168.1.222\ROOT"
  3    IDENTIFIED BY TESTDB
  4    USING 'MYSQL';

i had done all as above but i get an error so

=====================================================================
SQL> select * from "emp"@HSTEST;
select * from "emp"@HSTEST
                    *
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
NCRO: Failed to make RSLV connection
ORA-02063: preceding 2 lines from HSTEST 

=======================================================================
what should i do now to get the things work.
tnsping <netservice name> also works fine..

wat might be the error i am doing


thank a lot.... 

Heterogeneous Services

John, January 27, 2006 - 5:02 pm UTC

Hi Tom:

Read a few rounds of Heterogeneous Services documentation, but still have one question unsolved. Is unix version Oracle need a 3rd party unix odbc driver, in order connect to a MS SQL server? I understand the logic of HSODBC/Listener configuration, just unsure how to build up an ODBC connection on a Unix host...thanks for any clarification

Tom Kyte
January 28, 2006 - 12:54 pm UTC

you need an odbc driver where the hsodbc process runs. If that is on unix, you would need the odbc driver on unix.

if Oracle only exists on unix, the odbc driver would need to be "there"

UNIQUEIDENTIFIER data types (MS SQL) do not appear in HS select results

Bradley Kieser, January 30, 2006 - 11:26 am UTC

Just to let readers of this excellent thread know that there is a problem with HS on Oracle 9 when selecting column types UNIQUEIDENTIFIER on MS SQL. They are excluded from the result set.

All other column types work fine, just this.

Casting the columns to another data type doesn't work either.

Immensely useful!

Willem, February 04, 2006 - 12:09 am UTC

Hi Tom

This site is definitely the best Oracle resource available, thanks for sharing your time and knowledge with us.

doubts about HS.

Aman, March 05, 2006 - 11:12 pm UTC

hi sir.I did follow all the steps and I was successful in the first place only.Than I thought to play around with it.Here are my details:
Excel File = Book1
DSN= source
inithsodbc=initnet.ora
in listener.ora
the sid is defined as "net" for HS.The target database is Orcl which is also mentioned in the file.
in TNSNAMES.ORA ,
the TNS service is defined as "net" for the HS SID net.
than I created dblink and everything went dandy.

Doubts:
1.I firstly did enter the entry forthe target database ORCL than I removed it.I was able to connect both the times. I want to know why I didnt get any error when the target database was not registered in the LISTENER.ORA?
2.After selecting the data from excel file,I gave the next command as shutdown but I got the error, transaction in progress.give committ or rollback first.
Why I received this error?Why Oracle terms this selection of the data from Excel as a transaction?
3.I did rename the files and everything to mess up everything but even after restarting Listener too,the effects were not there and everything was working fine.I shutdown the database than I got the errors of Ora28545/46.why it is so?Why the changes didnt take effect dyanmically?
4.I was trying to mess up the dblink with different user logins.I have done the connectivity of Oracle with Access just now.I made the dblink named "dblink" with the credentials of "test" (Windows operating system user) with <whateverpassword > and it was ok.Than I tried to connect with scott/tiger, I was able to connect again.Than I tried wih a non existing user "abc" with password "xyz".To my surprise I was able to connect again.Why so?And as like before with Excel,with Access too,when I tried to shutdown the database after querying the table from Access database it refused giving ORA-01097.Why it is so?Also with the Excel,I am not able to select one field from the list.It does not let me to do so.With Access I can.Why so?
Here is the cut/paste for the link test.
<quote>
idle> startup
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
sys@ORCL> select table_name from all_tables@link;

TABLE_NAME
------------------------------
MSysAccessObjects
test

sys@ORCL> select table_name from all_tables@dblink;

TABLE_NAME
------------------------------
MSysAccessObjects
test

sys@ORCL> drop database link link;

Database link dropped.

sys@ORCL> create database link test_link
2 connect to
3 "scott" identified by "tiger" using 'net';

Database link created.

sys@ORCL> select table_name from all_tables@test_link;

TABLE_NAME
------------------------------
MSysAccessObjects
test

sys@ORCL> drop database link test_link;

Database link dropped.

sys@ORCL> create database link test_link
2 connect to
3 "abc" identified by "xyz" using 'net';

Database link created.

sys@ORCL> select table_name from all_tables@test_link;

TABLE_NAME
------------------------------
MSysAccessObjects
test

sys@ORCL> select * from test@test_link;

testID FirstName
---------- --------------------------------------------------
3 gunjan
4 sharma

sys@ORCL> shutdown
ORA-01097: cannot shutdown while in a transaction - commit or rollback first
sys@ORCL> rollback;

Rollback complete.
sys@ORCL> select "FirstName" from test@test_link;

FirstName
--------------------------------------------------
gunjan
sharma
</quote>

with best regards
Aman Sharma.

no title..

Aman Sharma, March 10, 2006 - 10:15 pm UTC

hi sir
waiting for the answer:-(.
regards
Aman Sharma.

Tom Kyte
March 11, 2006 - 3:34 pm UTC

I know nothing about dsn's or their "registration", I don't really know what you did exactly, nor what tools you are using (for example, what oracle tool precisely would say "please rollback or commit", none that I am personally familar with). All distributed things begin a transaction by default.

I don't have access, I've never used access, I cannot say anything about access.

I do not promise to personally answer every followup/review that include lots of new questions that involve hitting the page down key over and over again to read it all.

UDB to Oracle

Sami, March 30, 2006 - 5:09 pm UTC

Dear Tom,

In all reviews people used only SQL*Server and none of them used UDB. My requirements is that I need to pull some data from UDB into Oracle database.

So my questions is...

By using either "Oracle Generic Connectivity" or "Oracle Transparent Gateways", is it possible to connect to UDB database from Oracle database?

Thanks in advance.


Tom Kyte
March 31, 2006 - 11:39 am UTC

yes, just get an odbc driver

heck, the best example on this page "integrates" excel with Oracle using odbc

ctl-f for:
Worked example for using Excel through ODBC



A reader, May 16, 2006 - 1:53 pm UTC

There is a limitation with heterogenous services right, that image cannot be transferred over the link? Is there any improvements in 10gR2 or some other option to use for transferring data from Oracle to SQL Server ?

Thanks.

hs service with ms excel

Khurram, July 21, 2006 - 7:33 am UTC

Hi tom i have header record in excel sheet files which are dynamic 
cause its outsource file they keep tampering the header, hs treat it as a cloumn 
which mess the code.

e.g

SQL> DESC storage$@abc.world
 Name                            Null?    Type
 ------------------------------- -------- ----
 GAC Shipping                             VARCHAR2(255)
 F2                                       FLOAT(49)
 F3                                       VARCHAR2(255)
 F4                                       DATE
 F5                                       DATE
 F6                                       DATE
 F7                                       FLOAT(49)

SQL> SELECT SUBSTR('GAC SHIPPING',1,10) "Header",
  2  SUBSTR(f2,1,20) "F2",SUBSTR(f3,1,20) "F3"
  3  FROM storage$@abc.world;

Header     F2                   F3
---------- -------------------- --------------------
GAC SHIPPI                      Container
GAC SHIPPI 20                   CAXU2870092
GAC SHIPPI                      CAXU2882494
GAC SHIPPI                      CAXU2923660
GAC SHIPPI                      CLHU2416241
GAC SHIPPI                      CLHU2431992
GAC SHIPPI                      CRXU1529759
GAC SHIPPI                      CRXU1531587
GAC SHIPPI                      CRXU1532686
GAC SHIPPI                      CRXU1772368
GAC SHIPPI                      CRXU1794388
GAC SHIPPI                      CRXU2840770
GAC SHIPPI                      CRXU2999504
GAC SHIPPI                      CRXU3073375
GAC SHIPPI                      EISU3435028
GAC SHIPPI                      EISU3518570
GAC SHIPPI                      EISU3524418
GAC SHIPPI                      EISU3524790
GAC SHIPPI                      EISU3526431
GAC SHIPPI                      EISU3527633
GAC SHIPPI                      EISU3530771

Header     F2                   F3
GAC SHIPPI                      EISU3532970

23 rows selected.

To get rid of this header user will have to manually delete this header 
record which shouldnt be ,how can i ignore this header record???



Thanx in advance

Khurram


 

Tom Kyte
July 23, 2006 - 7:20 am UTC

not sure that you "can" - it is there in excel, it looks no different from any other row.

Maybe there is something you can use in the where clause to filter it out.

reader

A reader, July 27, 2006 - 11:16 am UTC

Referring to "May 25, 2004" posting

I get a TNS-12533
when I do a tnsping jelly

This is the tnsnames.ora
============================
ORCL101 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl101)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
JELLY =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=tcp)
(HOST=localhost)
(PORT=1521)
)
)
(CONNECT_DATA=(SID=FRUIT))
(HS=OK)
)

Here are the tnsping
=====================
orcl101 successful
jelly TNS-12533


C:\Documents and Settings\username>tnsping orcl101

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_D
CE_NAME = orcl101)))
OK (40 msec)

C:\Documents and Settings\username>tnsping jelly

TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 27-JUL-2006 10:55:02

Copyright (c) 1997, 2003, Oracle. All rights reserved.

Used parameter files:
C:\oracle\product\10.1.0\Db_2\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=tcp) (HOST=localhost) (PORT=1521)
TNS-12533: TNS:illegal ADDRESS parameters

Tom Kyte
July 27, 2006 - 12:19 pm UTC

if your file has stuff that is in column 1 - that is not valid. only the alias name should be there

alias_name = .......
^ column 1 ok

anything else, not ok

did you manually edit these files, or did you use the tools? The file looks "wrong"

reader

A reader, July 27, 2006 - 12:50 pm UTC

I went past tnsping

When trying to use database link, get the following

SQL> select * from all_tables@CUSTARD2
  2  ;
select * from all_tables@CUSTARD2
                         *
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
NCRO: Failed to make RSLV connection
ORA-02063: preceding 2 lines from CUSTARD2 

Tom Kyte
July 27, 2006 - 1:59 pm UTC

see metalink support Note 234517.1

To "A Reader"

Tom G., July 27, 2006 - 1:41 pm UTC

Reader -

I'm hardly an expert in connecting to a SQL Server database
from Oracle, but I ran across this exact same error just
yesterday afternoon. My Oracle database version is 9.2.0.5.

After installing an ODBC driver from Data Direct
(</code> http://www.datadirect.com <code> and making the necessary
changes to tnsnames.ora, listener.ora, and inithsodbc.ora
and also adjusting environmental variables (e.g. ODBC_HOME,
etc.), I received this error. I had forgotten to bounce
both the database and listener! After doing that, my
connection to a SQL Server database worked the first time.

For what that's worth...


reader

A reader, July 27, 2006 - 4:33 pm UTC

To Tom G,

That error looks like a catch all error

In my case, changed the IPC Key to "FRUIT" from default "PNPKEY" and I was able to get the service name from LSNRCTL> status and error went away

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = FRUIT)
(ORACLE_HOME = C:\oracle\product\10.1.0\Db_2)
(PROGRAM = hsodbc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = FRUIT)) <==== was PNPKEY
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
)

reader

A reader, July 27, 2006 - 4:43 pm UTC

What would be the reason to surround column name with quotes

SQL> desc sheet1$@nj
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------
 col1                                               FLOAT(49)
 col2                                               FLOAT(49)
 col3                                               FLOAT(49)
 col4                                               FLOAT(49)
SQL> select "col1" from sheet1$@nj;

      col1
----------
         1
         5
         9

SQL> select col1 from sheet1$@nj;
select col1 from sheet1$@nj
       *
ERROR at line 1:
ORA-00904: "COL1": invalid identifier 

Tom Kyte
July 27, 2006 - 5:43 pm UTC

nj is a database link to sql server or access isn't it?

they use mixed case identifiers, we do not.

Their column name is "col1" in lower case - it is not COL1. You need the quotes to preserve the case (sql is supposed to be case insensitive... quoted identifiers are a way to access mixed case identifiers or identifiers that use special characters)

reader

A reader, July 28, 2006 - 8:25 am UTC

If I want to connect from oracle on unix server to .xls on windows, would the following steps be right:

1. Create a stand alone listener on windows, OH on windows
and SID=FRUIT ( from the example)

2. configure tnsnames on oracle on unix
and create a database link on oracle on unix to
point to the windows listener

Tom Kyte
July 28, 2006 - 7:58 pm UTC

yes.

reader

A reader, August 02, 2006 - 12:31 pm UTC

I was trying to install "hs" as a new install

I followed:
<>
install an oracle listener on
it (second option - 'management/integration'; select CUSTOM and deselect
everything except for net services/listner; if the install does not create the
hs subdirectory, you can extract it manually from disk 2)
<>

'management/integration' does not get to anywhere

How to extract it manually:
There are .jar files in Disk2\stage\components\oracle.rdbms.hs_odbc\9.2.0.1\1\Datafiles

directory. I am not sure how to use this .jar files to extract manually

Tom Kyte
August 02, 2006 - 12:53 pm UTC

You don't really extract things manually from there - best bet would be to utilize support for an installation/configuration question

Installing Generic Connectivity agents on another machine

Tak Tang, August 08, 2006 - 1:03 pm UTC

In retrospect, manually extracting the hetrogenous service agents (July 4th 2004) is a really bad idea, because even if you do not run a database, it probably still counts as another licensable database installation.

Easier and rather more legitimate - install Oracle XE, which IS free. No, scratch that - check licensing terms on OTN for yourself. XE comes with a listener and the Generic Connectivity agents (hsodbc/hsolefs/hsolesql). If you do not need the XE database itself, you can shut it down and delete the datafiles to save space.

Tak


reader

A reader, August 11, 2006 - 5:18 pm UTC

If you select on the intall
database => custom => oracle transparent gateway
it would install HS. Again may have to pay a fee for OTGWY

it was a great thanks

Vamshree from Hyd,India, August 17, 2006 - 2:11 am UTC

hi tom,

The url was really very helpful in understanding the HS and some concepts were explained very well such as editing the iniSID.ora file,tnsname.ora and listener.ora file.

I was able to connect to SQL Server 2000 through Oracle 10g with ODBC drivers.

I am interested in the limitations of using this drivers and also interested to know how the TRANSPARANT Gateways are more helpful for distributed transactions.

I was able to select but was unable to insert and update.

what i tried was :

update test4@custard2 set "name" = 'vam';

and the error was :

ORA-28500 : connection from ORACLE to a NON-ORACLE system returned this message:

The OLEDB provider 'MSDAORA' was unable to begin a distributed transaction.(SQL State : 37000;SQL Code : 2391)

ORA02063: preceding 2 lines from CUSTARD2.

I am also interested to know how to create a database link with a BLANK password with 'sa' as userID.

Thanks & Regards,
Vamshree


Tom Kyte
August 17, 2006 - 8:58 am UTC

sa with a blank password???? ouch, that has to hurt doesn't it.

FROM Oracle 10g release 2 call procedure in stored procedure in mySQL5.0 using HS

richardshan, August 22, 2006 - 12:16 pm UTC

Hi, Tom,

I'm using oracle10g release2 windows version. 
I have configured the HS service to point to my mySQL5.0. 
all those database operations through the HS database links worked fine. 
The only exception is when I try to invoke the stored procedure/functions in mySQL using the documented DBMS_HS_PASSTHROUGH IN 
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_hspass.htm#i997379,
it came up a message see below:
SQL> EXEC DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@MYSQL('call hello(''rshan'')');
BEGIN DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@MYSQL('call hello(''rshan'')'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'EXECUTE_IMMEDIATE' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I checked the database, seems the DBMS_HS_PASSTHROUGH package is not list there. and the listed one is not documented in the above doc

SQL> select oBject_name, owner from dba_objects where object_type='PACKAGE' AND OBJECT_NAME LIKE 'DB
MS_HS%';

OBJECT_NAME
                         OWNER
----------------------------------------------------------------------------------------------------
---------------------------- ------------------------------
DBMS_HS
                         SYS
DBMS_HS_ALT
                         SYS
DBMS_HS_CHK
                         SYS
DBMS_HS_UTL
                         SYS

Any recommandation? thanks in advance! 

Tom Kyte
August 27, 2006 - 3:00 pm UTC

</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_hspass.htm#sthref3384 <code>

execute immediate is a function that returns a binary integer.

you are invoking it as if it were a procedure that doesn't return anything.

Can we define the length of fields?

Gowtham Sen., September 07, 2006 - 4:01 am UTC

HI Tak Tang,

Its really woderfull. I am trying to do this since 1 week. I didn't get it. All of my doubts cleared.

I have one doubt regarding the field lenghts. I created a sample excel sheet contains empno and empname.

which contains 4 reords as follos.
EmpNo     EmpName
122    abc
750    def
760    ghi
376    jklm

SQL>   desc Sheet1$@CUSTARD_07_09_2006
 Name                        Null?    Type
 ---------------------   --------------------------

 EmpNo                                  FLOAT(49)
 EmpName                                VARCHAR2(32512 CHAR)

Here the length of the fields are taken as 49 for float and 32512 for varchar2.

Can we define these field lengths.

Thanks and regards
Gowtham Sen. 

Tom Kyte
September 07, 2006 - 7:19 am UTC

create a view using substr and/or cast.

Alberto Dell'Era, September 20, 2006 - 8:05 am UTC

Many thanks to Tak Tang for the review above "Worked example for using Excel through ODBC" - really excellent (for the technical content and the concise style as well).

Reader

A reader, October 20, 2006 - 4:46 pm UTC

Tom,

This procedure outlined by Tak Tang has ever been published as an oracle document (Magazine article or Note) that I can look at

Tom Kyte
October 20, 2006 - 5:02 pm UTC

well......

right here! You are looking at it are you not?

Tak Tang RULES

Bill Sutton, November 01, 2006 - 7:11 pm UTC

Tak Tang, in my book you are a genius!

Your example of using the microsoft odbc driver manager / odbc driver via a system dsn to allow oracle on unix to commmunicate with sql server on windows was OUTSTANDING.

I had been studying Oracle Heterogeous Services intermittently over the last few days, gaining an in-depth understanding of how it operates, and I was poised to delve into the same for iODBC and FreeTDS on the unix side.

After reading your reply about establishing an oracle listener on the microsoft side and using it instead of unix-based iODBC and FreeTDS, in less than 30 minutes, I successfully connected and retrieved data from sql server.

Absolutely outstanding piece of work on your part and if you're ever in Oak Ridge, Tennessee, USA, dinner is on me :)

Sincerely,
Bill Sutton,
Senior Systems DBA, SAIC
OCM
suttonbi@saic.com

Tom's ok too....

Bill Sutton, November 01, 2006 - 7:14 pm UTC

Tom,

You do a pretty good job yourself ;-)

Thanks much for all the help your articles have given me in the past, present, and future, and yes, if you also find yourself in Oak Ridge, I'd buy you dinner as well :-)

Sincerely,
Bill Sutton

Using HS to Connect 9ir2 to Oracle7.3.4

Kevin, November 28, 2006 - 12:54 pm UTC

Hi

DB1:

SQL> select banner from v$version;

BANNER                                                                          
----------------------------------------------------------------                
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production                
PL/SQL Release 9.2.0.5.0 - Production                                           
CORE    9.2.0.6.0    Production                                                       
TNS for Solaris: Version 9.2.0.5.0 - Production                                 
NLSRTL Version 9.2.0.5.0 - Production        


DB2:

SQL> select banner from v$version;

BANNER                                                                          
----------------------------------------------------------------                
Oracle7 Workgroup Server Release 7.3.4.2.0 - Production                         
PL/SQL Release 2.3.4.2.0 - Production                                           
CORE Version 3.5.4.0.0 - Production                                             
TNS for 32-bit Windows: Version 2.3.4.0.0 - Production                          
NLSRTL Version 3.2.4.0.1 - Production   

I know that "standard" DB Links are out, as v7 is deprecated.  Do you think that an ODBC / HS solution would allow these two versions to connect?

Would be very grateful for any thoughts anyone may have around this....

KG

 

Tom Kyte
November 28, 2006 - 7:50 pm UTC

interesting, if you have a stable odbc driver to 7.3, no reason why it wouldn't work.

Can't Select Field Names in a Micosoft ACCESS table

Steve C, December 05, 2006 - 4:12 pm UTC

Thanks for the excellent instructions, Tak Tank. I got ODBC connections to MS Access and SQL Server easily following your instructions.

Here's the problem I had, though. While I can select all field names from a table (select *) in MS Access, I cannot select the individual field names.

This example works for me -
select * from table1@msaccess1

This example does not work -
select fieldname1 from table1@msaccess1

I receive this error on the second example -
ORA-00904: "FIELDNAME1": invalid identifier

I have tried variations, such as this, without success.
select t.fieldname1, from table1@msaccess1 t

Can anyone help?



Never Mind - Quoted Identifiers fixed the MS Access Field Name Problem

Steve C, December 05, 2006 - 4:20 pm UTC

I just found out that Quoted Identifiers fix my MS Access field name problem. This query does work - - -

select "Field1" from table1@msaccess1

I just had to use the quotes and capitalize the first letter of the field name.

Gateway Error Detail

jc, December 11, 2006 - 3:54 pm UTC

I've been using gateway for about 2 years now, the error descriptions I get back pains me... I have never got back the actual error. All i get is a very generic error like;

"ORA-28500: CONNECTION FROM ORACLE TO A NON-ORACLE SYSTEM RETURNED THIS MESSAGE: [TRANSPARENT GATEWAY FOR MSSQL]"

And that's it no details from the sorce system which is Sql/Server in my case. Is there another error msg to trap for other then "errmsg"?

Connection to an Excel workbook

jk, March 09, 2007 - 11:51 am UTC

Great thread. Thank you! I have never connected to an Excel workbook, but I need to do it now. There is one area where I am not clear. What needs to be setup on the server versus on the client. I have my dsn and the hs folder setup on the XP client. However, I am not clear where I need to modify tnsnames and the listener.
Tom Kyte
March 12, 2007 - 3:57 pm UTC

tnsnames = client
listner = server

to: jk from Philly re: Connecting to Excel

A reader, March 12, 2007 - 1:28 pm UTC

"What needs to be setup on the server versus on the client"? It's all server for Excel: the Excel file must be on the same Win box as Oracle. For databases like SQL Server, DB2 and the like, they don't have to be on the same box, probably because databases are designed to have multiple client connections. Excel is designed to be single-user; it's just very limited.


Excel data source

A reader, March 12, 2007 - 1:41 pm UTC

Thank you for the clarification. So, for Excel both the odbc driver and the Oracle database need to be on the same server. I just started looking at the odbc driver for Unix from DataDirect. I might have to consider it. Any other experiences?

RE: Any other experiences wtih Excel?

A reader, March 12, 2007 - 1:51 pm UTC

Any help...please!

Will, March 13, 2007 - 7:26 am UTC

Hi and thanks in advance for any help!

I am trying to setup a link between Oracle (on Windows platform) and a DB2 database, but I can't quite get it to work...

The error I am getting (when running a SQL query with the db link) is:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC]
ORA-02063: preceding 2 lines from UKINT



These are the steps I have done:

1. Connected as sysdba and ran CATHS.sql

2. Created (client access) ODBC datasource "HS_UKINTTST"(tested with WinSQL - connects/returns data OK)

3. Created inithsdb2.ora file (there was no file to copy under /hs/admin/ to copy):

HS_FDS_CONNECT_INFO = HS_UKINTTST
#HS_FDS_TRACE_LEVEL = 4
#HS_FDS_TRACE_FILE_NAME = C:\hsodbc.trc

4. Modified listener.ora

# LISTENER.ORA Network Configuration File: C:\oracle\ODS901\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = EL017879)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\ODS901)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = RproODS)
(ORACLE_HOME = C:\oracle\ODS901)
(SID_NAME = RproODS)
)
(SID_DESC=
(SID_NAME=hsdb2)
(ORACLE_HOME = C:\oracle\ODS901)
(PROGRAM=hsodbc)
)
)


5. Modified tnsnames.ora:

HS_UKINTTST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = EL017879)(PORT = 1521))
(CONNECT_DATA = (SID=hsdb2))
(HS=OK)
)

6. Created database link:

create database link UKINT
connect to "me" identified by "mypwd"
using 'HS_UKINTTST';

7. Run SQL:

select count(*) from f59rpim@UKINT


tnsping resolves the service name ok, and running lsnrctl services says Service "hsdb2", status UNKNOWN, has 1 handler(s) for this service.... I get the same error message even if I remove the inithsdb2.ora file...so I guess it is not getting as far as reading this?

Any pointers would be very very gratefully received!

Thanks again,
Will

SQL

KAMALAKANNAN, March 19, 2007 - 4:35 am UTC

I WANT TO WORK WITH SQL COMMANDS IT IS ASKING USER NAME,PASSWORD AND HOST STRING PLZ GIVE THE DEFAULT USER NAME,PASSWORD AND HOW TO FIND HOST STRING IN MY SYSTEM I AM NEW TO WORK WITH ORACLE
Tom Kyte
March 19, 2007 - 10:13 am UTC

ASK THE PERSON THAT INSTALLED YOUR DATABASE. THEY WILL KNOW.

There are no default usernames and passwords anymore.

The 'host' string (the tns connect string) is sort of up to you - it could be anything in the world, you told us what it was.

Alternative to Heterogeneous service

A reader, March 23, 2007 - 12:28 am UTC

Tom,

We are developing a Oracle connectivity to Sybase solution (can be sitting on different machines but same platform, say Solaris), which mostly do ad hoc queries to various reports. Just wondering if there is any alternative to the Oracle Heterogeneous service using Generic HSODBC agent.

Can we use the following approach (please provide source if so, and comment on the development complexitity and performance vs Oracle HS)?
1. Use external procedures to access Sybase
2. Use jdbc and java stored procedures without DB links

I've heard from forums that the performance of DB link through the HS to Sybase is pretty slow. How is that compared to the Transparent Agent?

Thanks
Tom Kyte
March 23, 2007 - 9:47 am UTC

1) sure, you could do that, but why
2) sure, but see #1

you would not have "ad hoc sql" access - that is, you would not be able to log into sqlplus and issue an arbitrary sql statement against sybase. You would be limited to whatever you programmed.


I've heard that Sybase is really slow on the forums. Now we are even ;)


Alternative to Heterogeneous service

A reader, March 23, 2007 - 12:34 am UTC

Sorry, the last sentence should read as - How fast is the Generic odbc agent compared to the Transparent Gateway agent?

Thanks
Tom Kyte
March 23, 2007 - 9:47 am UTC

They are comparable - they both do basically the same thing at that layer. The transparent gateway adds functionality.

Will from UK

Tak Tang, March 23, 2007 - 3:54 pm UTC

To Will from UK, dated 13th March 2007

Since you are missing the templates under .\hs\admin\, I suspect you might not have Generic Connectivity installed. Look in your oracle bin directory for a "hsodbc" exe. If you do not have one, then you definitely do not have generic connectivity installed.

The Oracle 9 installer inventory ("Installed Products" button) lists "Generic Connectivity Common Files" under "Oracle Transparent Gateways", so to install Generic Connectivity, you need to install the main "Oracle Transparent Gateway" item, but you do not need to install any of the system-specific gateways.

Under Oracle 10, the "Generic Connectivity Common files" are "required", so they are always installed.


Tak

10g and Sql server on Same Box

ian Gallacher, April 02, 2007 - 8:29 am UTC

Hi Tom,

Have searched ask tom but haven't found any comments on installing 10G on a windows server that is already running a sql server.

Is there any technical issues to installing 10g on same machine?

Thanks

Ian

Tom Kyte
April 03, 2007 - 8:47 pm UTC

you would have to define a "sql server" since we provide a "sql server - a server that you give sql and it answers" and have been for longer than anyone :)

As far as I know, the existence of another database would just have the obvious issues - eg: tuning = not going to happen. Just like if you have two oracle instances on a machine.

ian gallacher, April 04, 2007 - 6:32 am UTC

Hi Tom,

Thanks for getting back to me

Windows 2000 server, Microsoft Sql server ( don¿t know version ). Oracle 10G2

Installed 10G2 using default and imported database from 8i without any problems

Created two users and logged in via sqlplus and forms app via web browser without any problems

Stopped/started database ok

When setting up ¿backup¿ later in day via exp hit
ORA-00020: maximum number of processes(%s) exceeded
Also encountered when tried to run sqlplus

Stopped EM service and Isqlplus service and error went away !

Haven¿t re-booted server since ¿live¿ for SQL server users

Don¿t have spec as such of server ¿ was allocated to me when arrived on site

Export worked fine and now back home investigating error

Any thoughts

Ian

Tom Kyte
April 04, 2007 - 10:27 am UTC

you exceeded the number of configured processes.

perhaps people were using isqlplus and created lots of sessions - you exceeded what you had configured.

number of processes exceeded

John, April 04, 2007 - 6:50 am UTC

Ian,

Seems pretty obvious (at least for an Oracle DBA) - up the "processes" parameter and bounce the db:

alter system set processes=<whatever> scope=spfile;
shutdown immediate;
startup;


Nothing really to do with having that other database-like software installed <grin>

ian Gallacher, April 04, 2007 - 10:54 am UTC

Hi John,

Fine with upping the processes parameter BUT am trying to find out why it is occurring ! Have installed 9i/10g/xe many times on a variety of platforms (Xp,win2003 servers etc ) and have never had this error before.

Wouldn¿t normally install on server with other databases but in this case had no option!

Thanks

Ian

PS

Thought I¿d be a smarty and changed processes to 5 on my database to try and get error !

Oops ¿ can¿t open database now ¿ returning to backup !

migrate sqlserver to oracle

Chitta, August 30, 2007 - 10:35 am UTC

Hi Tom,
I am using Sql server 2000 DB and i want to migrate to oracle 9i.Could you please advice me what are all the precaution steps that i need to take? and what are all the prerquisties i need to collect from the DB?

also please give me any link regarding this.

Thanks&regards,
Chitta.
Tom Kyte
September 04, 2007 - 5:22 pm UTC

you need to read this:
http://www.oracle.com/pls/db102/portal.portal_db?selected=3#index-CON
from cover to cover first - honestly, we are very different databases.

Oracle 10g to SQl Server 2000

Mark, January 16, 2008 - 3:02 pm UTC

What route do you recommend for us to take to connect from Oracle 10g on a Linux box to SQL Server 2000. We are going to create a trigger that updates our SQL Server DB from Oracle. We have a already looked into Transformation Gateways which requires additional software. We were considering using Generic Connectivity but decided to ask you first. Thanks in advance!
Tom Kyte
January 16, 2008 - 4:06 pm UTC

what a horrible idea. to make the availability of Oracle dependent on sqlserver.

First, you'd need something that supported 2 phase commit - so we can safely commit and rollback - you're availability would be horrible - upgrades on either end would be a nightmare.

So, my suggestion will be..... do not do this, do not do 'synchronous replication'

Instead, the trigger can queue a message in AQ and an external process can dequeue this message and apply the change to sqlserver.

if sqlserver is up, great
if sqlserver is not up, great
if sqlserver version changes - and program breaks - fix program, not a big deal.
if oracle version changes, see right above

make this loosely coupled - definitely.

connecting to Sql server

Chitta, May 23, 2008 - 7:58 am UTC

Hi Tom,
i have both Oracle 9iR2 as well as Sql server 2000 DB, as per my Requirement i need to get consolidated report on bot db TABLES( Let us assume table a,b,c from oracle and d,e from sql), i can easily create a link from sql server but i have been restricted to do that, So i need to create link from Oracle, so please suggest which is the best way to do?

Regs,
Chitta
Tom Kyte
May 23, 2008 - 9:06 am UTC

umm, did you read this page??!?!?!?!

It, well, sort of points you down that direction....

how to use forms with hetrogeneous dblink

Esraa, May 29, 2008 - 3:32 am UTC

Hi Tom,
I am using Oracle 10G, Windows XP, Access 2007

To connect Access db to Oracle db:
I already finished setup listiner,tnsnames  and creating Db link .
I could select data from access db .
I could update data on access db.
But, only using Oracle Db using my database link 'access_db'.
I.e  I go to SQL>
SQL>  Select .......@access_db;
I find my records displayed.
-------------------------------------
My problem now is :
How to create datablock in Oracle form on that access  table?
I created a view in my schema on that table@access_db.
Then I created datablock on that view in my Oracle Form.
When running the form, it doesn't display any thing.
I also find error at the bottom:
FRM-40505  unable to berform query .
I read about error in help, I understood that :
Cause:  Processing error encountered. The table associated with the current block of the form might not exist, or your username might not have authority to perform the specified action on the table.

What is the problem??
 

Tom Kyte
May 29, 2008 - 8:01 am UTC

it has been over 13 years since I've used forms. I cannot even remember what the screens looked like...

Please use the otn.oracle.com forums - there you will find many forms users that might be able to help you out.



To Esraa

A reader, May 29, 2008 - 8:21 am UTC


FRM-40505 unable to Perform query


Have you checked your grants and synonyms for the objects used by your form?
Seems to me that that's the cause.


Regards,
MH

loading data from oracle to excel

A reader, June 03, 2008 - 10:59 am UTC

Tom/Tak - This is what I am trying to do. I have an Oracle database on a Unix server which has some filesystem space information data. I am trying to get that loaded into an Excel spreadsheet located on my machine. From some posts above, I am seeing some remarks that in order to load Oracle data to excel they have to be on the same box - Is that true? If so, what are my options? This is not a one time thing, the load has to automatically happen every month.

Thanks.
Tom Kyte
June 03, 2008 - 12:11 pm UTC

excel can query odbc data sources right? Oracle is one.

You have confused the direction here - on this page we describe how to get data from EXCEL into ORACLE.

It seems you want to go the other way?

A reader, June 03, 2008 - 5:07 pm UTC

Thats right, I want to get data from Oracle to Excel.

A reader, June 09, 2008 - 1:44 pm UTC

Tom - I didnt get a lot of useful searches from google. If anyone has any good ideas please let me know.
Tom Kyte
June 09, 2008 - 2:22 pm UTC

you are only kidding right?



A reader, August 28, 2008 - 12:25 pm UTC

Tak's example worked flawlessly.

But when I tried to connect to a SQL Server DB, I am getting this error:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. (SQL State: 28000; SQL Code: 18452)

Any idea on what else needs to be done either on the Oracle side, ODBC or the SQL Server side?


Tom Kyte
August 29, 2008 - 10:52 pm UTC

... Login failed for user '(null)' ....

could that be it? perhaps you need to supply a user/pass on the database link.

Access file that has password

Lisan, February 16, 2009 - 12:37 am UTC

how can i create a link to a file that has a password?

CREATE DATABASE LINK custard
CONNECT TO "home\tangt"
IDENTIFIED BY taktangspassword
USING 'jelly';

here,
home = my pc name
tangt = my pc username
taktangspassword = my pc password for that username

Now, how can i create a link to a file that has a password?

Tom Kyte
February 16, 2009 - 12:27 pm UTC

a link to a file?

huh???

I've no idea what you might mean by that at all

Link to MS Access DB

Lisan, February 17, 2009 - 12:17 am UTC

Sorry for confusing query. i mean i have an Access DB that has a password. i couldnt connect to that Access file with the following way as it includes pc name, pc username and password of that user.

CREATE DATABASE LINK custard
CONNECT TO "home\tangt"
IDENTIFIED BY taktangspassword
USING 'jelly';

here,
home = my pc name / hostname
tangt = my pc username
taktangspassword = my pc password for that username

i was able to connect Access DB file that doesnt have any password. i included Access DB password in DSN. but still i was unable to connect. hope u understand now.

thanks
Tom Kyte
February 17, 2009 - 8:31 am UTC

"U" is dead

http://en.wikipedia.org/wiki/U_of_Goryeo

now what??? what can we do for YOU?




can you, using an ODBC application, connect to Access using your DSN? When you can, then we can use it too. Your DSN is not setup (this is an ODBC issue...)


things like "error messages" are very useful as well - saying "it does not work" is not very descriptive.

Upload excel file into Oracle DB without specify the exact file

Ongpit, July 13, 2009 - 12:35 am UTC

Dear Tak / Tom...

Encountered from Tak's method, we need to specify the excel file during the setup of dns, is there any other method to upload excel file without specify it at the first place?
This is because I'll have hourly excel file uploaded into the oracle DB, thus I cannot hard code it 1st.
Also, in my excel file, there will be chinese char, any additional setting for those chinese char??
Tom Kyte
July 14, 2009 - 5:46 pm UTC

if the file is uploaded into the database, how do you expect to use the odbc stuff to read it since it sort of has to be in the file system.

and since it doesn't have a filename when in the database - the entire discussion here is "moot", doesn't make sense.

SQL Server to Oracle

Joe, August 05, 2009 - 11:05 am UTC

I have followed the instructions mentioned to the smallest spec. After creating the DB link, I try to query data and i receive following error.

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 RAMCO

InitRamco.ora
# HS init parameters
#
HS_FDS_CONNECT_INFO = RAMCO
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_TRACE_FILE_NAME = c:\app\TraceLog.trc
SET global_names=false

Listener
LISTENERRAMCO =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENERRAMCO=
(SID_LIST=
(SID_DESC=
(SID_NAME=RAMCO)
(ORACLE_HOME = C:\app\traxadm\product\11.1.0\db_1)
(PROGRAM=hsodbc)
)
)


TNSNames
RAMCO =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522))
(CONNECT_DATA=(SID= RAMCO))
(HS=OK)
)

Can't seem to figure out what's wrong. I see you're very experience, do any help will be appreciated.

Thanks,
Joe
Tom Kyte
August 05, 2009 - 11:59 am UTC

what is in the trace file and/or the listener.log file.

SQL Server to Oracle

Joe, August 05, 2009 - 12:51 pm UTC

I couldn't find any trace files. Here is the weird thing, I did exactly the same set-up in a co-workers computer and it worked. So there are 2 computers where the set-up works in and 2 computers where it doesn't.

Would you know what can be causing that? Also, is there ane easy way to turn on the trace? I updated the initRamco.ora file and set Trace to yes and re-started listener and nothing.

Thanks,
Joe





Tom Kyte
August 05, 2009 - 1:34 pm UTC

anything could be - environment not set, permissions wrong.

what did the listener.log have in it?

what is your SR number with support.

Creating *.ora

John Omana, August 10, 2009 - 11:29 am UTC

Tak Tang did a good job in detailing the steps for using Excel through ODBC...

I have a question with regards to the *.ora files that I have to edit. Please bear with me because I don't have much experience with this kind of stuff.

I can't find a copy of the inithsodbc.ora in ORACLE_HOME\hs\admin sub-directory. Actually I don't even have ORACLE_HOME\hs\admin.

Can I create these files and place them in a directory I like it has to be on the oracle sub-directory? If so, is there another step to do to be able to recognise this by oracle? The reason I would like to create a separate file is because I don't want to tamper with the file the company is using and what I am doing is

Thanks for your help.
Tom Kyte
August 13, 2009 - 8:44 am UTC

sounds like the heterogeneous services are not installed, if you do not have an HS directory tree. You'd need to work with your DBA to get that going - to have the software installed

a simple example

Duke Ganote, October 01, 2009 - 12:13 pm UTC

I can recommend Tim Hall's example of HS on a Win-platform:
http://www.oracle-base.com/articles/9i/HSGenericConnectivity9i.php

A reader, December 23, 2009 - 4:33 pm UTC

Hi tom;

I am trying to automate a db capacity report with your function.


For example:

exec test_dump_csv;

this writes to temp.csv following:
"date" ,"total", "free","used"

When I execute this again

It should append the new result to this file, not overwrite.. How can I do that?


Tom Kyte
December 31, 2009 - 2:21 pm UTC

.. How can I do
that?
...

you would start by reading about UTL_FILE and how files can be opened in append mode.


Or, you could do it like I would do it, which would be ....

to not even consider using a file, that would be the last way I'd want to have this data, you WANT a table - use a TABLE, not a file. That way you can actually *use* the data.

A reader, January 04, 2010 - 2:47 pm UTC

Thanks sir;
Should I use external table in this scenerio?
Tom Kyte
January 04, 2010 - 4:30 pm UTC

no, you would not be putting something like this in the file system, you would just use


INSERT

and put it into a honest to goodness database table.

You know, like an audit trail would
Or an application log would


Put it into a table and the data is useful
Put it in the file system and I say "why bother"

GK, September 09, 2010 - 11:01 am UTC

I followed "trifle" example :) and created DB Link as follows.
But when I run select statement

Select * from TableName@XXX_EH

I get following error message

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 XXX_EH

I am stuck, not sure what to do. Any help would really appreciated.

1. Created System DSN = ICTXXXSCRATCH02DSN

2. Created initSIDICTXXXSCRATCH02.ora file in ORACLE_HOME\hs\admin as below

HS_FDS_CONNECT_INFO = ICTXXXSCRATCH02DSN
HS_FDS_TRACE_LEVEL = OFF

3. Updated tnsnames.ora in ORACLE_HOME\NETWORK\ADMIN as

XXXSQLSVR =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=TCP)(HOST=SCRATCHSVR) # Oracle Server Name
(PORT=1521)
)
)
(CONNECT_DATA=(SID=SIDICTXXXSCRATCH02))
(HS=OK)
)

4. Updated Listener.ora in ORACLE_HOME\NETWORK\ADMIN as

(SID_DESC=
(SID_NAME=SIDICTXXXSCRATCH02)
(ORACLE_HOME=D:\oracle\product\10.2.0\db_1)
(PROGRAM=hsodbc)
)

Restarted the listener

5. CREATE public DATABASE LINK XXX_EH
CONNECT TO SA IDENTIFIED BY <PWD>
USING 'XXXSQLSVR'


GK, September 13, 2010 - 8:37 am UTC

One thing I noticed that there is no hsodbc.exe in ORACLE_HOME\bin directory. I am using Oracle 10g and connecting to 64 bit SQL Server.
Tom Kyte
September 13, 2010 - 2:36 pm UTC

time to talk to your DBA if you would like that feature enabled and installed.

assuming you are on a platform that supports it, which you don't give us any information about so....

GK, September 14, 2010 - 6:21 am UTC

After lots of googling, I found out that Oracle 10g 64 bit doesn't support hsodbc. There is no clear documentation from Oracle regarding which works on 32 bit and which on 64 bit.
Tom Kyte
September 14, 2010 - 7:16 am UTC

did you look in metalink?


11gR2 on 64 bit

Duke Ganote, September 14, 2010 - 12:51 pm UTC

hsodbc got renamed to or replaced with dg4odbc. We found 10gR2's 64-bit generic connectivity worked for relatively simple fare, like SQL Server, where Microsoft provided the driver along with the win64 platform. However, we had to wait for IBM to release the 64-bit version of DB2Connect in order to access DB2 v8 on z/OS; by then we'd upgraded to 11gR2, as I recall.
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.rn.doc/doc/c0024100.htm

connectivity to sql server

Rakesh, October 26, 2010 - 1:55 am UTC

our version of oracle is 10.2.0.4 we have hsodbc existing on our machine,can we connect to sql server 2008 using hsodbc or do we need to install DG4ODBC on our machine.

Thanks
Rakesh
Tom Kyte
October 26, 2010 - 7:54 pm UTC

you are good to go

storege of image file in database

sibsprasad, November 07, 2011 - 12:38 pm UTC

how can i store images in database using plsql,
plz give me some example with the procedure.
thank u sir....
Tom Kyte
November 07, 2011 - 3:37 pm UTC

it depends, it could be as easy as just calling dbms_lob.loadfromfile

but it depends. What is the CLIENT here, what is the environment. Are you a visual basic program calling the database, are you trying to load files that exist on the server into the database, are you building an application with APEX (application express) etc etc etc...

connecting from oracle 8

Arbee, November 30, 2011 - 7:57 am UTC

I'm trying to make a connection to sql server from an ORACLE 8.0.4 (I know, it's ancient) but can't seem to get it to work.
I've read a lot of articles on the internet regarding the Heterogeneous Services but just can't get it to work on my system. Therefor I tried Tak Tang's example from this thread on connecting to an Excel workbook ( http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206#18830681837358 ) but that gives me the same problem as with connecting to sql server.

Here is what I've got:
initFRUIT.ora
HS_FDS_CONNECT_INFO = SPONGE
HS_FDS_TRACE_LEVEL = OFF


LISTENER.ORA
STARTUP_WAIT_TIME_LISTENER = 0

LISTENER =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(Host = localhost)(Port = 1521))
    
CONNECT_TIMEOUT_LISTENER = 10

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = FRUIT)
      (ORACLE_HOME=d:\orant80)
      (PROGRAM=hsodbc)
    )
  )

TRACE_LEVEL_LISTENER = 0


TNSNAMES.ORA
JELLY.WORLD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(Host = localhost)(Port = 1521))
    (CONNECT_DATA = (SID = FRUIT))
    (HS = OK)
  )


These are results of some tests:

TNSLSNR80 status: FRUIT has 1 service handler(s)
TNSPING80 jelly: OK
When I test the Service Name Connectivity I get this: 'The test did not succeed.
ORA-12500: TNS:listener failed to start a dedicated server process'

Here's an extract from my listener.log
30-NOV-11 13:21:37 * 12502
TNS-12502: TNS:listener received no CONNECT_DATA from client
30-NOV-11 13:24:55 * (CONNECT_DATA=(SID=FRUIT)) * (ADDRESS=(PROTOCOL=tcp)(HOST=128.10.0.8)(PORT=4045)) * establish * FRUIT * 12500
TNS-12500: TNS:listener failed to start a dedicated server process
 TNS-12560: TNS:protocol adapter error
  TNS-00530: Protocol adapter error


One thing I noticed is that I do not have an hsodbc.exe file in my \bin, should it be there?
(I also tried Tgmsql80 in stead of hsodbc as program in Listener, resulting in an extended time of testing the connectivity but with the same result)

Is there any way I can test parts of the connection to limit the location of the problem? (e.g. does the listener get to the TNS)


To retrive out parameter

AR Thangal, November 11, 2012 - 11:57 pm UTC

Dear Tom,

I'm trying to call a procedure in MS SQL Server DB using HS Service form oracle database. the procedure was executed successfully, and i can see the result in MS SQL Server. but i have one OUT Parameter in this procedure, it should retrieve one value to oracle database. but it always retrieve Nothing. is there any limitations in retrieving data from MS sqlserver in the out parameter , or is there any further settings needed?....

following my code :
declare
c INTEGER;
nr integer;
v_jv VARCHAR2(20);
begin
c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@MSDB;
DBMS_HS_PASSTHROUGH.PARSE@MSDB(c,'begin; IMP_NEW_TRACER_NO &?; end;');
DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE@MSDB(c, 1, v_jv, null);
nr := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@MSDB(c);
DBMS_HS_PASSTHROUGH.GET_VALUE@MSDB(c, 1, v_jv);
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@MSDB(c);
commit;
dbms_output.put_line('v_tax='||v_jv);
end;
-------------------------------------------------
TNS LINSTENER :

SID_LIST_LISTENERSQL=
(SID_LIST=
(SID_DESC=
(SID_NAME=SQL)
(ORACLE_HOME= C:\oracle\product\10.2.0\db_1)
(PROGRAM=hsodbc)
)
)

LISTENERSQL =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=My_IP)(PORT=1524))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
-----------------------------------------------------
TNS Names :

SQL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.15.66)(PORT = 1524))
(CONNECT_DATA =
(SID = SQL)
)
(HS = OK)
)
-----------------------------------------------
SQL NET :

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (LDAP,TNSNAMES, ONAMES,EZCONNECT)
---------------------------------------------------------
intiSQL.ora:

HS_FDS_CONNECT_INFO = SQL
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_PROC_IS_FUNC = TRUE
HS_FDS_RESULTSET_SUPPORT = TRUE
-----------------------------------
Please Help Me..

thanks.

Oracle interfaces and datatypes

Lise Parker, April 05, 2013 - 11:35 am UTC

Hi Tom,

Our Oracle database contains PL/SQL that is called from different external interfaces as for instance .NET, .ASP, nservice bus etc.
I do not believe that our external facing PL/SQL should return any data that is not typed using an ANSI datatype or an Oracle datatype that is 'officialy' mapped to an ANSI type e.g. VARCHAR2 vs CHARACTER.
If my PL/SQL returned a date, then I think it should be returned as say a character with a defined format, rather than an Oracle DATE.
I know that the interface will take the DATE and there will be some implicit conversion. However, is it not better to use ANSI and remove this assumption? Is this not one of the reasons as to why XML was invented together with stylesheets as validation?

Your advice and thoughts much appreciated.

Thanks
Tom Kyte
April 22, 2013 - 2:11 pm UTC

... I do not believe that our external facing PL/SQL should return any data that is
not typed using an ANSI datatype or an Oracle datatype that is 'officialy'
mapped to an ANSI type e.g. VARCHAR2 vs CHARACTER. ...


why? why in the world would you say that??????


I say "use the heck out of the software you have purchased, use it to its fullest, squeeze everything you can out of it"

why would you want to do this?

connectivity to sql server

A reader, April 09, 2017 - 5:19 am UTC

The link to the main article is broken
Connor McDonald
April 10, 2017 - 12:29 am UTC

Performance issue in Oracle HS

Sobu, June 05, 2017 - 11:08 pm UTC

Hi Tom,
Thanks for this post.

I have a question related to performance while connecting to non-oracle DB using oracle HS.

In our case, our source system is oracle DB and destination is MSSQL database. We run all our logic pertaining to an interface in Oracle, generate the data, and populate staging tables. After this, we communicate data from oracle to MSSQL using oracle HS DB link connection.

My problem is, I cannot do a direct insert as "INSERT INTO dbo.ml_subclass_import@SQLSERVER_LINKML SELECT 1000, sub_class_1_id, sub_class_1_name FROM ml_subclass_export;", this errors out with ORA-02025. So i had to loop through as below. It working, but the problem is its taking 45 min to insert 64 k records. Which is not acceptable. I tried running job is multi thread and still it ran in 15 min. Is there any other way?

The other way round, if i run this "INSERT into ml_subclass_export select SUBCLASS_KEY,SUBCLASS_ID, SUBCLASS_NAME from dbo.ml_subclass_import@SQLSERVER_LINKML;", since i am inserting into current DB from remote, there is no ORA-02025 restriction, and this query runs in seconds.


-----------------------------------------------------
-- Code to insert from Oracle to remote MSSQL DB.
-----------------------------------------------------
DECLARE

TYPE sub_class_tab IS TABLE OF ml_subclass_export%ROWTYPE;
LP_sub_class_tab sub_class_tab;

CURSOR GET_SUBCLASS_DATA
IS
SELECT sub_class_1_id,
sub_class_1_name
FROM (SELECT NTILE(I_total_thread) OVER (ORDER BY ROWID) AS thread,
10010 subclass_key,
sub_class_1_id,
sub_class_1_name
FROM ml_subclass_export) t;
BEGIN
--
L_mark := 'inserting into ml_subclass_export';
--
OPEN GET_SUBCLASS_DATA;
LOOP
FETCH GET_SUBCLASS_DATA BULK COLLECT INTO LP_sub_class_tab LIMIT L_bulk_limit;
--
IF LP_sub_class_tab.COUNT > 0
THEN
--
FOR i IN LP_sub_class_tab.FIRST .. LP_sub_class_tab.LAST
LOOP
INSERT
INTO dbo.ml_subclass_import@SQLSERVER_LINKML (subclass_key,
subclass_id,
subclass_name)
VALUES (10010,
LP_sub_class_tab(i).sub_class_1_id,
LP_sub_class_tab(i).sub_class_1_name);
END LOOP;
--
COMMIT;
--
END IF;
--
EXIT WHEN LP_sub_class_tab.COUNT < L_bulk_limit;
END LOOP;
CLOSE GET_SUBCLASS_DATA;
EXCEPTION
WHEN OTHERS
THEN
/*O_error_message := SQL_LIB.CREATE_MSG('PACKAGE_ERROR',
SQLERRM || '@ ' || L_mark,
L_program_name,
TO_CHAR(SQLCODE));*/
O_error_message:= sqlerrm || L_mark;
RETURN FALSE;
END DAILY_EXTRACT_MAPLE_LAKE;
--------------------------------------------------------
-------------------------------------------------------
Connor McDonald
June 06, 2017 - 1:56 am UTC

I dont know if HS supports bulk binding, but it would be easy to test. Just change

FOR i IN LP_sub_class_tab.FIRST .. LP_sub_class_tab.LAST 
LOOP 
INSERT 
INTO dbo.ml_subclass_import@SQLSERVER_LINKML (subclass_key, 
subclass_id, 
subclass_name) 
VALUES (10010, 
LP_sub_class_tab(i).sub_class_1_id, 
LP_sub_class_tab(i).sub_class_1_name); 
END LOOP; 


to

FORALL i IN LP_sub_class_tab.FIRST .. LP_sub_class_tab.LAST 
LOOP 
INSERT 
INTO dbo.ml_subclass_import@SQLSERVER_LINKML (subclass_key, 
subclass_id, 
subclass_name) 
VALUES (10010, 
LP_sub_class_tab(i).sub_class_1_id, 
LP_sub_class_tab(i).sub_class_1_name); 


and see how that goes.

If bulk binding does not work, all I can suggest is reversing the process, that is, get your Oracle db to "ping" a message to the SQL Server instance which would then launch its own process to pull the database from Oracle into its own table (rather than the push method you currently have).


Oracle HS with SQL Server

Rajeshwaran Jeyabal, November 02, 2017 - 12:59 pm UTC

Team,

I am going through this link
https://www.experts-exchange.com/videos/447/Connecting-To-SQL-Server-From-Oracle-Using-Heterogeneous-Services-Generic-Gateway.html

and doing a setup in my local machine all works fine and in the end got this error message.
SQL> create public database link demoodbc_link
  2  connect to "demo"
  3  identified by "demo"
  4  using 'demoodbc';

Database link created.

SQL> select * from dept@demoodbc_link;
select * from dept@demoodbc_link
                   *
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 DEMOODBC_LINK


SQL>

Could you help us to understand, what are we missing here?
Chris Saxon
November 02, 2017 - 3:14 pm UTC

MOS note 2169496.1 says the following about this error:

SYMPTOMS

When configuring any Oracle Database Gateway to connect to non-Oracle Databases, you encounter the following error message:

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 DBLINK
 

CAUSE

The ORA-28545 error points to a problem with NET configuration, or a problem with the gateway module.


1 - Problem with the gateway module. If there is an issue during the gateway installation, but it completes without error, sometimes the gateway module is generated, but with a size of zero (‘0’) bytes. For example:


ls -l dg4*
-rwxr-xr-x 1 niamsorx oinstall 0 Dec 18 2009 dg4msql <<<====


Zero bytes in the gateway module means the installation was not successful and you have a problem with the gateway module. This will cause the ORA-28545 error.

2 - Problem with SQL*Net configuration


For example, the hostname specified in the tnsnames.ora points to a machine without an Oracle listener, the listener on that machine is not running, the port number is incorrect, or there is a problem with the gateway module specified in the listener PROGRAM parameter (see Item 1 above).

3 – There is a problem with the listener.ora configuration. The following sample listener.ora appears to be correct, but there is a syntax error. The listener will start but due to lines beginning with open or close parentheses in the first column, there will not be a service handler created for the gateway instance “DUBC1SQL2001”, resulting in an ORA-28545 error message.


LISTENER_GTO11203 =
     (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1548))
     )
SID_LIST_LISTENER_GTO11203 =
(SID_LIST =
(SID_DESC =
     (SID_NAME = DUBC1SQL2001)
     (ORACLE_HOME = M:\apps\oracle\product\11204)
     (PROGRAM = dg4odbc)
)
)
ADR_BASE_LISTENER_GTO11203 = M:\apps\oracle\product\11203

4 – There is a problem with the alias in the tnsnames.ora file configuration. For example, (SID=DUBC1SQL2010) defined in the tnsnames.ora file entry does not match (SID_NAME = DUBC1SQL2001) defined in the sample gateway listener.ora file in Item 3 above.


GTW_ALIAS=
     (DESCRIPTION=
      (ADDRESS=
       (PROTOCOL=TCP)
       (HOST=hostname)
       (port=1548))
      (CONNECT_DATA=
       (SID=DUBC1SQL2010))
      (HS=OK)
     )

 

 

 

SOLUTION

To solve the above problems -


1 - Problem with the gateway module name.


- Reinstall the gateway in a new ORACLE_HOME (i.e., separate from any existing ORACLE_HOME).

2 - Problem with SQL*Net configuration.


- Confirm the listener is stated and confirm the gateway instance has a service handler (i.e. LSNRCTL STATUS).
- Confirm the hostname specified in the tnsnames.ora (HOST=hostname) parameter matches the (HOST=hostname) parameter in the listener.ora file.
- Confirm the instance name specified in the tnsnames.ora (SID=instance) parameter matches the (SID_NAME=instance) parameter in the listener.ora file.
- Confirm the hostname specified in the tnsnames.ora (HOST=hostname) parameter matches the (HOST=hostname) parameter in the listener.ora file.

3 - The listener configuration must have spaces in the column one as shown below:


LISTENER_GTO11203 =
<space> (DESCRIPTION =
<space>  (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1548))
<space> )
SID_LIST_LISTENER_GTO11203 =
<space> (SID_LIST =
<space>  (SID_DESC =
<space>   (SID_NAME = DUBC1SQL2001)
<space>   (ORACLE_HOME = M:\apps\oracle\product\11204)
<space>   (PROGRAM = dg4odbc)
<space>  )
<space> )
ADR_BASE_LISTENER_GTO11203 = M:\apps\oracle\product\11203

4 - The alias in the oracle database tnsnames.ora file should read:


GTW_ALIAS=
<space> (DESCRIPTION=
<space>  (ADDRESS=
<space>   (PROTOCOL=TCP)
<space>   (HOST=hostname)
<space>   (port=1548))
<space>  (CONNECT_DATA=
<space>   (SID=DUBC1SQL2001))
<space>  (HS=OK)
<space> )

Oracle HS with SQL Server

Rajeshwaran Jeyabal, November 06, 2017 - 1:20 am UTC

Chris,

Thanks for the inputs, after validating the above points, the error still exists.

BTW, we have this (POC) setup available in a Public cloud, if you are interested to play with i can share you the credentials and details in an email to you.


SQL> conn sys/Password-1@orcl as sysdba
Connected.
SQL> select * from dept@demoodbc_link;
select * from dept@demoodbc_link
                   *
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 DEMOODBC_LINK


SQL>

1) got confimed by the DBA team, it is a 12c enterprise installation and no problem with gateway installtion.
2) Host name/Port name/SID_Name on both Tnsnames.ora and Listener.ora are same, please see below.

C:\Users\Administrator>type C:\OracleDB12c\product\12.1.0\dbhome_2\NETWORK\ADMIN\listener.ora
# listener.ora Network Configuration File: C:\OracleDB12c\product\12.1.0\dbhome_2\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\OracleDB12c\product\12.1.0\dbhome_2)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\OracleDB12c\product\12.1.0\dbhome_2\bin\oraclr12.dll")
    )
        (SID_DESC =
      (PROGRAM = dg4odbc)
          (SID_NAME = demoodbc)
          (ORACLE_HOME = C:\OracleDB12c\product\12.1.0\dbhome_2)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = win2012base)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )


C:\Users\Administrator>type C:\OracleDB12c\product\12.1.0\dbhome_2\NETWORK\ADMIN\tnsnames.ora
# tnsnames.ora Network Configuration File: C:\OracleDB12c\product\12.1.0\dbhome_2\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = win2012base)(PORT = 1522))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = win2012base)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

demoodbc=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = win2012base)(PORT = 1522))
    (CONNECT_DATA =
      (SID_NAME = demoodbc)
    )
        (HS=OK)
  )


C:\Users\Administrator>

3) Yes, The listener configuration have spaces.
4) Yes, The alias in the oracle database tnsnames.ora file have spaces.
5) The listener status is UNKNOWN for a long time, after the listener got reloaded. Don't it need to change into READY status? please advice.
C:\Users\Administrator>lsnrctl

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 05-NOV-2017 17:16:51

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=win2012base)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date                05-NOV-2017 16:49:22
Uptime                    0 days 0 hr. 27 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\OracleDB12c\product\12.1.0\dbhome_2\network\admin\listener.ora
Listener Log File         C:\OracleDB12c\diag\tnslsnr\win2012base\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=win2012base)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=win2012base)(PORT=5500))(Security=(my_wallet_directory=C:\ORACLEDB12C\admin\orcl\xdb_walle
t))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "demoodbc" has 1 instance(s).
  Instance "demoodbc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

Chris Saxon
November 06, 2017 - 11:18 am UTC

In your tnsnames:

    (CONNECT_DATA =
      (SID_NAME = demoodbc)
    )


Surely that should just be SID = ..., not SID_NAME?

Oracle HS with SQL Server

Rajeshwaran Jeyabal, November 06, 2017 - 11:31 am UTC

Thanks Chris, that helps.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database