SQL Functions to perform IN() clause using ADF Business Components

This blog explains about performing IN() clause with comma separated values in ADF Business components.

Scenario 1 : Get all the employees where employee first name in ('Bob','Rob','Peter','Dan'), Using ADF Business components, pass the comma separated values as a bind parameter and execute the query using the SQL function which I am going to outline in below.

Scenario2 : Same as above, instead of String, 'Number' data type

FM_GN_IN_NUMBER_LIST  - This function accepts comma separated Numbers, delimit the String by ',' and returns array of numbers. So that we can avoid writing query to de


Function 1

// Create num_table type
CREATE TYPE num_table AS TABLE OF NUMBER;

create or replace
FUNCTION     FM_GN_IN_NUMBER_LIST (p_in_list  IN  VARCHAR2)
RETURN num_table
AS
l_tab   num_table := num_table();
l_text  VARCHAR2(32767) := p_in_list || ',';
l_idx   NUMBER;
BEGIN
LOOP
l_idx := INSTR(l_text, ',');
EXIT WHEN NVL(l_idx, 0) = 0;
l_tab.extend;
l_tab(l_tab.last) := to_number(TRIM(SUBSTR(l_text, 1, l_idx - 1)));
l_text := SUBSTR(l_text, l_idx + 1);
END LOOP;

RETURN l_tab;
END;

Function2:

//Create table_of_varchar type as shown below

CREATE TYPE table_of_varchar AS TABLE OF VARCHAR2(2300);

create or replace
FUNCTION     FM_GN_IN_STRING_LIST (p_in_list  IN  VARCHAR2)
RETURN table_of_varchar
AS
l_tab   table_of_varchar := table_of_varchar();
l_text  VARCHAR2(32767) := p_in_list || ',';
l_idx   NUMBER;
BEGIN
LOOP
l_idx := INSTR(l_text, ',');
EXIT WHEN NVL(l_idx, 0) = 0;
l_tab.extend;
l_tab(l_tab.last) := TRIM(SUBSTR(l_text, 1, l_idx - 1));
l_text := SUBSTR(l_text, l_idx + 1);
END LOOP;
RETURN l_tab;
END;

Usage: Below is the example, how to call the function

EmployeeEO.EMP_ID IN (SELECT * FROM TABLE(CAST(fm.fm_gn_in_string_list(:BineEmpID) AS fm.table_of_varchar)))