----------------------------------------------------------------
----------------------- Podes Migration -----------------------
------------------------- Versi 5.2.1 -------------------------
-- 31 10 2008 --
-- Versi Oracle --
-- DDL Compatible with Oracle --
-- Purpose: --
-- 1) Create Physical Table --
-- 2) Insert table with relevan data --
-- 3) Insert View Name name into alias --
-- 4) Insert View column name into alias_kolom --
-- 5) Create Vew in Oracle --
-- 6) Create information Statistics about migrating tables --
----------------------------------------------------------------
DECLARE
----------------------------------------------------------------
----------------------- Podes Migration -----------------------
------------------------- Versi 5.2.1 -------------------------
-- 31 10 2008 --
-- Versi Oracle --
-- DDL Compatible with Oracle --
-- Purpose: --
-- 1) Create Physical Table --
-- 2) Insert table with relevan data --
-- 3) Insert View Name name into alias --
-- 4) Insert View column name into alias_kolom --
-- 5) Create Vew in Oracle --
-- 6) Create information Statistics about migrating tables --
----------------------------------------------------------------
CURSOR c_i IS
SELECT ac.table_name,
'CREATE TABLE '
|| ac.table_name
|| Chr (10)
|| ' ( '
|| Chr (10)
|| 'ID ID Number (8) Constraint PK_'
|| ac.table_name
|| ', '
|| Chr (10)
|| 'ID_PROV Varchar2(2),'
|| Chr (10)
|| 'ID_KAB Varchar2(2),'
|| Chr (10)
|| 'ID_KEC Varchar2(3),'
|| Chr (10)
|| 'ID_KEL Varchar2(3),'
|| Chr (10)
|| 'TAHUN nVarchar2(4), '
v_kolom_area
FROM all_tab_columns ac
WHERE EXISTS (
SELECT atc.table_name,
atc.column_name
FROM all_tab_columns atc
WHERE atc.owner =
'DBO_BAKOX_LAMA'
AND atc.column_name =
'KODE_DESA'
AND atc.column_name =
ac.column_name
AND atc.table_name =
ac.table_name)
AND ac.table_name LIKE '%POD%'
AND Upper (ac.table_name) =
'TB04NEWPOD'
AND ac.owner = 'DBO_BAKOX_LAMA'
ORDER BY 1;
CURSOR c_i1 (v_tablename VARCHAR2) IS
SELECT atc.table_name,
atc.column_name,
atc.column_id,
atc.data_type,
atc.data_length,
atc.data_precision
FROM all_tab_columns atc
WHERE atc.owner =
'DBO_BAKOX_LAMA'
AND atc.table_name =
v_tablename
--'TB04NEWPOD'
AND atc.column_id >= 7
ORDER BY atc.table_name,
atc.column_id;
CURSOR c_01 (v_tablename VARCHAR2) IS
SELECT atc.table_name,
atc.column_name,
atc.column_id,
atc.data_type,
atc.data_length,
atc.data_precision
FROM all_tab_columns atc
WHERE atc.owner =
'DBO_BAKOX_LAMA'
AND atc.table_name =
v_tablename
--'TB04NEWPOD'
AND atc.column_id >= 7
ORDER BY atc.table_name,
atc.column_id;
CURSOR c_j (v_tablename VARCHAR2) IS
--select a.nama_tabel, a.grup_view
--from (
SELECT Upper
(txv.table_name)
nama_tabel,
Substr
(Upper
(txv.view_name),
1,
Decode
(Instr
(Upper
(txv.view_name),
'PK', -1, 1),
0, LENGTH
(Upper
(txv.view_name)),
Instr
(Upper
(txv.view_name),
'PK', -1, 1)
- 1
)) grup_view
FROM tx_view_map txv
WHERE Upper (txv.table_name) =
v_tablename
GROUP BY Upper (txv.table_name),
Substr
(Upper (txv.view_name),
1,
Decode
(Instr
(Upper
(txv.view_name),
'PK', -1, 1),
0, LENGTH
(Upper
(txv.view_name)),
Instr
(Upper
(txv.view_name),
'PK', -1, 1)
- 1
))
--) a
--group by a.nama_tabel,a.grup_view
ORDER BY 1, 2;
CURSOR c_k (v_grup_view VARCHAR2) IS
SELECT a.nama_tabel, a.grup_view,
a.nama_view,
blt.title judul,
Nvl (blt.title2,
NULL) judul2,
a.podes, blt.jenis,
blt.kelompok,
Nvl (blt.tahun,
NULL) tahun,
a.id_area, a.view_ddl
FROM (SELECT Upper
(txv.table_name)
nama_tabel,
Substr
(Upper
(txv.view_name),
1,
Decode
(Instr
(Upper
(txv.view_name),
'PK', -1,
1),
0, LENGTH
(Upper
(txv.view_name)),
Instr
(Upper
(txv.view_name),
'PK', -1,
1)
- 1
)) grup_view,
Upper
(txv.view_name)
nama_view,
Decode
(Instr
(Upper
(txv.view_name),
'PK', -1, 1),
0, '2',
'4'
) id_area,
Decode
(Instr
(Upper
(txv.view_name),
'PK', -1, 1),
0, '0',
'1'
) podes,
REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(Upper
(txv.view_ddl),
', ',
','),
'KODE_PROVINSI',
'ID_PROV'),
'KODE_KABUPATEN',
'ID_KAB, TAHUN'),
'KODE_DESA,',
'ID_KEL,'),
'NAMA_KABUPATEN,',
''),
'NAMA_KECAMATAN,',
''),
'NAME_VILLAGE,',
' '),
',NAMA_KABUPATEN',
' '),
'DBO.',
' '),
'ID_KAB, TAHUN,ID_KEL, ',
'ID_PROV, ID_KAB, ID_KEC, ID_KEL, TAHUN, ')
view_ddl,
txv.view_ddl
orig_view_ddl
FROM tx_view_map txv
--where upper(txv.table_name)='TB04NEWPOD'
) a,
dbo_bakox_lama.title blt
WHERE Upper (a.grup_view) =
Upper (blt.tablename)
AND Upper (a.grup_view) =
v_grup_view
ORDER BY 1, 2, 3;
CURSOR c_l (v_view_name VARCHAR2) IS
SELECT a.*
FROM (SELECT Substr
(Upper
(pds.id_name),
1,
Decode
(Instr
(Upper
(pds.id_name),
'PK', -1,
1),
0, LENGTH
(Upper
(pds.id_name)),
Instr
(Upper
(pds.id_name),
'PK', -1,
1)
- 1
)) group_view,
Upper
(pds.id_name)
nama_view,
Upper
(pds.name_field)
nama_kolom,
pds.lengkap
AS deskripsi,
'1' AS podes,
REPLACE
(REPLACE
(Decode
(Instr
(pds.lengkap,
'(',
-1, 1),
0, NULL,
Substr
(pds.lengkap,
Instr
(pds.lengkap,
'(',
-1,
1)
- 1)
),
'(', ''),
')', '')
AS satuan,
'0' AS tematik
FROM dbo_bakox_lama.aliaspodes pds
WHERE EXISTS (
SELECT 1
FROM dbo_bakox_lama.aliaspodespk ppk
WHERE pds.id_name =
ppk.id_name
AND ppk.name_field =
pds.name_field)
UNION
SELECT Substr
(Upper
(pds.id_name),
1,
Decode
(Instr
(Upper
(pds.id_name),
'PK', -1,
1),
0, LENGTH
(Upper
(pds.id_name)),
Instr
(Upper
(pds.id_name),
'PK', -1,
1)
- 1
)) group_view,
Upper
(pds.id_name)
nama_view,
Upper
(pds.name_field)
nama_kolom,
pds.lengkap
AS deskripsi,
'0' AS podes,
To_char
(ia.satuwan)
satuan,
'1' AS tematik
FROM dbo_bakox_lama.aliaspodes pds,
dbo_bakox_lama.alias ia
WHERE NOT EXISTS (
SELECT 1
FROM dbo_bakox_lama.aliaspodespk ppk
WHERE pds.id_name =
ppk.id_name
AND ppk.name_field =
pds.name_field)
AND Substr
(Upper
(pds.id_name),
1,
Decode
(Instr
(Upper
(pds.id_name),
'PK', -1,
1),
0, LENGTH
(Upper
(pds.id_name)),
Instr
(Upper
(pds.id_name),
'PK', -1,
1)
- 1
)) =
Upper (ia.id_name)
AND Upper
(pds.name_field) =
Upper
(ia.name_field)) a,
tx_view_map txv
WHERE a.nama_view =
Upper (txv.view_name)
--and Upper (a.group_view) = upper(txv.view_name)
--and Upper (a.group_view) = 'TB04NEWPOD1'
AND Upper (txv.view_name) =
v_view_name
ORDER BY group_view, nama_kolom;
v_kolom_isi VARCHAR2 (5000);
sttmt VARCHAR2 (5500);
st_constraint VARCHAR2 (200);
v_id_sumber INTEGER;
cn_id_alias INTEGER;
cn_i INTEGER;
cn_j INTEGER;
cn_k INTEGER;
cn_l INTEGER;
BEGIN
SELECT MAX (ID)
INTO cn_id_alias
FROM sisn.alias;
cn_i := 0;
<>
FOR i IN c_i LOOP
cn_j := 0;
cn_i := cn_i + 1;
Dbms_output.put_line (cn_i || ' '
|| i.table_name);
--DBMS_OUTPUT.put_line (i.v_kolom_area);
<>
FOR n IN c_i1 (i.table_name) LOOP
cn_i := cn_i + 1;
v_kolom_isi :=
v_kolom_isi || n.column_name
|| ' ' || n.data_type || ' ('
|| n.data_length || '), '
|| Chr (10);
END LOOP l_kolom_isi;
st_constraint :=
'Constraint FK_' || i.table_name
|| '_KEL foreign key (ID_PROV,ID_KAB,ID_KEC,ID_KEL) references KELURAHAN(ID_PROV,ID_KAB,ID_KEC,ID_KEL)';
sttmt :=
i.v_kolom_area || Chr (10)
|| v_kolom_isi || st_constraint
|| ')';
Dbms_output.put_line (sttmt);
--EXECUTE IMMEDIATE sttmt;
SAVEPOINT p_insert_data_to_table;
-- Bontor 20081031
-- Migrating Data, inserting and filtering
-- -------------------------------------------------------------
<>
FOR m IN c_nama_kolom (i.table_name) LOOP
--deretan_kolom :='DAU';
deretan_kolom :=
deretan_kolom || ', '
|| j.nama_kolom;
--DBMS_OUTPUT.put_line (j.nama_kolom);
END LOOP l_insert_to_table;
BEGIN
SELECT atc.column_name
INTO v_kode
FROM all_tab_columns atc
WHERE atc.owner =
'DBO_BAKOX_LAMA'
AND atc.column_id =
h.tipe_level
AND atc.table_name =
i.table_name;
EXCEPTION
WHEN Value_error THEN
Dbms_output.put_line
('l_insert_to_table loop'
|| h.new_table_name
|| ', the column_name is too long.');
WHEN Too_many_rows THEN
Dbms_output.put_line
('l_insert_to_table loop'
|| h.new_table_name
|| ', too many rows.');
END;
IF h.tipe_level = 1 THEN
v_area_kolom :=
'RANK () OVER (ORDER BY '
|| v_kode || '), trim('
|| v_kode || ')';
where_kondisi :=
' WHERE EXISTS (SELECT 1 FROM provinsi p WHERE TRIM ('
|| i.table_name || '.'
|| v_kode || ') = p.id_prov)';
ELSIF h.tipe_level = 2 THEN
v_area_kolom :=
'RANK () OVER (ORDER BY '
|| v_kode || '),substr('
|| v_kode
|| ',1,2), trim(substr('
|| v_kode || ',3,2))';
where_kondisi :=
' where exists (select 1 from kabupaten kab where trim('
|| i.table_name || '.'
|| v_kode
|| ') = KAB.ID_PROV||KAB.ID_KAB)';
ELSE
v_area_kolom := '';
where_kondisi := '';
END IF;
BEGIN
sttmt_insert :=
'insert into ' || i.table_name
|| ' (' || h.insersi || ' '
|| deretan_kolom || ')'
|| Chr (10) || ' select '
|| v_area_kolom || ', '
|| v_tahun || ','
|| Trim (Substr (deretan_kolom,
3))
|| ' from DBO_BAKOX_LAMA.'
|| i.table_name
|| where_kondisi;
Dbms_output.put_line
(sttmt_insert);
--EXECUTE IMMEDIATE sttmt_insert;
EXCEPTION
WHEN Value_error THEN
Dbms_output.put_line
('Inserting to table: '
|| h.new_table_name
|| ', the size of data is exceeded.');
WHEN Dup_val_on_index THEN
Dbms_output.put_line
('Inserting to table: '
|| h.new_table_name);
Dbms_output.put_line
('Duplicate Value: '
|| Substr (Sqlerrm, 1,
200));
WHEN OTHERS THEN
Dbms_output.put_line
('Inserting to table>> '
|| Sqlcode || ', '
|| Substr (Sqlerrm, 1,
200));
END;
-- Bontor 20081031
/*
update dbo_bakox_lama.title
set status='1', MODIFIED_DATE =sysdate
where TABLENAME =h.table_name
;
*/
<>
FOR j IN c_j (i.table_name) LOOP
cn_j := cn_j + 1;
--BMS_OUTPUT.put_line (cn_i||cn_j||' '||j.Grup_View);
cn_k := 0;
SAVEPOINT p_insert_alias;
<>
FOR k IN c_k (j.grup_view) LOOP
cn_k := cn_k + 1;
Dbms_output.put_line ('l_k');
Dbms_output.put_line
(cn_i || cn_j
|| cn_k || ' '
|| k.nama_view
|| ': '
|| k.judul
|| ', '
|| k.judul2
|| ', '
|| k.jenis
|| ', '
|| k.kelompok
|| ', '
|| k.tahun
|| ', '
|| k.id_area);
-- Cari id_sumber
BEGIN
SELECT s.id_sumber
INTO v_id_sumber
FROM sisn.sumber s,
dbo_bakox_lama.sumber bs
WHERE s.nama_sumber =
bs.sumber_data
AND Upper (bs.nama_tabel) =
k.nama_view;
EXCEPTION
WHEN No_data_found THEN
v_id_sumber := NULL;
WHEN Too_many_rows THEN
v_id_sumber := 9999;
END;
/*
BEGIN
INSERT INTO sisn.alias
(ID,
nama_tabel,
judul_tabel,
judul_tabel_lengkap,
sisn,
podes,
jenis,
kelompok,
id_sumber,
judul_tabel_old,
id_tema,
id_area
)
VALUES (cn_id_alias,
k.Nama_view,
k.judul,
k.judul2,
'TRUE',
k.podes,
k.jenis,
k.kelompok,
Null,
j.Nama_View,
NULL, --v_id_tema,
'4'
);
EXCEPTION
WHEN Dup_val_on_index THEN
-- insert into log_aktivitas
INSERT INTO sisn.log_aktivitas
(kategori,
objek_1,
objek_2,
deskripsi,
error_log,
attr_1,
attr_2,
error_date,
session_login
)
VALUES ('Alias Insertion',
j.Nama_View,
m.judul,
NULL,
'DUP_VAL_ON_INDEX',
NULL,
NULL,
Sysdate,
USER
);
WHEN OTHERS THEN
v_errorcode := Sqlcode;
v_errortext :=
Substr (Sqlerrm,
1,
200
);
-- insert v_errorcode the into log_aktivitas
INSERT INTO sisn.log_aktivitas
(kategori,
objek_1,
objek_2,
deskripsi,
error_log,
attr_1,
attr_2,
error_date,
session_login
)
VALUES ('Alias Insertion',
j.Nama_View,
m.judul,
NULL,
v_errorcode ||
': ' ||
v_errortext,
NULL,
NULL,
Sysdate,
USER
);
END;
*/
Dbms_output.put_line (k.view_ddl);
cn_l := 0;
<>
FOR l IN c_l (k.nama_view) LOOP
cn_l := cn_l + 1;
--DBMS_OUTPUT.put_line ('l_l');
Dbms_output.put_line
(cn_i || cn_j
|| cn_k || ' '
|| cn_l || '.'
|| l.nama_kolom
|| ','
|| l.deskripsi
|| ','
|| l.podes
|| ','
|| l.satuan
|| ','
|| l.tematik);
/*
BEGIN
INSERT INTO sisn.alias_kolom
(ID,
nama_tabel,
nama_kolom,
judul_kolom,
podes_pk,
satuan,
tematik
)
VALUES (cn_i,
j.Nama_View,
l.nama_kolom,
l.descripsi,
l.podes,
l.satuan,
'FALSE'
);
--Untuk menentukan nilai dari podes_pk, trace apakah kolom pada tabel itu mengandung informasi tentang Podes;
--Kolom tematik diisi dengan TRUE FALSE dimana kolom tidak berisi angka dan nilai di kolom PODES_PK ='TRUE'
EXCEPTION
WHEN Dup_val_on_index THEN
ROLLBACK TO a_alias;
WHEN OTHERS THEN
v_errorcode := Sqlcode;
v_errortext :=
Substr (Sqlerrm,
1,
200
);
END;
*/
END LOOP l_l;
Dbms_output.put (Chr (10));
END LOOP l_k;
END LOOP l_j;
END LOOP l_i;
END;
0 comments:
Post a Comment