DECLARE
   fhandler          UTL_FILE.file_type;
   buf               VARCHAR2 (4000);
   v_service_id      NUMBER;                        --:= value from the file;
   v_result3_value   NUMBER;                        --:= value from the file;

--
   CURSOR unbilled_usage
   IS
      SELECT da.result1_value Bund_mins
           , da.result2_value Bund_Rate
           , da.result3_value Normal_Rate
           , pi.product_id
        FROM product_instance_history pi
           , service_history sh
           , derived_attribute_array da
           , service_da_array sa
       WHERE pi.product_instance_id = sh.base_product_instance_id
         AND sh.service_id = sa.service_id
         AND da.derived_attribute_id = 2005589
         AND da.index3_value = 0      -- RATE BAND (FOR DURATION BASED TABLE )
         AND da.index2_value = pi.product_id                     -- PRODUCT ID
         AND da.index1_value = sa.result1_value                    -- RATEPLAN
         AND sa.index1_value = 6                     -- RCONNECT RATEPLAN TYPE
-- AND SYSDATE BETWEEN SA.EFFECTIVE_START_DATE AND SA.EFFECTIVE_END_DATE
         AND sa.service_id = v_service_id
         AND sa.derived_attribute_id = 1005715;
--UBU_RECORDS unbilled_usage%ROWTYPE
BEGIN
   fhandler    := UTL_FILE.fopen ('C:', 'try1.txt', 'r');

   FOR i IN unbilled_usage
   LOOP                                     -- process each row one at a time
      UTL_FILE.get_line (fhandler, buf);
      DBMS_OUTPUT.put_line ('DATA FROM FILE: ' || buf);

      IF i.result3_value IS NOT NULL
      THEN
         v_result3_value    := TO_NUMBER (i.result3_value)
                               * v_text_mins_value;

         INSERT INTO table_temp
              VALUES (v_service_id, v_result3_value);
      END IF;
   END LOOP;

   UTL_FILE.fclose (fhandler);
   COMMIT;
EXCEPTION
   WHEN UTL_FILE.invalid_path
   THEN
      raise_application_error
                       (-20000
                      , 'Invalid path. Create directory or set UTL_FILE_DIR.'
                       );
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error :' || SQLERRM);
END;
/

SQL> BEGIN
  2    RAISE VALUE_ERROR;
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5      dbms_output.put_line('Error :'||SQLCODE||': '||SQLERRM);
  6  END;
  7  /
Error :-6502: ORA-06502: PL/SQL: numeric or value error

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  BEGIN
  2    RAISE VALUE_ERROR;
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5      dbms_output.put_line('Error: '||SQLERRM);
  6* END;
SQL> /
Error: ORA-06502: PL/SQL: numeric or value error

PL/SQL procedure successfully completed.

SQL> select SUBSTR(thedata
  2               ,instr(thedata,'|')+1
  3               , instr(thedata,'|',1,2) - instr(thedata,'|') - 1
  4               )x
  5    from ( select '1234|10|0|1' thedata
  6             from dual
  7         ) x
  8  /

X
--
10