aduh...error mulu ya pakai PL/SQL..abis kebiasaan pakai T-SQl sih...
bikin function yang didalamnya ada proses DDL(Data Definition language) koq ga mau y?
aq pikir2..masak DB sekelas oracle koq gt aja ga support..
hmmm akhirnya ketemu juga jawabannya..
scriptnya harus di kasih tambahan script lg ternyata
ternyata ini 'pragma autonomous_transaction;' yang dicari2 dari td..
ihh gemez......bikin lama kerjaan aj:D
========================================================
CREATE OR REPLACE Function NextProcessInDestinationTable
( modeInput IN Integer, tableNm IN varchar2, tablespaceNm IN varchar2
, dd IN varchar2, mm IN varchar2, yy IN varchar2)
RETURN Integer
IS
-- penambahan ini untuk mengatasi error msg :
-- ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
pragma autonomous_transaction; -- <===== ini dia :D
tailOfTable VARCHAR2(50);
sqlStatement VARCHAR2(2000);
i integer :=0;
BEGIN
IF ( tableNM = 'ACTIVITY' or tableNM = 'PENDING'
or tableNM = 'SETTLEMENT_LOP' or tableNm = 'RESOLVED') THEN
tailOfTable := substr(yy, 3, 2) || mm;
ELSIF ( tableNM = 'CUSTOMER' or tableNM = 'MERCHANT') then
tailOfTable := substr(yy, 3, 2) || mm ||dd;
END IF;
DBMS_OUTPUT.PUT_LINE('>>>>> ' || tailOfTable);
if (modeinput = 0) then
select count(1) into i
from all_tables
where table_name = (tableNm || tailOfTable) and tablespace_name = tablespaceNm;
if ( i > 0 ) then
-- processinsertdata please wait y:D
return 1;
else
--create table if not exists
sqlStatement := 'create table ' || tableNm || tailOfTable || ' as select * from ' || ' template_' || TableNm;
Execute immediate (sqlStatement);
end if;
end if;
return 0;
END;
========================================================
Friday, August 27, 2010
Monday, August 09, 2010
Cara menggunakan SQL Loader via script untuk import data dari csv file
1. Tentukan file csv nya. misal file csv namanya data.csv berisi :
Source MDN,Destination MDN,Type,Transaction Date,return code
62882100000000,62881300000000,Topup,08/01/2010 23:59,145
882100000000,8821000000000,Topup,08/01/2010 23:58,145
882100000000,8826000000000,Topup,08/01/2010 23:58,145
881300000000,881300000000,Topup,08/01/2010 23:58,145
8821000000000,8821000000000,Topup,08/01/2010 23:58,145
881300000000,881600000000,Topup,08/01/2010 23:57,145
881600000000,881500000000,Topup,08/01/2010 23:57,145
8821000000000,8821000000000,Topup,08/01/2010 23:57,444
881800000000,881500000000,Topup,08/01/2010 23:56,444
881800000000,8822000000000,Topup,08/01/2010 23:56,444
881800000000,881400000000,Topup,08/01/2010 23:56,444
881800000000,881200000000,Topup,08/01/2010 23:56,444
881100000000,881100000000,Topup,08/01/2010 23:56,444
881800000000,8821000000000,Transfer,08/01/2010 23:56,444
881800000000,8822000000000,Transfer,08/01/2010 23:55,675
882000000000,8821000000000,Transfer,08/01/2010 23:55,675
881800000000,881300000000,Transfer,08/01/2010 23:55,675
881800000000,8822000000000,Transfer,08/01/2010 23:55,675
881100000000,881300000000,Transfer,08/01/2010 23:55,675
881800000000,8822000000000,Transfer,08/01/2010 23:55,675
881800000000,8822000000000,Transfer,08/01/2010 23:55,675
881900000000,881975tt4714,Transfer,08/01/2010 23:55,675
8821000000000,8826000000000,Transfer,08/01/2010 23:55,675
881300000000,881300000000,Transfer,08/01/2010 23:55,675
2. Buat table yang digunakan untuk menampung hasil insert, sesuaikan fieldnya dengan field csv nya misal seperti ini
Contoh model 1 :
CREATE TABLE TABLE_EX
(
SOURCE_MDN VARCHAR2(35 BYTE),
DEST_MDN VARCHAR2(35 BYTE),
TYPE VARCHAR2(10 BYTE),
TRX_DATE DATE,
RETUN_CODE VARCHAR2(10 BYTE)
)
Apabila ingin memasukkan datetime Process loader sekaligus saat loader maka buat table seperti berikut :
Contoh model 2:
CREATE TABLE TABLE_EX
(
SOURCE_MDN VARCHAR2(35 BYTE),
DEST_MDN VARCHAR2(35 BYTE),
TYPE VARCHAR2(10 BYTE),
TRX_DATE DATE,
RETUN_CODE VARCHAR2(10 BYTE),
LOADER_DATE DATE
)
3. Buat file type nya loader.ctl dengan isi seperti dibawah ini :
file .ctl untuk contoh model 1 adalah :
LOAD DATA
TRUNCATE
INTO TABLE "IZZA"."TABLE_EX"
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
source_mdn "decode(substr(trim (:source_mdn), 1,2), '88', '62'||trim (:source_mdn), trim (:source_mdn))"
, dest_mdn "decode(substr(trim (:dest_mdn), 1,2), '88', '62'||trim (:dest_mdn), trim (:source_mdn))"
, Type "trim(:type)"
, trx_date date "yyyy-mm-dd hh24:mi:ss"
, return_code
)
file .ctl untuk contoh model 2 adalah :
LOAD DATA
TRUNCATE
INTO TABLE "IZZA"."TABLE_EX"
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
source_mdn "decode(substr(trim (:source_mdn), 1,2), '88', '62'||trim (:source_mdn), trim (:source_mdn))"
, dest_mdn "decode(substr(trim (:dest_mdn), 1,2), '88', '62'||trim (:dest_mdn), trim (:source_mdn))"
, Type "trim(:type)"
, trx_date date "yyyy-mm-dd hh24:mi:ss"
, return_code
, LOADER_DATE "sysdate"
)
maksud dari script diatas adalah:
- IZZA : schema yang dipergunakan table yang akan diinsert data
- TABLE_EX : nama table yang digunakan menmapung data insert
- untuk field source_mdn :
apabila data mdn ini karakter 1-2 = 88 maka tambahkan 62 plus data asli field tersebut
- untuk field dest_mdn :
apabila data mdn ini karakter 1-2 = 88 maka tambahkan 62 plus data asli field tersebut
- untuk field type :
apabila terdapat sepasi diawal atau diakhir maka spasi dihilangkan baru diinsert
- untuk field trx_date :
data diformat sesuai yang format tanggal diinginkan karena tipe data dari table tersebut adalah date
- untuk field return_code :
data dimasukkan apa adanya
- untuk field loader_date
diisi fungsi "sysdate" yaitu mengambil data datetime saat itu. model pengisian seperti ini bisa digunakan apabila kita menginginkan memasukkan data dalam jumlah banyak sekaligus memberi tanggal kapan data tersebut dimasukkan ke system. kita juga bisa memasukkan fungsi-fungsi oracle yang lain
proses loader diatas akan menjalankan proses truncate table "table_EX" dulu sebelum meng-insert data yang ada di file csv ke table tersebut.
4. Jalankan script berikut di cmd command windows :
sqlldr izza/ismatulizzah@mis control='c:\loader.ctl', data='c:\data.csv', log='c:\loaderlog.log', BAD='c:\databad.bad', DISCARD='c\datadsc.dsc' direct='yes' skip=1
izzah => username
telco1 => password
control ='c:\loader.ctl' => lokasi dari file ctl nya
log='c:\loaderlog.log' => lokasi dari file log hasil proses yg dijalankan success atau ada fail nya
BAD='c:\databad.bad' => file akan berisi row data yang error dari data yang diproses
Skip = 1 => Baris Pertama dari isi file csv tidak diinsert ke table
direct = yes => ini bisa mempercepat proses loader file2 kapasitas besar kalau untuk file2 kecil ga begitu terlihat perbedaan timingnya.
lebih jelasnya baca artikel tentang sqlldr
Source MDN,Destination MDN,Type,Transaction Date,return code
62882100000000,62881300000000,Topup,08/01/2010 23:59,145
882100000000,8821000000000,Topup,08/01/2010 23:58,145
882100000000,8826000000000,Topup,08/01/2010 23:58,145
881300000000,881300000000,Topup,08/01/2010 23:58,145
8821000000000,8821000000000,Topup,08/01/2010 23:58,145
881300000000,881600000000,Topup,08/01/2010 23:57,145
881600000000,881500000000,Topup,08/01/2010 23:57,145
8821000000000,8821000000000,Topup,08/01/2010 23:57,444
881800000000,881500000000,Topup,08/01/2010 23:56,444
881800000000,8822000000000,Topup,08/01/2010 23:56,444
881800000000,881400000000,Topup,08/01/2010 23:56,444
881800000000,881200000000,Topup,08/01/2010 23:56,444
881100000000,881100000000,Topup,08/01/2010 23:56,444
881800000000,8821000000000,Transfer,08/01/2010 23:56,444
881800000000,8822000000000,Transfer,08/01/2010 23:55,675
882000000000,8821000000000,Transfer,08/01/2010 23:55,675
881800000000,881300000000,Transfer,08/01/2010 23:55,675
881800000000,8822000000000,Transfer,08/01/2010 23:55,675
881100000000,881300000000,Transfer,08/01/2010 23:55,675
881800000000,8822000000000,Transfer,08/01/2010 23:55,675
881800000000,8822000000000,Transfer,08/01/2010 23:55,675
881900000000,881975tt4714,Transfer,08/01/2010 23:55,675
8821000000000,8826000000000,Transfer,08/01/2010 23:55,675
881300000000,881300000000,Transfer,08/01/2010 23:55,675
2. Buat table yang digunakan untuk menampung hasil insert, sesuaikan fieldnya dengan field csv nya misal seperti ini
Contoh model 1 :
CREATE TABLE TABLE_EX
(
SOURCE_MDN VARCHAR2(35 BYTE),
DEST_MDN VARCHAR2(35 BYTE),
TYPE VARCHAR2(10 BYTE),
TRX_DATE DATE,
RETUN_CODE VARCHAR2(10 BYTE)
)
Apabila ingin memasukkan datetime Process loader sekaligus saat loader maka buat table seperti berikut :
Contoh model 2:
CREATE TABLE TABLE_EX
(
SOURCE_MDN VARCHAR2(35 BYTE),
DEST_MDN VARCHAR2(35 BYTE),
TYPE VARCHAR2(10 BYTE),
TRX_DATE DATE,
RETUN_CODE VARCHAR2(10 BYTE),
LOADER_DATE DATE
)
3. Buat file type nya loader.ctl dengan isi seperti dibawah ini :
file .ctl untuk contoh model 1 adalah :
LOAD DATA
TRUNCATE
INTO TABLE "IZZA"."TABLE_EX"
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
source_mdn "decode(substr(trim (:source_mdn), 1,2), '88', '62'||trim (:source_mdn), trim (:source_mdn))"
, dest_mdn "decode(substr(trim (:dest_mdn), 1,2), '88', '62'||trim (:dest_mdn), trim (:source_mdn))"
, Type "trim(:type)"
, trx_date date "yyyy-mm-dd hh24:mi:ss"
, return_code
)
file .ctl untuk contoh model 2 adalah :
LOAD DATA
TRUNCATE
INTO TABLE "IZZA"."TABLE_EX"
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
source_mdn "decode(substr(trim (:source_mdn), 1,2), '88', '62'||trim (:source_mdn), trim (:source_mdn))"
, dest_mdn "decode(substr(trim (:dest_mdn), 1,2), '88', '62'||trim (:dest_mdn), trim (:source_mdn))"
, Type "trim(:type)"
, trx_date date "yyyy-mm-dd hh24:mi:ss"
, return_code
, LOADER_DATE "sysdate"
)
maksud dari script diatas adalah:
- IZZA : schema yang dipergunakan table yang akan diinsert data
- TABLE_EX : nama table yang digunakan menmapung data insert
- untuk field source_mdn :
apabila data mdn ini karakter 1-2 = 88 maka tambahkan 62 plus data asli field tersebut
- untuk field dest_mdn :
apabila data mdn ini karakter 1-2 = 88 maka tambahkan 62 plus data asli field tersebut
- untuk field type :
apabila terdapat sepasi diawal atau diakhir maka spasi dihilangkan baru diinsert
- untuk field trx_date :
data diformat sesuai yang format tanggal diinginkan karena tipe data dari table tersebut adalah date
- untuk field return_code :
data dimasukkan apa adanya
- untuk field loader_date
diisi fungsi "sysdate" yaitu mengambil data datetime saat itu. model pengisian seperti ini bisa digunakan apabila kita menginginkan memasukkan data dalam jumlah banyak sekaligus memberi tanggal kapan data tersebut dimasukkan ke system. kita juga bisa memasukkan fungsi-fungsi oracle yang lain
proses loader diatas akan menjalankan proses truncate table "table_EX" dulu sebelum meng-insert data yang ada di file csv ke table tersebut.
4. Jalankan script berikut di cmd command windows :
sqlldr izza/ismatulizzah@mis control='c:\loader.ctl', data='c:\data.csv', log='c:\loaderlog.log', BAD='c:\databad.bad', DISCARD='c\datadsc.dsc' direct='yes' skip=1
izzah => username
telco1 => password
control ='c:\loader.ctl' => lokasi dari file ctl nya
log='c:\loaderlog.log' => lokasi dari file log hasil proses yg dijalankan success atau ada fail nya
BAD='c:\databad.bad' => file akan berisi row data yang error dari data yang diproses
Skip = 1 => Baris Pertama dari isi file csv tidak diinsert ke table
direct = yes => ini bisa mempercepat proses loader file2 kapasitas besar kalau untuk file2 kecil ga begitu terlihat perbedaan timingnya.
lebih jelasnya baca artikel tentang sqlldr
Subscribe to:
Posts (Atom)