Bashscripts.org

#!/bin/bash

julianDay=$(date +'%j')
formatDate=$(date +'%y-%m-%d')
formatDateSQL=$(date +'%d/%m/%y')

#SQL statement
SQLquery="SELECT "$julianDay", cal.date_calendrier, mc.coquille AS coque, 'EXPLOITATION' AS numeroSerie, 'EXPLO' AS type, c.nom_ligne_voyageur, DECODE(c.flag_real,-1,'NR',1,'P','R') AS f_real FROM t_course c, t_course_morceau_course cmc, t_calendrier cal, t_horaire_point hp, t_morceau_course mc, t_mission mi WHERE (cmc.id_course = c.id_course) AND c.id_mission = mi.id_mission AND mi.nom_ligne_voyageur = c.nom_ligne_voyageur AND mc.id_point_debut = hp.id_point AND cmc.id_morceau_course = mc.id_morceau_course AND cmc.classe_tm = mc.classe_tm AND c.jour_exploitation = cal.id_calendrier AND cal.id_calendrier = hp.jour_exploitation AND c.jour_exploitation = hp.jour_exploitation AND c.classe_tm = hp.classe_tm AND hp.id_course = c.id_course AND hp.id_course = cmc.id_course AND hp.classe_tm = cmc.classe_tm AND mc.classe_tm = hp.classe_tm AND c.classe_tm = mc.classe_tm AND c.classe_tm = cmc.classe_tm AND mi.type_mission = 1 AND mi.categorie_mission = 1 AND c.classe_tm = 3 AND c.flag_real <> -1 AND mc.coquille IS NOT NULL AND ((c.jour_exploitation = fnc_convert_date_to_id('"$formatDateSQL"') AND (hp.depart_heure > 10800)) OR (c.jour_exploitation = fnc_convert_date_to_id('"$formatDateSQL"') AND hp.depart_heure<10800)) GROUP BY cal.no_jour, cal.date_calendrier, mc.coquille, c.nom_ligne_voyageur, c.flag_real;"

#Export result
echo $SQLquery > SQLQuery.sql

#DataBase name
ORACLE_SID="bsy"
export ORACLE_SID

sqlplus 'login/passwd@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.*.*.*)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ora8i))) @SQLQuery.sql'  << FIN set echo off set colsep ; set pagesize 0 set trimspool on

SPOOL export.txt

SPOOL off

FIN

exit