Home » SQL & PL/SQL » Client Tools » Delimited output from sqlplus (merged by CM) (pl/sql)
icon4.gif  Delimited output from sqlplus (merged by CM) [message #445731] Wed, 03 March 2010 11:26 Go to next message
devi_vi
Messages: 10
Registered: March 2010
Location: bangalore
Junior Member
hi all.,
i have a requirement to get a delimited output file by executing a select query.

For e.g, select id, name, age from customers;
i need the output as,
id,name,age
123,devi,23
34,abi,20
4900,infy,23

i tried select id||','||name||','||age from customers;
but am getting the following output....
id||','||name||','||age
123,devi,23
34,abi,20
4900,infy,23

But i want to remove those pipes inbetween the column name.... can some one help me in this....

i tried colsep also... but there am getting the output as.,
id,name,age
123, devi, 23
34, abi, 20
4900, infy, 23

some unwanted spaces in between...
but i want the output as this...
id,name,age
123,devi,23
34,abi,20
4900,infy,23

the query which am using is stored in a .sql file....

can someone help me in this....
Re: Delimited output from sqlplus (merged by CM) [message #445733 is a reply to message #445731] Wed, 03 March 2010 11:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SET COLSEP ','
icon4.gif  Re: Delimited output from sqlplus (merged by CM) [message #445736 is a reply to message #445731] Wed, 03 March 2010 12:42 Go to previous messageGo to next message
devi_vi
Messages: 10
Registered: March 2010
Location: bangalore
Junior Member
But if i use colsep, am getting some unwanted spaces between the column values...

how to avoid that....
Re: Delimited output from sqlplus (merged by CM) [message #445738 is a reply to message #445736] Wed, 03 March 2010 12:54 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>how to avoid that....

RTRIM & LTRIM
Re: Delimited output from sqlplus (merged by CM) [message #445741 is a reply to message #445736] Wed, 03 March 2010 13:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
devi_vi wrote on Wed, 03 March 2010 19:42
But if i use colsep, am getting some unwanted spaces between the column values...

how to avoid that....

You are back to your first query.

Regards
Michel

Re: Delimited output from sqlplus (merged by CM) [message #445743 is a reply to message #445731] Wed, 03 March 2010 14:58 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
WITH tmp AS
     (SELECT '123' ID, 'devi' NAME, '23' age
        FROM DUAL
      UNION ALL
      SELECT '34' ID, 'abi' NAME, '20' age
        FROM DUAL
      UNION ALL
      SELECT '4900' ID, 'infy' NAME, '23' age
        FROM DUAL)
SELECT 'id' || ',' || 'name' || ',' || 'age' AS col1
  FROM DUAL
UNION ALL
SELECT ID || ',' || NAME || ',' || age col1
  FROM tmp

[Updated on: Wed, 03 March 2010 14:58]

Report message to a moderator

Re: Delimited output from sqlplus (merged by CM) [message #445859 is a reply to message #445731] Thu, 04 March 2010 07:05 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
You can try something like this


select id || ',' || name || ',' || age "id,name,age"
from   customers
Re: Delimited output from sqlplus (merged by CM) [message #445866 is a reply to message #445859] Thu, 04 March 2010 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This will not work as soon as the alias will have more than 30 characters (depending on the version).

Regards
Michel
Re: Delimited output from sqlplus (merged by CM) [message #445906 is a reply to message #445866] Thu, 04 March 2010 11:35 Go to previous messageGo to next message
devi_vi
Messages: 10
Registered: March 2010
Location: bangalore
Junior Member
thank u so much for your ideas.....
Re: Delimited output from sqlplus (merged by CM) [message #446523 is a reply to message #445906] Tue, 09 March 2010 01:54 Go to previous messageGo to next message
devi_vi
Messages: 10
Registered: March 2010
Location: bangalore
Junior Member
how to change the size of the alias names... its not working if the alias names are greater than 30 chars... is there any ways to change the default size..?
Re: Delimited output from sqlplus (merged by CM) [message #446527 is a reply to message #446523] Tue, 09 March 2010 02:39 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.
Use the other solutions that have been provided.

Regards
Michel
Previous Topic: how to enable dbms profiler
Next Topic: DIM-00020
Goto Forum:
  


Current Time: Sat Dec 03 12:28:17 CST 2016

Total time taken to generate the page: 0.14304 seconds