Function to get the list of Sting with covered with Quotes for individual columns in the List.

CREATE OR REPLACE FUNCTION get_not_in_clause (
   p_list_of_columns                    IN   VARCHAR2
)
   RETURN VARCHAR2
IS
   ---------------------------------------------------------------------------------
   -- Created By : Vijaykumar
   -- Creation Date :  15-Feb-2014
   -- purpose : purpose of this function is to split the string which is comma or any other
   --           separator and return the string with inverted quote string for each column in
   --          parameter list.
   --          ex :- 'CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY'
   --          the output of the this function will as below
   --          'CREATED_BY','CREATION_DATE','LAST_UPDATE_DATE','LAST_UPDATED_BY'
   -------------------------------------------------------------------------------
   l_count                                      NUMBER;
   l_string                                     VARCHAR2 (50);
   l_concatenated_string                        VARCHAR2 (2000);
BEGIN
   ------------------------------------------------------------------------
   --- below select statement is used to  Get the total count of Commas in
   --- the parameter string and adding 1 to consider the last column in the
   --- parameter list
   ------------------------------------------------------------------------
   BEGIN
      SELECT (LENGTH (REGEXP_REPLACE (p_list_of_columns, '[^,]')) / LENGTH (',')) + 1
        INTO l_count
        FROM DUAL;
   END;

   DBMS_OUTPUT.put_line ('Count Of Comma occurenece : ' || l_count);

   IF l_count IS NULL
   THEN
      l_concatenated_string                          := '''' || p_list_of_columns || '''';
   ELSE
      l_count                                        := l_count;
      DBMS_OUTPUT.put_line ('Count Of Comma occurenece 1: ' || l_count);

      FOR i IN 1 .. l_count
      LOOP
         DBMS_OUTPUT.put_line ('i : ' || i);

         SELECT REGEXP_SUBSTR (p_list_of_columns
                             , '[^,]+'
                             , 1
                             , i
                              )
           INTO l_string
           FROM DUAL;

         DBMS_OUTPUT.put_line ('String : ' || l_string);

         IF l_concatenated_string IS NULL
         THEN
            l_concatenated_string                          := '''' || l_string || '''';
         ELSE
            l_concatenated_string                          := l_concatenated_string || ',' || '''' || l_string || '''';
         END IF;
      END LOOP;
   END IF;

   RETURN l_concatenated_string;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('UNexpected Error : ' || SUBSTR (SQLERRM
                                                           , 1
                                                           , 250
                                                            ));
      RETURN NULL;
END;

Scrpt to get the Nth Occurrence of a string in the String

User below script to get the Nth occurrence of the string within a string separated by any string separator.

Ex.. i have a string like 'CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY' and within this string if i want to split the string for Individual column then use the below script.


SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

Please find below the use and output for the above select statement.

SQL> SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL;

Enter value for v_string: CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY

Enter value for l_comma_position: 1

old   1: SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

