Home » SQL & PL/SQL » SQL & PL/SQL » Number datatype Format (ORACLE 9i -- win2000)
icon9.gif  Number datatype Format [message #343478] Wed, 27 August 2008 06:53 Go to next message
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 #343480 is a reply to message #343478] Wed, 27 August 2008 06:58 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Can you provide the length and type of the column empno?


Regards
Oli
Re: Number datatype Format [message #343483 is a reply to message #343480] Wed, 27 August 2008 07:08 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #343825 is a reply to message #343483] Thu, 28 August 2008 05:18 Go to previous messageGo to next message
pankajkmeena
Messages: 46
Registered: September 2007
Member
Thankx
But now there is trailing space
My Number is only positive.Can i trim both leading and trailing space.
Re: Number datatype Format [message #343830 is a reply to message #343825] Thu, 28 August 2008 05:37 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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

icon12.gif  Re: Number datatype Format [message #344201 is a reply to message #343965] Fri, 29 August 2008 01:44 Go to previous messageGo to next message
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



Re: Number datatype Format [message #344208 is a reply to message #344201] Fri, 29 August 2008 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"set echo off" and execute it from a script.

Regards
Michel
icon7.gif  Re: Number datatype Format [message #344221 is a reply to message #344208] Fri, 29 August 2008 02:14 Go to previous message
pankajkmeena
Messages: 46
Registered: September 2007
Member
Thankx Michel and ThomasG
it works now i can create my Flat File from database .
Previous Topic: Need a Help to get best output (merged)
Next Topic: Column Names assigned to a cursor at Runtime
Goto Forum:
  


Current Time: Sat Dec 03 11:54:41 CST 2016

Total time taken to generate the page: 0.06959 seconds