Functions

If a function is called in a query it cannot have output parameters. If a function has DML, it cannot be used in select query. Using a function is select query for selecting large data can result in frequent context switching between SQL and PLSQL enging causing performance issue. Create a function with multiple out parameters and use in select statement. It should not allow.

create or replace FUNCTION S_FN_TEST
(
    p_in_var_1   IN   VARCHAR2,
    p_in_num_1   IN   NUMBER,
    p_out_var_1  OUT  VARCHAR2,
    p_out_num_1  OUT  NUMBER
)
RETURN NUMBER AS 
BEGIN

  p_out_var_1 := p_in_var_1;
  p_out_num_1 := p_in_num_1;
  RETURN 1;
END S_FN_TEST;

DECLARE

v_var_1  VARCHAR2(100);
n_num_1  NUMBER; 
n_ret   NUMBER;
BEGIN
select s_fn_test('abc',1,v_var_1, n_num_1) INTO n_ret from dual;

END;

Functions can have out parameters. If function has output parameters it cannot be used in a select statement.

06572. 00000 -  "Function %s has out arguments"
*Cause:    A SQL statement references either a packaged, or a stand-alone,
           PL/SQL function that contains an OUT parameter in its argument
           list. PL/SQL functions referenced by SQL statements must not
           contain the OUT parameter.
*Action:   Recreate the PL/SQL function without the OUT parameter in the
           argument list.

You cannot create a function having DML statments in it and use the same in queries. Below is an example. Create a function with DML statement and use in select statement.

CREATE OR REPLACE FUNCTION S_FN_TEST_1
(
    p_in_var_1   IN   VARCHAR2,
    p_in_num_1   IN   NUMBER
)
RETURN NUMBER AS 
BEGIN
  UPDATE EMPLOYEES
  SET FIRST_NAME = 'Alex'
  WHERE EMPLOYEE_ID = 102;
  
  RETURN 1;
END S_FN_TEST_1;

SELECT S_FN_TEST_1('AAA',1) FROM DUAL;

ORA-14551: cannot perform a DML operation inside a query 
ORA-06512: at "HR.S_FN_TEST_1", line 8
14551. 00000 -  "cannot perform a DML operation inside a query "
*Cause:    DML operation like insert, update, delete or select-for-update
           cannot be performed inside a query or under a PDML slave.
*Action:   Ensure that the offending DML operation is not performed or
           use an autonomous transaction to perform the DML operation within
           the query or PDML slave.

Oracle has its own set of built in functions which can be used in development projects 

Some of the usefule and commly used functions are 

Aggregate functions like AVG, COUNT, MIN, MAX, SUM

Conversion functions like TO_CHAR, TO_DATE, TO_CLOB, CAST, COALESCE 

Date and Time Functions like ADD_MONTHS , LAST_DAY , MONTHS_BETWEEN

Analytic Functions 

 A more detailed list of oracle built in functions can be got from below sites

https://www.databasestar.com/oracle-sql-functions/#Aggregate_Functions 

https://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj29026.html

You can also get a comprehensive list of all the oracle build in functions with their parameters from the STANDARD  oracle SYS package.

Below is the query for the same.

SELECT PACKAGE_NAME, OBJECT_NAME, DATA_TYPE, SEQUENCE, POSITION,  DEFAULTED, PLS_TYPE FROM ALL_ARGUMENTS 
WHERE OBJECT_NAME NOT LIKE '%SYS$%'
AND  package_name = 'STANDARD'
order by OBJECT_NAME;

The DATA_TYPE at Position 0 will be the return type. Positions from 1 are the input parameters and the sequence of the parameter.

Performace issues of user functions in PLSQL

Exceesive usage of function calls in a query can result in performance issues especially it the query output is returning a large number of rows as it results in a lot of context switching between the SQL and PLSQL engine and also an icrease in I/O.

So there is a trade off of query performance with good coding practice and one has to decide on the approach based on application performance and code maintainability.

Such performance issues can be optimised by using RESULT_CACHE when writing the function.

Here we specify the result of the function is to be cached. So each time the function is called with different set of parameters oracle adds the result to the result cache. So when the function is repeated Oracle retrieves the results from cache rather the re executing the function. Under certain circumstances this caching can result in significant performance gains.

create or replace FUNCTION s_fn_create_emp_email (
                  p_first_name IN VARCHAR2,
                  p_last_name  IN VARCHAR2
                  ) RETURN VARCHAR2 RESULT_CACHE IS
BEGIN
     RETURN p_first_name || '.' || p_last_name || 'ggle.co.us';
END s_fn_create_emp_email;

SELECT s_fn_create_emp_email(FIRST_NAME, LAST_NAME) from EMPLOYEES; 

The output of this query is 107 rows.

SELECT * 
FROM   SYS.v$result_cache_statistics
WHERE  name IN ('Create Count Success','Find Count')
and CON_ID = 3;

The first time the function is executed it gives the following output

ID	NAME	                VALUE	CON_ID
49	Create Count Success	483		3 
51	Find Count				398		3

The find count is 398. On subsequent execution of the query the count is increased to 505. The count has increased by 107 i.e. the output number of rows. This shows in subsequery query executions the entire result is got from CACHE.

ID	NAME	                VALUE	CON_ID
49	Create Count Success	483		3 
51	Find Count				505		3

Thought for the day

"Best Way to Stay Happy is to be busy."

--Dipika Agarwal