| Getting ORA-6502 while using SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns) [message #569271] |
Tue, 23 October 2012 22:23  |
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 #569332 is a reply to message #569275] |
Wed, 24 October 2012 19:37  |
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
|
|
|
|
|
|
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
