As it is known, invoking PL/SQL code from SQL cause context switching. SQL is a non-procedural language and it is very different from PL/SQL language. In addition, PL/SQL engine and SQL engine are seperate and distinct two things. Thus, when PL/SQL functions are used within SQL, context switching occurs. In order to reduce this overhead, a new feature was introduced in Oracle 12.1, **pragma UDF** (UDF stands for User Defined Functions). If a function is created with **pragma UDF**, it is compiled in a different manner. Besides, another new enhancement came with Oracle 12c. PL/SQL functions can be defined inside the **WITH** clause. It is also compiled in the same manner as **pragma UDF** functions. However, it is not stored in the data dictionary. Hence, cannot be called from another SQL or PL/SQL statement.

In this post, I will show different ways to call PL/SQL functions from SQL.

```
CREATE TABLE T_TEST
AS
SELECT
ROUND(DBMS_RANDOM.VALUE(1, 100)) C1,
DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(5, 10)) C2
FROM DUAL
CONNECT BY LEVEL <= 500000;
```

```
CREATE OR REPLACE FUNCTION F_CALC(P1 NUMBER, P2 VARCHAR2)
RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN ( POWER(P1, LENGTH(P2) ) / ( MOD(P1, LENGTH(P2))+1 ) ) + ( SQRT(P1) );
END;
```

```
CREATE OR REPLACE FUNCTION F_CALC_UDF(P1 NUMBER, P2 VARCHAR2)
RETURN NUMBER DETERMINISTIC
IS
PRAGMA UDF;
BEGIN
RETURN ( POWER(P1, LENGTH(P2) ) / ( MOD(P1, LENGTH(P2))+1 ) ) + ( SQRT(P1) );
END;
```

I compared four different methods in this demo. First one is native SQL. Second with traditional PL/SQL function. Third with **pragma UDF** function and last one is **WITH** clause. The demo script runs the test case five times to get an approximate result. By the way, I used dynamic SQL instead of static for **WITH** clause. Because, PL/SQL language does not support the new syntax in Oracle 19c. For this reason, I also used dynamic SQL for the other examples in order to evaluate equitably.

In addition, if you use PL/SQL declared **WITH** clause, it should be the top level query or the top level query must include **WITH_PLSQL** hint. Otherwise, the statement will fail to compile as shown below.

```
SELECT * FROM (
WITH
FUNCTION CALC(P1 NUMBER, P2 VARCHAR2)
RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN ( POWER(P1, LENGTH(P2) ) / ( MOD(P1, LENGTH(P2))+1 ) ) + ( SQRT(P1) );
END;
SELECT T.*, CALC(C1, C2) NEW_C FROM T_TEST T
);
Error at line 2
ORA-32034: desteklenmeyen WITH yan tümcesi kullanımı
```

```
SELECT /*+ WITH_PLSQL */ * FROM (
WITH
FUNCTION CALC(P1 NUMBER, P2 VARCHAR2)
RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN ( POWER(P1, LENGTH(P2) ) / ( MOD(P1, LENGTH(P2))+1 ) ) + ( SQRT(P1) );
END;
SELECT T.*, CALC(C1, C2) NEW_C FROM T_TEST T
);
```

**Oracle 19c** has been used for the demo.

