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;

This Post Has 20 Comments

srinivasan September 6, 2020
is it possible to pass table name dynamically?
1. Vinish Kapoor September 6, 2020
  Yes sure. You can use a variable or a page item and concatenate it to the SQL query.
  Below is an example:
  v_sql := 'select dname d, deptno r from '|| :P29_tblname;
  Populate the page item P29_tblname on page load or on any other event using the dynamic action.
Arnaldo November 9, 2020
Thanks for your help!
sohaib December 1, 2020
v_sql := 'select dname d, deptno r from dept';
in this section why 'where' condition can not
work ??
rvnShaik December 3, 2020
how to return the sql query using dynamic sql with bind parameter.
For eg. select Display,Return from dual; ---> this should return from function when its called.
where display is the 'select emp_name from emp where emp_no = :1';
and :1 is the 123';
VARSHA March 25, 2021
Hi,
How to specify static value in where clause in the example below
'Select empname, empid from employee where designation = 'sales executive' '
1. Vinish Kapoor March 26, 2021
  Specify in a single quote two times:
  'Select empname, empid from employee where designation = ''sales executive'''
2. VARSHA March 26, 2021
  Thanks for the help
VARSHA March 25, 2021
Hi Vinish,
I'm facing one problem please help me to resolve this..
I'm filling the login user name in a Page item if the user is "Sales Executive"
Sql Query for the Sales Executive field(which is of Select List type) in the form

SELECT EM.EMP_NAME, EM.ID FROM PH_EMPLOYEE_MASTER EM, PH_EMPLOYEE_DETAIL ED
WHERE EM.ID = ED.EID AND ED.DESIGNATION = 'Sales Executive'
AND EM.EMP_NAME = :APP_USER
This is working fine but when the ADMIN login to view the record the same query is running and showing field value blank.(I should show the saved data)
How to solve this?
Gokul April 13, 2021
Hi Vinish,
I used the same approach for my Page, but i still get an error.
the only diffrence is My page is Interactive GRID. the popup LOV is one of the item of Interactive grid. it supports upto 4000 chars only.
Can you assist to suggest some solution..
Regards,
Gokul
VARSHA May 22, 2021
Hi Vinish,
How to add union in SQL query
'Select empname, empid from employee where designation = ' ' Sales Executive' '
union
Select empname, empid from employee where designation = ' 'Cashier' ' '
1. Vinish Kapoor May 22, 2021
  Your below query seems ok. What is the issue?
  'Select empname, empid from employee where designation = ‘ ‘ Sales Executive’ ‘ union Select empname, empid from employee where designation = ‘ ‘Cashier’ ‘ ‘
2. VARSHA May 22, 2021
  Thanks for the reply
  Sorry it is working
3. VARSHA May 22, 2021
  Can we add nested if else here?
4. Vinish Kapoor May 22, 2021
  Yes, but ELSE is a must.
VARSHA June 5, 2021
Hi Vinish
I'm using this query to fill the column based on Previous column value but i'm getting the below error
ERR-1002 Unable to find item ID for item "EMP_NAME" in application "109".
What i'm doing wrong?
1. Vinish Kapoor June 5, 2021
  what is your query?
Baitul November 17, 2021
I cannot reference an Item to this query, it raises no-data-found. how can I solve the problem?
Kyle November 21, 2023
Is it possible for the returned query to have a dynamic number of columns? I am trying to do this but it seems to create the grid at compile time so will always have the columns for the query based on the default values.
This is for an interactive grid
1. Vinish Kapoor November 21, 2023
  Yes, I have created an Apex app to demonstrate this, you can download it from GitHub https://github.com/devvinish/sql-command