SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE content IS TABLE OF VARCHAR2(50)
    INDEX BY BINARY_INTEGER;
  
  v_tab  content;
  v_idx  NUMBER;
  counter NUMBER := 1;
  -- v_content VARCHAR2(50);
  v_content_value VARCHAR2(50);
  v_total NUMBER;
BEGIN
  -- Initialise the collection.
  << load_loop >>
  FOR i IN (select replace('Album:8|Application:25|Game:25|Music:8|Track:8','|',chr(10)) as content_text from dual) LOOP
    v_tab(counter) := i.content_text;
  END LOOP load_loop;
  
  -- Traverse sparse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The content: ' || v_tab(v_idx));
    -- v_content := v_tab(v_idx);
    select replace(replace(replace(replace(replace(v_tab(v_idx),'Album:',''),'Application:',''),'Game:',''),'Music:',''),'Track:','')
    as content_value_number
    into v_content_value
    from dual;
    DBMS_OUTPUT.PUT_LINE('The content value: ' || chr(10) ||  v_content_value);
    v_total := v_total + to_number(v_content_value);
    DBMS_OUTPUT.PUT_LINE('The content value in number : ' || chr(10) || v_total);
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 29
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

    select cast(v_content_value as INTEGER)  into v_total
    from dual;

SQL> with data as (select 'Album:8|Application:25|Game:25|Music:8|Track:8' val from dual)
  2  select xmlquery(regexp_replace(val,'[^0-9]+','+') returning content).getNumberVal() total
  3  from data
  4  /
     TOTAL
----------
        74