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