Home » SQL & PL/SQL » SQL & PL/SQL » Fixed Width Flat file via Oracle table (11g,win 7)
Fixed Width Flat file via Oracle table [message #655902] Thu, 15 September 2016 17:59 Go to next message
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 #655905 is a reply to message #655902] Thu, 15 September 2016 20:58 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
https://community.oracle.com/thread/3972193

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Fixed Width Flat file via Oracle table [message #655906 is a reply to message #655905] Thu, 15 September 2016 21:06 Go to previous messageGo to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
actually I need the out in .txt file that's why I didn't format the code, however I'm gonna format it below:
Empno 10
Ename 60
Job   30
Re: Fixed Width Flat file via Oracle table [message #655907 is a reply to message #655902] Thu, 15 September 2016 21:48 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
shumail wrote on Thu, 15 September 2016 15:59
Hello Expert
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......
use NVL() function to do as needed for NULL column
Re: Fixed Width Flat file via Oracle table [message #655909 is a reply to message #655907] Fri, 16 September 2016 00:16 Go to previous messageGo to next message
Littlefoot
Messages: 21367
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'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.
Re: Fixed Width Flat file via Oracle table [message #655912 is a reply to message #655902] Fri, 16 September 2016 01:51 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
If you Google for Tom Kyte Print Table you'll find many examples of TK's famous procedure.
I like Tim Onions' code, http://www.todc.co.uk/scripts/print_table.sql.html
Re: Fixed Width Flat file via Oracle table [message #656067 is a reply to message #655912] Wed, 21 September 2016 13:27 Go to previous messageGo to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
Thanks for your replies...
Re: Fixed Width Flat file via Oracle table [message #656194 is a reply to message #655909] Tue, 27 September 2016 15:42 Go to previous message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
Littlefoot wrote on Fri, 16 September 2016 01:16
I'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

Previous Topic: Column Data Modification
Next Topic: Distinct Comma Separated Values
Goto Forum:
  


Current Time: Sat Feb 24 09:18:40 CST 2018

Total time taken to generate the page: 0.02235 seconds