Press "Enter" to skip to content

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:

Oracle Apex function returning SQL query.

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;

Related Tutorials:

Have you found the answer to your question? If not, you can discuss it with me in the comments section below or join my Q&A community OrclQA.com for developers and ask your question. It is FREE.

Vinish Kapoor

Follow

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

guest
10 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
srinivasan
srinivasan
7 months ago

is it possible to pass table name dynamically?

Arnaldo
Arnaldo
5 months ago

Thanks for your help!

sohaib
sohaib
4 months ago
v_sql := 'select dname d, deptno r from dept';

in this section why ‘where’ condition can not
work ??

rvnShaik
rvnShaik
4 months ago

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
VARSHA
19 days ago

Hi,
How to specify static value in where clause in the example below
‘Select empname, empid from employee where designation = ‘sales executive’ ‘

VARSHA
VARSHA
Reply to  Vinish Kapoor
18 days ago

Thanks for the help

VARSHA
VARSHA
19 days ago

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
Gokul
13 hours ago

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