FUNCTION TO REMOVE SPECIFIC CHARACTER FOR A STRING AND MULTIPLY THE NUMBERS

create or replace function  bom_multi_drill_down  (v_input_temp varchar2  )  return number is 
   v_input  varchar2(200);
   v_output   number;
   v_dummy    number;
   v_loop     VARCHAR2 (100);
  
  
BEGIN

v_input := v_input_temp;

v_output := 1;

   WHILE TRUE
   LOOP
      v_dummy := to_number(SUBSTR (v_input, 1, INSTR (v_input, ',') - 1));         
      IF v_dummy IS NULL
      THEN
         v_output := v_output ;
      ELSE
         v_output := (v_output * v_dummy) ;
         
        
      END IF;

      v_input := SUBSTR (v_input, INSTR (v_input, ',') + 1);

      IF INSTR (v_input, ',') = 0
      THEN
         IF v_input IS NULL
         THEN
            v_output := v_output ;
         ELSE
            v_output := (v_output * v_input);
         END IF;

         v_input := NULL;
      END IF;

 

      IF v_input IS NULL
      THEN
         EXIT;
      END IF;
   END LOOP;
  
   DBMS_OUTPUT.put_line ('OUTPUT : ' || v_output);
  
   return(v_output);
  
END bom_multi_drill_down ;