SQL & PL/SQL

In this blog, I will share a PL/SQL procedure that validates data from the CATV_INDUSTRY_DATA_COLLECTION table in Oracle. The procedure checks various fields for validity, collects error messages, and updates the records accordingly. However, as the table contains over 800,000 records, performance issues and data corruption have become significant concerns. I am reaching out to the Oracle community for insights on optimizing this procedure for better performance and reliability.

I look forward to your suggestions and insights on how to enhance this PL/SQL procedure for better performance and reliability.

Here’s the current implementation of the validate_excel_data procedure:

PROCEDURE validate_excel_data IS
    TYPE t_data IS TABLE OF CATV_INDUSTRY_DATA_COLLECTION%ROWTYPE;
    v_data t_data;
    CURSOR cur IS
        SELECT * FROM CATV_INDUSTRY_DATA_COLLECTION;

    TYPE t_error IS TABLE OF VARCHAR2(4000);
    v_error_msg t_error;

    -- Temporary collections for valid IDs
    TYPE t_mso_ids IS TABLE OF AMS_MSO_MASTER.MSO_ID%TYPE;
    TYPE t_channel_ids IS TABLE OF AMS_CHANNEL_MASTER_IND.CHANNEL_ID%TYPE;
    v_valid_mso_ids t_mso_ids;
    v_valid_channel_ids t_channel_ids;

BEGIN
    -- Fetch valid MSO_IDs and CHANNEL_IDs into collections
    SELECT MSO_ID BULK COLLECT INTO v_valid_mso_ids FROM AMS_MSO_MASTER;
    SELECT CHANNEL_ID BULK COLLECT INTO v_valid_channel_ids FROM AMS_CHANNEL_MASTER_IND;

    OPEN cur;
    LOOP
        FETCH cur BULK COLLECT INTO v_data LIMIT 1000;
        EXIT WHEN v_data.COUNT = 0;

        -- Initialize error messages array
        v_error_msg := t_error();

        FOR i IN 1..v_data.COUNT LOOP
            DECLARE
                v_temp_error_msg VARCHAR2(4000) := NULL;
                v_mso_id_valid BOOLEAN := FALSE;
                v_channel_id_valid BOOLEAN := FALSE;
            BEGIN
                -- Check if MSO_ID is valid
                FOR j IN 1..v_valid_mso_ids.COUNT LOOP
                    IF v_data(i).MSO_ID = v_valid_mso_ids(j) THEN
                        v_mso_id_valid := TRUE;
                        EXIT; -- Exit loop if found
                    END IF;
                END LOOP;

                -- Check if CHANNEL_ID is valid
                FOR j IN 1..v_valid_channel_ids.COUNT LOOP
                    IF v_data(i).CHANNEL_ID = v_valid_channel_ids(j) THEN
                        v_channel_id_valid := TRUE;
                        EXIT; -- Exit loop if found
                    END IF;
                END LOOP;

                -- Validation checks and error message generation
                IF NVL(TRIM(v_data(i).MSO_ID), '0') = '0' THEN
                    v_temp_error_msg := v_temp_error_msg || 'MSO_ID is missing, ';
                ELSIF NOT v_mso_id_valid THEN
                    v_temp_error_msg := v_temp_error_msg || 'Invalid MSO_ID, ';
                END IF;

                IF v_data(i).PACK_TYPE IS NULL OR v_data(i).PACK_TYPE NOT IN ('SD', 'HD') THEN
                    v_temp_error_msg := v_temp_error_msg || 'Invalid PACK_TYPE, ';
                END IF;

                IF NVL(TRIM(v_data(i).AFFILIATE), '0') = '0' THEN
                    v_temp_error_msg := v_temp_error_msg || 'AFFILIATE is missing, ';
                ELSIF NOT REGEXP_LIKE(TRIM(v_data(i).AFFILIATE), '^[0-9.]+$') THEN
                    v_temp_error_msg := v_temp_error_msg || 'AFFILIATE contains invalid characters, ';
                END IF;

                IF v_data(i).PACK_MRP_PRICE IS NULL THEN
                    v_temp_error_msg := v_temp_error_msg || 'PRICE is missing, ';
                ELSIF TRIM(v_data(i).PACK_MRP_PRICE) = '' THEN
                    v_temp_error_msg := v_temp_error_msg || 'PRICE is blank, ';
                ELSIF NOT REGEXP_LIKE(TRIM(v_data(i).PACK_MRP_PRICE), '^[0-9.]+$') THEN
                    v_temp_error_msg := v_temp_error_msg || 'PRICE contains invalid characters, ';
                END IF;

                IF NVL(TRIM(v_data(i).CHANNEL_ID), '0') = '0' THEN
                    v_temp_error_msg := v_temp_error_msg || 'CHANNEL_ID is missing, ';
                ELSIF NOT v_channel_id_valid THEN
                    v_temp_error_msg := v_temp_error_msg || 'Invalid CHANNEL_ID, ';
                END IF;

                IF v_data(i).CHANNEL_MODE IS NULL OR v_data(i).CHANNEL_MODE NOT IN ('FTA', 'PAY') THEN
                    v_temp_error_msg := v_temp_error_msg || 'Invalid CHANNEL_MODE, ';
                END IF;

                IF v_data(i).BOUQUET_TYPE IS NULL OR UPPER(TRIM(v_data(i).BOUQUET_TYPE)) NOT IN ('A LA CARTE', 'BOUQUET') THEN
                    v_temp_error_msg := v_temp_error_msg || 'Invalid BOUQUET_TYPE, ';
                END IF;

                IF v_data(i).SERVICE_PROVIDER_TYPE IS NULL OR v_data(i).SERVICE_PROVIDER_TYPE NOT IN ('DTH', 'MSO', 'KEY', 'IND') THEN
                    v_temp_error_msg := v_temp_error_msg || 'Invalid SERVICE_PROVIDER_TYPE, ';
                END IF;

                -- Remove trailing comma and space, if any
                IF v_temp_error_msg IS NOT NULL THEN
                    v_temp_error_msg := RTRIM(v_temp_error_msg, ', ');
                END IF;

                -- Add error message to collection
                v_error_msg.EXTEND;
                v_error_msg(i) := v_temp_error_msg;
            END;
        END LOOP;

        -- Update records with error messages using FORALL
        FORALL i IN 1..v_data.COUNT
            UPDATE CATV_INDUSTRY_DATA_COLLECTION
            SET ERROR_MSG = v_error_msg(i)
            WHERE ID = v_data(i).ID;

        COMMIT; -- Commit after each batch to avoid rollback segment issues
    END LOOP;

    CLOSE cur;
END validate_excel_data;