CREATE OR REPLACE FUNCTION total_sales(zip_code varchar2) return clob  SQL_MACRO is
BEGIN
  RETURN q'{
   SELECT * 
   from csv
   where aa = total_sales.zip_code
  }';
END;

select * from total_sales('LG9860');

different between marco

https://stackoverflow.com/questions/70222940/difference-between-oracle-macro-and-udf

The SQL & PL/SQL languages have separate runtime engines. This means every time a SQL statement calls a PL/SQL UDF, there's a context switch (and vice-versa).

While each switch is fast, calling a PL/SQL function thousands or millions of times in a SQL statement can make it significantly slower.

SQL macros work differently. At parse time the database resolves the expression to become part of the statement. It searches for the parameter names in the return string. Then effectively does a find/replace of these with the text of whatever you've passed for these parameters.

For example, if you run:

select date_string ( date_col ) 
from   some_table;
The final SQL statement is effectively:

select to_char ( date_col, 'yyyy-mm-dd' )
from   some_table;

This means there's no runtime context switch. This can lead to good performance gains, for example:

create function date_string_macro ( dat date ) 
   return varchar2 sql_macro(scalar) is
begin
   return q'{ to_char(dat, 'yyyy-mm-dd') }';
end;
/

create function date_string_sql ( dat date ) 
   return varchar2 is
begin
   return to_char(dat, 'yyyy-mm-dd' );
end;
/

declare
  start_time pls_integer;
begin
  start_time := dbms_utility.get_time ();
  for rws in (
    select *
    from   dual
    where  date_string_sql ( sysdate + level ) > '2021'
    connect by level <= 1000000
  ) loop
    null;
  end loop;
  dbms_output.put_line ( 
    'PL/SQL runtime = ' || ( dbms_utility.get_time () - start_time ) 
  );

  start_time := dbms_utility.get_time ();
  for rws in (
    select *
    from   dual
    where  date_string_sql ( sysdate + level ) > '2021'
    connect by level <= 100000
  ) loop
    null;
  end loop;
  dbms_output.put_line ( 
    'Macro runtime  = ' || ( dbms_utility.get_time () - start_time ) 
  );
end;
/

PL/SQL runtime = 570 Macro runtime = 54 Around 10x faster in this case!

Because the expression becomes part of the SQL statement, as a side benefit the optimizer has full visibility of the underlying expression. This may lead to better execution plans.

This enables you to get the code-reuse benefits of PL/SQL functions (e.g. common formulas, string formatting, etc.) with the performance of pure SQL.

So why not make all existing PL/SQL function macros?

There are a couple of other important differences between them.

First up, order of argument evaluation. PL/SQL uses application-order, macros use normal-order. This can lead to behaviour differences in some cases:

create or replace function first_not_null ( 
  v1 int, v2 int
) 
  return int as
begin
  return coalesce ( v1, v2 );
end first_not_null;
/

select first_not_null ( 1, 1/0 ) from dual;

ORA-01476: divisor is equal to zero

create or replace function first_not_null  ( 
  v1 int, v2 int
) 
  return varchar2 sql_macro ( scalar ) as
begin
  return ' coalesce ( v1, v2 ) ';
end first_not_null;
/

select first_not_null ( 1, 1/0 ) from dual;

FIRST_NOT_NULL(1,1/0)
1 Secondly - and more importantly - resolving the expression only happens in SQL. If you call a SQL macro in PL/SQL, it returns the string as-is:

exec dbms_output.put_line ( first_not_null ( 1, 0 ) );
coalesce ( v1, v2 ) 

Finally SQL macros also have a table variant. This allows you to create template queries you can pass tables and columns to.

I've discussed table macros in several blog posts, showing how you can use table macros to write generic top-N per group functions and reusable CSV-to-rows functions

BTW: not only that SQL and PL/SQL have different engines, they also have different data types. There is nearly one-to-one mapping between their data types but it is not exact. Most common problem is max. length of VARCHAR in SQL(4K) versus in PL/SQL(32K). –

True, though you're only going to convert functions to macros that you use in SQL already, so it's a lesser issue. Also you can increase the max length of varchar2 to 32k by setting max_string_size = extended –