1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Export data from oracle in comma separated value format

Discussion in 'SQL PL/SQL' started by orafan, Mar 11, 2009.

  1. orafan

    orafan Active Member

    Messages:
    23
    Likes Received:
    3
    Trophy Points:
    90
    Hi

    a simple question maybe, is there any way to export data from an oracle query to a comma separated file (csv) format without using commercial tools like TOAD etc. Can it be done with SQL Plus?

    Thanks and btw you guys have a rocking site here!

    Regards
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    yes , One way would be to try some thing as below .

    Code (Text):

    09:19:59 SQL> SET HEAD OFF
    09:20:14 SQL> SET PAGESIZE 0
    09:20:27 SQL> SET LINESIZE 250
    09:20:36 SQL> SPOOL EMP.DAT
    09:20:52 SQL> SELECT  EMPNO||','||ENAME||','||JOB||','||MGR||','||HIREDATE||','|
    |SAL||','||COMM||','||DEPTNO EMPDATA FROM EMP;
    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,0,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.

    Elapsed: 00:00:00.00
    09:20:56 SQL> SPOOL OFF;
     
    Change the Sqlplus setting as per you need.
     
  3. orafan

    orafan Active Member

    Messages:
    23
    Likes Received:
    3
    Trophy Points:
    90
    hey that's brilliant, why didn't i think of that before..

    thanks man! :)
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    And another way will be t spool by changing the COLUMN SEPARATOR (COLSEP) setting of SQLPLUS session as below.

    Code (Text):

    SQL> set pagesize 0
    SQL> set linesize 350
    SQL> show colsep
    colsep " "
    SQL> select * from emp;
          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          0         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> set colsep ,
    SQL> select * from emp;
          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,         0,        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>
     
    But It may not be proper as per your requirement.
     
  5. orafan

    orafan Active Member

    Messages:
    23
    Likes Received:
    3
    Trophy Points:
    90
    hey man.. that's even better... changing the COLUMN Separator.. wow! But i had already exported by the commas in my select query but i will remember this tip for next time.

    Thanks again!