OraFAQ Forum: SQL & PL/SQL » Fixed Width Flat file via Oracle table

Home » SQL & PL/SQL » SQL & PL/SQL » Fixed Width Flat file via Oracle table (11g,win 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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......

Report message to a moderator

Re: Fixed Width Flat file via Oracle table [message #655905 is a reply to message #655902]

Thu, 15 September 2016 20:58

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: Fixed Width Flat file via Oracle table [message #655906 is a reply to message #655905]

Thu, 15 September 2016 21:06

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:

Code: [Select all] [Show/ hide]

Empno 10
Ename 60
Job   30

Report message to a moderator

Re: Fixed Width Flat file via Oracle table [message #655907 is a reply to message #655902]

Thu, 15 September 2016 21:48

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: Fixed Width Flat file via Oracle table [message #655909 is a reply to message #655907]

Fri, 16 September 2016 00:16

Littlefoot
Messages: 21825
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.

Report message to a moderator

Re: Fixed Width Flat file via Oracle table [message #655912 is a reply to message #655902]

Fri, 16 September 2016 01:51

John Watson
Messages: 8979
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

Report message to a moderator

Re: Fixed Width Flat file via Oracle table [message #656067 is a reply to message #655912]

Wed, 21 September 2016 13:27

shumail
Messages: 149
Registered: September 2012
Location: Canada

Senior Member

Thanks for your replies...

Report message to a moderator

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: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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 29 15:47:34 CDT 2025