CREATE OR REPLACE FUNCTION palomar_live.concatenate_list (
    p_cursor in Number
    )
  RETURN  CLOB
IS
  l_return  CLOB;

cursor get_desc IS 
    SELECT to_clob(program_cat_desc_text) as desc_text 
    FROM palomar_live.PROGRAM_CAT_DESC
    WHERE programs_id =  p_cursor
    order by order_num;
BEGIN
    for I in get_desc 
    LOOP
    l_return := l_return||I.desc_text;
    END LOOP;
  RETURN l_return;
END;

create view curri_arc.curricusearch_programs
AS
(
select  p.program_title, 
        p.programs_id as p_id, 
        palomar_live.concatenate_list(p.programs_id) as program_desc,
        'Palomar' as school, 
        'http://www.curricunet.com/palomar/reports/program_report.cfm?programs_id='||p.programs_id as url_report,
        'Palomar_live' as datasource,
        'CA' as state,
        at.award_type_title as award_title,
        s.subject_code as subject_code
from    palomar_live.programs p,
        palomar_live.award_types at,
        palomar_live.subjects s
where   p.award_types_id = at.award_types_id(+)
AND     p.program_status_id IN (1,8)
AND     p.subjects_id = s.subjects_id
UNION
select  p.program_title, 
        p.programs_id as p_id, 
        hawkeye.concatenate_list(p.programs_id) as program_desc,
        'Hawkeye' as school, 
        'http://www.curricunet.com/hawkeye/reports/program_report.cfm?programs_id='||p.programs_id as url_report,
        'hawkeye' as datasource,
        'IA' as state,
        at.award_type_title as award_title,
        s.department_title as subject_code
from    hawkeye.programs p,
        hawkeye.award_types at,
        hawkeye.departments s
where   p.award_types_id = at.award_types_id(+)
AND     p.program_status_id IN (1)
AND     p.departments_id = s.departments_id
AND     trim(p.program_title) != 'test'

)