new   1: SELECT REGEXP_SUBSTR ('CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY', '[^,]+',

REGEXP_SUB

----------

CREATED_BY

SQL> /

Enter value for v_string: CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY

Enter value for l_comma_position: 2

old   1: SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

new   1: SELECT REGEXP_SUBSTR ('CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY', '[^,]+',

REGEXP_SUBSTR

-------------

CREATION_DATE

SQL> /

Enter value for v_string: CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY

Enter value for l_comma_position: 3

old   1: SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

new   1: SELECT REGEXP_SUBSTR ('CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY', '[^,]+',

REGEXP_SUBSTR('C

----------------

LAST_UPDATE_DATE

SQL> /

Enter value for v_string: CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY

Enter value for l_comma_position: 4

old   1: SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

new   1: SELECT REGEXP_SUBSTR ('CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY', '[^,]+',

REGEXP_SUBSTR('

---------------

LAST_UPDATED_BY

SQL> 

How to split comma separated string and pass to IN clause of select statement/ Create Comma Separated String using Select statement

SELECT   COLUMN_NAME
       , COLUMN_ID
    FROM DBA_TAB_COLUMNS
   WHERE TABLE_NAME = :P_TABLE_NAME
     AND OWNER = :P_OWNER
     AND COLUMN_NAME NOT IN (SELECT REGEXP_SUBSTR (:L_STRING
                                                     , '[^,]+'
                                                     , 1
                                                     , LEVEL
                                                      )
                                   FROM DUAL
                             CONNECT BY REGEXP_SUBSTR (:L_STRING
                                                     , '[^,]+'
                                                     , 1
                                                     , LEVEL
                                                      ) IS NOT NULL)
ORDER BY COLUMN_ID ASC

WHERE AS THE l_STRING CAN BE PASSED AS 'COLUMN1,COLUMN2,COLUMN3'

Use Below SQL to create comma separated string for multiple rows.
Limitations : The below SQL has String length Constraint. If the length of number of rows in a column getting converted into one row searating each other by using any separator, in that case the length should not exceed 2000 characters. 

SELECT SUBSTR(SYS_CONNECT_BY_PATH(''''||lookup_code||''''
                                    , ',')
                , 2)
              lookup_code
      FROM (SELECT lookup_code
                 , ROW_NUMBER() OVER (ORDER BY lookup_code) rownumber
                 , COUNT( *) OVER () cunt
              FROM fnd_lookup_values
             WHERE lookup_type = 'ITEM_TYPE'
               )
     WHERE rownumber = cunt
START WITH rownumber = 1

CONNECT BY rownumber = PRIOR rownumber + 1

How to Search a String for a LONG/CLOB datatype column

CREATE OR REPLACE FUNCTION apps.xx_search_long(
   p_obj_type                 IN             VARCHAR2
 , p_obj_name                 IN             VARCHAR2
 , p_owner                    IN             VARCHAR2
 , p_search_str               IN             VARCHAR2
)
   RETURN VARCHAR2
IS
   temporary_varchar                                 VARCHAR2(31000);
   v1                                                LONG;
   v_src_code                                        CLOB;
   v_length                                          NUMBER;
BEGIN
   BEGIN
      SELECT DBMS_METADATA.get_ddl(p_obj_type
                                 , p_obj_name
                                 , p_owner
                                  )
        INTO v_src_code
        FROM DUAL;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line('Exception : ' || SUBSTR(SQLERRM
                                                     , 1
                                                     , 150
                                                      ));
   END;
   v_length                                       := DBMS_LOB.getlength(v_src_code);
   DBMS_OUTPUT.put_line('Length of CLOB - ' || v_length);
   IF DBMS_LOB.INSTR(v_src_code
                   , p_search_str
                    ) > 0
   THEN
      DBMS_OUTPUT.put_line('Found ....');
      RETURN('FOUND');
   ELSE
      DBMS_OUTPUT.put_line('Not Found ....');
      RETURN('NOT FOUND');
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN('NOT_FOUND');
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line('Error .' || SQLERRM);
      RETURN('EXCEPTION' || SUBSTR(SQLERRM
                                 , 1
                                 , 150
                                  ));
END;
/

Sample Scripts to Convert the Rows into One Column with seperator

 SELECT header_id, RTRIM(EXTRACT(EXML,'//ordereditem/text()'),',') ORDERED_ITEM
   FROM (SELECT E.header_id,
          xmlelement("HDR",
              XMLAGG(xmlelement("ordereditem",E.ordered_item||',') ORDER BY ordered_item))EXML
   FROM oe_order_lines_all E
   where header_id= 278563
   GROUP BY E.header_id);
***************************************************************************
SELECT header_id, LTRIM( SYS_CONNECT_BY_PATH( ORDERED_ITEM, ',' ), ',' ) iTEM_STRING
      FROM (SELECT LINE_ID, HEADER_ID, ORDERED_ITEM, ROW_NUMBER( ) OVER (PARTITION BY HEADER_ID ORDER BY ROWNUM) RN
              FROM OE_ORDER_LINES_aLL
             WHERE header_id = 278563)
     WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY HEADER_ID = PRIOR HEADER_ID
       AND RN = PRIOR RN + 1
START WITH rn = 1
  ORDER BY HEADER_ID;
***************************************************************************
SELECT   header_id
       , RTRIM(XMLAGG(XMLELEMENT(e
                               , ordered_item || ','
                                )).EXTRACT('//text()')
             , ','
              ) ordered_item
    FROM oe_order_lines_All
    where header_id = 278563
GROUP BY header_Id
***************************************************************************
SELECT     header_id
         , SUBSTR(SYS_CONNECT_BY_PATH(ordered_item
                                    , ','
                                     )
                , 2
                 ) name_list
      FROM (SELECT ordered_item
                 , header_id
                 , COUNT(*) OVER(PARTITION BY header_id) cnt
                 , ROW_NUMBER() OVER(PARTITION BY header_id ORDER BY ordered_item) seq
              FROM oe_order_lines_all
             WHERE header_id = 278563)
     WHERE seq = cnt
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq
       AND PRIOR header_id = header_id;