I want to generate the sql to dump all the tables data in the database to flat files, one file per table, one line per record. I was thinking of trying to use the legacy character ascii code 31 as the delimiter to separate the fields and do it from sqlplus. But a problem is how do you get out of having to specify linesize? Isn't there any way to get away from creating fixed sized records in sqlplus? I would prefer each line to just be as large as it has to be instead of setting the linesize to be the absolute maximum. (32767) or figure out what the maximum size is on a per-table basis.
Is there any way to do that?
What I was trying so far (so I would execute each resulting script d30_x somehow.) Ok but how to get rid of that linesize thing?
/* create dump file using us 31 to separate fields */
set wrap off
set linesize
set feedback off
set pagesize 0
set verify off
undef tab;
define myfile=d30_&&tab..sql;
prompt myfile is &myfile;
spool &myfile
prompt set linesize 2500
prompt set pagesize 0
prompt set wrap off
prompt set verify off
prompt spool &tab..lst
prompt select
select lower(column_name)||'||chr(31)
||'
from user_tab_columns
where table_name = upper('&tab') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('&tab'))
order by column_id
/
select lower(column_name)
from user_tab_columns
where table_name = upper('&tab') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('&tab'))
order by column_id
/
prompt from &tab
prompt /
prompt spool off
spool off