You will see that effect if func2 is throwing a no_data_found exception, rather than returning null, for the input value 4333. That probably makes sense if that function is looking up the value from another table.
Because your call to func1 is in a SQL context that exception is silently fatal, and execution bails when it's encountered, without any indication of what is wring. If you called func1 from a PL/SQL context you would see the exception.
As a demo with a dummy func2:
create or replace function func2(p_num number) return varchar2 as
begin
if p_num = 4333 then
raise no_data_found;
end if;
return case p_num
when 1111 then 'val1'
when 2222 then 'val2'
when 121212 then 'val3'
else null
end;
end func2;
/
and your original function:
create or replace FUNCTION FUNC1(num_list varchar2,separator varchar2) RETURN typeAs PIPELINED
as
l_val varchar2(20);
begin
FOR nums in (select regexp_substr(num_list,'[^'||separator||']+', 1, level) num from dual
connect by regexp_substr(num_list, '[^'||separator||']+', 1, level) is not null)
loop
PIPE ROW(typeA(NVL(nums.num,0) ,NVL(FUNC2(nums.num),0)));
end loop;
end;
/
select * from table(FUNC1('1111,4333,121212',','));
NUM VAL
-------------------- --------------------
1111 val1
select * from table(FUNC1('1111,2222,121212',','));
NUM VAL
-------------------- --------------------
1111 val1
2222 val2
121212 val3
You could change func1 to call the function explicitly and handle the exception:
create or replace FUNCTION FUNC1(num_list varchar2,separator varchar2) RETURN typeAs PIPELINED
as
l_val varchar2(20);
begin
FOR nums in (select regexp_substr(num_list,'[^'||separator||']+', 1, level) num from dual
connect by regexp_substr(num_list, '[^'||separator||']+', 1, level) is not null)
loop
begin
l_val := FUNC2(nums.num);
exception
when no_data_found then
l_val := null;
end;
PIPE ROW(typeA(NVL(nums.num,0) ,NVL(l_val,0)));
end loop;
end;
/
select * from table(FUNC1('1111,4333,121212',','));
NUM VAL
-------------------- --------------------
1111 val1
4333 0
121212 val3
select * from table(FUNC1('1111,2222,121212',','));
NUM VAL
-------------------- --------------------
1111 val1
2222 val2
121212 val3
db<>fiddle demo.
Or you could change func2 so it catches the exception and returns null, or changes its query so it can't get the exception in the first place. Whether that is appropriate is up to you - it may be used in other places where the exception needs to be propagated...
(Incidentally, as func2 returns a string, the nvl() for that should really use a string too - even if that is 0, as it is implicitly converted to now.)
TABLEfunction, the arguments that you passed and what output you are seeing in tabular form.Also, show part offunc2if it'srelevant to your explanationfunc2is probably throwing a no-data-found exception, rather than returning null?