In the last blog post on SQL Macros , ( https://zahirmohideen.blogspot.com/2023/03/sql-macros.html) , I was exploring SQL macros.
In Oracle 23c , scalar macro is ported from 21c .
BTW , I am testing this feature on 23c Free - Developer's Release ( https://www.oracle.com/database/free/)
Here is the test case from the previous post.
BANNER
___________________________________________________________________
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL> CREATE OR replace FUNCTION calculate_pies (
2 p_arg1 NUMBER
3 ) RETURN VARCHAR2 sql_macro ( scalar ) is
4 begin
5 return q'{
6 p_arg1 * 3.14
7 }';
8 end;
9* /
Function CALCULATE_PIES compiled
SQL> SELECT
2 level,
3 calculate_pies(level)
4 FROM
5 dual
6 CONNECT BY
7* level <= 5;
LEVEL CALCULATE_PIES(LEVEL) ________ ________________________ 1 3.14
2 6.28
3 9.42
4 12.56
5 15.7
Another new feature is that we don't need DUAL to get the results.Albeit this feature is tiny , it goes parallel with all other RDBMSes
SQL> SELECT
2 level,
3 calculate_pies(level)
4 CONNECT BY
5* level <= 5;
LEVEL CALCULATE_PIES(LEVEL)
________ ________________________
1 3.14
2 6.28
3 9.42
4 12.56
5 15.7
More to come on 23c New Features.
Stay Tuned.
