Exporting Data From Oracle to Flat File

Hi here's an example of how an sqlplus  export script should look if you would like to export data from Oracle to a flat file .

------

set colsep |     -- separate columns with a "|" character

set headsep off  -- remove heading separators

set linesize 1000   -- line size set to 1000 or whatever your maximum length is

set numw 20       -- 20 is the length of numbers, you set this at whatever length you want your numbers to be. This should also avoid getting numbers in scientific notations such as 1.77382E... etc.

set pagesize 0   -- no headers

set trimspool on -- get rid of trailing blanks set echo off -- so you don't get the data on the screen that slows down the process significantly... if you want to monitor the file you can use the unix command tail -f /usr/loadfiles/loadfile1.dat set feedback off -- no messages

spool /usr/loadfiles/loadfile1.dat -- location where the flat file will be exported... You might have to convert dates, numbers etc. to varchars

select col1,col2,col3,... from tableX  where ... ; -- query for downloading the data... You can put here your "latest data" vs. "cold data" filtering conditions etc.

spool off

exit

------

If you want everything into a single line you can use the concatenate query (without trailing blanks after every column): select col1|| '|' ||col2|| '|' ||col3,... from tableX  where ... In that case you don't need the "set colsep... statement). There are many ways of specifying the query. Also (this is trivial, but worth mentioning) be sure you have enough space and write permissions in the file system you are exporting the file.

You put the text between the two "------" into a file called (for example) script.sql or something similar, then run from the unix prompt as follows:

unix prompt> sqlplus username/password@oracle_connection @script.sql

You can put multiple sqlplus statements into a unix shell script for exporting from multimple sources/tables. Be aware that I did not test this, so you will have to experiment a bit in your specific environment, but it should give you a good conceptual idea on how to do it...