-
ora-06502 sometimes
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.
-
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.
Code:
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;
/
-
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?
-
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?
Last edited by gandolf989; 12-28-2004 at 12:04 PM.
-
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.
-
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.
-
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.
-
Does it fail when you run this?
And on which record?
Code:
SELECT basename( file_name ) basexname
FROM dba_data_files
ORDER BY file_name;
-
it does not fail. no error. i didn't want to list all 319 filenames in the thread.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|