Creating LOV of files sitting in directory
-- 1. Create JAVA classe to retrieve directory listing
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "XXFND_DIRECTORY_LIST" AS
import java.io.*;
public class XXFND_DIRECTORY_LIST
{
public static String getList(String directory)
{
File path = new File( directory );
String[] list = path.list();
String element;
element = "";
for(int i = 0; i < list.length; i++)
{
element = element + "; " + list;
}
return element;
}
}
/
-- 2. Create a function PLSQL to call java class
CREATE OR REPLACE FUNCTION Get_Dir_List( p_directory IN VARCHAR2 ) return varchar2
IS
language java
name 'XXFND_DIRECTORY_LIST.getList( java.lang.String ) return string';
/
-- 3. Create a view calling PLSQL function
create or replace VIEW XXFND_USR_TMP_LS AS
select filename from (
SELECT trim(regexp_substr(Get_Dir_list('/usr/tmp'),
'[^;]+',
1,
rownum)) filename
FROM dual
CONNECT BY LEVEL <= length(regexp_replace(Get_Dir_list('/usr/tmp'),
'[^;]+')) + 1
)
-- 4. View result
select *
from XXFND_USR_TMP_LS
Use this view to create the value set for the concurrent program
