title: How do I fix "numeric or value error" message?
body: <p>I have been stuck with this issue now all morning. I actually saw this code here and decide to use it for our purpose here.</p>
  
  <p>The issue that I am running into is that when we execute the code, sometimes it writes a file from db to the folder.</p>
  
  <p>Other times, we get "numeric or value error"</p>
  
  <p>Can any expert please help me fix it?</p>
  
  <p>Here is the code I am using:</p>
  
  <pre><code>create or replace
  PROCEDURE getfile(pfname   VARCHAR2, display_name IN VARCHAR2)
  IS
    vblob      BLOB;
    vstart     NUMBER := 1;
    bytelen    NUMBER := 32000;
    len        NUMBER;
    my_vr      RAW(32000);
    x          NUMBER;
    v_name     VARCHAR2(32760);
    lv_str_len NUMBER;
    l_output   utl_file.file_type;
  BEGIN
    -- define output directory
    --lv_str_len := Length(pfname);
  
    --v_name := display_name||upper(substr(pfname,lv_str_len-3,lv_str_len));
    v_name := display_name;
  
    l_output := utl_file.Fopen('My_DIR', v_name, 'w', 32760);
  
    -- get length of blob
    SELECT dbms_lob.Getlength(FILENAME)
    INTO   len
    FROM   GENERAL.GUBFILE
    WHERE  gubfile_name = pfname;
  
    -- dbms_output.put_line('Length: '||len);
    -- save blob length
    x := len;
  
    -- select blob into variable
    SELECT BLOBVALUE
    INTO   vblob
    FROM   FILES
    WHERE  filename = pfname;
  
    -- if small enough for a single write
    IF len &lt; 32760 THEN
      -- dbms_output.put_line('Single write ');
      utl_file.Put_raw(l_output, vblob);
  
      utl_file.Fflush(l_output);
    ELSE -- write in pieces
      -- dbms_output.put_line('multi write '||vstart);
      vstart := 1;
  
      WHILE vstart &lt; len LOOP
          dbms_lob.READ(vblob, bytelen, vstart, my_vr);
  
          utl_file.Put_raw(l_output, my_vr);
  
          utl_file.Fflush(l_output);
  
          -- set the start position for the next cut
          vstart := vstart + bytelen;
  
          -- set the end position if less than 32000 bytes
          x := x - bytelen;
  
          IF x &lt; 32000 THEN
            bytelen := x;
          END IF;
      END LOOP;
    END IF;
  
    dbms_output.Put_line('End');
  
    utl_file.Fclose(l_output);
  END getfile;
  </code></pre>
  
  <p>The exact error is:</p>
  
  <p>ORA-06502: PL/SQL: numeric or value error
  ORA-06512: at "USER.GETFILE", line 40
  ORA-06512: at line 8</p>