Tuesday, 27 September 2016

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

No comments:

Post a Comment