format string in oracle SQL to replace placeholders with variable names given at the end of the string

I am assuming that the last comma is the delimiter between the template string and the delimited terms. You can use a recursive sub-query factoring clause and simple string functions:

WITH terms ( value, terms, num_terms ) AS (
  SELECT SUBSTR( value, 1, INSTR( value, ', ', -1 ) - 1 ),
         SUBSTR( value, INSTR( value, ', ', -1 ) + 2 ),
         REGEXP_COUNT(
           SUBSTR( value, INSTR( value, ', ', -1 ) + 2 ),
           '.+?(\| |$)'
         )
  FROM   table_name
),
term_bounds ( rn, value, terms, start_pos, lvl ) AS (
  SELECT ROWNUM,
         REPLACE(
           value,
           '%' || num_terms,
           CASE num_terms
           WHEN 1
           THEN terms
           ELSE SUBSTR( terms, INSTR( terms, '| ', 1, num_terms - 1 ) + 2 )
           END
         ),
         terms,
         CASE
         WHEN num_terms > 1
         THEN INSTR( terms, '| ', 1, num_terms - 1 )
         ELSE 1
         END,
         num_terms
  FROM   terms
UNION ALL
  SELECT rn,
         REPLACE(
           value,
           '%' || (lvl - 1),
           CASE lvl - 1
           WHEN 1
           THEN SUBSTR( terms, 1, start_pos - 1 )
           ELSE SUBSTR(
                  terms,
                  INSTR( terms, '| ', 1, lvl - 2 ) + 2,
                  start_pos - INSTR( terms, '| ', 1, lvl - 2 ) - 2
                )
           END
         ),
         terms,
         CASE
         WHEN lvl > 2
         THEN INSTR( terms, '| ', 1, lvl - 2 )
         ELSE 1
         END,
         lvl - 1
  FROM   term_bounds
  WHERE  lvl > 1
)
SEARCH DEPTH FIRST BY rn SET rn_order
SELECT value
FROM   term_bounds
WHERE  lvl = 1;

Which, for the sample data:

CREATE TABLE table_name ( value ) AS
SELECT '%1||'-'||%2||%3, SITE_NO| SITE_NAME| COUNTRY' FROM DUAL UNION ALL
SELECT '0.1 * %1, WIND_RES' FROM DUAL UNION ALL
SELECT '%1, TOTAL' FROM DUAL UNION ALL
SELECT 'CASE WHEN LENGTH(%1) < 8 THEN NULL ELSE TO_DATE(%1,'yyyymmdd')END, MIN_DATE' FROM DUAL UNION ALL
SELECT '%1(+)=%3 and %2(+)=%4, ABC| LMN| PQR| XYZ' FROM DUAL UNION ALL
SELECT '%1, %2, %3, %4, %5, %6, %7, %8, %9, %10, %11, ONE| TWO| THREE| FOUR| FIVE| SIX| SEVEN| EIGHT| NINE| TEN| ELEVEN' FROM DUAL UNION ALL
SELECT '%%%%%%%7, HELLO| 1| 2| 3| 4| 5| 6' FROM DUAL

Outputs:

| VALUE                                                                                      |
| :----------------------------------------------------------------------------------------- |
| SITE_NO||'-'||SITE_NAME||COUNTRY                                                 |
| 0.1 * WIND_RES                                                                             |
| TOTAL                                                                                      |
| CASE WHEN LENGTH(MIN_DATE) < 8 THEN NULL ELSE TO_DATE(MIN_DATE,'yyyymmdd')END |
| ABC(+)=PQR and LMN(+)=XYZ                                                                  |
| ONE, TWO, THREE, FOUR, FIVE, SIX, SEVEN, EIGHT, NINE, TEN, ELEVEN                          |
| HELLO                                                                                      |

db<>fiddle here

Or as a PL/SQL function:

CREATE FUNCTION substitute_values(
  i_value          IN VARCHAR2,
  i_terms          IN VARCHAR2,
  i_term_delimiter IN VARCHAR2 DEFAULT '| '
) RETURN VARCHAR2 DETERMINISTIC
IS
  TYPE term_list_type IS TABLE OF VARCHAR2(200);
  v_start  PLS_INTEGER := 1;
  v_end    PLS_INTEGER;   
  v_index  PLS_INTEGER;
  v_terms  term_list_type := term_list_type();
  v_output VARCHAR2(4000) := i_value;
BEGIN
  LOOP
    v_end := INSTR(i_terms, i_term_delimiter, v_start);
    v_terms.EXTEND;
    IF v_end > 0 THEN
      v_terms(v_terms.COUNT) := SUBSTR(i_terms, v_start, v_end - v_start);
    ELSE
      v_terms(v_terms.COUNT) := SUBSTR(i_terms, v_start);
      EXIT;
    END IF;
    v_start := v_end + LENGTH(i_term_delimiter);
  END LOOP;
  
  LOOP
    v_index  := TO_NUMBER(REGEXP_SUBSTR(v_output, '^(.*?)%(\d+)', 1, 1, NULL, 2));
    IF v_index IS NULL THEN
      RETURN v_output;
    ELSIF v_index > v_terms.COUNT THEN
      RETURN NULL;
    END IF;
    v_output := REGEXP_REPLACE(v_output, '^(.*?)%(\d+)', '\1' || v_terms(v_index));
  END LOOP;
END;
/

Then:

SELECT SUBSTITUTE_VALUES(
         SUBSTR(value, 1, INSTR(value, ', ', -1) - 1),
         SUBSTR(value, INSTR(value, ', ', -1) + 2)
       ) AS value
FROM   table_name

Outputs:

VALUE
SITE_NO||'-'||SITE_NAME||COUNTRY
0.1 * WIND_RES
TOTAL
CASE WHEN LENGTH(MIN_DATE) < 8 THEN NULL ELSE TO_DATE(MIN_DATE,'yyyymmdd')END
ABC(+)=PQR and LMN(+)=XYZ
ONE, TWO, THREE, FOUR, FIVE, SIX, SEVEN, EIGHT, NINE, TEN, ELEVEN
HELLO

db<>fiddle here