select data from oracle table and save the output as csv file

Shell Programming and Scripting
1

Hi

I need to execute a select statement in a solaris environment with oracle database. The select statement returns number of rows of data.

I need the data to be inserted into a CSV file with proper format. For that we normally use "You have to select all your columns as one big string, concatenating each column".
But my select query contains group by and order by functions using some alias names of the columns.
Can someone help me with the changes required to the sql query format to make sure that the data is inserted properly into csv file.

select
e.employee_name emp_name||'  ,'||
e.employee_address emp_add||'  ,'||
d.department_name dest_name

This is not working as there are some alias names for the columns.

2

Better you can use the "set options" available with SQL*PLUS.

something like this :

set colsep=','
3

I think you need something like this:

select emp_name||'  ,'|| emp_add||'  ,'|| dest_name
  from ( 
    select 
      e.employee_name emp_name,
      e.employee_address emp_add,
      d.department_name dest_name
      ...

---------- Post updated at 09:35 AM ---------- Previous update was at 09:26 AM ----------

With colsep the result will depend on linesize and the output could be different from what you expect:

SQL> set lines 132
SQL> set colsep ,
SQL> select ename name, customerid id from t;

NAME      ,        ID
----------,----------
name1     ,         1
name2     ,         2

SQL> select name||', '||id from (select ename name, customerid id from t);

NAME||','||ID
----------------------------------------------------
name1, 1
name2, 2

SQL> set lines 10
SQL> select ename name, customerid id from t;

NAME
----------
        ID
----------
name1
         1

name2
         2


SQL> select name||', '||id from (select ename name, customerid id from t);

NAME||','|
----------
name1, 1
name2, 2