ora-06502 sometimes

[gandolf989]

One of your variables is too small. In any event you should not hard code the size of variables, but instead TYPE it to the column and table that the data represents. I also changed your number to BINARY_INTEGER.

CREATE OR REPLACE FUNCTION get_base_name
   ( file_name_in  IN dba_data_files.file_name%TYPE ) 
RETURN dba_data_files.file_name%TYPE
IS
  Result              dba_data_files.file_name%TYPE;
  last_slash_pos      BINARY_INTEGER;
BEGIN
  last_slash_pos  :=  0;
  FOR i in 1..length( file_name_in )
  loop
     IF SUBSTR(file_name_in, i, 1) = '/' or SUBSTR(file_name_in, i, 1) = '\'
     THEN
        last_slash_pos := i;
     END IF;
  END LOOP;
  
  IF last_slash_pos = 0 
  THEN
      Result := file_name_in;
  ELSE
      Result := substr(file_name_in, last_slash_pos + 1); 
  END IF;
  
  RETURN Result;
END get_base_name;
/