Oracle - IF condition not working inside Macro function

Question

create or replace FUNCTION macro_If_condition( p_1 varchar2 )
RETURN varchar2 SQL_MACRO is
  v_sql varchar2(1000);
BEGIN
  if p_1 is not null then
    v_sql := q'[     select p_1 from dual         ]' ;
    else
    v_sql := q'[     select 'NULLL' from dual         ]' ;    
  end if;
  RETURN v_sql;
  END;

SELECT * FROM macro_If_condition( 'input1') t;

Op: NULLL --- its incorrect bcz we have passed input1 but don’t know why it went to ELSE condition

Can anyone explain why the IF condition is not working. You can try the above sample code.

Chris Saxon Chris Saxon · Accepted Answer · 2022-07-28T14:37:01

The value of string parameters in the body of SQL macros is always null. You cannot use them to change the expression you return like this.

If you want to convert null inputs into the string nulll, you need to place this logic in the return string itself. For example, using a case expression like this:

create or replace function macro_if_condition ( p_1 varchar2 )
return varchar2 sql_macro is
  v_sql varchar2(1000);
begin
  v_sql := q'[
    select case when p_1 is not null then p_1 else 'nulll' end p1_val
    from   dual         
  ]' ;    
  
  return  v_sql;
end;
/

select * from macro_if_condition ( null );

P1_VA
-----
nulll

select * from macro_if_condition ( 'input' );

P1_VA
-----
input

Oracle - IF condition not working inside Macro function

1 Answers