Home » SQL & PL/SQL » SQL & PL/SQL » Fixed Width output file
Fixed Width output file [message #437713] Tue, 05 January 2010 14:25 Go to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Hi,

I'm working on an interface that requires fixed width output format for it's columns. I just want to make sure i understand this format and how to produce it in dbms_output.putline code language:

a=9 width
b=30 width
c=2 width
de= 5 width

I can't test this because my system is down.

DECLARE
v_blank_space varchar2(1):=' ';


Cursor get_1 IS
select a,b,c,de from dual;

Begin

FOR rec_1 IN get_1
     LOOP
   
dbms_output.put_line(substr(nvl(rec_1.a,' '),1,9),1,v_blank_space);
dbms_output.putline(substr(rec_1.b,1,30),v_blank_space));
dbms_output.putline(substr(rec_1.c,1,2),v_blank_space));
dbms_output.putline(substr(rec_1.de,1,5),v_blank_space));

end loop;
end;



Is the dbms_output.put_line's the correct format for fixed width output?

Thanks

Anne
Re: Fixed Width output file [message #437715 is a reply to message #437713] Tue, 05 January 2010 14:28 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>I can't test this because my system is down.
No worries, & no hurry.
We can wait for it to be available again.
Re: Fixed Width output file [message #437716 is a reply to message #437713] Tue, 05 January 2010 14:33 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Why you want to trim the lengths?

What would happen if a column is say number(5)
it contains values 10000,100 and you want to diaply of width 3
Both will become 100,100.Will it make sense?


You can use SUBSTR(column_name, 1,n)
n--> 1,2,3,4...

Read more..

if you are displaying the column in sqlplus then you can use
COLUMN COLUMN_NAME FORMAT A5

select a,b,c,de from dual;

is invalid

Regards,
Ved

[Updated on: Tue, 05 January 2010 14:40]

Report message to a moderator

Re: Fixed Width output file [message #437719 is a reply to message #437713] Tue, 05 January 2010 14:41 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use PL/SQL, use SQL and read:
SQL*PlusĀ® User's Guide and Reference
Chapter 6 Formatting SQL*Plus Reports

Regards
Michel
Previous Topic: START AND END DATE OF MONTH
Next Topic: Reading a Package
Goto Forum:
  


Current Time: Tue Sep 27 19:35:11 CDT 2016

Total time taken to generate the page: 0.05828 seconds