Hi

I need to execute a select statement in a solaris environment with oracle database. The select statement returns number of rows of data. I need to execute the query every five minutes and store the output to a file named as the timestamp at that time like 20091105001415.txt or csv (yyyymmddhh(24hr format)miss)

i thought of a making script and put it in the cron ...
I dont want the headers for the query output and the column values needs to be comma separated or tab separated
and i just tried this way......

#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/9.2.0
export PATH=$PATH:$ORACLE_HOME/bin
sqlplus -s username/password@schema<<!
Spool on
set heading off
Spool /aaa/ss.txt
SELECT MU.MSISDN,TA.X, TA.Y, TA.STREET || ' ' || TA.DISTRICT || ' ' || TA.CITY || ' ' || TA.STATE || ' ' || TA.COUNTRY || ' ' || TA.
ZIP AS ADDRESS, TA.TIME_STAMP FROM TEAM_ACTIVITY TA, MOBILE_UNIT MU WHERE TO_TIMESTAMP(TO_CHAR(MSG_DATE_INFO,'DD-MON-YYYY HH24:MI:SS
'), 'DD-MON-YYYY HH24:MI:SS') > TO_TIMESTAMP(TO_CHAR(SYSDATE-INTERVAL '05' MINUTE, 'dd/mon/YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS
') AND TEAM_ID in (select team_id from team where mobile_unit1 in (select mu_id from mobile_unit where com_id='473' ) ) AND TA.MOBIL
E_UNIT1=MU.MU_ID;
Spool off
!

9090900819 70.2545924 3.0725022 Institute of Child Health & Hospital Hallis Road Chennai Tamil Nadu Indi a 04-NOV-09 9090900719 70.2545924 3.0725022 Institute of Child Health & Hospital Hallis Road Chennai Tamil Nadu Indi a 04-NOV-09

9090900819,70.2545924,3.0725022,Institute of Child Health & Hospital Hallis Road Chennai Tamil Nadu India,04-NOV-09 23:29:33 9090900719,70.2545924,3.0725022,Institute of Child Health & Hospital Hallis Road Chennai Tamil Nadu India,04-NOV-09 23:26:33

Perhaps you could add

set wrap off
set colsep ,

You have to select all your columns as one big string, concatenating each column:

SELECT MU.MSISDN,TA.X, TA.Y, TA.STREET || ' ' || TA.DISTRICT || ' ' || TA.CITY || ' ' || TA.STATE || ' ' || TA.COUNTRY || ' ' || TA. ZIP AS ADDRESS, TA.TIME_STAMP

SELECT MU.MSISDN || ',' || TA.X || ',' || TA.Y || ',' || TA.STREET || ' ' || TA.DISTRICT || ' ' || TA.CITY || ' ' || TA.STATE || ' ' || TA.COUNTRY || ' ' || TA. ZIP || ',' || TIME_STAMP

That's probably easier ;-)

hi,
its coming but am having a newline between each record that i dont require...needs to be removed....and at the end of the records am getting 10 rows selected, this also needs to removed how can i achieve this.....

if i use set wrap off, row values gets truncated...