How to Spool to a CSV Formatted File Using Sqlplus

How do I spool to a CSV formatted file using SQLPLUS?

If you are using 12.2, you can simply say

set markup csv on
spool myfile.csv

formatting csv file using sqlplus in shell script

You'd need to tell SQL*Plus how wide you want the columns to be. Assuming I've counted the length of each header correctly

column sys_creation_date format a17
column sys_update_date   format a15
column dl_service        format a10
column eff_date_time     format a13

You may be happier switching, though, to SQLcl since that makes generating CSV files easier. Just

select /*csv*/ * from emp

or

set sqlformat csv
select * from emp

with sqlplus how to spool data into csv where column contains newline

You could replace the linefeeds by usage of the function replace:

select colA,colb, ... REPLACE(colX, chr(10),'\n'), ...

Thus the line feeds will be replaced by \n. You could also delete them this way or change the content of the table before creating the CSV.

Clumsy, but the best sqlplus offers.

how to output any given table in oracle to csv using sqlplus

After searching stack overflow I could not find an exact answer to this problem - therefore I developed my own solution.

Here is the sql script that I wrote:

SET echo off
SET verify off 
SET heading off 
SET pages 50000 
SET feedback off
SET newpage none 
SET termout off
SET linesize 900
SET trimspool on
SET serveroutput on

define table_name = &1
define spool_path = &2
var rc refcursor
column qry new_val capture

SELECT  'select ''"'' || ' || listagg(column_name,' || ''","'' || ') within group (order by column_id) || ' || ''"'' as rec from &table_name' qry 
  FROM user_tab_cols 
 WHERE table_name = '&table_name';



spool &spool_path

SELECT listagg(column_name,',') WITHIN GROUP (ORDER BY column_id) 
  FROM user_tab_cols 
 WHERE table_name = '&table_name';

BEGIN

FOR v_rec IN (&capture) LOOP

dbms_output.put_line(v_rec.rec);

END LOOP;

END;
/

spool off

EXIT

The script requires two parameters - the first parameter is the table name, and the second is the spool path.

Essentially the first query:

SELECT  'select ''"'' || ' || listagg(column_name,' || ''","'' || ') within group (order by column_id) || ' || ''"'' as rec from &table_name' qry 
  FROM user_tab_cols
 WHERE table_name = '&table_name';

Dynamically creates a new select statement (which loads into the &capture substitution variable) using user_tab_cols and listagg to force each column name onto a single line - this dynamically created select statement will essentially concatenate each column together separated by commas. Later when we spool we loop through the dynamically generated select statement to produce each row of data.

The header is produced by a similar query (the first action once we begin spooling), however for this we can just listagg the column header names together directly.

It is easier to understand by testing the two queries on a table of your choice to see the result!

Spooling Serveroutput into a CSV file

You can use SQL%ROWCOUNT and the below select to achieve your requirement

    DECLARE
      l_sql_text  VARCHAR2(32767);
      l_sql_count NUMBER;
    BEGIN
      insert into tttt values(4);

      l_sql_count:= SQL%rowcount;
      SELECT
             (
                    SELECT t2.sql_fulltext
                    FROM   v$sql t2
                    WHERE  t1.prev_sql_id = t2.sql_id
                    AND    t1.prev_child_number = t2.child_number ) prev_sql_fulltext
      INTO   l_sql_text
      FROM   v$session t1
      WHERE  t1.audsid = Sys_context('userenv', 'sessionid');

      dbms_output.put_line('Query,Count');

      dbms_output.Put_line(l_sql_text
      ||','
      ||l_sql_count);
    END;

Output

Query,Count
INSERT INTO TTTT VALUES(4),1

---

Related Topics