Help Required: How can I export my Oracle data into a flat file 
Author Message
 Help Required: How can I export my Oracle data into a flat file
Hello All,

            I am new to oracle, I want to export the table data(From oracle)
alone into flat file.From the flat file I want to import the data into
another database(db2) table.

Can any one help me.

Regards
Antony



Wed, 18 Jun 1902 08:00:00 GMT
 Help Required: How can I export my Oracle data into a flat file

Antony,
            I am not an expert on this matter...  I don't know if there is a
better/more efficient way to accomplish the same.

SPOOL filename
SELECT * FROM table;

The above statement would write all columns in all records to filename.  The
fields will be fixed length.  If you want delimited fields you would have to
write the SQL statement and concatenate the delimiter and columns.  I hope
there's an easier way...

Abey Joseph.



Quote:
> Hello All,

>             I am new to oracle, I want to export the table data(From
oracle)
> alone into flat file.From the flat file I want to import the data into
> another database(db2) table.

> Can any one help me.

> Regards
> Antony



Wed, 18 Jun 1902 08:00:00 GMT
 Help Required: How can I export my Oracle data into a flat file

The easier way is to use a 3rd party tool to export the data :
I use Crystal Reports 8 from seagate and it exports in comma delimited format (
along with many, many others)  which I can use in many other apps (or as a data
file for SqlLoader to use)
many other report writers will allow exports to a dilimited format, as will
Access, Excel etc...

Quote:

>Antony,
>            I am not an expert on this matter...  I don't know if there is a
>better/more efficient way to accomplish the same.

>SPOOL filename
>SELECT * FROM table;

>The above statement would write all columns in all records to filename.  The
>fields will be fixed length.  If you want delimited fields you would have to
>write the SQL statement and concatenate the delimiter and columns.  I hope
>there's an easier way...

>Abey Joseph.



>> Hello All,

>>             I am new to oracle, I want to export the table data(From
>oracle)
>> alone into flat file.From the flat file I want to import the data into
>> another database(db2) table.

>> Can any one help me.

>> Regards
>> Antony

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----==  Over 80,000 Newsgroups - 16 Different Servers! =-----


Wed, 18 Jun 1902 08:00:00 GMT
 Help Required: How can I export my Oracle data into a flat file
spool filename;
select .... blah blah blah
spool off;



Quote:
> Hello All,

>             I am new to oracle, I want to export the table data(From
oracle)
> alone into flat file.From the flat file I want to import the data into
> another database(db2) table.

> Can any one help me.

> Regards
> Antony



Wed, 18 Jun 1902 08:00:00 GMT
 Help Required: How can I export my Oracle data into a flat file

There are a couple of other methods available.
First is the export utility.  You can export a table from the original
database and import it into the target database. Similar to this:
   exp userid=userid/password file=exp.dmp table=table_name
   imp userid=userid/password file=exp.dmp full=y

Also, you can use the copy command (see SQL documentation). I
can't remember the syntax off-hand but it allows you to copy the
entire table or a portion thru the use of a select statement.

Both of these methods are much faster than creating simple flat files.
Hope this helps
DawgByte

Quote:

>The easier way is to use a 3rd party tool to export the data :
>I use Crystal Reports 8 from seagate and it exports in comma delimited format
(
>along with many, many others)  which I can use in many other apps (or as
a data
>file for SqlLoader to use)
>many other report writers will allow exports to a dilimited format, as will
>Access, Excel etc...


>>Antony,
>>            I am not an expert on this matter...  I don't know if there
is a
>>better/more efficient way to accomplish the same.

>>SPOOL filename
>>SELECT * FROM table;

>>The above statement would write all columns in all records to filename.
 The
>>fields will be fixed length.  If you want delimited fields you would have
to
>>write the SQL statement and concatenate the delimiter and columns.  I hope
>>there's an easier way...

>>Abey Joseph.



>>> Hello All,

>>>             I am new to oracle, I want to export the table data(From
>>oracle)
>>> alone into flat file.From the flat file I want to import the data into
>>> another database(db2) table.

>>> Can any one help me.

>>> Regards
>>> Antony

>-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
>http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
>-----==  Over 80,000 Newsgroups - 16 Different Servers! =-----

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----==  Over 80,000 Newsgroups - 16 Different Servers! =-----


Wed, 18 Jun 1902 08:00:00 GMT
 Help Required: How can I export my Oracle data into a flat file
I don't think this will work for importing into db2, per the origianl
question.

One alternate way, especially if you need to format the data, is to
use PL/SQL util_file package.  If you're learning pl/sql, this is a
good way to get into several concepts at once- cursors/selecting,
formatting different data types, file i/o, etc.


Quote:

>There are a couple of other methods available.
>First is the export utility.  You can export a table from the original
>database and import it into the target database. Similar to this:
>   exp userid=userid/password file=exp.dmp table=table_name
>   imp userid=userid/password file=exp.dmp full=y

Jay M. Scheiner
Programmer/Analyst
Wolpoff & Abramson, LLP
remove _nospm_ from email address
Opinions are my own only!
To email, remove _nospm_ from address.


