Come faccio a eseguire lo spool su un file in formato CSV utilizzando SQLPLUS?

Voglio estrarre alcune query in un formato di output CSV. Sfortunatamente, non posso usare alcun client di fantasia o qualsiasi altra lingua per farlo. Devo usare SQLPLUS.

Come lo faccio?

Puoi anche usare quanto segue, sebbene introduca spazi tra i campi.

set colsep , -- separate columns with a comma set pagesize 0 -- No header rows set trimspool on -- remove trailing blanks set headsep off -- this may or may not be useful...depends on your headings. set linesize X -- X should be the sum of the column widths set numw X -- X should be the length you want for numbers (avoid scientific notation on IDs) spool myfile.csv select table_name, tablespace_name from all_tables where owner = 'SYS' and tablespace_name is not null; 

L’output sarà come:

  TABLE_PRIVILEGE_MAP ,SYSTEM SYSTEM_PRIVILEGE_MAP ,SYSTEM STMT_AUDIT_OPTION_MAP ,SYSTEM DUAL ,SYSTEM ... 

Questo sarebbe molto meno noioso che digitare tutti i campi e concatenarli con le virgole. Puoi seguire un semplice script sed per rimuovere gli spazi bianchi che appaiono prima di una virgola, se lo desideri.

Qualcosa del genere potrebbe funzionare … (le mie abilità sed sono molto arrugginite, quindi probabilmente avrà bisogno di lavoro)

 sed 's/\s+,/,/' myfile.csv 

Uso questo comando per gli script che estrae i dati per le tabelle dimensionali (DW). Quindi, io uso la seguente syntax:

 set colsep '|' set echo off set feedback off set linesize 1000 set pagesize 0 set sqlprompt '' set trimspool on set headsep off spool output.dat select '|', .*, '|' from 
where spool off

E funziona. Non uso sed per formattare il file di output.

Vedo un problema simile …

Ho bisogno di spoolare il file CSV da SQLPLUS, ma l’output ha 250 colonne.

Cosa ho fatto per evitare fastidiose formattazioni di output SQLPLUS:

 set linesize 9999 set pagesize 50000 spool myfile.csv select x from ( select col1||';'||col2||';'||col3||';'||col4||';'||col5||';'||col6||';'||col7||';'||col8||';'||col9||';'||col10||';'||col11||';'||col12||';'||col13||';'||col14||';'||col15||';'||col16||';'||col17||';'||col18||';'||col19||';'||col20||';'||col21||';'||col22||';'||col23||';'||col24||';'||col25||';'||col26||';'||col27||';'||col28||';'||col29||';'||col30 as x from ( ... here is the "core" select ) ); spool off 

il problema è che perderai i nomi delle intestazioni delle colonne …

puoi aggiungere questo:

 set heading off spool myfile.csv select col1_name||';'||col2_name||';'||col3_name||';'||col4_name||';'||col5_name||';'||col6_name||';'||col7_name||';'||col8_name||';'||col9_name||';'||col10_name||';'||col11_name||';'||col12_name||';'||col13_name||';'||col14_name||';'||col15_name||';'||col16_name||';'||col17_name||';'||col18_name||';'||col19_name||';'||col20_name||';'||col21_name||';'||col22_name||';'||col23_name||';'||col24_name||';'||col25_name||';'||col26_name||';'||col27_name||';'||col28_name||';'||col29_name||';'||col30_name from dual; select x from ( select col1||';'||col2||';'||col3||';'||col4||';'||col5||';'||col6||';'||col7||';'||col8||';'||col9||';'||col10||';'||col11||';'||col12||';'||col13||';'||col14||';'||col15||';'||col16||';'||col17||';'||col18||';'||col19||';'||col20||';'||col21||';'||col22||';'||col23||';'||col24||';'||col25||';'||col26||';'||col27||';'||col28||';'||col29||';'||col30 as x from ( ... here is the "core" select ) ); spool off 

So che è un po ‘hardcore, ma funziona per me …

Con le versioni più recenti degli strumenti client, esistono diverse opzioni per formattare l’output della query. Il resto è lo spooling su un file o il salvataggio dell’output come file a seconda dello strumento client. Ecco alcuni dei modi:

  • SQL * Plus

Utilizzando i comandi SQL * Plus è ansible formattare per ottenere l’output desiderato. Utilizzare SPOOL per lo spooling dell’output in un file.

Per esempio,

 SQL> SET colsep , SQL> SET pagesize 20 SQL> SET trimspool ON SQL> SET linesize 200 SQL> SELECT * FROM scott.emp; EMPNO,ENAME ,JOB , MGR,HIREDATE , SAL, COMM, DEPTNO ----------,----------,---------,----------,---------,----------,----------,---------- 7369,SMITH ,CLERK , 7902,17-DEC-80, 800, , 20 7499,ALLEN ,SALESMAN , 7698,20-FEB-81, 1600, 300, 30 7521,WARD ,SALESMAN , 7698,22-FEB-81, 1250, 500, 30 7566,JONES ,MANAGER , 7839,02-APR-81, 2975, , 20 7654,MARTIN ,SALESMAN , 7698,28-SEP-81, 1250, 1400, 30 7698,BLAKE ,MANAGER , 7839,01-MAY-81, 2850, , 30 7782,CLARK ,MANAGER , 7839,09-JUN-81, 2450, , 10 7788,SCOTT ,ANALYST , 7566,09-DEC-82, 3000, , 20 7839,KING ,PRESIDENT, ,17-NOV-81, 5000, , 10 7844,TURNER ,SALESMAN , 7698,08-SEP-81, 1500, , 30 7876,ADAMS ,CLERK , 7788,12-JAN-83, 1100, , 20 7900,JAMES ,CLERK , 7698,03-DEC-81, 950, , 30 7902,FORD ,ANALYST , 7566,03-DEC-81, 3000, , 20 7934,MILLER ,CLERK , 7782,23-JAN-82, 1300, , 10 14 rows selected. SQL> 
  • Versione per sviluppatori SQL pre 4.1

