function

create or replace function getSignUsers(strExamID varchar2,strUserCode varchar2,strSignCount varchar2) return varchar2 is
Result varchar2(1000);
i integer :=0;
cursor c1 is
select cname from exam_apply_current_user a , empymt@pqpms b
where exam_id = strExamID
and a.user_code = strUserCode
and a.sign_count = strSignCount
and a.sign_user_code = b.empyno
order by cname;
begin
Result := '';
for r1 in c1 loop
if c1%notfound then
exit;
end if;
if i = 0 then
Result := r1.cname;
else
Result := result||','||r1.cname;
end if;
i := i +1;
end loop;
return(Result);
end getSignUsers;


create or replace function getWeekly(var_date in date)
return varchar2
is

tmp_weekly varchar2(10) := null;
begin
--var_date + 1, for Sunday is first day of a week
select case when to_char(var_date+1 , 'iw') = '01' and to_char(var_date , 'iw') = '01' and to_char(var_date , 'mmdd') > '0201'
then to_char(var_date , 'yyyy')||'-'||to_char(to_number(to_char(var_date - 6 , 'iw')) + 1)
else case when to_char(var_date+1 , 'iw') = '01' and to_char(var_date , 'iw') <> '01' and to_char(var_date , 'mmdd') > '0201'
then to_char(var_date , 'yyyy')||'-'||to_char(to_number(to_char(var_date , 'iw')) + 1)
else to_char(var_date+1 , 'yyyy-iw') end end into tmp_weekly from dual ;

return tmp_weekly ;
end getWeekly;