How to pass string data type to number datatype in plsql

Question

I have procedure like this...

declare
v_psg varchar2(10);
id_no number;

begin

select value into v_psg from settings_am where key = 'PSG';
select id into id_no from product where to_char(psg) in (v_psg);
end;`

The value returned from select value into v_psg from settings_am where key = 'PSG'; would be

'1','2','3'

when i run this procedure i am returned with ora error - ORA-01403.

please advise how i should pass the v_psg value to psg column of product table?

EDIT - Tried with test case suggested

Answer 1

If you got ORA-01403, you were kind of lucky. It is the NO_DATA_FOUND error, which means that one (probably the first) query didn't return anything.

Those two statements could be combined into

select id 
from product
where to_char(psg) in (select value 
                       from settings_am 
                       where key = 'PSG'
                      );

Why would you select value first, and then use it in another query? Besides, it just wouldn't work. v_psg is declared as VARCHAR2 variable. The way you described it, it contains the following string: '1','2','3', as if this is what you have:

SQL> create table settings_am (key  varchar2(10),
  2                            value varchar2(20));    --> note size here

Table created.

SQL> insert into settings_am (key, value)
  2    values ('PSG', q'['1','2','3']');

1 row created.

SQL> select * From settings_am;

KEY        VALUE
---------- --------------------
PSG        '1','2','3'

SQL>

As you can see, I enlarged the value column size, although variable you declared says 10. Why? Because of

SQL> select length(value) from settings_am where key = 'PSG';

LENGTH(VALUE)
-------------
           11

i.e. you can't put something that is long 11 into something that accepts length 10.

Or, if your data actually contains 3 rows for the PSG key, are those values already enclosed into single quotes? If so, that's strange; people usually don't do that. Anyway, suppose that you managed to get string '1,2,3' (which is what I presume you actually have) into a VARCHAR2 variable, then you have to split it into rows in order to be able to use it in the IN clause:

SQL> create table product (id number, psg varchar2(10));

Table created.

SQL> insert into product (id, psg) values (100, '1');

1 row created.

SQL> insert into product (id, psg) values (200, '2');

1 row created.

SQL>

Query is then (where lines #3 - 5 represent splitting a string into rows):

SQL> select p.id
  2  from product p
  3  where p.psg in (select regexp_substr('&&v_psg', '[^,]+', 1, level)
  4                  from dual
  5                  connect by level <= regexp_count('&&v_psg', ',') + 1
  6                 );
Enter value for v_psg: 1,2,3

        ID
----------
       100
       200

So, wouldn't it be simpler to use

SQL> select id
  2  from product
  3  where to_char(psg) in (select value
  4                         from settings_am
  5                         where key = 'PSG'
  6                        );

        ID
----------
       100
       200

SQL>

Note that both options also show why your query is wrong: you can't put two values (rows) into a variable declared as id_no number; as you'd get TOO_MANY_ROWS error.

---

Finally, what is it that you'd want to do? What problem are you trying to solve? Apparently, except for special cases (only one row for each value) your query can't work. If you could provide test case (create table & insert into sample data), as well as expected output, it would be easier to help you.