Sample

# Baca list data dari file, untuk digunakan sebagai master data dalam pencarian detail data

# Set Tanggal Kemarin
date
echo "Mulai Pemecahan file..."
VCURDATE=`TZ=y24 date +%c %Y`
tgl=`echo $VCURDATE | cut -c9-10`
bln=`echo $VCURDATE | cut -c5-7`
thn=`echo $VCURDATE | cut -c21-24`
if [ $tgl -lt 10 ]
then
tgl="0"$tgl
fi

tanggal=$tgl"-"$bln"-"$thn
#tanggal="11-Nov-2007"

ls -ltr /data/backup/daily_trans_$tanggal.txt

cd /export/home/

cat $1 | while read x; do
echo "ambil code dealer dan branch dari tld id "$x"..."
branch=`cat /data/backup/daily_trans_$tanggal.txt | grep -i "|"$x"|" | head -1 | awk '{FS="|"}{print $17}'`
dealer=`cat /data/backup/daily_trans_$tanggal.txt | grep -i "|"$x"|" | head -1 | awk '{FS="|"}{print $18}'`
code=`echo $dealer | cut -c1-3`
if [ "$code" == "" ]
then
echo "tld id "$x" tidak ditemukan..."
else
nmfile="/data/backup/daily_trans_"$code"_"$branch"_"$tanggal".txt"
echo "generate file dealer "$code" untuk branch "$branch"..."
cat /data/backup/daily_trans_$tanggal.txt | grep -i "|"$x"|" | awk '{FS="|"}{print $1"|"$2"|"$3}' > $nmfile
fi
done
echo "Selesai..."
date

# Cara menggunakannya
# sh script.sh list.txt

#list.txt
0001
0002
0003
dst...

Query with specific partition table

this query only for table with partition

=========


SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF

-- Dates
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD';

COLUMN S NEW_VALUE start_date
SELECT trunc(sysdate-1) S FROM dual;

---- + Debug
DEFINE ptab_td = 'p_tab_trans_data_&start_date'
DEFINE ptab_tr = 'p_tab_trans_&start_date'
---- - Debug

-- Spool File
DEFINE spool_file = /home/sql/trans_&start_date..txt

SET PAGESIZE 0
SET LINESIZE 100
SET SPACE 1

CLEAR COLUMNS

SPOOL &spool_file

select
to_char(tr.ID)||'|'||to_char(tr.CREATED,'YYYYMMDDHH24MISS')||'|'||'62'||substr(tr.INITIATOR,2)||'|'||
(select (case when substr(td.value,1,1) = 0 then concat(62,substr(td.value,2)) else 62 ||td.value end) from vtudadm.trans_data partition (&ptab_td) td where td.transid = tr.id and td_key = 'target_msisdn')||'|'||
(select td.value from trans_data partition (&ptab_td) td where td.transid = tr.id and td_key = 'amount')||'|'||
to_char(vtudadm.get_tld(tr.INITIATOR))||'|'||
(select td.value from trans_data partition (&ptab_td) td where td.transid = tr.id and td_key = 'origcmd')||'|'||
(select td.value from trans_data partition (&ptab_td) td where td.transid = tr.id and td_key = 'lacid')||'|'||
(select td.value from trans_data partition (&ptab_td) td where td.transid = tr.id and td_key = 'cellid') LINEREC
from trans partition (&ptab_tr) tr
where tr.created between to_timestamp('&start_date'||'000000.000','YYYYMMDDHH24MISS.FF3')
and to_timestamp('&start_date'||'235959.999','YYYYMMDDHH24MISS.FF3')
and tr.type_ = 'LOAD'
and tr.state = 2
and tr.result = '0';


SPOOL OFF

CLEAR COLUMNS
CLEAR BREAKS
SET TERMOUT ON
SET VERIFY ON
SET FEEDBACK ON