How to Import/Export Data To-From Oracle 8i Tables

[Ray Vonhollen]

Is there any command line utility, similar to bcp (bulk copy program)
that one can use to Import and or Export data from Oracle 8i tables?
Usually format is csv, pipe delimited or tab delimited text files.

Thanks in advance for your help.

Ray

[Vladimir Ivanovich]

Ray,

First you have to create a parameter file.
Export example: parfile_name 'D:\EXP_TEST.txt'

TABLES=SHEMA_NAME.TABLE1,SHEMA_NAME.TABLE2,SHEMA_NAME.TABLE3
FILE=D:\EXP_TEST.dmp
LOG=D:\EXP_TEST.log
COMPRESS=N
DIRECT=Y
FEEDBACK=1000
STATISTICS=NONE
CONSISTENT=Y

Thank run from the command line:
EXP.EXE SYS/<password>@<SID> PARFILE=D:\EXP_TEST.txt

See Oracle Docs for more info, or type exp help=y at the command prompt

Vladimir Ivanovich, Oracle DBA

"Ray Vonhollen" <RVONH...@pacbell.net> wrote in message
news:3B0AE616...@pacbell.net...

[Ray Vonhollen]

Vladimir,

Thanks very much for the information. It appears that exp.exe and
imp.exe are pretty much dependant upon one anothers file format as
imp.exe needs to use a exp.exe generated file.

I have also found SQL*LOADER via your kindly direction to the Ora Tech
Network site.

One more question, have you ever run across any 3rd-party utility that
easily accepts, as its parameters the
schema,servername,password,tablename and a comma or pipe delimited text
file?

Thanks again for all your help.

Ray.

[ProNews/2 User]

Also check out NXTRact at http://www.kiyoinc.com/nxtract.html

There is information on Oracle export and import and working with .DMP
files.

On Wed, 23 May 2001 17:44:53, Ray Vonhollen <RVONH...@pacbell.net>
wrote:

[Dmitry]

Hi,

Take a look at Chyfo (http://www.ispirer.com/products/)
Chyfo can export data from Oracle and any other databases to text files
(CSV, TAB delimited, fixed length formats).
Chyfo generates DDL statements like CREATE TABLE, CREATE INDEX for Oracle,
control files for SQLLoader.
This is command prompt tool that can accept many parameters.

This is easy to use tool. For example, the command:
d:>chyfopro /d=ora_base /u=scott /p=tiger /t=dept.% /of=tab
will export all tables in the schema DEPT to TAB delimited text files.

Best regards, Dmitry

"Ray Vonhollen" <RVONH...@pacbell.net> wrote in message

news:3B0BF715...@pacbell.net...

[Randall Roberts]

Ray;

As you've noticed, Oracle import and export are not what one normally thinks
of as imports and exports. They use a proprietary format. And as I guess
you've also noticed, SQL*Loader only goes into the database, not out.

I see your question come up frequently and the answer always seems to come
around to this; write a select statement that embeds the commas, tabs, or
whatever and spool it to a text file.

SPOOL comafile.txt

SELECT col1 || ',' || col2 || ',' || col3...
FROM table

SPOOL off

I first spool a DESC table to a text file so that I have all the column
names to edit down into my select. But the short answer to your question
is, no I don't know of a third party tool that just takes the table name and
will do it for you.

Best!

Randall

Ray Vonhollen <RVONH...@pacbell.net> wrote in message

news:3B0AE616...@pacbell.net...

[Randall Roberts]

Ray;

As you've noticed, Oracle import and export are not what one normally thinks
of as imports and exports. They use a proprietary format. And as I guess
you've also noticed, SQL*Loader only goes into the database, not out.

I see your question come up frequently and the answer always seems to come
around to this; write a select statement that embeds the commas, tabs, or
whatever and spool it to a text file.

SPOOL comafile.txt

SELECT col1 || ',' || col2 || ',' || col3...
FROM table

SPOOL off

I first spool a DESC table to a text file so that I have all the column
names to edit down into my select. But the short answer to your question
is, no I don't know of a third party tool that just takes the table name and
will do it for you.

Best!

Randall

Ray Vonhollen <RVONH...@pacbell.net> wrote in message
news:3B0AE616...@pacbell.net...

[Ray Vonhollen]

Randall,

Thanks for the info. Its extremely useful.

Makes complete sense.

Are there alternate commands to reverse the process on loading a table
from an ASCII delimited file.
In a sense where a file is opened then a subsequent Insert Into
tablename Select col1,col2,col3 etc from delimited file.?

Thanks in advance.

Ray.

[John Dorlon]

I have a utility that can export single tables, multiple tables,
all tables in a schema, single views, multiple views, all views
in a schema, or any sql statement from a command line. You
can export to many formats : insert statments, delimited text,
fixed field spacing, html doc, xml doc, sql loader file, Excel
File.

The utility does a ton of other stuff too. You can download it
at www.ezsql.net. It has a 60 day trial period.

-John

[Randall Roberts]

Ray;

As you've noticed, Oracle import and export are not what one normally thinks
of as imports and exports. They use a proprietary format. And as I guess
you've also noticed, SQL*Loader only goes into the database, not out.

I see your question come up frequently and the answer always seems to come
around to this; write a select statement that embeds the commas, tabs, or
whatever and spool it to a text file.

SPOOL comafile.txt

SELECT col1 || ',' || col2 || ',' || col3...
FROM table

SPOOL off

I first spool a DESC table to a text file so that I have all the column
names to edit down into my select. But the short answer to your question
is, no I don't know of a third party tool that just takes the table name and
will do it for you.

Best!

Randall

Ray Vonhollen <RVONH...@pacbell.net> wrote in message
news:3B0AE616...@pacbell.net...

[Ray Vonhollen]

Randall,

Thanks for the information. Its extremely helpful.

One further question to come complete circle on this thread.

Is there a way to reverse the process and move data from an Ascii
delimited text file and into a table, using a similar method?

Thanks in advance for your help.

Ray