Friday, May 19, 2006

sqlplus => filename + date from spool

I want to use spool on sqlplus from create file from date.
my peogram
genCSV.sh

#/bin/sh

export ORACLE_BASE=/export/home/oracle
export ORACLE_HOME=/export/home/oracle/product/10.1.0
export NLS_LANG=THAI_THAILAND.TH8TISASCII

SID='ORCL'
USER='username'
PASS='password'

/export/home/oracle/product/10.1.0/bin/sqlplus ${USER}/${PASS}@${SID} @/export/home/oracle/CSVExport/sql/bb_service_export.sql


...........................................

and my sql script (bb_service_export.sql)
column sd new_value sd
select to_char(sysdate, 'YYYYMMDD') sd from dual ;
set heading off;
set pages 0
set pagesize 0;
set echo off;
set feedback off;
set termout off;
set linesize 250;
set trimspool on;
spool /export/home/oracle/CSVExport/data-out/BB_xxx_&sd..csv
SELECT
xxx.CENTRAL_SID || ',' ||
xxx.SERVICE_NAME_EN || ',' ||
xxx.SERVICE_NAME_TH
FROM
xxx
WHERE
xxx.STATUS = 3;
spool off;
exit;


.................
out put when i run
#./genCSV.sh
#ls ../data-out/
BB_xxx_20060518.csv

No comments: