Hi All,
I am currently running a large data export from an old legacy database.
I need to export all tables and data into csv files with a new csv file created for each table as it loops through the tables. Ideally this sort of action would be done using the UTL_FILE or export function in SQL developer. Unfortunately, both of these options are not available because the export is from an Oracle 7.2 environment and I believe UTL_FILE came in 7.3.
The only way that I can think of is by spooling from SQL+.
I have created the following sql file and run it against the database in SQL + but it is not creating the csv files albeit it is displaying the data in the table(s).
Can anyone advise as to where I have gone wrong please?
set serveroutput on;
set lines 80
set head off
set colsep ','
set pages 0
set feed off
spool datamig.sql
select 'set colsep '',''' from dual;
select 'set lines 9999' from dual;
select 'set head off' from dual;
select 'set pages 0' from dual;
select 'set feed off' from dual;
begin
for table_rec in (select table_name from user_tables WHERE table_name IN ('LEGAL_AID_AMENDMENT')) loop
dbms_output.put_line('spool '||table_rec.table_name||'.csv');
dbms_output.put_line('select * from '||table_rec.table_name||';');
end loop;
end;
/
select ' spool off' from dual;
spool off
@datamig.sql
Thanks
Chris