DMF_GenerateInserts.sql

Source: DMF/PuC/AIF/DMF_GenerateInserts.sql

 
 
DECLARE    
 
    p_table_name    VARCHAR2(50) := 'W_RTL_PROMO_EXT_DTS';
    p_dmf_table_name VARCHAR2(50) := 'ORI_PROMOTION';
    p_owner         VARCHAR2(50) := 'RADM01'; --RMS01
    p_table_domain  VARCHAR2(50) := 'PROMOTION';
	p_flow_id		VARCHAR2(50) := 6001;
	p_interface_id	VARCHAR2(50) := 999999;
	p_destination 	VARCHAR2(50) := 1;
    v_sql   		CLOB;
    v_data_type  	VARCHAR2(400);
 
BEGIN
 
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('REGISTERING TABLE: ' || UPPER(p_dmf_table_name));
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------');
 
    ------------------------------------------------------------------
    -- INSERT INTO DSA_TABLES
    ------------------------------------------------------------------
    v_sql :=
        'INSERT INTO dmf.dsa_tables ' ||
        '(table_domain, table_name, table_owner, table_type, table_source, dsa_gen_date, status) VALUES (' ||
        '''' || p_table_domain || ''',' ||
        '''' || UPPER(p_dmf_table_name) || ''',' ||
        '''DSA'',' ||
        '''P'',' ||
        '''RMS'',' ||
        'SYSDATE,' ||
        '''A'');';
 
    DBMS_OUTPUT.PUT_LINE(v_sql);
    --EXECUTE IMMEDIATE v_sql;
 
    DBMS_OUTPUT.PUT_LINE('-------------------------------------');
 
    ------------------------------------------------------------------
    -- INSERT INTO MIGR_TABLES
    ------------------------------------------------------------------
    v_sql :=
        'INSERT INTO dmf.migr_tables ' ||
        '(table_domain, table_name, flow_id, interface_id, destination, load_ctrl_to_final_type, status) VALUES (' ||
        '''' || p_table_domain || ''',' ||
        '''' || UPPER(p_dmf_table_name) || ''',' || 
        '''' || p_flow_id || ''',' || 
        '''' || p_interface_id || ''',' || 
        '''' || p_destination || ''',' || 
        '''T'',' ||
        '''A'');';
 
    DBMS_OUTPUT.PUT_LINE(v_sql);
    --EXECUTE IMMEDIATE v_sql;
 
    DBMS_OUTPUT.PUT_LINE('-------------------------------------');
 
    ------------------------------------------------------------------
    -- COLUMNS LOOP
    ------------------------------------------------------------------
    FOR c IN (
        SELECT
            *
        FROM all_tab_columns
        WHERE owner = UPPER(p_owner)
        AND table_name = UPPER(p_table_name)
        AND COLUMN_ID IS NOT NULL
        ORDER BY column_id
    )
    LOOP
         ------------------------------------------------------------------
        -- BUILD FULL ORACLE DATATYPE (DDL STYLE)
        ------------------------------------------------------------------
        v_data_type := c.data_type;
 
        -- VARCHAR / CHAR
        IF c.data_type IN ('VARCHAR2','CHAR','NCHAR','NVARCHAR2') THEN
            IF c.char_used = 'C' THEN
                v_data_type := c.data_type || '(' || c.char_length || ' CHAR)';
            ELSE
                v_data_type := c.data_type || '(' || c.data_length || ')';
            END IF;
 
        -- NUMBER
        ELSIF c.data_type = 'NUMBER' THEN
            IF c.data_precision IS NULL THEN
                v_data_type := 'NUMBER';
            ELSIF c.data_scale IS NULL OR c.data_scale = 0 THEN
                v_data_type := 'NUMBER(' || c.data_precision || ')';
            ELSE
                v_data_type := 'NUMBER(' || c.data_precision || ',' || c.data_scale || ')';
            END IF;
 
        -- TIMESTAMP
        ELSIF c.data_type LIKE 'TIMESTAMP%' THEN
            IF c.data_scale IS NOT NULL THEN
                v_data_type := 'TIMESTAMP(' || c.data_scale || ')';
            ELSE
                v_data_type := 'TIMESTAMP';
            END IF;
        END IF;
 
        --------------------------------------------------------------
        -- DSA_COLUMNS INSERT
        --------------------------------------------------------------
        v_sql :=
            'INSERT INTO dmf.dsa_columns ' ||
            '(table_name, column_name, data_type, column_id, status) VALUES (' ||
            '''' || UPPER(p_dmf_table_name) || ''',' ||
            '''' || c.column_name || ''',' ||
            '''' || v_data_type  || ''',' ||
            c.column_id || ',' ||
            '''A'');';
 
        DBMS_OUTPUT.PUT_LINE(v_sql);
        --EXECUTE IMMEDIATE v_sql;
 
 
        --------------------------------------------------------------
        -- MIGR_COLUMNS INSERT
        --------------------------------------------------------------
        v_sql :=
            'INSERT INTO dmf.migr_columns ' ||
            '(table_name, column_name, data_type, mandatory_flg, status, dsa_table_name, dsa_column_name,dc_file_column_sequence,dc_column_name) VALUES (' ||
            '''' || UPPER(p_dmf_table_name) || ''',' ||
            '''' || c.column_name || ''',' ||
            '''' || v_data_type || ''',' ||
            '''' || CASE WHEN c.nullable = 'N' THEN 'Y' ELSE 'N' END || ''',' ||
            '''A'',' ||
            '''' || UPPER(p_dmf_table_name) || ''',' ||
            '''' || c.column_name || ''',' ||
            c.column_id || ',' ||
            '''' || c.column_name || 
            ''');';
 
        DBMS_OUTPUT.PUT_LINE(v_sql);
        --EXECUTE IMMEDIATE v_sql;
 
    END LOOP;
 
    COMMIT;
 
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('REGISTRATION COMPLETED');
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------');
 
END;
/