0

I have this function in oracle which is supposed to get a comma separated list of numbers as input and iterate on each number and pass it to another function and then pipe the output.

create or replace FUNCTION FUNC1(num_list varchar2,separator varchar2) RETURN typeAs PIPELINED 
as 
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;

Noting that :

create or replace TYPE typeA AS OBJECT (num varchar2(20),val varchar2(20))
create or replace TYPE typeAs AS TABLE OF typeA;

the function works correctly in case all the numbers in the list will return an output other than null when passed to FUNC2. But if a number returns null when passed to FUNC2 , all followed numbers in the list are not shown in the output of FUNC1.

Edit: Results:

select * from table(FUNC1('1111,4333,121212',',')) :
output:
-----------------
1111 | val1

the other two numbers are not shown in the output as FUNC2('4333') is null , knowing that FUNC2('121212') has value. while :

select * from table(FUNC1('1111,2222,121212',',')) :
output:
-----------------
1111     | val1
2222     | val2
121212   | val3
6
  • 1
    Show us the query that you use to call the TABLE function, the arguments that you passed and what output you are seeing in tabular form.Also, show part of func2 if it'srelevant to your explanation Commented Dec 6, 2018 at 10:16
  • select * from table(FUNC1('1111,4333,121212',',')) : >output: >----------------- >1111 | val1 the other two numbers are not shown in the output as FUNC2('4333') is null , knowing that FUNC2('121212') has value. while : select * from table(FUNC1('1111,2222,121212',',')) : >output: >----------------- >1111 | val1 >2222 | val2 >121212| val3 Commented Dec 6, 2018 at 10:33
  • @Kaushik I edited the question Commented Dec 6, 2018 at 10:40
  • Works fine for me. Please check dbfiddle.uk/… There's some information that is missing in your question. Commented Dec 6, 2018 at 10:49
  • 1
    It looks like your func2 is probably throwing a no-data-found exception, rather than returning null? Commented Dec 6, 2018 at 10:58

1 Answer 1

2

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.)

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.