Oracle PL/SQL varchar2 parameter of a function is returning NULL value

I have created a function that returns a table. This function has varchar2 parameter and the real surprising thing is that this parameter is returning NULL value and I cannot understnd what is going on here.

The purpose of this function is to execute a dynamic SQL query and return a table.

For simplicity, I have removed all the complicated lines of code. Below code is only returning the parameter value. I need to use this parameter in IF BLOCK. However, my if block always returns false and that is because parameter is getting NULL value.

create or replace function traudit04011 (
XPARAM IN VARCHAR2)
return clob 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;
 
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line('error');
 
 
end;

Now execute the above function :

select * from traudit04011('This is a parameter that always returns null')

What's wrong here. I have already spent 6 hours figuring this out. The strange part is that if I change the datatype of parameter to INT, the function starts working. :)

Any help would be appreciated..

You have used the Input parameter in the query with normal string concatenation.

You need to use function_name.input_variable_name directly in the dynamic query as follows:

sql_statement := 'select traudit04011.XPARAM d1 from dual';

DB<>Fiddle