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;