packages tree.create_dml_errors_view

tree.create_dml_errors_view

Repository spec: tree.purge_old, body: tree.purge_old

Signature

PROCEDURE create_dml_errors_view;

Show code (68 lines)

PROCEDURE create_dml_errors_view
AS
    q_block     VARCHAR2(32767);
    q           CLOB;
    comments    DBMS_UTILITY.LNAME_ARRAY;  -- TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
BEGIN
    DBMS_LOB.CREATETEMPORARY(q, TRUE);

    -- backup current comments
    FOR c IN (
        SELECT table_name, column_name, comments
        FROM user_col_comments
        WHERE table_name = tree.view_dml_errors
    ) LOOP
        comments(comments.count) :=
            'COMMENT ON COLUMN ' || c.table_name || '.' || c.column_name ||
            ' IS ''' || REPLACE(c.comments, '''', '''''') || '''';
    END LOOP;

    -- create header with correct data types
    q_block :=
        'CREATE OR REPLACE VIEW ' || tree.view_dml_errors || ' (' ||
        '    log_id, action, table_name, table_rowid, dml_rowid, err_message' || CHR(10) ||
        ') AS' || CHR(10) ||
        'SELECT 0, ''-'', ''-'', ''UROWID'', ROWID, ''-''' || CHR(10) ||
        'FROM DUAL' || CHR(10) ||
        'WHERE ROWNUM = 0' || CHR(10) ||
        '--' || CHR(10) ||
        '-- THIS VIEW IS GENERATED' || CHR(10) ||
        '--' || CHR(10);
    --
    DBMS_LOB.WRITEAPPEND(q, LENGTH(q_block), q_block);
    q_block := '';

    -- append all existing tables
    FOR c IN (
        SELECT
            RTRIM(t.table_name, tree.dml_tables_postfix) AS data_table,
            t.owner || '.' || t.table_name              AS error_table
        FROM all_tables t
        WHERE t.owner           = tree.dml_tables_owner
            AND t.table_name    LIKE '%' || dml_tables_postfix
        ORDER BY 1
    ) LOOP
        q_block := 'UNION ALL' || CHR(10);
        q_block := q_block || 'SELECT' || CHR(10);
        q_block := q_block || '    TO_NUMBER(e.ora_err_tag$),' || CHR(10);
        q_block := q_block || '    e.ora_err_optyp$,' || CHR(10);
        q_block := q_block || '    ''' || c.data_table || ''',' || CHR(10);
        q_block := q_block || '    CAST(e.ora_err_rowid$ AS VARCHAR2(30)),' || CHR(10);
        q_block := q_block || '    e.ROWID,' || CHR(10);
        q_block := q_block || '    e.ora_err_mesg$' || CHR(10);
        q_block := q_block || 'FROM ' || c.error_table || ' e' || CHR(10);
        --
        DBMS_LOB.WRITEAPPEND(q, LENGTH(q_block), q_block);
        q_block := '';
    END LOOP;
    --
    EXECUTE IMMEDIATE q;

    -- add comments
    FOR i IN comments.FIRST .. comments.LAST LOOP
        EXECUTE IMMEDIATE comments(i);
    END LOOP;
EXCEPTION
WHEN OTHERS THEN
    tree.raise_error();
END;