CREATE OR REPLACE PROCEDURE getEventPageSelection(nodeNumbers IN NUM_ARRAY,
                                         selNumQryStr IN VARCHAR2,
                                          numOfRows IN  NUMBER  ,
                                          headerCacheSize IN NUMBER,
                                          totalCount IN NUMBER,
                                          selPageQryStr IN VARCHAR2,
                                          p_results OUT EVENTPAGE) 
 AS
  selRowNum NUMBER;
  startNum NUMBER;
  endNum NUMBER;
  pageDiff NUMBER;

 BEGIN
 
 -- Walk through the Node Number List and Insert into temporary table
    FOR nodenum IN 1 .. nodeNumbers.count
    LOOP
       INSERT INTO my_nodes values (nodeNumbers(nodenum));
    END LOOP;
  
 --Executing SQL Statement to find row Nume
   IF LENGTH(selNumQryStr)  > 0 THEN
	   EXECUTE IMMEDIATE selNumQryStr INTO selRowNum;
 END IF;
 
   IF selRowNum > 0 THEN   
 	  /*As we will be fetching the header cache above the row rentention
 		    reduce the total rows by header cache */
 	  IF headerCacheSize > 0 THEN      
 	      endNum := selRowNum + numOfRows - headerCacheSize - 1;
 	      startNum := selRowNum - headerCacheSize;
 	  ELSE
 	      endNum := selRowNum + numOfRows - 1;
 	      startNum := selRowNum;
 	  END IF;
 
 	  /*This is to care of the situation where the number of rows
 	    are less than the page in that case we should get all the
 	    rows not starting the rention row */  
 	  IF (totalCount > 0 AND totalCount < endNum) THEN
 	      pageDiff := endNum - totalCount;
 	      startNum := startNum - pageDiff;
 	      endNum := endNum - pageDiff;
 	  END IF;  
       
 	  EXECUTE IMMEDIATE selPageQryStr || endNum || ') where num >= ' || startNum BULK COLLECT INTO p_results;
  END IF;	  
                     
 END;

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;