Shell Programming and Scripting

Hi Friends,

Can u please help mw with the following query .

I need to run a database sql statement to select particular fields from a table.I need to store the retrieved filed values into variables so that i can print it in a specific format. But the particular select query retrieves more than 1 row .I have redirected the result of the query to a temporary text file.Lets say there are 10 rows retrieved. These retrieved values(filed_name value) are written into say 1.txt.
Now 1.txt looks some what like this :

fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value

I thought of using a grep command to get the values and store it in a variable.But the format of this text file makes it impossible.

Can please help me to put the data as below in 1.txt

fld_name value
fld_name value
fld_name value
fld_name value
fld_name value
fld_name value

or
When we run a select statement the rows are retrieved in the below format :
fld_name value
fld_name value
fld_name value

fld_name value
fld_name value
fld_name value


fld_name value
fld_name value
fld_name value


3 rows retrieved

Is there any way to store exactly this way to 1.txt?

Thanks in advance
Jisha

You can write the following statement on the SQL prompt, before the Select query

Thank you gauravgoel. .
My script is like this :

`isqlrf <database_name> - <<EOFSQL
select ....
from .....
where .....;
EOFSQl`

How i put the output of the above query was as below:

values=`isqlrf <database_name> - <<EOFSQL
select ....
from .....
where .....;
EOFSQl`

echo $values > 1.txt

I dont know anything about spools.Do u think my way of approach is correct?

Many Thanks
Jisha

as I mentioned before your select put

spool 1.txt

and after select put

spool off;


and after this you neednot redirect the output to 1.txt

I did like this :
`isqlrf <database_name> - spool 1.txt;<<EOFSQL
select ....
from .....
where .....;
spool off;
EOFSQl`

I executed & nothing happened .The file was also not created.

Thanks
Jisha

try like this

`isqlrf <database_name>;<<EOFSQL
spool 1.txt
select ....
from .....
where .....;
spool off;
EOFSQl`


you may need to tweak it around a bit,
Sorry cant check right now for you as dont have access to UNIX system right now

Thanks again.
I tried it like this:
`isqlrf <database_name> <<EOFSQL
spool 1.txt
select ....
from .....
where .....;
spool off;
EOFSQl`

It gave me a "syntax error has occured"

Then i tried it :
`isqlrf <database_name>;<<EOFSQL
spool 1.txt
select ....
from .....
where .....;
spool off;
EOFSQl`

This didnt give me any answer at all ..The command prompt was also not there after execution.I had to pred ctrl+c to go to the command prompt..Do u know y this happens?

Thanks,
jisha