AndroidCreate list string in Coldfusion is very simple. We just use ValueList function of Coldfusion. What we can do it in Oracle? As we all know, there are a lot of built-in functions in Oracle and new more functions are including whenever upgrading Oracle version. Being not DBA (database administrator), I cannot know how many built-in functions are in Oracle and how many new functions are added into each version.
Today, I need to create list string in Oracle. My first though, I would search built-in function like ValueList in Oracle. As my though, I've search again and again but have no result at all. That's why, I decided myself to create new function for creating list string as follow:
2RETURN VARCHAR2
3IS
4 CURSOR C_TEST IS
5 SELECT PERSON_ID FROM FROM PERSON_TABLE;
6 CON_STRING VARCHAR2(32000);
7BEGIN
8 FOR C IN C_TEST LOOP
9 CON_STRING := C.PERSON_ID||','|| CON_STRING;
10END LOOP;
11RETURN SUBSTR(CON_STRING,1, LENGTH(CON_STRING)-1);
12END;
When I rendering about code in Oracle, it 100% works and not encountered any issue. After few minutes, I found wm_concat function. I'm not sure whether it's lucky or not. And usage is very simple.
2INTO V_PERSON_ID
3FROM PERSON_TABLE;
Top of Page
#1 by Travis on 5/3/11 - 6:45 AM
CREATE OR REPLACE FUNCTION mySchema.valueList (cToList IN SYS_REFCURSOR)
RETURN VARCHAR2
IS
lComplete VARCHAR2(32767);
iValue VARCHAR2(32767);
BEGIN
LOOP
FETCH cToList
INTO iValue;
EXIT WHEN cToList%NOTFOUND;
lComplete := lComplete || ', ' || iValue;
END LOOP;
CLOSE cToList;
RETURN trim(LTRIM(lComplete, ','));
END;
/
Then call it in any SQL statement within that schema by:
valueList(CURSOR(SELECT... FROM...)) someAlias
#2 by ppshein on 5/3/11 - 7:25 AM
Well, your function is cool and I feel my first coding is cool as well. Anyway, thanks for letting me know another create List function.