asked on

how to write PL/SQL function

How to do a pl/sql function that takes two strings representing a list of
numbers separated by commas and returns a string representing the list
of each nth element added together. You don't know how long the list
will be, but assume it has to fit in a PL/SQL varchar2.

So,

add_nums('1,2,3,4','3,4,5,6') -> '4,6,8,10'
add_nums('1,2,3','15,14,13') -> '16,16,16'

It should fail gracefully when the lengths of the two lists are not
equal.

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sujith

A neater version:

create or replace function test_func(p_arg1 varchar2, p_arg2 varchar2)
return varchar2
as
begin
if ( instr(p_arg1,',') = 0 and instr(p_arg2,',') = 0 ) then
return to_number(p_arg1) + to_number(p_arg2);
elsif (instr(p_arg1,',') = 0 OR instr(p_arg2,',') = 0) then
raise_application_error(-20001, 'Length of the strings are not equal');
else
return to_char(to_number(substr(p_arg1, 1, instr(p_arg1,',') - 1)) + to_number(substr(p_arg2, 1, instr(p_arg2,',') - 1)))
||','||
test_func(substr(p_arg1, instr(p_arg1,',') + 1 ), substr(p_arg2, instr(p_arg2,',') + 1 ));
end if;
end;
/

gajmp

We can achive this in SQL itself. pls check the below SQL. In this we have to provide two string with comma delimeter. Sting should not end with comma. we can give any length of string lk
str = 1,2,3,4 str1=4,5,6,7 op=5,7,9,10,7
str=1,2,3,4,5 str1=2,3,4,5 op=3,5,7,9,5
str=1,2,3,4 str1=3,4,5,6 op=4,6,8,10

select op from (
select r, substr(sys_connect_by_path(tot, ','),2) op
from (
select r, sum(a)+sum(b) tot
from (
select rownum r, to_number(decode(rownum, 1, substr('&&str', 1, instr('&&str',',',1)-1),
length(translate('&&str', ',1234567890', ','))+1, substr('&&str',instr('&&str', ',',1,rownum-1)+1,length('&&str')),
substr('&&str', instr('&&str', ',',1,rownum-1)+1, (instr('&&str',',',1, rownum)-1 - instr('&&str', ',',1,rownum-1))))) a, 0 b
from all_objects
where rownum <= length(translate('&&str', ',1234567890', ','))+1
union
select rownum r, 0 a, to_number(decode(rownum, 1, substr('&&str1', 1, instr('&&str1',',',1)-1),
length(translate('&&str1', ',1234567890', ','))+1, substr('&&str1',instr('&&str1', ',',1,rownum-1)+1,length('&&str1')),
substr('&&str1', instr('&&str1', ',',1,rownum-1)+1, (instr('&&str1',',',1, rownum)-1 - instr('&&str1', ',',1,rownum-1))))) b
from all_objects
where rownum <= length(translate('&&str1', ',1234567890', ','))+1)
group by r) x
start with r=1
connect by prior r = r-1
order by r desc)
where rownum < 2