In alternativa, è ansible utilizzare il nuovo /*csv*/ hint in SQL Developer .

 /*csv*/ 

Ad esempio, nella mia versione per sviluppatori SQL 3.2.20.10 :

inserisci la descrizione dell'immagine qui

Ora puoi salvare l’output in un file.

  • SQL Developer Versione 4.1

Novità di SQL Developer versione 4.1, utilizzare quanto segue come comando sqlplus ed eseguire come script. Non c’è bisogno del suggerimento nella query.

 SET SQLFORMAT csv 

Ora puoi salvare l’output in un file.

Se usi il 12.2, puoi semplicemente dire

 set markup csv on 

So che questo è un thread vecchio, tuttavia ho notato che nessuno ha menzionato l’opzione di sottolineatura, che può rimuovere le sottolineature sotto i titoli delle colonne.

 set pagesize 50000--50k is the max as of 12c set linesize 10000 set trimspool on --remove trailing blankspaces set underline off --remove the dashes/underlines under the col headers set colsep ~ select * from DW_TMC_PROJECT_VW; 

È grezzo, ma:

 set pagesize 0 linesize 500 trimspool on feedback off echo off select '"' || empno || '","' || ename || '","' || deptno || '"' as text from emp spool emp.csv / spool off 

È ansible formattare in modo esplicito la query per produrre una stringa delimitata con qualcosa sulla falsariga di:

 select '"'||foo||'","'||bar||'"' from tab 

E imposta le opzioni di output come appropriato. Come opzione, la variabile COLSEP su SQLPlus consente di produrre file delimitati senza dover generare in modo esplicito una stringa con i campi concatenati insieme. Tuttavia, dovrai inserire virgolette attorno a stringhe su qualsiasi colonna che potrebbe contenere caratteri virgola incorporati.

preferisci usare “set colsep” nel prompt di sqlplus invece di modificare i nomi dei colli uno per uno. Usa sed per modificare il file di output.

 set colsep '","' -- separate columns with a comma sed 's/^/"/;s/$/"/;s/\s *"/"/g;s/"\s */"/g' $outfile > $outfile.csv 

Una volta ho scritto un piccolo script SQL * Plus che utilizza dbms_sql e dbms_output per creare un csv (in realtà un ssv). Puoi trovarlo sul mio repository githup .

Usa vi o vim per scrivere sql, usa colsep con un control-A (in vi e vim precedono il ctrl-A con un ctrl-v). Assicurati di impostare la lineize e il pageize su qualcosa di razionale e accendi trimspool e trimout.

spoollo su un file. Poi…

 sed -e 's/,/;/g' -e 's/ *{ctrl-a} */,/g' {spooled file} > output.csv 

Quella cosa sed può essere trasformata in una sceneggiatura. Il “*” prima e dopo il ctrl-A spreme tutti gli spazi inutili. Non è bello che si siano presi la briga di abilitare l’output html da sqlplus ma NON csv nativo ?????

Lo faccio in questo modo perché gestisce le virgole nei dati. Li trasformo in punti e virgola.

È necessario tenere presente che i valori dei campi potrebbero contenere virgole e virgolette, quindi alcune delle risposte suggerite non funzionerebbero, in quanto il file di output CSV non sarebbe corretto. Per sostituire i caratteri di quotazione in un campo e sostituirlo con il carattere di virgoletta doppia, è ansible utilizzare la funzione REPLACE fornita da oracle per modificare una virgoletta singola in virgolette.

 set echo off set heading off set feedback off set linesize 1024 -- or some other value, big enough set pagesize 50000 set verify off set trimspool on spool output.csv select trim( '"' || replace(col1, '"', '""') || '","' || replace(col2, '"', '""') || '","' || replace(coln, '"', '""') || '"' ) -- etc. for all the columns from yourtable / spool off 

Oppure, se vuoi il carattere di citazione singola per i campi:

 set echo off set heading off set feedback off set linesize 1024 -- or some other value, big enough set pagesize 50000 set verify off set trimspool on spool output.csv select trim( '"' || replace(col1, '''', '''''') || '","' || replace(col2, '''', '''''') || '","' || replace(coln, '''', '''''') || '"' ) -- etc. for all the columns from yourtable / spool off 

Potresti usare il suggerimento csv. Guarda il seguente esempio:

 select /*csv*/ table_name, tablespace_name from all_tables where owner = 'SYS' and tablespace_name is not null;