Access Oracle on other (non_VMS) machine -> what's needed?

[Willem Grooters]

OpenVMS 8.3-1H1 (Clustered), RdB 7.2
A Pascal application accesses a local RdB database. We need access to an Oracle database on a Unix or Windows system to read specific data (no write access (yet)), in the same executable.
I'm rathjer sure I'll need extra software; form earlier days, I remember SQL*Net, and I've seen TNSNAMES.ORA when SQLSRV is installed on the VMS system. It that indeed sufficient, or do I need more? The Oracle/RdB site gives me too little information (and since I'm not on a system that supports Flash, I cannot access Metalink anymore...)
Or could I do without anything else and jsut access that database with the right parameters?

[Hoff]

ODBC and JDBC are the "low-end" recommendation (when the operations are not tied together into a transaction) and there are client libraries available for those protocols for various platforms including for OpenVMS.

http://labs.hoffmanlabs.com/node/668

The "higher-end" recommendation tends to involve a distributed transaction manager; using RTR and DECdtm transaction manager to connect the databases.

http://labs.hoffmanlabs.com/node/1149

Both Oracle Rdb and Oracle Classic can perform distributed transactions, as well.

Documentation? Folks here can use the local Unix box or Windows box to research and read Oracle Metalink on your behalf; the local box has a functional Flash client. But that's not going to be particularly effective for anyone involved. You'll need to figure out how to deal with the Flash requirements.

[Willem Grooters]

Hoff,

I _think_ you misunderstood the question: It's my OpenVMS application that needs access to the Oracle database, residing on another system, not the other way around. I _know_ it can be done, but I'm not sure what middleware I need extra (on either box).

[labadie_1]

Install Python for a process or for all using the LD devices
http://www.vmspython.org/DownloadAndInstallationPython

And use a tool listed on
http://www.orafaq.com/wiki/Python

such as cx_oracle

[Willem Grooters]

@labadie:
You're referring to the inability to access Metalink, I presume...
The _current_ system is an old PC/Windows2000/IE6 without Flash and I don't have administration rights, required to have it installed...No problem from my own systems (and on the VMS box, it might be an idea - I'll keep that in mind)

[Hoff]

>I _think_ you misunderstood the question: It's my OpenVMS application that needs access to the Oracle database, residing on another system, not the other way around. I _know_ it can be done, but I'm not sure what middleware I need extra (on either box).

This access can range from a database query outside of a transaction context (see the first link I posted for the usual options) to a request arising inside a distributed transaction. The database linkage (and transaction, if applicable) can occur between the application and the server, or between the servers on behalf of the client.

As for what you need on the remote (database) end, you'll first need to get the Flash access sorted out to gain access into Metalink. You'll then need to check with the DBA for the target database; around getting the Oracle ODBC server configured and running running. If you're the DBA, then you'll want to have a look at the documentation around configuring and launching the ODBC server on the Unix or Microsoft Windows box.

While an ODBC client is often integrated into the base platform or into a database client and an ODBC server is often integrated into a server platform or into a database server, OpenVMS itself lacks these libraries and tools. It would be handy to have both; an ODBC client library, and an ODBC server for connections into (for instance) RMS.

The two links I posted earlier include various details around ODBC and JDBC, and links to various associated resources and documentation.

[Hein van den Heuvel]

The standard way would be to just download the Oracle Client software, install that selecting the interface components and use a PRO*C or PRO*COBOL module to use OCI calls to an Oracle server, on box of off box.
The client comes with TNSnames and Message files and the tools like SQLplus, all in an inconvenient Gigabyte sizes tar-ball. Actually, It is 4 Zips, each containing a BACKUP saveset, expanding to 1.4GB, which need to be unloaded to an install directory on an ODS 5 drive. Personnaly I typically use a large RAM drive for the BCK, Install and Temp directories.
And then install patches. Such fun.


Oracle RDB may have some (DBI) components to help you, but I've never needed to investigate that so far.

The Windows-2000 story is of course too sad too comment. Are you supposed to get a job done or not?

Groetjes,
Hein

[John Gillings]

Willem,

I don't understand why you seem to go out of your way to pose questions, which people make a genuine attempt to answer, then you dismiss their answers and present important detail which make the solutions impossible.

If you showed the slightest signs of appreciation it might be worthwhile. For me this is my last response to any of your questions.

[Willem Grooters]

I did not dismiss Hoff's comments, I won't rule out that I may have misjudged the information: The first link seemed to handle handles INCOMING traffic in the first place:

...Connecting an application to a remote database or remote database server and usually a server running Oracle RdB...

...involved with connecting Internet services to an OpenVMS system....

and my question is about access to another system _from_ the VMS box. That may not have been too clear from my question - in that I apologize.
The second link didn't answer the question either, and I could't understand what it had to do with the question.

Hoff's second post however shows, if I'm understanding well, that the ODBC engines on both machines will take care of the required connection. If so, it surely is candidate for a solution. As is Hein's suggestion to install the Oracle client.

Labadie's contribution: shame on me - judged to fast: but it's on python, and that's not what I'm after in this context (unless the Pthon connection can directly be used by the Pascal code)

On the Flash issue: it has nothing to do with the problem; It's just that I couldn't access Metalink when I needed this information, from this system (not mine) and I needed the basic info quickly (otherwise I wouldn't have bothered the forum with the question). For the ordinary work, it's no problem to work with it.

[Robert Atkinson · Accepted solution]

Willem, we use Oracle Client 10Gr2 to access oracle 9i on a Windows server.

As said, you can use SQL*Plus at command line level, or the API's to program it instead.

Rob.

[Willem Grooters]

Will investigate all answers.

(John - I have an answer on your accusation that I will not discuss on-line - if I only had your address: Hp.com doesn't accept)