CREATE TABLE tmp_mstr(NAME VARCHAR2(100),VAL VARCHAR2(100))
/
INSERT INTO tmp_mstr values('KEY1',q'[B2.LKP_VAL||'-'||B4.LKP_VAL]')
/
INSERT INTO tmp_mstr values('KEY1',q'[B1.LKP_VAL||'-'||B3.LKP_VAL]')
/
CREATE TABLE TMP_DYNASQL(ID NUMBER,NAME VARCHAR2(100))
/
INSERT INTO TMP_DYNASQL values(2,q'[B2.LKP_VAL||'-'||B4.LKP_VAL]')
/
INSERT INTO TMP_DYNASQL values(2,q'[B1.LKP_VAL||'-'||B3.LKP_VAL]')
/
COMMIT
/
--In SQLPLUS define
SQL>var vref refcursor
--Run Below code in SQLPLUS
DECLARE
v_sel_str VARCHAR2(500);
p_select_str VARCHAR2(500);
v_strt_str VARCHAR2(500);
v_len NUMBER;
v_sql VARCHAR2(5000);
--vref SYS_REFCURSOR;
BEGIN
--select q'[B2.LKP_VAL||'-'||B4.LKP_VAL,B1.LKP_VAL||'-'||B3.LKP_VAL]' into v_sel_str from dual;
SELECT NAME into v_sel_str FROM TMP_DYNASQL where rownum=1;

      dbms_output.put_line(v_sel_str);
      v_len := LENGTH(v_sel_str) - LENGTH(REPLACE(v_sel_str, ',', ''));
      dbms_output.put_line('delimited string is ::'||substr(v_sel_str,1,instr(v_sel_str,',')-1));
      FOR i in 1 .. v_len + 1 LOOP
        
        v_strt_str := SUBSTR(v_sel_str,
                             1,
                             CASE
                               WHEN INSTR(v_sel_str, ',') = 0 THEN
                                LENGTH(v_sel_str)
                               ELSE
                                INSTR(v_sel_str, ',') - 1
                             END);
     --dbms_output.put_line(v_strt_str);           
     --v_sel_str := substr(v_sel_str, instr(v_sel_str, ',') + 1);
     v_sql     := v_sql ||
                     'SELECT name, val
                          FROM tmp_mstr
                      WHERE name = :v_strt_str';
        v_sel_str := substr(v_sel_str, instr(v_sel_str, ',') + 1);
        --v_sql := SUBSTR(v_sql, 1, INSTR(v_sql, 'U', -1) - 1);
        DBMS_OUTPUT.PUT_LINE('v_sql '||v_sql);
        DBMS_OUTPUT.PUT_LINE('v_sql '||v_strt_str);
    END LOOP;
    open :vref for v_sql using v_strt_str;
end;
SQL> print :vref;

no rows selected