Using Functions in Oracle SQL

Oracle subquery using WITH clause is a popular choice among the developers. I am a fan of using WITH clause since it makes the SQL neat and can write much complex queries. I’ve used it many times but wasn’t aware until recently that Functions can be declared in WITH clause section. Too bad that I missed it for long but seems this was a new feature added in Oracle 12C R1.

It’s easy and straight forward and declaration is similar to normal function declaration in PLSQL. Given below is a simple example of declaring a function in the WITH clause. Cursors, variables and other PLSQL elements can be used with the function but as expected you cannot perform any DML operations inside the function.

Function declaration in WITH clause

WITH
--Function declaration
function func_get_country(input varchar2) return varchar2 IS
  country_ varchar2(2000);
  cursor get_country(user_id_ varchar2) IS
    select country_db from person_info where user_id = user_id_;  
  begin
    open get_country(input);
    fetch get_country into country_;
    close get_country;
    return country_;
end func_get_country;
SELECT a.identity, func_get_country(a.identity) country FROM fnd_user a

It’s also possible to use sub functions and procedures as well just as in regular PLSQL

WITH
--Function declaration
function func_get_country(input varchar2) return varchar2 IS
  country_ varchar2(2000);
  country_name_ varchar2(2000);
  cursor get_country(user_id_ varchar2) IS
    select country_db from person_info where user_id = user_id_;
    
   --sub procedure inside function
   procedure sub_proc_country_name (country_ IN VARCHAR2,
                       name_ OUT VARCHAR2) IS
                       
     cursor get_country_name is
     select full_name from iso_country where country_code = country_;
     country_name_ varchar2(2000);                                        
   begin
     open get_country_name;
     fetch get_country_name into country_name_;
     close get_country_name;
     name_ := country_name_;
   end sub_proc_country_name;  
  begin
    open get_country(input);
    fetch get_country into country_;
    close get_country;
    sub_proc_country_name(country_, country_name_);
    return country_name_;
end func_get_country;
SELECT a.identity, func_get_country(a.identity) country FROM fnd_user a

I have not done any performance comparison but according to Tim at oracle-base.com, using function in SQL WITH clause uses lot less CPU and is significantly faster.

Usage in IFS

WITH clause is supported in IFS quick reports and IALs from Apps 9 onwards and the recommended RDBMS version for Apps9 is Oracle 12.1.0.2. Therefore it’s safe to assume that functions within WITH conditions are supported from IFS application 9 onwards. Apart from the regular benefits, you can raise application errors to control additional row level of security and display errors.

WITH
  function test_func(input varchar2) return varchar2 as
    output varchar2 (32000);
    country_ varchar2(2000);
    current_user_ varchar2(2000) := &AO.Fnd_Session_Api.Get_Fnd_User();
    cursor get_country( user_id_ varchar2) IS
    select country from &AO.person_info
where user_id = user_id_;    
  begin 
    open get_country(input);
    fetch get_country into country_;
    close get_country;

IF  (current_user_ = 'IFSAPP') THEN
  &AO.Error_Sys.Appl_General('CustomerOrder', 'CQRERROR:  User :P1 is not allowed to run the report.', current_user_);
END IF;
    return country_;
  end;

SELECT test_func(a.identity) col_1 FROM &AO.fnd_user a

Using Functions in Read Only Custom fields gave an compilation error during publish. It seems like the problem is with the IFS SQL parser when generating the CFP code but I didn’t have enough time to dig deep to see if it’s a bug or whether there’s any workaround.

Further Readings

oracle-base.com WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1)

https://erp-integrations.com/2017/08/31/using-functions-in-with-clause-in-oracle-12c/

PL/SQL New Features and Enhancements in Oracle Database 12c Release 1 (12.1)

Hope you find this article useful. Please share, like and comment.