Re: Unload Tables
Date: 16 Oct 1999 09:00:37 GMT
Message-ID: <38083eb5$2$209_at_nntp1.ba.best.com>
Johan den Boer wrote:
> Does anyone know how to make an unload of a table to an ascii files
>
Sure, Johan.
To unload the emp table, just make the following script in SQL*Plus:
set heading off -- no column titles set feedback off -- no 'n rows' result messages set linesize 999 -- long lines set pagesize 999 -- long pages
set pause off -- no pauses
spool emp.asc -- make file
[Quoted] select to_char(empno)||','||name||','||title||','||to_char(deptno) from emp
/
spool off -- close the file
this produces the following file:
100,Smith,Clerk,5
101,Jones,Manager,10
If you want fixed length, just do a rpad around the fields and skip the concatenation with comma's :
select
rpad(to_char(empno),5)||rpad(name,10)||rpad(title,10)||to_char(deptno)
from emp
Result:
100 Smith Clerk 5 101 Jones Manager 10
You can also use TOAD (look at quest.com and download the free TOAD for evaluation), which has a table export feature wich creates these files for you. Even with insert statements so you can also export the data. Advantage of doing this yourself is that you can do every selection of data. So if you need to export the names of the departements also, you can change the query with a join to the dept table. Hope this helps !
Mike Philippens
Mike F.M.Philippens
Oracle-Home
E-mail mphilipp_at_oracle-home.com
Visit Oracle-Home _at_ www.oracle-home.com for the best Oracle Resources !
-- Posted via CNET Help.com http://www.help.com/Received on Sat Oct 16 1999 - 11:00:37 CEST