Wed, 18 Jun 1902 08:00:00 GMT
 Help Required: How can I export my Oracle data into a flat file

Quote:

> spool filename;
> select .... blah blah blah
> spool off;



> > Hello All,

> > I am new to oracle, I want to export the table data(From
> oracle)
> > alone into flat file.From the flat file I want to import the data into
> > another database(db2) table.

> > Can any one help me.

Hi Use the previous reply (spool ...) with the addition of fixed length
fields (by substr or column length) or have the fields with comma delimiter.
It'll easier for the DB2 loader to understans which field in it's place.

Quote:

> > Regards
> > Antony

Sent via Deja.com http://www.deja.com/
Before you buy.


Wed, 18 Jun 1902 08:00:00 GMT
 Help Required: How can I export my Oracle data into a flat file
Using SQLPlusPlus (freeware) you could generate a script which will
allow
you to export data to a flat file.

SQL> EXEC S2.BLDDAT('DEPT')
| ----------------------------------------------------------------------
----
-
| SET HEADING OFF
| SET PAGESIZE 0
| SET TERMOUT OFF
| SET FEEDBACK OFF
| SET LINESIZE 2000
| SET TRIMSPOOL ON
| SPOOL sqlpp_dept.dat
| SELECT
|    deptno||CHR(44)
|    ||chr(34)||dname||chr(34)||CHR(44)
|    ||chr(34)||loc||chr(34)||CHR(44)
|    ||deptsal
| FROM sqlpp.dept;
| SPOOL OFF
|
| SET TERMOUT ON

PL/SQL procedure successfully completed.

You can even generate the control file required by SQL Loader to load
the
data back to Oracle.

SQL> EXEC S2.BLDCTL('DEPT')
| ----------------------------------------------------------------------
----
-
| LOAD DATA
| INFILE 'SQLPP_DEPT.dat'
| REPLACE INTO TABLE DEPT
| FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
| TRAILING NULLCOLS(
|    DEPTNO                         ,
|    DNAME                          ,
|    LOC                            ,
|    DEPTSAL                        )
| ----------------------------------------------------------------------
----
-
|
| Use the following command from command line to import data back to
oracle.
| SQLLDR USERID=sqlpp CONTROL=sqlpp_dept.ctl

PL/SQL procedure successfully completed.

regards,
M. Armaghan Saqib
+---------------------------------------------------------------
| 1. SQL PlusPlus => Add power to SQL Plus command line
| 2. SQL Link for XL => Integrate Oracle with XL
| 3. Oracle CBT with sample GL Accounting System
| Download free: http://www.geocities.com/armaghan/
+---------------------------------------------------------------
| SQLPlusPlus now on http://www.ioug.org/
|   "PL/SQL package that extends SQL*Plus to another dimension.
|   Contains a PL/SQL code generator and set of extremely useful
|   utilites with extensive documentation." IOUG Web Site
+---------------------------------------------------------------



Quote:
> Hello All,

>             I am new to oracle, I want to export the table data(From
oracle)
> alone into flat file.From the flat file I want to import the data into
> another database(db2) table.

> Can any one help me.

> Regards
> Antony

Sent via Deja.com http://www.deja.com/
Before you buy.


Wed, 18 Jun 1902 08:00:00 GMT
 Help Required: How can I export my Oracle data into a flat file
Hi,

Try to use Oraxcel LITE to get it into Excel initially. Then use Excel to
save it into a flat file or csv. Oraxcel LITE is free and implements the
feature you require. You can get Oraxcel LITE from

http://www.oraxcel.com

Regards, Gerrit-Jan Linker


Quote:

> The easier way is to use a 3rd party tool to export the data :
> I use Crystal Reports 8 from seagate and it exports in comma delimited
format (
> along with many, many others)  which I can use in many other apps (or as a
data
> file for SqlLoader to use)
> many other report writers will allow exports to a dilimited format, as
will
> Access, Excel etc...


> >Antony,
> >            I am not an expert on this matter...  I don't know if there
is a
> >better/more efficient way to accomplish the same.

> >SPOOL filename
> >SELECT * FROM table;

> >The above statement would write all columns in all records to filename.
The
> >fields will be fixed length.  If you want delimited fields you would have
to
> >write the SQL statement and concatenate the delimiter and columns.  I
hope
> >there's an easier way...

> >Abey Joseph.



> >> Hello All,

> >>             I am new to oracle, I want to export the table data(From
> >oracle)
> >> alone into flat file.From the flat file I want to import the data into
> >> another database(db2) table.

> >> Can any one help me.

> >> Regards
> >> Antony

> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----==  Over 80,000 Newsgroups - 16 Different Servers! =-----



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. export data 2 flat file - unbelievable results

2. data export into flat files

3. formating and exporting data from sql server to a flat file

4. Exporting data to a Flat file

5. append exported data in flat file

6. formating and exporting data from sql server to a flat file

7. Exporting data to flat file.

8. Tools for exporting data to flat file

9. HELP: writing data to flat files from Oracle

10. export to flat file cuts fields to 256 characters, please help

11. Need help exporting tables to flat files

12. How does one export an Oracle table to a Comma Delimited Flat File


 
Powered by phpBB® Forum Software