PL/SQL (MOSC)

MOSC Banner

Overloaded Function to Convert a Column to Comma-Separated String

edited Aug 15, 2012 6:14AM in PL/SQL (MOSC) 9 commentsAnswered
Dear Gurus,

I wanted to create a function to convert a column to comma-separated string:

CREATE OR REPLACE TYPE VARCHAR2_TBL AS TABLE OF VARCHAR2(100);
/

CREATE OR REPLACE FUNCTION TABLE_TO_STRING (
                    NT_IN        IN VARCHAR2_TBL,
                    DSTCT IN CHAR DEFAULT 'N',
                    DELIMITER_IN IN VARCHAR2 DEFAULT ', '
                    ) RETURN VARCHAR2 IS

       V_IDX PLS_INTEGER;
       V_STR VARCHAR2(32767);
       --V_ARR_SORT VARCHAR2_TBL;  /* Sort input into this array */

   BEGIN
         /* SORTING ... */
            FOR V_IDX IN NT_IN.FIRST..NT_IN.LAST
            LOOP
               IF v_idx = 1 then
                  V_STR := NT_IN(V_IDX);
               else
                  V_STR := V_STR || DELIMITER_IN || NT_IN(V_IDX);

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center