Home » RDBMS Server » Server Administration » Supressing blank spaces
Supressing blank spaces [message #373827] |
Mon, 14 May 2001 19:25 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Tara Kibler
Messages: 2 Registered: May 2001
|
Junior Member |
|
|
I want to output a few rows of data from a table to a file. Right now, I'm just spooling a file and doing 'select * from table' and my output file has a ton of blank spaces in it. How do I supress the blank spaces and only get the data in the output file, with each column delimited by something (space, comma, etc.)?
I have tried formatting the columns, changing the linesize, 'trimming' the rows. I don't know what else to try....
Thanks,
Tara
|
|
|
Re: Supressing blank spaces [message #373834 is a reply to message #373827] |
Mon, 14 May 2001 22:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Sarada
Messages: 27 Registered: April 2001
|
Junior Member |
|
|
The best thing I would suggest is to use the concanetation.
For eg.SELECT TNAME, TABTYPE, CLUSTERID FROM TAB;
will return always with lot of white spaces immaterial of your formatting and all.
CUSTOMER TABLE
CUSTOMER_INFO TABLE
CUSTOMER_IOT TABLE
CUSTOMER_TAB TABLE
DATE_TST TABLE
DEPT TABLE
EMP TABLE
EMP_VIEW VIEW
EMP_VIEW2 VIEW
EXECUTABLES TABLE
Use as follows:
SET PAGES 0
SELECT TNAME||','||TABTYPE||','||CLUSTERID FROM TAB;
CUSTOMER,TABLE,
CUSTOMER_INFO,TABLE,
CUSTOMER_IOT,TABLE,
CUSTOMER_TAB,TABLE,
DATE_TST,TABLE,
DEPT,TABLE,
EMP,TABLE,
EMP_VIEW,VIEW,
EMP_VIEW2,VIEW,
EXECUTABLES,TABLE,
Ofcourse you need to do some conversions for non-character datatype. But it is worth it. Anyother settings you need can be added and spooled to a file. That file can be used as datafile for SQLLOADER.
HTH
Orashark
|
|
|
Re: Supressing blank spaces [message #373835 is a reply to message #373827] |
Mon, 14 May 2001 22:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Sarada
Messages: 27 Registered: April 2001
|
Junior Member |
|
|
The best thing I would suggest is to use the concanetation.
For eg.SELECT TNAME, TABTYPE, CLUSTERID FROM TAB;
will return always with lot of white spaces immaterial of your formatting and all.
CUSTOMER TABLE
CUSTOMER_INFO TABLE
CUSTOMER_IOT TABLE
CUSTOMER_TAB TABLE
DATE_TST TABLE
DEPT TABLE
EMP TABLE
EMP_VIEW VIEW
EMP_VIEW2 VIEW
EXECUTABLES TABLE
Use as follows:
SET PAGES 0
SELECT TNAME||','||TABTYPE||','||CLUSTERID FROM TAB;
CUSTOMER,TABLE,
CUSTOMER_INFO,TABLE,
CUSTOMER_IOT,TABLE,
CUSTOMER_TAB,TABLE,
DATE_TST,TABLE,
DEPT,TABLE,
EMP,TABLE,
EMP_VIEW,VIEW,
EMP_VIEW2,VIEW,
EXECUTABLES,TABLE,
Ofcourse you need to do some conversions for non-character datatype. But it is worth it. Anyother settings you need can be added and spooled to a file. That file can be used as datafile for SQLLOADER.
HTH
Orashark
|
|
|
Re: Supressing blank spaces [message #373843 is a reply to message #373827] |
Tue, 15 May 2001 11:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
Andrew again...
Messages: 270 Registered: July 2000
|
Senior Member |
|
|
-- Set the column separator to whatever you want. ' ' is the default
set colsep ','
-- Trims trailing spaces off each line outputted.
set trimspool on
-- To show all settings
show all
-- to show a specific setting
show colsep
|
|
|
Goto Forum:
Current Time: Fri Sep 06 16:55:03 CDT 2024
|