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

rdhanek · #1 (**permalink**) 2 Weeks Ago

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.

Code:

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.

panyam · #2 (**permalink**) 2 Weeks Ago

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

something like this :

Code:

set colsep=','

radoulov · #3 (**permalink**) 2 Weeks Ago

I think you need something like this:

Code:

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 ----------

Quote:

Originally Posted by panyam

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

something like this :

Code:

set colsep=','

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

Code:

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

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
select data from oracle table and save the output as csv file or .txt file in unix/solaris - script	aemunathan	Shell Programming and Scripting	4	2 Weeks Ago 10:13 PM
how to extract the data from database (oracle) and send the output as an .xls file?	psiva_arul	Shell Programming and Scripting	1	10-13-2009 01:58 PM
load a data from text file into a oracle table	raji35	Shell Programming and Scripting	2	01-15-2009 03:48 AM
To load data from variable to oracle table ....???	Amit.Sagpariya	Shell Programming and Scripting	2	12-15-2008 08:36 AM
To load data from variable to oracle table	Amit.Sagpariya	Shell Programming and Scripting	1	12-15-2008 07:20 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by