ora-06502 sometimes

[DBAtrix]

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.

[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;
/

[DBAtrix]

i made those changes, with the same result (ora-6502). any ideas why the query by itself is okay, but as an inline view the query fails?

[gandolf989]

Originally posted by DBAtrix
i made those changes, with the same result (ora-6502). any ideas why the query by itself is okay, but as an inline view the query fails?

IF you do a "SELECT file_name FROM dba_data_files;", what do you get?

I'm thinking the problem relates to the data that you are bringing back.
Do you have any files located in the root directory?

[DBAtrix]

do you mean "select file_name from dba_data_files"?

there are no files in root directory. i wouldn't fail, anyway.

SQL> select basename ('nopath.txt') from dual;

BASENAME('NOPATH.TXT')
--------------------------------------------------------------------------------
nopath.txt

1 row selected.

here are the begining of all file names.
SQL> r
1 select substr(file_name, 1, 7), count(*)
2 from dba_data_files
3* group by substr(file_name, 1, 7)

SUBSTR( COUNT(*)
------- ----------
/oracle 319

1 row selected.

[gandolf989]

Somewhere there is a value that is too big for a variable. But I am not sure what that would be. It would help to know the full value for the file_name that it is failing on.

[DBAtrix]

then wouldn't it fail on this? this runs okay. that's what baffles me.

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

BASEXNAME
--------------------------------------------------------------------------------
system01.dbf
undotbs01.dbf
example01.dbf
indx01.dbf
tools01.dbf
users01.dbf
...
ABB0010D_dm01.dbf
ABB0010D_dm_idx01.dbf
ABB0010E_dm01.dbf
ABB0010E_dm_idx01.dbf

319 rows selected.

[gandolf989]

Does it fail when you run this?
And on which record?

SELECT basename( file_name ) basexname
  FROM dba_data_files
 ORDER BY file_name;

[DBAtrix]

it does not fail. no error. i didn't want to list all 319 filenames in the thread.

[stecal]

CREATE OR REPLACE FUNCTION RAY.basename

Is this a cut and paste job, or is there a difference between RAY.basename and DKRAY.basename? Your original error message references DKRAY.basename, but the function you posted is RAY.basename.