Re: How to export Oracle data to Non-Oracle db?

Jackling,

To some extent the answer depends on what kinds of columns you have. If you have blobs, longs or user-defined types you may have no alternative to writing a program to do it. With simpler columns, if you have a lot of tables, you may still be better writing a program (although this time you could write SQL of the form:

select col1 || ',' || col2 || ',' || col3 || .... || coln from table1;

and spool the output. Note, for CHAR and VARCHAR2 columns, you might be better writing:
  .. || ',"' || char_colx || '",' || ...

and for DATE columns you need to have something like:

 ... || ',' || to_char( date_col, 'DD-MON-YYYY, HH24:MI:SS' ) || ',' || ...
)

However, if the volume of data is not too great you can use MS Access, or MS Query (an optional extra for Excel).(I guess you could use SQL Server in the same way, but I haven't tried it.)

Install the Oracle client software on a Windows machine. Make sure ODBC for Oracle is installed. Create a new database in Access. Right click on the Tables tab and either import or link. Select an ODBC database and then the Oracle ODBC alias you created. You should be shown a list of tables. Select all the tables you're interested in (one at a time!) and - hey, presto! - you have Oracle in Access.

You can then Analyse in Excel (it's not obvious where this option is so you have to hunt around.) If you get the spreadsheet, you can then export it in either comma separated or fixed length format, ready for SQL*LOADER input.

However, more than a few hundred Mbytes could be difficult this way.

Len

>ver: 8.1.7 Std edition (on Solaris 8)
>
>I'm a newbie in Oracle. And at first glance, it appears to me that there are
>limited ways to export data from other databases (like Sybase, SQL server
>etc.)
>
>To import into oracle, SQL*Loader can read some delimited text files and put
>to oracle db.
>
>But I fail to find a way readily to export or output data from oracle db to
>e.g. Sybase.( In Sybase, there is a utility called bcp which is convenient
>to move(import/export) data across platforms by using Text / ASCII file
>format.)
>
>Question:
>on export -
>- Any way to export/output oracle data into text/ASCII files? or,
>- How can I export/output oracle data to another db platform like Sybase
>etc. in whatever way you can recommend? And we may need to do this export on
>regular basis.
>- I saw something called Open Gateway. Does it serve the purpose?
>on import -
>- We need to import data on regular basis and thus want it to be schedule.
>Is SQL*Loader the only means to serve this purpose or any better suggestion?
>
>hope can learn from expertise.
>thanks,
>jackling.
>
>
>
  Received on Sun Feb 25 2001 - 14:33:14 CST