Oracle function is not returning variable value. Need to return a value stored in a variable

Question

I have a simple ask to return value stored in a variable from a function. I have a complex code of 1000 lines. I just made this function simple for understanding purposes

create or replace function traudit040_func_ss 
(p_Ambulance IN  varchar2
)
return clob sql_macro as
vc_desc varchar2(1000) := '' ;

begin
                    IF (p_Ambulance = 10) then                     
                             vc_desc := 'No';
                    END IF;               
                   

return  'select ''' || vc_desc || ''' v1 from dual';


end;

Now I am executing the function using the below statement

select * from traudit040_func_ss(10)

Please help me here. I am getting output as NULL, however the value I am expecting is "No" I think above IF condition is not working or I am missing something. Thank you in advance.

Answer 1

Don't select from the function. Select the function from dual:

select traudit040_func_ss('10') from dual;

If your input is a string, then present it with a string, and process it as a string, not a number (never trust implicit conversions).

create or replace function traudit040_func_ss 
    (p_Ambulance IN  varchar2) return clob sql_macro as

vc_desc varchar2(1000) := '' ;
begin
    If (p_Ambulance = '10') then                     
        vc_desc := 'No';
    end if;               

    return  'select ''' || vc_desc || ''' v1 from dual';
end;