1.CREATE TABLE FOR THE FILES
create global temporary table FILE_NAME
( filename varchar2(255) )
2.CREATE JAVA SOURCE.
This java is used to read the file from directory and insert the file name into the FILE_NAME table.
create or replace
and compile java source named “FILE_READ”
as
import java.io.*;
import java.sql.*;
public class FILE_READ
{
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 FILE_NAME (file_name)
VALUES (:element)};
}
}
}
/
3.CREATE A PROCEDURE FOR READ THE FILE NAME FROM DIRECTORY.
create or replace
procedure FILE_DIR_LIST( p_directory in varchar2 )
as language java
name ‘FILE_READ.getList( java.lang.String )’;
/
4.GIVE PATH PERMISSION TO SPECIFIC USER FROM SYS USER.
SQL> call dbms_java.grant_permission(‘SCHEMA_NAME’, ‘java.io.FilePermission’,'<PATH>’,’read,write’);
Call completed.
SQL> EXEC dbms_java.grant_permission( ‘SCHEMA_NAME’, ‘java.io.FilePermission’, ‘<PATH>’,’read,write’);
PL/SQL procedure successfully completed.
5.EXECUTE THE PROCEDURE AND SELECT THE VALUES FROM TABLE.
EXEC FILE_DIR_LIST (‘FILE PATH LOCATION’);
PL/SQL procedure successfully completed.
SELECT * FROM FILE_NAME;
Now you are able to see the file name values in the FILE_NAME table.
