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.

The code you'\ve posted doesn't have a PL/SQL variable called service_id, so SELECT SERVICE_ID INTO service_id ... will fail; have you just left that out of the code you posted? Having local variables with the same names as table columns is going to cause you problems though - probably causing what you're seeing... — Alex Poole
– Alex Poole, Commented Apr 6, 2016 at 13:43

GTG · Accepted Answer · 2016-04-06 13:42:48Z

2

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.

answered Apr 6, 2016 at 13:42

GTG

4,9542 gold badges25 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex Poole Over a year ago

I think service_id is supposed to be a local PL/SQL variable, not a bind variable, and just isn't shown in the posted declare section; if so a colon isn't right. The names of the other variables may support that but I might be wrong. Changing the name is the way to fix this though.

saz Over a year ago

Thank you so much! This was frustrating me to no end. Learnt something today. I have changed variable names, prefixed with v_ and now it works fine!

Alex Poole Over a year ago

@user1100221 - see also Column Name Precedence and the rest of that section of the documentation.

Jrmde · Accepted Answer · 2016-04-06 14:41:25Z

1

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.

edited Apr 6, 2016 at 14:41

answered Apr 6, 2016 at 13:51

Jrmde

113 bronze badges

3 Comments

Alex Poole Over a year ago

Prefix which service_id? Both are being seen as the column name at the moment, so qualifying either with the table name won't make any difference. In a named PL/SQL block (i.e. a stored procedure) you could prefix one of them with the block name to show you mean the local variable. Or you could use block labelling. Or change the variable name to avoid the clash; some people prefer to use qualification though.

Jrmde Over a year ago

You are quite correct, good catch. I've modified my answer to reflect the correct information.

Alex Poole Over a year ago

You can label an anonymous block too (I linked to that in the previous comment) but I'm not sure I've ever seen that done in the real world.

Collectives™ on Stack Overflow

Oracle query in an array loop - weird result

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related