DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: ora-06502 sometimes

  1. #1
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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;
    /

  3. #3
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    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?

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

  5. #5
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    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.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

  7. #7
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    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.

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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;

  9. #9
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    it does not fail. no error. i didn't want to list all 319 filenames in the thread.

  10. #10
    Join Date
    May 2002
    Posts
    2,645
    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
  •  


Click Here to Expand Forum to Full Width