Fixed Width Flat file via Oracle table [message #655902] |
Thu, 15 September 2016 17:59 |
|
shumail
Messages: 149 Registered: September 2012 Location: Canada
|
Senior Member |
|
|
Hello Expert
I really appreciate if some one help me out , I want to create flat file with .txt extension by using oracle table.
I'm wondering that I can achieve this task by using SQL Developer and few SQL Plus commands but the problem is that I want to create this flat file with some specified columns width.
For example : If I have scott.emp table then I want to create this flat file with the following width:
Empno 10
Ename 60
Job 30
I case if any column value is null then system don't care about it and assign the column specified width. for example if we have job column value is null then system still assign width 30. Can somebody help me out. Thanks in advance......
|
|
|
|
|
|
|
|
|
Re: Fixed Width Flat file via Oracle table [message #656194 is a reply to message #655909] |
Tue, 27 September 2016 15:42 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Littlefoot wrote on Fri, 16 September 2016 01:16I'd use RPAD function and add as many spaces as needed to each of these column values. You might also need to nest RPAD with SUBSTR, just in case if some column value length is, actually, larger than the ones you specified.
Actually, you will not need substr. RPAD takes care of truncation if the string is longer then the length specified.
test>select rpad('1234567890',5,' ') from dual;
RPAD(
-----
12345
|
|
|