1

I am writting an sql script to export the content of tables in csv files (one table per file). I managed to write a successful script, thanks to the spool function like this :

spool export.csv append

select 'fielda;fieldb;...' from dual
select fielda,fieldb,....  from table

spool off.

The first problem with this, is that I have to do a select from dual to get only the fields name on the first line.

The second problem with this, is that I have to write each field, and it becomes very painfull when you have 10 tables each having more than 20 fields. So my question was, is there any pl sql function, that takes in parameter only the table name, and export the full content in a csv file.

Thanks in advance.

user2443476
  • 1,935
  • 9
  • 37
  • 66
  • 1
    why dont you use the sqldeveloper to export, which comes with oracle – Exhausted Dec 04 '14 at 10:07
  • I have an oracle database installed on windows server. I cannot install sql developer, I need to develop a sql script, which will be executed thanks to a vbs script every night. So I am trying to optimize the sql script without having to write each filed manually. The ideal, would be an sql script which takes a liste of tables name, and for each, it exports the content in a csv file with the same name. – user2443476 Dec 04 '14 at 10:16
  • FWIW, SQL Developer is included with Oracle Database - it's in $ORACLE_HOME - so you do have it. Not to say a script isn't a valid solution. – thatjeffsmith Dec 04 '14 at 13:50

4 Answers4

2

Below might work for you

set termout off
set serveroutput off
set feedback off
set colsep ';'
set lines 100000
set pagesize 0
set echo off
set feedback off

spool on

spool D:\csv_generator_tmp.sql
select qr from
(select 'select '||a.column_name||'||'';''||' qr,a.COLUMN_ID
from user_tab_cols a
where a.table_name = upper('cust')
and  a.column_id=1
union
select b.column_name||'||'';''||',b.COLUMN_ID
from user_tab_cols b
where b.table_name = upper('cust') and b.column_id<>1
and b.column_id<>(select max(c.column_id) from user_tab_cols c)
union 
select d.column_name||' from cust;',d.COLUMN_ID
from user_tab_cols d
where d.table_name = upper('cust')
and  d.column_id=(select max(d.column_id) from user_tab_cols d))
order by column_id asc;

spool off;

spool on

spool D:\cust.csv
@D:\csv_generator_tmp.sql

spool off;
/
Shantanu
  • 186
  • 1
  • 8
0

No but you can use user_tables and user_tab_cols to select the columns for tables of interest:

select utc.table_name, utc.column_name
  from user_tab_cols utc
  where utc.table_name = 'CIRCUIT'

You could manage this through a cursor and generate your select columns. You can then execute this query.

wmorrison365
  • 5,995
  • 2
  • 27
  • 40
  • The problem with that is that I am getting for one table for example, each fieldname in one ligne, and not a single line with all the fielsnames separated by';' like my from dual request. – user2443476 Dec 04 '14 at 10:51
  • Perhaps but your question was "is there any pl sql function, that takes in parameter only the table name, and export the full content in a csv file". The answer is no, but, there is a table that will let you generically generate the correct sql by table name (and you can use it to generate the name headers). You could define your own "CSV_TABLE" that would include only those table names to export and use that with `user_tab_cols` to govern generat the generation (simple join). It has the makings of your own simple proc, doesn't it? – wmorrison365 Dec 04 '14 at 13:32
0

Its better to go with UTL_FILE. I would refer to user the Ask tom link https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:88212348059

with that you can create the file regularly by calling the function.

Exhausted
  • 1,867
  • 2
  • 23
  • 33
0

You can use Python and cx_Oracle module to extract data to disk in CSV format.

Here’s how you connect to Oracle using cx_Oracle:

constr='scott/tiger@localhost:1521/ORCL12'
con = cx_Oracle.connect(constr)
cur = con.cursor()

After data fetch you can loop through Python list and save data in CSV format.

for i, chunk in enumerate(chunks(cur)):
    f_out.write('\n'.join([column_delimiter.join(row[0]) for row in chunk]))
    f_out.write('\n')

I used this approach when I wrote TableHunter-For-Oracle

Alex B
  • 2,165
  • 2
  • 27
  • 37