V. 9.2.0.5

not sure why i sometimes get ora-06502

SQL> r
1 select baseXname from (
2 select basename(file_name) baseXname from dba_data_files)
3* where basexname like 'B%'
select basename(file_name) baseXname from dba_data_files)
*
ERROR at line 2:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DKRAY.BASENAME", line 15

The inline view run alone does not produce the error:

SQL>
SQL> select basename(file_name) baseXname from dba_data_files;

BASEXNAME
--------------------------------------------------------------------------------
system01.dbf
undotbs01.dbf
example01.dbf
indx01.dbf
tools01.dbf
users01.dbf
...

===============================================
CREATE OR REPLACE FUNCTION RAY.basename(file_name_in in varchar2) return varchar2
is

Result varchar2(150);
last_slash_pos number;
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 basename;
/


TIA,
d.