Hello,
I want to export all table in a schema into csv files. I want that the csv files having the same name as the tables.
I have following SQLPlus Code:
connect username/password
set serveroutput on;
set lines 80
set head off
set colsep ','
set pages 0
set feed off
spool runme.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) 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
@runme.sqlI have found this code on the internet. When I enter the username and password on the top of the code, the connection is succeeded. But the rest of the code doas not work. I dont have any SQL Plus experience and therefore i dont understand the complete code. And I dont also need any SQL Plus experience. I only want to export all the tables in the schema in a CSV-file.
iI hope you can help me. I am very thankful. Maybe you can tell me how part of the code i have to adapt. Or Maybe you can give me a niew code for my plan?
Soooo. You're using a tool, but don't want to learn how to use it? Ummm, okaaaayy...
And what do you mean by "it doesn't work"?
Help us out a bit here. Sure the output could be tidied up a bit. But when I run it I get a .csv file for each table:
SQL> ho dir *.csv
Volume in drive C is System
Volume Serial Number is 4416-06A6
Directory of C:\Users\csaxon
10/03/2016 11:30 360 export.csv
1 File(s) 360 bytes
0 Dir(s) 62,632,062,976 bytes free
SQL>
SQL> create table t1 as
2 select level c1, sysdate c2 from dual
3 connect by level <= 10;
SQL> create table t2 as
2 select level c1, 'stuff' c2 from dual
3 connect by level <= 10;
SQL>
SQL> set serveroutput on;
SQL>
SQL> set lines 80
SQL> set head off
SQL> set colsep ','
SQL> set pages 0
SQL> set feed off
SQL>
SQL> spool runme.sql
SQL>
SQL> select 'set colsep '',''' from dual;
set colsep ','
SQL> select 'set lines 9999' from dual;
set lines 9999
SQL> select 'set head off' from dual;
set head off
SQL> select 'set pages 0' from dual;
set pages 0
SQL> select 'set feed off' from dual;
set feed off
SQL> select 'set trimspool on' from dual;
set trimspool on
SQL>
SQL> begin
2 for table_rec in (select table_name from user_tables) loop
3
4 dbms_output.put_line('spool '||table_rec.table_name||'.csv');
5 dbms_output.put_line('select * from '||table_rec.table_name||';');
6
7 end loop;
8 end;
9 /
spool T1.csv
select * from T1;
spool T2.csv
select * from T2;
SQL>
SQL> select ' spool off' from dual;
spool off
SQL> spool off
SQL>
SQL> @runme.sql
SP2-0042: unknown command "SQL>" - rest of line ignored.
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SP2-0042: unknown command "SQL>" - rest of line ignored.
SP2-0042: unknown command "SQL> begin" - rest of line ignored.
SP2-0226: Invalid line number
SP2-0226: Invalid line number
1,09-APR-18
2,09-APR-18
3,09-APR-18
4,09-APR-18
5,09-APR-18
6,09-APR-18
7,09-APR-18
8,09-APR-18
9,09-APR-18
10,09-APR-18
1,stuff
2,stuff
3,stuff
4,stuff
5,stuff
6,stuff
7,stuff
8,stuff
9,stuff
10,stuff
SP2-0042: unknown command "SQL>" - rest of line ignored.
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.
SQL>
SQL> ho dir *.csv
Volume in drive C is System
Volume Serial Number is 4416-06A6
Directory of C:\Users\csaxon
10/03/2016 11:30 360 export.csv
09/04/2018 16:47 220 T1.csv
09/04/2018 16:47 315 T2.csv
3 File(s) 895 bytes
0 Dir(s) 62,632,062,976 bytes freeSo, what's the issue?
Anyway, there's a better way of doing this.
Database Export in SQL Developer.
Find it under Tools -> Database Export...
This includes options to export all the tables into separate csv files. For more details, see:
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/SQLdev3.0_Import_Export/sqldev3.0_import_export.htm?print=preview#t6