-- File: delimlist.sql -- Author: Keats Kirsch -- Date: 25 Apr 2002 -- Description: function which takes a query and returns a delimited list -- constructed from the first column of its results. The default delimiter -- is '|', but the user can supply any string. Results longer than 4000 -- characters are truncated. --PROMPT CREATE OR REPLACE FUNCTION hdms.delimlist CREATE OR REPLACE FUNCTION delimList(qry VARCHAR2, delim VARCHAR2 := '|') RETURN VARCHAR2 IS TYPE rctype IS REF CURSOR; csr rctype; res VARCHAR2(32767); colval VARCHAR2(4000); BEGIN OPEN csr FOR qry; FETCH csr INTO res; LOOP FETCH csr INTO colval; EXIT WHEN csr%NOTFOUND; res := res || delim || colval; IF (LENGTH(res) > 4000) THEN res := substr(res, 1, 4000); EXIT; END IF; END LOOP; CLOSE csr; RETURN res; -- EXCEPTION -- WHEN VALUE_ERROR THEN -- RETURN NULL; -- WHEN OTHERS THEN -- RETURN null; END; / /* -- Examples: SELECT delimlist( 'SELECT d_terrestrial_habitat_id' || ' FROM animal_cag_terr_hab' || ' WHERE element_global_id = ' || element_global_id) AS terrestrial_habitat FROM element_global WHERE element_global_id = 17442; SELECT elcode_bcd, delimlist( 'SELECT DLOOKUP(''D_TERRESTRIAL_HABITAT'', d_terrestrial_habitat_id)' || ' FROM animal_cag_terr_hab' || ' WHERE element_global_id = ' || element_global_id) AS terrestrial_habitat FROM element_global WHERE element_global_id = 17442; */