DataHub/src/01gl_create_entry_state_tbl.txt at main · GateDigger/DataHub · GitHub

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
DECLARE

    v_tab CHAR := chr(9);
    v_line_break VARCHAR2(2 CHAR) := chr(10);

    v_entry_state_dataowner VARCHAR2(255 CHAR); --Defaults to CURRENT_SCHEMA if left empty
    v_entry_state_tbl VARCHAR2(255 CHAR) := 'DH_ENTRY_STATE';
    v_entry_state_dspl_attr VARCHAR2(255 CHAR) := 'DISPLAY_CODE';

    v_sql_stmt CLOB;

BEGIN

    IF v_entry_state_dataowner IS NULL
    THEN
        SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') INTO v_entry_state_dataowner FROM dual;
    END IF;

    v_sql_stmt := 'CREATE TABLE "' || v_entry_state_dataowner || '"."' || v_entry_state_tbl || '"' || v_line_break
        || '(' || v_line_break
        || v_tab || '"OID" NUMBER GENERATED ALWAYS AS IDENTITY (MINVALUE 1 NOMAXVALUE INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE NOKEEP NOSCALE) NOT NULL ENABLE,' || v_line_break
        || v_tab || '"' || v_entry_state_dspl_attr || '" VARCHAR2(31 CHAR) NOT NULL ENABLE,' || v_line_break
        || v_tab || 'CONSTRAINT "' || v_entry_state_tbl || '_PK" PRIMARY KEY ("OID") USING INDEX ENABLE' || v_line_break
        || ')';
    dbms_output.put_line(v_sql_stmt);
    dbms_output.put_line(NULL);
    EXECUTE IMMEDIATE v_sql_stmt;

    v_sql_stmt := 'COMMENT ON TABLE "' || v_entry_state_dataowner || '"."' || v_entry_state_tbl || '" IS ''Table of all possible entry states''';
    dbms_output.put_line(v_sql_stmt);
    dbms_output.put_line(NULL);
    --EXECUTE IMMEDIATE v_sql_stmt;

    v_sql_stmt := 'BEGIN' || v_line_break
        || v_tab || 'INSERT INTO "' || v_entry_state_dataowner || '"."' || v_entry_state_tbl || '" ("' || v_entry_state_dspl_attr || '") VALUES (''in_process_queue'');' || v_line_break
        || v_tab || 'INSERT INTO "' || v_entry_state_dataowner || '"."' || v_entry_state_tbl || '" ("' || v_entry_state_dspl_attr || '") VALUES (''in_export_queue'');' || v_line_break
        || v_tab || 'INSERT INTO "' || v_entry_state_dataowner || '"."' || v_entry_state_tbl || '" ("' || v_entry_state_dspl_attr || '") VALUES (''in_user_queue'');' || v_line_break
        || v_tab || 'INSERT INTO "' || v_entry_state_dataowner || '"."' || v_entry_state_tbl || '" ("' || v_entry_state_dspl_attr || '") VALUES (''in_backlog'');' || v_line_break
        || v_tab || 'INSERT INTO "' || v_entry_state_dataowner || '"."' || v_entry_state_tbl || '" ("' || v_entry_state_dspl_attr || '") VALUES (''in_dumpster'');' || v_line_break
        || v_tab || 'INSERT INTO "' || v_entry_state_dataowner || '"."' || v_entry_state_tbl || '" ("' || v_entry_state_dspl_attr || '") VALUES (''in_target_system_queue'');' || v_line_break
        || v_tab || 'INSERT INTO "' || v_entry_state_dataowner || '"."' || v_entry_state_tbl || '" ("' || v_entry_state_dspl_attr || '") VALUES (''in_target_system'');' || v_line_break
        || v_tab || 'COMMIT;' || v_line_break
        || 'END;';

    dbms_output.put_line(v_sql_stmt);
    dbms_output.put_line(NULL);
    --EXECUTE IMMEDIATE v_sql_stmt;

END;