Table Function to return look up value for LOV’s In Oracle Apex

1. Overview

This document is about table function to return lookup values For LOV’s in oracle apex.

2. Technologies and Tools Used

The following technologies have been used for table function to return lookup values For LOV’s.

Oracle Apex

3. Use Case

Let us have the requirement on table function to return lookup values For LOV’s in oracle apex.

4. Steps with Screenshot

Step 1:

Create Object in a database as mentioned in below code.

Code:

CREATE OR REPLACE TYPE t_tvf_row AS OBJECT (

id VARCHAR2(100),

description VARCHAR2(10000)

);

Step 2:

Create a collection type ( Nested table) for the above created object type.

Syntax:

Type type_name is table of object_name;

Code:

create or replace type lov_typ is table of t_tvf_row;

Step 3:

Create a function name “lov_return” and return type as “lov_typ” as mentioned above.

/*Based on your requirement ,you can modify the following code to return lookup value for LOV’s*/

Code:

CREATE OR REPLACE FUNCTION lov_return (lv_lookup_type IN VARCHAR2)
RETURN lov_typ     ———–>/*lov_typ is collection type which we have created in previous step 2*/
AS
lv_lov lov_typ := lov_typ ();
BEGIN
SELECT t_tvf_row (LOOKUP_CODE, MEANING)       ——-> /*t_tvf_row is object type*/
BULK COLLECT INTO lv_lov   FROM HOMES_FND_LOOKUPS
WHERE LOOKUP_TYPE = lv_lookup_type
AND NVL (active_flag, ‘Y’) = ‘Y’
AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
AND NVL (end_date, SYSDATE);

RETURN lv_lov;
END;

Step 4: