How to use awk for printing line from database table?

Shell Programming and Scripting
1

Hi ,
I have inserted some records in a table having column "value1 varchar2(4000)" and want to spool in a file.
I have written as below

set echo off
set feed off
set hea off
set wra off
set lin 500
spo temp_table

and fired select query as below

select value1 from temp_table;
spo off

Now the output length of each column is 500 as i have given above.
The data in the temp_table varry in length and seperated by semicolon";" as below.

asdfg;sdeeeedf;;dfffff;;;fffdfd;;;dfdfdfdf;;;;dfdfdfdfdf;;;27-JUL-2010;
werwerg;ssdf;;dfffff;;;feee;;;dfdfdwewrefdf;;;;dfdfdfdfdf;;;25-JUL-2010;
asdfg;sdfsdf;;dfffff;;;fffdfd;;;dfdwerfdfdf;;;;dfdfdfdfdf;;;22-JUL-2010;
aasdasddfg;sasdasddfs;;eeeeefff;;;fffwewdfd;;;dfdfdfdf;;;;dfdfdfdfdf;;;29-JUL-2010;

and at last of each line semicolon is there.

How to copy the data as above in a file using awk,as it is going as below format with space or white character at the end with length of each line as 500

asdfg;sdeeeedf;;dfffff;;;fffdfd;;;dfdfdfdf;;;;dfdfdfdfdf;;;27-JUL-2010; 
werwerg;ssdf;;dfffff;;;feee;;;dfdfdwewrefdf;;;;dfdfdfdfdf;;;25-JUL-2010; 
asdfg;sdfsdf;;dfffff;;;fffdfd;;;dfdwerfdfdf;;;;dfdfdfdfdf;;;22-JUL-2010; 
aasdasddfg;sasdasddfs;;eeeeefff;;;fffwewdfd;;;dfdfdfdf;;;;dfdfdfdfdf;;;29-JUL-2010;
2 
while read LINE; do printf "%-500s\n" "$LINE"; done < infile > newfile
3

Thanks for reply, but this is not working and giving same output.

4

It is working fine on my box. How do you check if you have a width of 500 per line?
Maybe open the new file with

od -c outfile

or check the size of the file which must be larger than the input file.
You can also check while opening with vi and do ":set list" for example.

---------- Post updated at 11:05 AM ---------- Previous update was at 11:02 AM ----------

Here for proof:

$> ls -l infile
-rw-r--r-- 1 isau isau 15 22. Jul 11:02 infile
$> cat infile
eins
zwei
drei
$> while read LINE; do printf "%-10s\n" "$LINE"; done < infile > outfile
$> ls -l outfile
-rw-r--r-- 1 root root 33 22. Jul 11:04 outfile
$> od -c outfile
0000000   e   i   n   s                          \n   z   w   e   i
0000020                      \n   d   r   e   i
0000040  \n
0000041
5

Sorry man, But I think you have not understand my prob properly.
Let me explain u.
I have already set the lin size as 500 so what ever line is coming from table they all have same lenght as 500 even if the content is less let say 230 but some white character or say space is coming after semicolon.
When i am doing spool then the contents are coming with white character or space as below

asdfg;sdeeeedf;;dfffff;;;fffdfd;;;dfdfdfdf;;;;dfdfdfdfdf;;;27-JUL-2010;<space    or white char                                                            space>
werwerg;ssdf;;dfffff;;;feee;;;dfdfdwewrefdf;;;;dfdfdfdfdf;;;25-JUL-2010;<space     or white char                                                                      space>
asdfg;sdfsdf;;dfffff;;;fffdfd;;;dfdwerfdfdf;;;;dfdfdfdfdf;;;22-JUL-2010; <space  or white char                                                           space>
aasdasddfg;sasdasddfs;;eeeeefff;;;fffwewdfd;;;dfdfdfdf;;;;dfdfdfdfdf;;;29-JUL-2010;<space  or white char                                                           space>

but i want in below format

asdfg;sdeeeedf;;dfffff;;;fffdfd;;;dfdfdfdf;;;;dfdfdfdfdf;;;27-JUL-2010;
werwerg;ssdf;;dfffff;;;feee;;;dfdfdwewrefdf;;;;dfdfdfdfdf;;;25-JUL-2010;
asdfg;sdfsdf;;dfffff;;;fffdfd;;;dfdwerfdfdf;;;;dfdfdfdfdf;;;22-JUL-2010;
aasdasddfg;sasdasddfs;;eeeeefff;;;fffwewdfd;;;dfdfdfdf;;;;dfdfdfdfdf;;;29-JUL-2010;

I think now you can understand my problem.
Thanks anyway.

6

I don't seem to understand the requirement too,
if you want to trim the white space characters at the end of each line, just enable trimspool:

set trimspool on
7