CSV

A CSV file is a file with Character Separated Values (previously Comma Separated Values). Here is a sample CSV file (with comma as separator character):

empno,empname,salary
1,"Larry",1500
2,"Ed",1000
3,"Bruce",1200

CSV to Oracle

One can save a Microsoft Excel spreadsheet to a CSV file and use Oracle's SQL*Loader program to upload the CSV file into a table in the database. Use the following SQL*Loader directive to indicate that the data is in CSV format:

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

Also look at the examples in the SQL*Loader FAQ.

Oracle to CSV

Sample SQL*Plus script to spool table values to a CSV file:

SQL> SPOOL query.csv
SQL> SELECT '"'||ename||'", "'||job||'", '||sal||', '||comm FROM emp;
SQL> SPOOL OFF

Fast solution for huge files

CREATE TYPE collist IS TABLE OF VARCHAR2 (4000)
/
CREATE OR REPLACE PROCEDURE bulk_csv (directory_name VARCHAR2, file_name VARCHAR2, query VARCHAR2)
  AUTHID CURRENT_USER IS
  fh             UTL_FILE.file_type;
  stmt           VARCHAR2 (32767) := NULL;
  header         VARCHAR2 (32767) := NULL;
  curid          NUMBER;
  desctab        DBMS_SQL.DESC_TAB;
  colcnt         NUMBER;
  namevar        VARCHAR2 (32767);
  TYPE cola IS TABLE OF collist
                  INDEX BY BINARY_INTEGER;
  res            cola;
  rcur           SYS_REFCURSOR;
  current_line   VARCHAR2 (32767);
  next_line      VARCHAR2 (32767);
BEGIN curid := DBMS_SQL.open_cursor;
  DBMS_SQL.parse (curid, query, DBMS_SQL.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS (curid, colcnt, desctab);
  FOR i IN 1 .. colcnt
  LOOP
     DBMS_SQL.DEFINE_COLUMN (curid,
                             i,
                             namevar,
                             32767);
  END LOOP;
  IF DBMS_SQL.execute (curid) = 0
  THEN
     FOR i IN 1 .. colcnt
     LOOP
        IF (i > 1)
        THEN
           header := header || ',';
           stmt := stmt || ',';
        END IF;
        header := header || desctab (i).col_name;
        stmt :=
              stmt
           || CASE
                 WHEN desctab (i).col_type IN
                         (DBMS_SQL.Varchar2_Type,
                          DBMS_SQL.Char_Type)
                 THEN
                    '"'||desctab (i).col_name || '"'
                 WHEN desctab (i).col_type IN
                         (DBMS_SQL.Number_Type,
                          DBMS_SQL.Date_Type,
                          DBMS_SQL.Binary_Float_Type,
                          DBMS_SQL.Binary_Bouble_Type,
                          DBMS_SQL.Timestamp_Type,
                          DBMS_SQL.Timestamp_With_TZ_Type,
                          DBMS_SQL.Interval_Year_to_Month_Type,
                          DBMS_SQL.Interval_Day_To_Second_Type,
                          DBMS_SQL.Timestamp_With_Local_TZ_type)
                 THEN
                    'to_char("' || desctab (i).col_name || '")'
                 WHEN desctab (i).col_type = DBMS_SQL.Raw_Type
                 THEN
                    'rawtohex("' || desctab (i).col_name || '")'
                 WHEN desctab (i).col_type = DBMS_SQL.Rowid_Type
                 THEN
                    unsupport datatype : ROWID
                 WHEN desctab (i).col_type = DBMS_SQL.Long_Type
                 THEN
                    unsupport datatype : LONG
                 WHEN desctab (i).col_type = DBMS_SQL.Long_Raw_Type
                 THEN
                    unsupport datatype : LONG RAW
                 WHEN desctab (i).col_type = DBMS_SQL.User_Defined_Type
                 THEN
                    unsupport datatype : User Defined Type
                 WHEN desctab (i).col_type = DBMS_SQL.MLSLabel_Type
                 THEN
                    unsupport datatype : MLSLABEL
                 WHEN desctab (i).col_type = DBMS_SQL.Ref_Type
                 THEN
                    unsupport datatype : REF
                 WHEN desctab (i).col_type = DBMS_SQL.Clob_Type
                 THEN
                    unsupport datatype : CLOB
                 WHEN desctab (i).col_type = DBMS_SQL.Blob_Type
                 THEN
                    unsupport datatype : BLOB
                 WHEN desctab (i).col_type = DBMS_SQL.Rowid_Type
                 THEN
                    unsupport datatype : ROWID
                 WHEN desctab (i).col_type = DBMS_SQL.Bfile_Type
                 THEN
                    unsupport datatype : BFILE
                 WHEN desctab (i).col_type = DBMS_SQL.Urowid_Type
                 THEN
                    unsupport datatype : UROWID
                 ELSE
                    unsupport datatype : '||desctab (i).col_type||'
              END;
     END LOOP;
     stmt := 'select collist(' || stmt || ') from (' || query || ')';
     fh :=
        UTL_FILE.fopen (directory_name,
                        file_name,
                        'W',
                        32767);
     OPEN rcur FOR stmt;
     LOOP
        FETCH rcur
        BULK COLLECT INTO res
        LIMIT 10000;
        current_line := header;
        next_line := NULL;
        FOR f IN 1 .. res.COUNT
        LOOP
           FOR g IN 1 .. res (f).COUNT
           LOOP
              IF (g > 1)
              THEN
                 next_line := next_line || ',';
              END IF;
              IF (  NVL(LENGTHB (current_line),0)
                  + NVL(LENGTHB (next_line),0)
                  + NVL(LENGTHB (res (f) (g)),0)
                  + 3 > 32767)
              THEN
                 UTL_FILE.put_line (fh, current_line);
                 current_line := NULL;
              END IF;
              IF (NVL(LENGTHB (next_line),0) + NVL(LENGTHB (res (f) (g)),0) + 3 > 32767)
              THEN
                 UTL_FILE.put_line (fh, next_line);
                 next_line := NULL;
              END IF;
              next_line := next_line || res (f) (g);
           END LOOP;
           current_line :=
                 CASE
                    WHEN current_line IS NOT NULL
                    THEN
                       current_line || CHR (10)
                 END
              || next_line;
           next_line := NULL;
        END LOOP;
        UTL_FILE.put_line (fh, current_line);
        EXIT WHEN rcur%NOTFOUND;
     END LOOP;
     CLOSE rcur;
     UTL_FILE.fclose (fh);
  END IF;
  DBMS_SQL.CLOSE_CURSOR (curid);
END;
/
CREATE OR REPLACE DIRECTORY tmp AS '/tmp';
EXEC bulk_csv('TMP','emp.csv','SELECT * FROM EMP ORDER BY ENAME')

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#