Can't save comma separated number string in varchar2()

Question

I've got a list of items I want to add in a single click, for this purpose I created a table with a column with a type varchar2(4000), in this column I want to list id's that refer to the other table so I can paste the value of this column as a parameter. ex. select t.* from table_name t where t.point_id in (varchar2 string of comma seprated point_ids).

The problem I've got is that when I put more than 1 id in the varchar2 field I get ORA-06502: PL/SQL: numeric or value error: character to number conversion error

How can I avoid this error? My field is varchar2, not number and I don't want it to be converted. I need the value I'm parsing to be saved. ex. (11, 12)

Picture of my Table:

EDIT: Note - My select is working okay, the problem I'm having is with saving the information.

My Insert :

procedure lab_water_pointsgroup (v_group_id lab_water_pointsgroups.group_name%type,
                          v_group_name lab_water_pointsgroups.group_code%type,
                          v_group_code lab_water_pointsgroups.lab_points_ids%type,
                          v_lab_points_ids lab_water_pointsgroups.group_id%type) as
begin
update lab_water_pointsgroups
   set group_name = v_group_name,
       group_code = v_group_code,
       lab_points_ids = v_lab_points_ids
 where  group_id = v_group_id;
if ( SQL%RowCount = 0 ) then
insert into lab_water_pointsgroups
  (group_id, group_name, group_code, lab_points_ids)
values
  (v_group_id, v_group_name, v_group_code, v_lab_points_ids);
end if;
end;

That error message is probably a good thing, because it is generally not good to store CSV data in a single SQL column. Why do you want to store CSV like this? — Tim Biegeleisen
Are you sure that column's datatype is VARCHAR2? Please, post table's description (run DESC table_name in SQL*Plus and copy the result over here - edit the question, don't post it as a comment). — Littlefoot
I get invalid SQL Statement trying to run desc. Edit: Updated with picture of my table. — Alexander Hristov
can you post your code for insert statement in the question. — Gagan Deep
You shouldn't be storing comma separated values to begin with. Normalize your data model and you don't have that problem — a_horse_with_no_name

Peter Peter · Accepted Answer · 2019-04-01T10:29:03

Not sure how exactly I can help you here as you gave no example. Have a look at the below demo, maybe the contruct with xmltable solves your problem. HTH KR

create table testtab (id number);
insert into  testtab values (1);

select * from testtab where id in ('1');   -- works
select * from testtab where id in (1);     -- works
select * from testtab where id in (1,2);   -- works
select * from testtab where id in ('1,2'); -- ORA-01722: invalid number
select * from testtab where id in (select to_number(xt.column_value) from xmltable('1,2') xt); -- works

Can't save comma separated number string in varchar2()

3 Answers