```
DECLARE
V_START NUMBER := DBMS_UTILITY.GET_TIME;
TYPE REC IS RECORD(
C1 NUMBER,
C2 VARCHAR2(20),
C3 NUMBER
);
TYPE ARR_TYPE IS TABLE OF REC;
ARR ARR_TYPE;
C1 SYS_REFCURSOR;
V_SQLTEXT VARCHAR2(4000);
PROCEDURE SHOW_ELAPSED_TIME(P_NAME VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(P_NAME||' elapsed time: '||TO_CHAR((DBMS_UTILITY.GET_TIME - V_START)/100));
V_START := DBMS_UTILITY.GET_TIME;
END;
BEGIN
FOR I IN 1..5
LOOP
-----------------------------------------------------------------------------------
V_SQLTEXT := 'SELECT T.*, ( POWER(C1, LENGTH(C2) ) / ( MOD(C1, LENGTH(C2))+1 ) ) + ( SQRT(C1) ) NEW_C FROM T_TEST T';
OPEN C1 FOR V_SQLTEXT;
FETCH C1 BULK COLLECT INTO ARR;
CLOSE C1;
SHOW_ELAPSED_TIME('Native SQL');
-----------------------------------------------------------------------------------
V_SQLTEXT := 'SELECT T.*, F_CALC(C1, C2) NEW_C FROM T_TEST T';
OPEN C1 FOR V_SQLTEXT;
FETCH C1 BULK COLLECT INTO ARR;
CLOSE C1;
SHOW_ELAPSED_TIME('Traditional PL/SQL');
-----------------------------------------------------------------------------------
V_SQLTEXT := 'SELECT T.*, F_CALC_UDF(C1, C2) NEW_C FROM T_TEST T';
OPEN C1 FOR V_SQLTEXT;
FETCH C1 BULK COLLECT INTO ARR;
CLOSE C1;
SHOW_ELAPSED_TIME('Pragma UDF');
-----------------------------------------------------------------------------------
V_SQLTEXT :=
q'[WITH
FUNCTION CALC(P1 NUMBER, P2 VARCHAR2)
RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN ( POWER(P1, LENGTH(P2) ) / ( MOD(P1, LENGTH(P2))+1 ) ) + ( SQRT(P1) );
END;
SELECT T.*, CALC(C1, C2) NEW_C FROM T_TEST T]';
OPEN C1 FOR V_SQLTEXT;
FETCH C1 BULK COLLECT INTO ARR;
CLOSE C1;
SHOW_ELAPSED_TIME('WITH clause');
DBMS_OUTPUT.PUT_LINE(' ');
-----------------------------------------------------------------------------------
END LOOP;
END;
```

```
Native SQL elapsed time: 2,47
Traditional PL/SQL elapsed time: 6,4
Pragma UDF elapsed time: 2,89
WITH clause elapsed time: 2,98
---------------------------------------------
Native SQL elapsed time: 2,4
Traditional PL/SQL elapsed time: 5,34
Pragma UDF elapsed time: 2,19
WITH clause elapsed time: 2,37
---------------------------------------------
Native SQL elapsed time: 1,99
Traditional PL/SQL elapsed time: 6,72
Pragma UDF elapsed time: 3,13
WITH clause elapsed time: 3,15
---------------------------------------------
Native SQL elapsed time: 2,75
Traditional PL/SQL elapsed time: 6,96
Pragma UDF elapsed time: 3,13
WITH clause elapsed time: 3,24
---------------------------------------------
Native SQL elapsed time: 3,21
Traditional PL/SQL elapsed time: 7,74
Pragma UDF elapsed time: 3
WITH clause elapsed time: 3,31
---------------------------------------------
```

Undoubtedly, native SQL is the fastest way amoung the others. For 500K rows, the average result of five times run is 2,5 seconds. However, the point of this post is not to prove that native SQL is the fastest. To show the right way when we need to use PL/SQL inside SQL. Although, according to the graph, **pragma UDF** and **WITH** clause increase and decrease in the same direction and at the same rate, the avarage execution time of the **pragma UDF** function is 2,86 whereas, **WITH** clause is 3,01 seconds. We can say that **pragma UDF** performs better as compared to **WITH** clause. If you look at the results of the traditional PL/SQL function, you will see how context switching increases the overhead. It seems obvious that **Pragma UDF** function is more than two times faster than standalone PL/SQL function.

### Conclusion

The results show that it is beneficial to use **pragma UDF** function to reduce context switching overhead. However, it might become more expensive to call it from PL/SQL. When you state the **pragma UDF** clause in the function, you will say that the function will be exclusively used from SQL. On the other hand, **WITH** clause can be used as another alternative to reduce the context switching effect. If the function that you want to call from SQL is specific to a single SQL or if you cannot install your PL/SQL function or procedure to the database or maybe you need to work read only database, in such cases you can use **WITH** clause. Finally, as it is shown in the demo script, I used **DETERMINISTIC** function for both PL/SQL and **WITH** clause. If you use 12c, you might have noticed that deterministic functions cannot work in **WITH** clause as stated here by Jonathan Lewis. This behaviour was fixed in 18c onward.