Re: Configuring ODBC on Oracle 7.2 / VMS Alpha

drbohner_at_existinglight.net (Daniel Bohner) wrote in message news:<f09efbe8.0202201029.1686136c_at_posting.google.com>...
> Howdy,
>
> It has been a long time since I have played with Oracle - and I have
> forgotten most of the setup to configure ODBC... to allow clients to
> connect using clients such a Brio or Access.
>
> Anyone got a quick cheat-sheet on what I need to do on the Oracle /
> Client side to get this completed?
>
> Also, My client wants to be able to export the data from Oracle to
> other databases (DB2, MS SQL...) Is there a fairly inexpensive route
> to do this? They need this process to be a one shot a year type deal.
>
> Daniel Bohner
> drbohner_at_existinglight.net
> www.existinglight.net

Generally when you install the oracle client on a pc it comes with an odbc administrator which should help. Basically you need to ensure that the client machine has a tnsnames.ora file that points to your oracle db and then create an odbc definition that uses the same name for the instance.

For example, you tnsnames.ora file would look something like:

oracle_connection.world=

    (..... host....

       (sid=instance1)

Then your odbc connection would need to use oracle_connection.

As for moving data around, you have a bunch of options. Depending on how much data you need to move you could just use sqlplus and spool to a file

SQL> spool myfile.txt
SQL> select ....from...where...;

If you want a comma sepearated file:

SQL> set colsep ,
SQL> spool myfile.txt
SQL> select...

You could also use the utl_file functionality that oracle provides. Although in Oracle 7.2 is has some serious limitations - mainly that the each row can only by 3750 bytes (or something like that) in length.

In order to do this you have to add an entry into your init.ora file utl_file_dir = /disk1/...

If you are using SQL Server you can just set up a DTS package to go grab the data out of your oracle table directly. This would most likely be the most efficient because you can read and write the data all on one step.

I am sure that there are other options as well. I hope that this helps.

Good Luck Received on Fri Feb 22 2002 - 00:09:35 CET