plsql - Oracle query in an array loop - weird result -

i looping through array , performing insert/update. in loop, check if record exists. if not, perform insert. if does, perform update. string_split_fnc package takes string , splits on delimiter (~) , stores split strings in array.

declare  service_name varchar(50) := 'service1'; service_version varchar(10) := '2016'; integer; record_count number; type t_array_of_varchar table of varchar(2000) index 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) record_count test_service service_desc = service_name , service_version = service_version;     if record_count = 0        insert test_service(service_id, service_desc, service_version) values (service_seq.nextval, service_name, service_version);     end if;  select service_id service_id test_service service_desc = service_name , service_version = service_version;       := main_array.first;     loop         record_count := 0;         split_array := string_split_fnc.split(main_array(i),'~');         if (instr(split_array(1), service_version) > 0)             dbms_output.put_line('record count prior: ' || record_count || ' service id: ' || service_id || ' config: ' || split_array(2));             select count(*) record_count test_ref_service_config service_id = service_id , 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                          insert 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) record_count test_ref_service_config service_id = service_id , config_name = split_array(2) , default_value = split_array(3);                if record_count = 0                       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) service_id = service_id , config_name = split_array(2) , 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;         := main_array.next(i);          exit when null;     end loop;     end; 

assume there no records in test_ref_service_config table. now, let run service_name = service1 , service_version = 2015. service_id 500. 3 elements main_array inserted.

now, run service_name = service1 , service_version = 2017. service id 502. should insert last 2 records main_array.

the query below returns record_count = 1 when should 0:

select count(*) record_count test_ref_service_config service_id = service_id , config_name = split_array(2);       

when run query separately, count 0.

select count(*) test_ref_service_config service_id = 502 , config_name = 'service.path'; 

why query in loop returning count 1 when should 0? 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)                config_name := split_array(3);                select count(*) record_count test_ref_service_config service_id = service_id , config_name = config_name;   

now record_count 121!!! in advance help.

i think happening here due choosing same name variables columns in table.

when referring variable in query, should prefix : symbol. so, instead of doing this:

           select count(*) record_count test_ref_service_config service_id = service_id , config_name = config_name;  

you should this:

           select count(*) record_count test_ref_service_config service_id = :service_id , config_name = :config_name;  

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

the result of omitting : prefix oracle interprets "give me records value of service_id column equals value of service_id column ...", apply records.