-
Notifications
You must be signed in to change notification settings - Fork 0
/
04loc_decorate_main_tbl.txt
61 lines (43 loc) · 2.29 KB
/
04loc_decorate_main_tbl.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
DECLARE
v_tab CHAR := chr(9);
v_line_break VARCHAR2(2 CHAR) := chr(10);
v_main_dataowner VARCHAR2(255 CHAR); --Defaults to CURRENT_SCHEMA if left empty
v_main_tbl VARCHAR2(255 CHAR) := 'WEIRDO_TBL';
v_entry_state_dataowner VARCHAR2(255 CHAR); --Defaults to v_main_dataowner if left empty
v_entry_state_tbl VARCHAR2(255 CHAR) := 'DH_ENTRY_STATE';
v_external_system_dataowner VARCHAR2(255 CHAR); --Defaults to v_main_dataowner if left empty
v_external_system_tbl VARCHAR2(255 CHAR) := 'DH_EXTERNAL_SYSTEM';
v_data_flow_dataowner VARCHAR2(255 CHAR); --Defaults to v_main_dataowner if left empty
v_data_flow_tbl VARCHAR2(255 CHAR) := 'DH_DATA_FLOW';
v_sql_stmt CLOB;
BEGIN
IF v_main_dataowner IS NULL
THEN
SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') INTO v_main_dataowner FROM dual;
END IF;
IF v_entry_state_dataowner IS NULL
THEN
v_entry_state_dataowner := v_main_dataowner;
END IF;
IF v_external_system_dataowner IS NULL
THEN
v_external_system_dataowner := v_main_dataowner;
END IF;
IF v_data_flow_dataowner IS NULL
THEN
v_data_flow_dataowner := v_main_dataowner;
END IF;
v_sql_stmt := 'ALTER TABLE "' || v_main_dataowner || '"."' || v_main_tbl || '" ADD' || v_line_break
|| '(' || v_line_break
|| v_tab || '"STATUS" REFERENCES "' || v_entry_state_dataowner || '"."' || v_entry_state_tbl || '"("OID") ENABLE,' || v_line_break
|| v_tab || '"STATUS_DTL" VARCHAR2(63 CHAR),' || v_line_break
|| v_tab || '"TARGET_SYSTEM" REFERENCES "' || v_external_system_dataowner || '"."' || v_external_system_tbl || '"("OID") ENABLE,' || v_line_break
|| v_tab || '"CURRENT_MOD_SRC_TYPE" VARCHAR2(63 CHAR),' || v_line_break
|| v_tab || '"CURRENT_MOD_SRC" VARCHAR2(63 CHAR),' || v_line_break
|| v_tab || '"SOURCE_OBJ" REFERENCES "' || v_data_flow_dataowner || '"."' || v_data_flow_tbl || '"("OID") ENABLE NOT NULL ENABLE,' || v_line_break
|| v_tab || '"TARGET_OBJ" REFERENCES "' || v_data_flow_dataowner || '"."' || v_data_flow_tbl || '"("OID") ENABLE' || v_line_break
|| ')';
dbms_output.put_line(v_sql_stmt);
dbms_output.put_line(NULL);
--EXECUTE IMMEDIATE v_sql_stmt;
END;