Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Print the field value in a single line in the file

sasikala
Advisor

Print the field value in a single line in the file

Hi ,

I am writing a plsql script to fetch the table data and write it into the unix file.

I am preparing a select statement in the FOR loop. The fields are selected and displayed while writing. One of the field holds the letter of 100 character.

But while printing it is not printed in single line instead it is traversing into miltiple lines.

the part of the code is

FOR i in (
SELECT trim(substr(DESC_TXT,1,100)) P3_DESC)
from table
where ...

LOOP

utl_file.putf(fp,'%s',rpad(i.p3_desc,100));
utl_file.fflush(fp);

END LOOP;

IDEAL OUTPUT should be
======================

OEM RoHS Compliant Universal Supply ROHS

but the ACTUAL O/P is
=====================
OEM RoHS Compliant Universal
Supply ROHS

Could anyone help me to how to print the value in the single line.

Appreciate your help.

Regards
Sasikala

4 REPLIES
Hein van den Heuvel
Honored Contributor

Re: Print the field value in a single line in the file

This would appear to be largely a platform independent Oracle question. As such, I woudl recommend posting in an Oracle forum.
Now if it works on say Windows, but not on HPUX, then you are correct posting here first, but there is no indication of that.


>> But while printing it is not printed in single line instead it is traversing into miltiple lines.

Could it be that the file is fine, but that the printing that is wrapping for you?
Did you check with 'vi' or better still, od -c?

Just create a file with a long line:
#perl -le 'print "X" x 200' > x

not print:
#cat x
and check:
#od -x c


The provided example, which may well be out of context, seems to porivde an interesting example of program bloat.

- Why first TRIM only to PAD back to the same length right after?

- Why use formatted IO when just replacing the entire argument using "%s".
Just use UTL_FILE.PUT !?

Good luck!

Hein



Yogeeraj_1
Honored Contributor

Re: Print the field value in a single line in the file

hi Sasikala,

can you confirm if the field does not have any hidden characters like chr(10)?

Then, for example, you can eliminate all the trailing invisible characters (chr(10) using a similar query:

e.g.
select col1, trim(trailing chr(10) from col2) "TRIM" from test;

revert!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
sasikala
Advisor

Re: Print the field value in a single line in the file

Hi,

Yes the field has the control character.
And i got the way to bring the result to single line. Please find the

REGEXP_REPLACE(rpad((substr(P3.DESC_TXT,1,100)),100), '[[:cntrl:]]{1}', ' '),

The REGEXP_REPLACE replaces here the control character to space so that all the infomation comes in single line.

Thanks
Sasikala
Yogeeraj_1
Honored Contributor

Re: Print the field value in a single line in the file

hi Sasikala,

thank you for the feedback.

Please close this thread if this issue has been resolved.

All the best.

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)