Oracle query in an array loop - weird result

Question

I am looping through an array and performing an insert/update. In the loop, I check if record exists. If not, perform insert. If it does, then perform update. STRING_SPLIT_FNC is a package that takes a string and splits it on delimiter (~) and stores the split strings in an array.

DECLARE

service_name VARCHAR(50) := 'Service1';
service_version VARCHAR(10) := '2016';
i INTEGER;
record_count NUMBER;
TYPE T_ARRAY_OF_VARCHAR IS TABLE OF VARCHAR(2000) INDEX BY BINARY_INTEGER;
main_array T_ARRAY_OF_VARCHAR;
split_array STRING_SPLIT_FNC.T_ARRAY;

BEGIN
    main_array(1) := '2014|2015|2016|~service.info~25500~NULL~1';
    main_array(2) := '2014|2015|2016|2017~service.path~/mypath/myfolder/myfile.zip~0';
    main_array(3) := '2014|2015|2016|2017|2018~service.date~Yes~NULL~1';

    SELECT COUNT(SERVICE_ID) INTO record_count FROM TEST_SERVICE WHERE SERVICE_DESC = service_name AND SERVICE_VERSION = service_version;
    IF record_count = 0 THEN
       INSERT INTO TEST_SERVICE(SERVICE_ID, SERVICE_DESC, SERVICE_VERSION) VALUES (SERVICE_SEQ.nextval, service_name, service_version);
    END IF;

SELECT SERVICE_ID INTO service_id FROM TEST_SERVICE WHERE SERVICE_DESC = service_name AND SERVICE_VERSION = service_version;


    i := main_array.FIRST;
    LOOP
        record_count := 0;
        split_array := STRING_SPLIT_FNC.SPLIT(main_array(i),'~');
        IF (INSTR(split_array(1), service_version) > 0) THEN
            DBMS_OUTPUT.PUT_LINE('Record count prior: ' || record_count || ' Service Id: ' || service_id || ' Config: ' || split_array(2));
            SELECT COUNT(*) INTO record_count FROM TEST_REF_SERVICE_CONFIG WHERE SERVICE_ID = service_id AND CONFIG_NAME = split_array(2);      
            DBMS_OUTPUT.PUT_LINE('Record count after: ' || record_count || ' Service Id: ' || service_id || ' Config: ' || split_array(2));
            IF record_count = 0 THEN          
               INSERT INTO TEST_REF_SERVICE_CONFIG (REF_CONFIG_ID, SERVICE_ID, CONFIG_NAME, DEFAULT_VALUE, ALLOW_OVERRIDE) VALUES (REF_SERVICE_CONFIG_SEQ.nextval, service_id, split_array(2), split_array(3), TO_NUMBER(split_array(5)));
               DBMS_OUTPUT.PUT_LINE('Inserted Service: ' || service_name || '[' || service_version || '], Config: ' || split_array(2) || ' [' || split_array(3) || '], Override: ' || split_array(5));
            ELSE
               record_count := 0;
               SELECT COUNT(REF_CONFIG_ID) INTO record_count FROM TEST_REF_SERVICE_CONFIG WHERE SERVICE_ID = service_id AND CONFIG_NAME = split_array(2) AND DEFAULT_VALUE = split_array(3);
               IF record_count = 0 THEN   
                   DBMS_OUTPUT.PUT_LINE('Record count after [in update part]: ' || record_count);
                   UPDATE TEST_REF_SERVICE_CONFIG SET DEFAULT_VALUE = split_array(3), ALLOW_OVERRIDE = split_array(5) WHERE SERVICE_ID = service_id AND CONFIG_NAME = split_array(2) AND DEFAULT_VALUE = split_array(3);
                   DBMS_OUTPUT.PUT_LINE('Updated Service: ' || service_name || '[' || service_version || '], Config: ' || split_array(2) || ' [' || split_array(3) || '], Override: ' || split_array(5));
               ELSE
                   record_count := 0;
                   DBMS_OUTPUT.PUT_LINE('No insert or update performed.  Service: ' || service_name || '[' || service_version || '], Config: ' || split_array(2) || ' [' || split_array(3) || '], Override: ' || split_array(5));
               END IF;
            END IF;
        ELSE 
            DBMS_OUTPUT.PUT_LINE('Specified service/version not found ' || service_name || '[' || service_version || ']');
        END IF;
        i := main_array.NEXT(i);

        EXIT WHEN i IS NULL;
    END LOOP;
    END;

Assume there are no records in the TEST_REF_SERVICE_CONFIG table. Now, let us say I run this for service_name = Service1 and service_version = 2015. The service_id is 500. All the 3 elements from the main_array get inserted.

Now, I run for service_name = Service1 and service_version = 2017. The service id is 502. It should insert only the last 2 records from main_array.

The query below returns record_count = 1 when it should be 0:

SELECT COUNT(*) INTO record_count FROM TEST_REF_SERVICE_CONFIG WHERE SERVICE_ID = service_id AND CONFIG_NAME = split_array(2);      

When I run the query separately, I get count as 0.

SELECT COUNT(*) FROM TEST_REF_SERVICE_CONFIG WHERE SERVICE_ID = 502 AND CONFIG_NAME = 'service.path';

Why is the query in the loop returning count as 1 when it should be 0? I also tried:

DECLARE
config_name VARCHAR(250);
...
LOOP
            record_count := 0;
            split_array := STRING_SPLIT_FNC.SPLIT(main_array(i),'~');
            IF (INSTR(split_array(1), service_version) > 0) THEN
               config_name := split_array(3);
               SELECT COUNT(*) INTO record_count FROM TEST_REF_SERVICE_CONFIG WHERE SERVICE_ID = service_id AND CONFIG_NAME = config_name;  

Now I get record_count as 121!!! Thanks in advance for your help.

Answer 1

I think what is happening here is due to you choosing the same name for your variables as your columns in the table.

When you are referring to a variable in your query, you should prefix that with a : symbol. So, instead of doing like this:

           SELECT COUNT(*) INTO record_count FROM TEST_REF_SERVICE_CONFIG WHERE SERVICE_ID = service_id AND CONFIG_NAME = config_name; 

You should do like this:

           SELECT COUNT(*) INTO record_count FROM TEST_REF_SERVICE_CONFIG WHERE SERVICE_ID = :service_id AND CONFIG_NAME = :config_name; 

or better yet, use different naming conventions for your variables and column names to make your code more readable.

The result of you omitting the : prefix is that Oracle interprets this as "give me all the records where the value of the SERVICE_ID column equals the value of the SERVICE_ID column ...", which would apply to ALL the records.

Answer 2

I think you might have an aliasing issue.

 WHERE **SERVICE_ID = service_id** AND CONFIG_NAME = split_array(2);      

The way to fix this is to change the variable name to be different from the column name. If this were a PL/SQL procedure or function you could prefix the variable name with the name of the procedure or function but you cannot do that in an anonymous block.