IF condition not working correctly in Oracle PL/SQL

Question

My IF condition is always executing ELSE statement. I could not find reason why it's returning "No". It should return "Yes".

It looks like parameter XPARAM is getting NULL value but why?

Note: I have to return a table from that function because variable sql_statement will be dynamic.

Below is the code and the output:

create or replace function traudit04011 (
XPARAM IN VARCHAR2)
return VARCHAR2 sql_macro as
 
sql_statement varchar2(5000);
vc_condition varchar(1000);
p_search_by1 varchar(100) := '';
 
 
begin 
   
IF  XPARAM is not NULL THEN 
vc_condition:='yes';
ELSE
vc_condition:='No';
END IF;
 
sql_statement := 'select ''' || vc_condition || ''' d1 from dual'; -- In real world, instead of XPARAM there will be a variable storing dynamic query
  
return sql_statement; 
 
end;

Output :

I tried replacing parameter with local variable but the output is the same:

create or replace function traudit04011 (
XPARAM VARCHAR2)
return VARCHAR2 sql_macro as
 
sql_statement varchar2(5000);
vc_condition varchar(1000);
p_search_by1 varchar(100) := '';
 
 
begin 
p_search_by1:= XPARAM;
   
IF  p_search_by1 is not NULL THEN 
vc_condition:='yes';
ELSE
vc_condition:='No';
END IF;
 
sql_statement := 'select ''' || vc_condition || ''' d1 from dual'; -- In real world, instead of XPARAM there will be a variable storing dynamic query
  
return sql_statement; 
 
end;

I am executing the above function from the below statement :

select * from traudit04011('service_date')

Any help is deeply appreciated. I am completely blocked here.

SQlFiddle --- > https://dbfiddle.uk/j537Ye0K

Answer 1

The parameter in a SQL Macro needs to be directly referenced in the returned string.

For example, the below function processes the parameter with a CASE statement returned by the string. The output is correct: "Yes" when the input is not null, and "No" when the input is null.

create or replace function is_input_not_null_direct(p_value in varchar2)
return VARCHAR2 sql_macro as
begin
   return
   q'[
      select case when p_value is not null then 'Yes' else 'No' end result from dual
   ]';
end;
/

-- This should return 'Yes'.
select * from is_input_not_null_direct('not null value');

RESULT
------
Yes

-- This should return 'No'.
select * from is_input_not_null_direct('');

RESULT
------
No

But this below function processes the parameter in PL/SQL, stores the result in a variable, and then returns that variable. But the code does not work and returns the wrong result "No" for both cases:

create or replace function is_input_not_null_indirect(p_value in varchar2)
return VARCHAR2 sql_macro as
   v_indirect_variable varchar2(4000) := case when p_value is not null then 'Yes' else 'No' end;
begin
   return
      'select ''' || v_indirect_variable || ''' result from dual';
end;
/

-- This should return 'Yes'.
select * from is_input_not_null_indirect('not null value');

RESULT
------
No

-- This should return 'No'.
select * from is_input_not_null_indirect('');

RESULT
------
No

Fiddles: https://dbfiddle.uk/GZI3xzob, https://dbfiddle.uk/Xb5KL4SC

But why?

I'm not entirely sure why SQL Macros behave this way and I can't find this behavior explained in the documentation.

What I think is happening is that SQL Macros are trying to ensure that not every execution is dynamic, because that kind of behavior would lead to lots of hard parsing and poor performance. So each statement is checked to see if the output depends on the input, and if there is no relationship than the same static text is returned. But that checking isn't working correctly here and is fooled by a simple indirect variable.

I'm not sure if this behavior is a bug or an unavoidable consequence of this kind of pseudo-dynamic code.

Workarounds

There's always a dynamic SQL solution, but you may need to use a different type of dynamic SQL.

• SQL Macro - If you could cram all of your logic in the returned string, then this should work. But if your logic is super complicated, a SQL Marco probably won't work. SQL Macros are powerful, but they are really wrappers meant to simplify the most common types of dynamic SQL. Not all problems can be solved with a SQL Macro.
• Dynamic Ref Cursor - If your data is going to be consumed by an application, a dynamic ref cursor is the easiest and most powerful solution. Write whatever SQL statement you want, and the application can run it. The downside is that ref cursors cannot be easily consumed by the database, so you can't use the results directly in a SQL statement.
• Polymorphic Table Functions - Another kind-of-simplified version of dynamic SQL. You can run any SQL statement, but the returned column types can only be sort-of dynamic.
• Execute immediate with table function - As Paul W suggested, this is one of the most powerful and common ways to get dynamic results. But you must create a static type that describes all the columns returned. As long as the column types are always the same, you can run any SQL statement.
• Oracle Data Cartridge Interface - If you need 100% flexibility with your results, and even with the shapes of your results, and need it in a SQL format, you're stuck with ODCI. It's infinitely powerful, but also complex and buggy. Very few people really need to use this feature, because in practice, a query with no predictable shape is usually worthless. If you need this feature, I recommend you start with my open source program here.