I'm trying to use a local variable's value in another query in PLSQL package but every time the 2nd query is returning null value

Question

BEGIN
    --{
        SELECT CONCAT(CONCAT('''',REPLACE(VALUE_1, ',', ''',''')),'''') into glv_v_PROC_FEE
        FROM TABLE_A  ;
        EXCEPTION WHEN NO_DATA_FOUND THEN   
        glv_v_PROC_FEE := ' ';
    --}
END;
dbms_output.put_line('glv_v_PROC_FEE: '||glv_v_PROC_FEE);   
BEGIN
    --{
        SELECT sum(ORDER) INTO  glv_v_PROCESSING_FEE_WITH_VAT
        FROM TABLE_B WHERE COL1 in (glv_v_PROC_FEE);
        EXCEPTION WHEN NO_DATA_FOUND THEN   
        glv_v_PROCESSING_FEE_WITH_VAT := ' ';
        
    --}
END;
dbms_output.put_line('glv_v_PROCESSING_FEE_WITH_VAT: '||glv_v_PROCESSING_FEE_WITH_VAT);

image attached shows local variable's value

Answer 1

You can't use such a value in IN; you'll have to split it to rows, such as

SELECT sum(ORDER) INTO  glv_v_PROCESSING_FEE_WITH_VAT
FROM TABLE_B 
WHERE COL1 in (select regexp_substr(glv_v_PROC_FEE, '[^,]+', 1, level)
               from dual
               connect by level <= regexp_count(glv_v_proc_fee, ',') + 1
              );

---

On the other hand, why wouldn't you skip two queries and use just

select sum(b.order)
  into glv_v_processing_fee_with_vat
  from table_b b join table_a a on a.value_1 = b.col1;