ORA-06502: PL/SQL: numeric or value error on dbms_backup_restore.searchfiles debuging

OraDeveloper used Ask the Experts™
This kind of problem is a really challenge for DBA  guru, so if you can give me solution I appreciate you very much.

I made a function listed bellow is SYS schema.
I granted execute privilege to user to use this function.
The user execute this function from his schema in the package's procedure he owns ( called LOAD_DATA )
The function runs perfect and I have no problems with this, BUT !
When he tries to debug procedure LOAD_DATA, he get's this stupid error:
" ORA-06502: PL/SQL: numeric or value error: character to number conversion error ";
The execution fails on the row sys.dbms_backup_restore.searchfiles(pattern, ns);

He uses PL/SQL Developer.
The exception occurs only  when debuging, not running the procedure LOAD_DATA.
It mustn't happen, it's some kind of bug.

The question isn't easy, and you wouldn't find any information in the internet, so don't even search.

Please help me !!!
How can I deal with it ? I just must to be able to debug !!!

Thank you

CREATE OR REPLACE FUNCTION SYS.ud_get_files_list(i_path IN VARCHAR2,i_ext IN VARCHAR2 := NULL, i_file_separator IN VARCHAR2 := '/')
  RETURN dbms_utility.lname_array
   NAME:       ud_get_files_list
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        07/02/2007  Sasha        1. Created this function.
   pattern   VARCHAR2(1024) := i_path||i_file_separator;
   ns        VARCHAR2(1024);
   v_ret_tab dbms_utility.lname_array;
  sys.dbms_backup_restore.searchfiles(pattern, ns);
  FOR each_file IN (SELECT fname_krbmsft AS name FROM x$krbmsft) LOOP
    IF UPPER(SUBSTR(each_file.name,1,(INSTR(each_file.name,i_file_separator,-1) - 1))) = UPPER(i_path)
       (i_ext IS NULL
        UPPER(i_ext) = UPPER(SUBSTR(each_file.name,INSTR(each_file.name,'.',-1) + 1))) THEN
      v_ret_tab(v_ret_tab.COUNT + 1) := each_file.name;
    END IF;
  RETURN v_ret_tab;
END ud_get_files_list;

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

I'm not sure about this one.  As DBMS_BACKUP_RESTORE is an undocumented package, (Oracle doesn't support it as it's internal) so I don't know what the valid arguments are for dbms_backup_restore.searchfiles.  

However the value of "pattern" is dependant on ud_get_files_list parameters (i_path, and i_file_separator).  So you need to see what the procedure LOAD_DATA is doing.  What values is it using to call your function ud_get_files_list?  This may help you track down the problem.

Note:  you shouldn't be creating functions in the SYS schema.  It doesn't belong to you - it's owned by the database, and it's off limits.  You have no reason to create stuff in there, be it functions procedures / or packages - don't do it!


Hi, first of all I need to say that I have no choice but to create it in SYS schema, due to the table x$krbmsft - it exists only in SYS.
Second, as I wrote, the function works fine in a runtime, when I don't debug.
The first parameter is a path to a directory where the files are, and second is always NULL.
I now exactly what LOAD_DATA is doing, and the function SYS.ud_get_files_list has to list all the files in specified directory.

Could you create a function in your DB and check it ?

Thanks !
Top Expert 2009

Did you report it to the makers of the software, allaroundautomations? We also use the tool and they are usually responsive to your messages if you mail them a report.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!


Hi! No, I didn't, maybe someone in expert exchange could find a solution ?

Thank you !!!
Top Expert 2009
This is the maker of the software, email these guys:  http://www.allroundautomations.com/plsqldev.html
Or ask on their forum.

I've no idea myself, but if it works in runtime and not in debug, I'd speculate it is a tool problem and not something anyone here will be able to solve.

Good luck. I'm not dodging your question, on the contrary, I'm recommending you actually contact them for a faster solution, that is what I do, and I have 5 licenses for that tool.

Good suggestion by mrjoltcola, the guys at allroundautomations are very helpful.  I've used the debugger in PL/SQL extensively during development work, and I've never had any issues.  I will check on tomorrow using the debugger in SQL developer, and see if I can reproduce your error.

Also, when are your developers getting the error?  As soon as they are trying to run in debug mode?  Or are they trying to step inside dbms_backup_restore.searchfiles?  That code is obfuscated, so you won't be able to debug that code - I've never tried to do that so I'm not sure what error you'd get.


Hi Andytw, the developers getting the error when debuging the procedure they own in wich there is execution of SYS.ud_get_files_list.
They even get an exception when they just run debug to the whole LOAD_DATA procedure without any breakpoints in it , from the beginning to the end.
They don't try to step into SYS.ud_get_files_list.

Thanks for your help, also I wrote this in allroundautomations forum, waiting for they response.
I found this in internet: http://forums.oracle.com/forums/thread.jspa?threadID=662413
They say that it's not happen in TOD, only in PL/SQL Developer.
I do like PL/SQL developer very much, and don't want to start using TOD, I hate it.
Top Expert 2009

>>They say that it's not happen in TOD, only in PL/SQL Developer.

Which is why I said I thought it was a tool problem, not an Oracle problem.

I also have Toad, but I prefer PL/SQL developer for PL/SQL specific work.


Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial