Re^2: SQL statment to insert if none exits and to obtain the primary key of a record with one visit!

Or, for those of us who had to do this sort of thing _before_ Oracle 9i, you had do some nasty things with stored procedures. For instance, when I had to deal with sitescope, it's idea of 'logging to a server' was a 0NF table of varchar2(255) fields. So I had a stored procedure to get things from that table to 3NF, which used a whole bunch of functions like:

CREATE OR REPLACE FUNCTION sitescope.get_id_servername ( v_value sites
+copelog.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_va
+lue;
        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;
/
[download]

so I could then do:

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 UPDA
+TE;
        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.serv
+ername    ),
                        get_id_class        ( sitescopelog_record.clas
+s     ),
                                      sitescopelog_record.sample,
                        get_id_category     ( sitescopelog_record.cate
+gory  ),
                        get_id_groupname    ( sitescopelog_record.grou
+pname ),
                        get_id_monitorname  ( sitescopelog_record.moni
+torname   ),
                        get_id_status       ( sitescopelog_record.stat
+us    ),
                                      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 o
+f 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 CURRE
+NT 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 record
+s');
        END IF;
    END;
/
[download]

(which I triggered from a perl script, that maintained an error log ... therefore this is just barely clinging to being on-topic)

Comment on Re^2: SQL statment to insert if none exits and to obtain the primary key of a record with one visit! Select or Download Code