Read one line from a file

# i got this script from other website
# hope can help anyone who read this
# this script is for get one line from the file
# save it as read.sh

PN=`basename "$0"` # Program name
VER='1.2'

usage () {
echo >&2 "$PN - get one line of text, $VER (hs '96)
usage: $PN [line] [file ...]

If no line is given, the default line is 1."
exit 1
}

msg () {
for i
do echo "$PN: $i" >&2
done
}

fatal () { msg "$@"; exit 1; }

# Check arguments before setting them
set -- `getopt h "$@"`
while [ $# -gt 0 ]
do
case "$1" in
--) shift; break;;
-h) usage;;
-*) usage;;
*) break;; # First file name
esac
shift
done

if [ $# -gt 0 ] && expr "$1" + 0 >/dev/null 2>&1
then # Seems to be a line number
Line=$1
shift
fi

exec sed -n "${Line:-1}{p;q;}" "$@"
# end of script

# for example you can create a file
# save it as selamat.txt
Selamat pagi
Selamat siang
Selamat malam

# how to use this script
bash-2.05$sh read.sh 1 selamat.txt
result : Selamat pagi
bash-2.05$sh read.sh 3 selamat.txt
result : Selamat malam

Store Procedure

CREATE OR REPLACE PROCEDURE TAMPUNGAN.GET_ALL_TRANS (P_TRANS_DT VARCHAR2) IS
CURSOR LOADRec (P_TRANS_DATE VARCHAR2) IS
select tr.ID, tr.DESCRIPTION, tr.CREATED
from trans tr
where tr.CREATED between to_timestamp(P_TRANS_DATE||'000000.000','YYYYMMDDHH24MISS.FF3')
and to_timestamp(P_TRANS_DATE||'235959.999','YYYYMMDDHH24MISS.FF3')
and tr.DESCRIPTION<>'Commission';
-- and rownum <= 1000;

CURSOR GETTransData (P_TRANS_ID NUMBER) IS
select td.td_key, trim(td.value) value
from trans_data td
where td.transid=P_TRANS_ID
and td.td_key in ('target','amount');

CURSOR GETTransExt (P_TRANS_ID NUMBER) IS
select txt.type_, txt.result
from trans_ext_trans txt
where txt.transid=P_TRANS_ID;

v_trans_id number;
v_description varchar2(40);
v_created date;
v_result varchar2(20);
v_target varchar2(255);
v_amount varchar2(255);

v_loop_count integer;
v_trans_dt varchar2(10);

BEGIN
v_loop_count := 0;

-- for default value use GET_ALL_TRANS('YYYYMMDD');
-- is mean get data of yesterday data
if P_TRANS_DT = 'YYYYMMDD' then
v_trans_dt := to_char(sysdate-1,'YYYYMMDD');
else
v_trans_dt := P_TRANS_DT;
end if;

for CurrRLDRec in LOADRec (v_trans_dt)
loop
begin
v_trans_id := CurrRLDRec.id;
v_description := CurrRLDRec.description;
v_created := CurrRLDRec.created;

v_result := null;
v_target := null;
v_amount := null;

for CurrTERec in GETTransExt (v_trans_id)
loop
v_result := CurrTERec.result;
end loop;

for CurrTDRec in GETTransData (v_trans_id)
loop
if CurrTDRec.td_key = 'target' then
if substr(CurrTDRec.value,1,1) <> '0' then
v_target := '0'||CurrTDRec.value;
else
v_target := CurrTDRec.value;
end if;
else
if CurrTDRec.td_key = 'amount' then
v_amount := CurrTDRec.value;
end if;
end if;
end loop;

-- Insert data to table
insert into tamp_daily
(TRANSID, DESCRIPTION, CREATED, RESULT, TARGET, AMOUNT)
values
(v_trans_id, v_description, v_created, v_result, v_target, v_amount);

v_loop_count := v_loop_count + 1;

-- commit if v_loop_count is more than those value
if v_loop_count >= 10000 then
commit;
v_loop_count := 0;
end if;

exception
when NO_DATA_FOUND then null;
when others then rollback;
end;
end loop;

commit;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END GET_ALL_TRANS;

tampung ke file dari hasil select

cara ini gw lakuin di unix.

biar lebih mudah gw pisah-pisah filenya sesuai dengan fungsinya.

pertama buat file execute buat jalanin file sql, isinya sbb :
#/bin/sh

ORACLE_HOME=/app/oracle/product/10.2.0/Db_1
export ORACLE_HOME
#jika memang belum diset path oracle homenya
ORACLE_SID=ORACLE_SID
export ORACLE_SID
#sama juga seperti oracle_home
#jika pathnya sudah diset di os-nya gak perlu lagi jalanin perintah diatas

date

cd /export/home/
#supaya posisi direktori berada di file yg mau gw jalanin

$ORACLE_HOME/bin/sqlplus user/password @allSelect.sql

date
#biar gw tau mulai jam berapa dan kelar jam berapa ini query.

#End of File---

kedua buat file sql yang berfungsi untuk menampung file-file sql lain yang akan di jalankan
--Start file

@@dailySql1.sql
@@dailySql2.sql

exit
--End of file

nah yg ketiga ini adalah query yg diperlukan, tapi dikasih contoh yg dailySql1.sql aja :
SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF

-- Dates, changes format date
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY';

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

---- + Debug, digunakan kalau melakukan query dengan input date
--DEFINE start_date = &1
---- - Debug, Read from command line

-- Spool File
DEFINE spool_file = dailySql_&start_date..txt

SET PAGESIZE 0
SET LINESIZE 220
-- Linesize adalah jumlah maksimal karakter yg akan ditampung
SET SPACE 1

BREAK ON REPORT

SPOOL &spool_file

select id||"|"||nama||"|"||alamat||"|"||no_telp from nama_table
where tanggal between trunc(sysdate-1) and trunc(sysdate);

SPOOL OFF

CLEAR BREAKS
SET TERMOUT ON
SET VERIFY ON
SET FEEDBACK ON

SQL Loader

cara menjalankan sql loader :
sqlldr userid=user/password control=/export/home/loader.ctl log=/export/home/loader.log bad=/export/home/loader.bad

loader.log dan loader.bad akan terbuat dengan sendirinya setelah menjalankan perintah load

isi dari loader.ctl :
load data infile '/export/home/dataloader.txt' into table nama_table fields terminated by "|" TRAILING NULLCOLS (ID,NAMA,ALAMAT,NO_TLP)

TRAILING NULLCOLS dimaksudkan jika ada salah satu field yg kosong maka diabaikan, dan akan melanjutkan loader kedata selanjutnya.