Oracle Apex function returning SQL query.

Oracle Apex Function Returning SQL Query Example

by

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:

20 thoughts on “Oracle Apex Function Returning SQL Query Example”

    • 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.

  4. 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';

    Reply

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

    Reply

  6. 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?

    Reply

  7. 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

    Reply

  8. 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' ' '

    Reply

  9. 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?

    Reply

  11. 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

    Reply

Leave a Comment