Home » SQL & PL/SQL » SQL & PL/SQL » Getting ORA-6502 while using SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns) (Oracle, 10, Windows)
Getting ORA-6502 while using SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns) [message #569271] Tue, 23 October 2012 22:23 Go to next message
ravikumar05
Messages: 5
Registered: April 2010
Junior Member
Hi,

My task is to create a procedure to process csv files from a folder on the server. I have used dba_directories to get the files. Now when I am using SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns), it is giving me ORA-6502 Error. I tried to grant permissions to the user, but still getting the error messages.

ORA-6502: PL/SQL: numeric or value error: character to number conversion error
ORA-6512 at SYS.DBMS_BACKUP_RESTORE line 6532
ORA-6512: AT SYS.LIST_FILES, line 12
ORA6512 AT line
ORA-6512 at "DATA_UPLOAD" line 54

I tried to get this done by java, but it giving me some other errors.

Any help would be greatly appreciated.

Thanks
Re: Getting ORA-6502 while using SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns) [message #569273 is a reply to message #569271] Tue, 23 October 2012 22:50 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
consider accessing *CSV files by utilizing EXTERNAL TABLES

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

It is a REAL challenge to debug code that can not be seen!
Re: Getting ORA-6502 while using SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns) [message #569275 is a reply to message #569271] Wed, 24 October 2012 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 59296
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SYS.DBMS_BACKUP_RESTORE.SEARCHFILES is an undocumented procedure and should not be used.

Quote:
I tried to get this done by java, but it giving me some other errors.


If you don't post the code and the errors we can't help you.
My advice: use a supported way and so fix your errors in your Java procedure.
Note that there are several procedures to do this in Java and at least one on AskTom.

Regards
Michel
Re: Getting ORA-6502 while using SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns) [message #569332 is a reply to message #569275] Wed, 24 October 2012 19:37 Go to previous messageGo to next message
ravikumar05
Messages: 5
Registered: April 2010
Junior Member
I am using the code below: I got these examples from other forums.

CREATE OR REPLACE PROCEDURE list_directory
(directory VARCHAR2)
IS
ns VARCHAR2(1024);
v_directory VARCHAR2(1024);
BEGIN
v_directory := directory;
SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(v_directory, ns);
FOR each_file IN (SELECT fname_krbmsft AS name FROM x$krbmsft) LOOP
DBMS_OUTPUT.PUT_LINE(each_file.name);
END LOOP;
END;
/

GRANT EXECUTE ON list_directory TO John;

SQL> exec list_directory('/u01/app/oracle/admin/testdb/bdump');


SQL> exec list_directory('\\gro-cognos1\Banjobs\PPRD\utl_reports\Angel Grade Upload');

BEGIN list_directory('\\gro-cognos1\Banjobs\PPRD\utl_reports\Angel Grade Upload'
); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object BANINST1.LIST_DIRECTORY is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


For JAVA: I followed exactly the same from

GRANT JAVAUSERPRIV TO John;

CREATE global temporary TABLE directory_listing
( name VARCHAR2(1024) )
ON COMMIT DELETE rows;


CREATE OR REPLACE
AND compile JAVA source named "Directory_Listing"
AS
import JAVA.io.*;
import JAVA.SQL.*;
PUBLIC class Directory_Listing {
PUBLIC static void GetList(String directory) throws SQLException {
File PATH = NEW File( directory );
String[] list = PATH.list();
String element;
FOR(int i = 0; i < list.LENGTH; i++) {
element = list[i];
#sql { INSERT INTO directory_listing (name) VALUES (:element) };
}
}
}

CREATE OR REPLACE PROCEDURE list_directory ( directory IN VARCHAR2 )
AS language JAVA
name 'Directory_Listing.GetList( java.lang.String )';


exec list_directory( '/u01/app/oracle/admin/testdb/bdump' );

Thanks
Re: Getting ORA-6502 while using SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns) [message #569333 is a reply to message #569332] Wed, 24 October 2012 20:36 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
SELECT OWNER, OBJECT_TYPE, STATUS FROM ALL_OBJECTS WHERE OBJECT_NAME = 'LIST_DIRECTORY';

post results from SQL above

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Getting ORA-6502 while using SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns) [message #569334 is a reply to message #569332] Wed, 24 October 2012 21:59 Go to previous messageGo to next message
ravikumar05
Messages: 5
Registered: April 2010
Junior Member
Thanks everyone. I was able to get it with using Java option. Now it is working fine.

Ravi
Re: Getting ORA-6502 while using SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns) [message #569337 is a reply to message #569334] Wed, 24 October 2012 23:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59296
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So what was the problem?

Regards
Michel
Re: Getting ORA-6502 while using SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns) [message #569421 is a reply to message #569271] Thu, 25 October 2012 21:25 Go to previous message
ravikumar05
Messages: 5
Registered: April 2010
Junior Member
This is funny. I copied the same java code from different forum and this time it started working fine. Earlier it was the permission issue. I was giving the GRANT JAVAUSERPRIV TO SYSTEM, instead of GRANT JAVAUSERPRIV TO BAUSER.

Thanks again

Ravi
Previous Topic: Sum and GroupBy
Next Topic: if data is not there in table why the procedure getting stuck?
Goto Forum:
  


Current Time: Thu Oct 02 08:27:15 CDT 2014

Total time taken to generate the page: 0.09800 seconds