Home » SQL & PL/SQL » SQL & PL/SQL » Number datatype Format (ORACLE 9i -- win2000)
Number datatype Format [message #343478] |
Wed, 27 August 2008 06:53 |
pankajkmeena
Messages: 46 Registered: September 2007
|
Member |
|
|
i have one table having column empno type number(9) .
whenever i spool this column in sql it is displaed as 10 digit format (9 number+ 1 space)
column EMPNO format 000000000
select EMPNO from EMP
where rownum<2;
EMPNO
----------
100212121
Why there is leading space in every number datatype
Thankx in advance
|
|
|
|
Re: Number datatype Format [message #343483 is a reply to message #343480] |
Wed, 27 August 2008 07:08 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
The space is a placeholder for a possible minus (-) sign in case of negative numbers.
That can be switched off by adding an "fm" to the format mask.
SQL> column col format 00
SQL> select 1 col from dual;
COL
---
01
SQL> column col format fm00
SQL> select 1 col from dual;
COL
---
01
SQL>
|
|
|
|
Re: Number datatype Format [message #343830 is a reply to message #343825] |
Thu, 28 August 2008 05:37 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
When you look at the file with a hex-editor you see that there isn't ONE space at the end, but a lot of spaces all the way to the right side of the page width.
You can change that with "set trimspool on"
Then the newline character is printed directly after the last character in the line, in this case the 1.
Of course, if you have additional columns after this one then there still will be spaces between them.
Maybe you should tell us what you are actually try to ACHIEVE with all this.
|
|
|
Re: Number datatype Format [message #343897 is a reply to message #343830] |
Thu, 28 August 2008 07:26 |
pankajkmeena
Messages: 46 Registered: September 2007
|
Member |
|
|
Below is extract of file that i used to upload in database using sqlldr
000039685000039685021372686F9000000010100330415INR30415INR2051070601 000730 CR INT CR B0000396850000000000117600000000000000000000 0000000000000005300033275DEP 000000000
000039685000039685021372687F9000000010200330415INR30415INR8051070601 000730 CR INT CR B0000396850000000000117600p00000000000000000 0000000000000005300033275DEP 000000000
000039685000039685021375379F9000000020100330404INR30404INR2046030601 000730 CR INT CR B0000396850000000011755000000000000000000000 0000000000000005300059828DEP 000000000
some times we require to generate same file(in same format) from database (after upload) in case original file is not available.
Now i am able to get char fields correctly but Number fields are not as per reqiurement.
1) There is leading space
2)column seperator ca not be made blank
3)Flat File contains number in ZONED DECIMAL FORMAT
Below is control file for sqlldr
LOAD DATA
INFILE 'data\GLIFEXT.dat'
APPEND
INTO TABLE glif
(
key_1 POSITION(01:39) CHAR,
SOC POSITION(40:42) CHAR,
BRCH POSITION(43:47) CHAR,
FCY_CODE POSITION(48:50) CHAR,
GL_CLASS_CODE POSITION(51:75) CHAR,
DESCRIPTION POSITION(76:105) CHAR,
POSTING_IND POSITION(106:106) CHAR "nvl(:POSTING_IND,' ')",
TRANS_DATE POSITION(107) ZONED(9,0),
LCY_AMT POSITION(116) ZONED(17,3),
FCY_AMT POSITION(133) ZONED(17,3),
REVERSAL_CODE POSITION(150) CHAR "nvl(:REVERSAL_CODE,' ')",
REVERSAL_DATE POSITION(151) ZONED(9,0),
GLIF_REFERENCES POSITION(160:175) CHAR,
SOURCE_APPLN POSITION(176:178) CHAR,
PS_JOURNAL_ID POSITION(179:188) CHAR "nvl(:PS_JOURNAL_ID,' ')",
PS_JOURNAL_NBR POSITION(189) ZONED(9,0),
CNTL_CENTRE POSITION(198:202) CHAR "nvl(:CNTL_CENTRE,' ')"
)
I ma trying to generate this file as
set head off pages 0 lines 215 colsep "#"
column JOURNUBR format 000000000
column REVERDATE format 000000000
column TRANSDATE format 000000000
column LCY_AMT1 format 0000000000000000MI
column FCY_AMT1 format 0000000000000000MI
spool mytest
select KEY_1,SOC,BRCH,FCY_CODE,GL_CLASS_CODE,DESCRIPTION,POSTING_IND,TRANS_DATE as TRANSDATE,LCY_AMT*100 as LCY_AMT1,FCY_AMT*100 as FCY_AMT1,REVERSAL_CODE,REVERSAL_DATE as REVERDATE,GLIF_REFERENCES,SOURCE_APPLN,PS_JOURNAL_ID,PS_JOURNAL_NBR as JOURNUBR
from glif
where key_1='000039685000039685021372687F90000000102';
spool off
One way is to generate this file from database and then manipulate using some sheel script as per requirement
Can u suggest some SQL*PLUS /SQL /PL-SQL way
|
|
|
Re: Number datatype Format [message #343965 is a reply to message #343897] |
Thu, 28 August 2008 09:08 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Ah, yes.
For creating such files it is most of the time better to not output separate comlumns, but to concetenate the values into one colum.
for example instead of
select col1,
col2,
col3,
.....
do a
select to_char(col1, 'fm00000') ||
to_char(col2, 'fm00000') ||
to_char(col3, 'fm00000') ||
.....
since with to_char / lpad / rpad / trim / etc ... functions you have better control over the output than by just setting the column format.
[Updated on: Thu, 28 August 2008 09:09] Report message to a moderator
|
|
|
Re: Number datatype Format [message #344201 is a reply to message #343965] |
Fri, 29 August 2008 01:44 |
pankajkmeena
Messages: 46 Registered: September 2007
|
Member |
|
|
Thankx thats working
Now the last problem .
How to Disable Sql Prompt during Spooling.
It shows
SQL> select KEY_1||SOC||BRCH||FCY_CODE||GL_CLASS_CODE||DESCRIPTION||POSTING_IND||substr(to_char(TRANS_DATE,'fm000000000'),-9)||
2 substr(translate(to_char(LCY_AMT*100,'fmS0000000000000000'),'-+','P0'),2)||substr(translate(to_char(LCY_AMT*100,'fmS0000000000000000'),'-+','P0'),1,1)||
3 substr(translate(to_char(FCY_AMT*100,'fmS0000000000000000'),'-+','P0'),2)||substr(translate(to_char(FCY_AMT*100,'fmS0000000000000000'),'-+','P0'),1,1)||
4 REVERSAL_CODE||substr(to_char(REVERSAL_DATE ,'fm000000000'),-9) ||GLIF_REFERENCES||SOURCE_APPLN||PS_JOURNAL_ID||substr(to_char(PS_JOURNAL_NBR,'fm000000000'),-9) as LINE1
5 from glif
6 where rownum <100;
SQL> spool off
|
|
|
|
|
Goto Forum:
Current Time: Wed Nov 13 06:41:56 CST 2024
|