CREATE OR REPLACE FUNCTION sitescope.get_id_servername ( v_value sitescopelog.servername%TYPE ) RETURN archive_servername.id%TYPE AS
        PRAGMA  AUTONOMOUS_TRANSACTION;
        v_id    archive_servername.id%TYPE;
    BEGIN
        SELECT id INTO v_id FROM archive_servername WHERE value = v_value;
        RETURN (v_id);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            INSERT INTO archive_servername ( value ) VALUES ( v_value );
            COMMIT;
            SELECT id INTO v_id FROM archive_servername WHERE value = v_value;
            RETURN (v_id);
    END;
/

##</code><code>##

CREATE OR REPLACE PROCEDURE sitescope.archive_from_sitescopelog AS
        CURSOR sitescopelog_cursor IS
            SELECT --+ INDEX(sitescopelog arc_idx_datex)
            * FROM sitescope.sitescopelog WHERE ROWNUM < 2000 FOR UPDATE;
        sitescopelog_record sitescopelog_cursor%ROWTYPE;
        v_exit_now  archive_disable.disable%TYPE;
        v_problem_count NUMBER;
    BEGIN
    <<outer_loop>>
        LOOP
        SELECT disable INTO v_exit_now FROM sitescope.archive_disable;
        IF v_exit_now = 1 THEN
            EXIT outer_loop;
        END IF;
        OPEN sitescopelog_cursor;
        <<inner_loop>>
            LOOP
                FETCH sitescopelog_cursor INTO sitescopelog_record;
                IF sitescopelog_cursor%NOTFOUND THEN
                    EXIT inner_loop;
                END IF;
                BEGIN
                    INSERT INTO sitescope.archive (
                        datex,
                        servername,
                        class,
                        sample,
                        category,
                        groupname,
                        monitorname,
                        status,
                        monitorid,
                        value1,
                        value2,
                        value3,
                        value4,
                        value5,
                        value6,
                        value7,
                        value8,
                        value9,
                        value10
                    ) VALUES (
                        TO_DATE         ( sitescopelog_record.datex, 'YYYY-MM-DD HH24:MI:SS' ),
                        get_id_servername   ( sitescopelog_record.servername    ),
                        get_id_class        ( sitescopelog_record.class     ),
                                      sitescopelog_record.sample,
                        get_id_category     ( sitescopelog_record.category  ),
                        get_id_groupname    ( sitescopelog_record.groupname ),
                        get_id_monitorname  ( sitescopelog_record.monitorname   ),
                        get_id_status       ( sitescopelog_record.status    ),
                                      sitescopelog_record.monitorid,
                                      sitescopelog_record.value1,
                                      sitescopelog_record.value2,
                                      sitescopelog_record.value3,
                                      sitescopelog_record.value4,
                                      sitescopelog_record.value5,
                                      sitescopelog_record.value6,
                                      sitescopelog_record.value7,
                                      sitescopelog_record.value8,
                                      sitescopelog_record.value9,
                                      sitescopelog_record.value10
                    );
                    DELETE FROM sitescope.sitescopelog WHERE CURRENT of sitescopelog_cursor;
                    COMMIT;
                EXCEPTION
                    WHEN OTHERS THEN
                        INSERT INTO sitescope.problem (
                            datex,
                            servername,
                            class,
                            sample,
                            category,
                            groupname,
                            monitorname,
                            status,
                            monitorid,
                            value1,
                            value2,
                            value3,
                            value4,
                            value5,
                            value6,
                            value7,
                            value8,
                            value9,
                            value10
                        ) VALUES (
                            sitescopelog_record.datex,
                            sitescopelog_record.servername,
                            sitescopelog_record.class,
                            sitescopelog_record.sample,
                            sitescopelog_record.category,
                            sitescopelog_record.groupname,
                            sitescopelog_record.monitorname,
                            sitescopelog_record.status,
                            sitescopelog_record.monitorid,
                            sitescopelog_record.value1,
                            sitescopelog_record.value2,
                            sitescopelog_record.value3,
                            sitescopelog_record.value4,
                            sitescopelog_record.value5,
                            sitescopelog_record.value6,
                            sitescopelog_record.value7,
                            sitescopelog_record.value8,
                            sitescopelog_record.value9,
                            sitescopelog_record.value10
                        );
                        DELETE FROM sitescope.sitescopelog WHERE CURRENT of sitescopelog_cursor;
                        COMMIT;

                END;
            END LOOP inner_loop;
        IF sitescopelog_cursor%ROWCOUNT = 0 THEN
            /* dbms_output.put_line ( 'ROWCOUNT = 0'); */
            EXIT outer_loop;
        END IF;
        CLOSE sitescopelog_cursor;
        END LOOP outer_loop;

        SELECT COUNT(*) INTO v_problem_count
            FROM sitescope.problem;
        IF v_problem_count != 0 THEN
            dbms_output.put_line ( v_problem_count || ' problem records');
        END IF;
    END;
/