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;