Populating Oracle Application LOV values from server

Populating
Oracle Application LOV values from server path

Description

This
blog is used to explain how to populate the LOV values of the files placed in specific server path by using a java object.



We can use this code to create a VALUE SET and used as LOV for a Parameter

— This source code is used
to populate the lov list from the server path.
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED “XXIC_DIR_FILE_LIST” AS
import java.io.*;
public class XXIC_DIR_FILE_LIST
{
public static String getList(String directory)  throws NullPointerException
{
File path = new File( directory );
String[] list = path.list();
String element;
element = “”;
for(int i = 0; i < list.length; i++)
{
element = element + “; “ + list[i];
}
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 ‘XXIC_DIR_FILE_LIST.getList(
java.lang.String ) return string’
;
/
— 3. Create a view calling
PLSQL function
create or replace VIEW XXIC_FILES_TMP_LOV AS
select filename from ( SELECT trim(regexp_substr(Get_Dir_list(‘/interface/inbound/dir’),‘[^;]+’,1,rownum)) filename FROM dual
                        CONNECT BY LEVEL <= length(regexp_replace(Get_Dir_list(/interface/inbound/dir),‘[^;]+’)) + 1                     )
/

END XXIC_DIR_FILE_LIST;

— By
— VenkataRamana Ganne
  • September 27, 2016 | 19 views