Oracle Apex Function Returning SQL Query Example

In Oracle Apex, you will find the options where the SQL query source could be a Function Returning SQL Query or the PL/SQL Function Body Returning SQL Query for the items such as List, POPUP LOV, etc. And these options are beneficial when your lists or POPUP LOVs needs to be based on dynamic SQL. In this tutorial, you will learn how to use the Function Returning SQL Query option in Oracle Apex.

Oracle Apex - Function Returning SQL Query Example

To demonstrate the example, I am taking only two items on my page. First is P3_A, which is a numeric field, and second is the P3_DEPT, which is a Select List item.

I require to change the P3_DEPT list source table to DEPT if the P3_A is equal to 1 and if other than 1, then the table should be EBA_DEMO_IR_DEPT.

To do this, I have changed the SQL query source to PL/SQL Function Body Returning SQL Query and added the following code in it:

declare
v_sql varchar2(4000);
begin

   if :p3_a = 1 then
     v_sql := 'select dname d, deptno r from dept';
   else
     v_sql := 'select dname d, deptno r from eba_demo_ir_dept';
   end if;
   
   return v_sql;
end;

Also, changed the Cascading property as shown in the below image:

Now save the changes and run the page. The list item will be updated and will show different values when you will enter the value in item P3_A.

But what if your SQL queries are very large (more than 4000 characters) and Oracle Apex code editor supports only 4000 characters length?

To resolve this issue, you can store your queries in a CLOB column in a table and then return it using the database function. The following are the steps:

1. Create a Table with CLOB Column

create table apex_sql (sql_id integer, sql_query clob);

2. Insert Your SQL Queries

Assume these are your huge SQL queries:

insert into apex_sql values (1, 'select dname d, deptno r from dept');

insert into apex_sql values (2, 'select dname d, deptno r from eba_demo_ir_dept');

Commit;

3. Create a Function Returning CLOB

CREATE OR REPLACE FUNCTION Get_large_sql(i_sql_Id IN INTEGER)
  RETURN clob IS
  vc clob;
BEGIN

  SELECT sql_query
                         into vc
    FROM Apex_sql
   WHERE sql_Id = i_sql_Id;
   
   return vc;

EXCEPTION
  WHEN OTHERS THEN
    RETURN empty_clob();
END;

Then in Oracle Apex, change the PL/SQL Function Body Returning SQL Query to the following:

begin
   if :p3_a = 1 then
     Return Get_large_sql(1);
   else
     Return Get_large_sql(2);
   end if;
end;

Oracle Apex Function Returning SQL Query Example