8 Replies Latest reply: Feb 8, 2013 6:35 PM by SamFisher RSS

    How to store the comma separated values

    SamFisher
      Hello All,

      I have a table named discount, having discount_id (number datatype) as one of the columns.
      User gives an input as comma separated value, (ex: '123,27890,3543')
      I am using built in proc which splits the comma separated values.
      DECLARE
         l_input   VARCHAR2 (4000) := '123,27890,3543';
         l_count   BINARY_INTEGER;
         l_array   DBMS_UTILITY.lname_array;
      BEGIN
         DBMS_UTILITY.comma_to_table (
            list     => REGEXP_REPLACE (l_input, '(^|,)', '\1x'),
            tablen   => l_count,
            tab      => l_array);
         DBMS_OUTPUT.put_line (l_count);   
      
         FOR i IN 1 .. l_count
         LOOP
            DBMS_OUTPUT.put_line (
                  'Element '
               || TO_CHAR (i)
               || ' of array contains: '
               || SUBSTR (l_array (i), 2));
         END LOOP;
      END;
      
      Result:
      3
      Element 1 of array contains: 123
      Element 2 of array contains: 27890
      Element 3 of array contains: 3543
      Result set is stored in an array.
      I would like to do a select query on discount table as
      Select * from discount where discount_id in (123, 27890, 3543).

      I'm looking for options,
      Do I need to create a new physical table as
      Create table New_table (id number) and do bulk insert into this table.
      Select * from discount where discount_id in (select distinct id from new_table).
      or
      Is there a better way?

      Thx
      Shank.
        • 1. Re: How to store the comma separated values
          rp0428
          >
          Result set is stored in an array.
          I would like to do a select query on discount table as
          Select * from discount where discount_id in (123, 27890, 3543).
          >
          You would need to use dynamic sql to do that and construct a query that looks like that.

          Which means you would need to create a PIPELINED function, pass '123,27890, 3543' as a parameter and get back a result set.
          • 2. Re: How to store the comma separated values
            SamFisher
            Thx RP. I will give a try for sure.

            This was one of the alternate way I found.
            CREATE OR REPLACE TYPE id_obj AS OBJECT
            (id number);
            
            create or replace type tbl_typ is table of id_obj;  
            
            
            DECLARE
               --l_input   VARCHAR2 (4000) := '123,27890,3543';
               l_input   VARCHAR2 (4000) := '23,890,543';
               l_count   BINARY_INTEGER;
               l_array   DBMS_UTILITY.lname_array;
               v_tbl tbl_typ := tbl_typ(); 
            BEGIN    
               DBMS_UTILITY.comma_to_table (
                  list     => REGEXP_REPLACE (l_input, '(^|,)', '\1x'),
                  tablen   => l_count,
                  tab      => l_array);
               DBMS_OUTPUT.put_line (l_count);   
             
               FOR i IN 1 .. l_count
               LOOP
                  DBMS_OUTPUT.put_line (
                        'Element '
                     || TO_CHAR (i)
                     || ' of array contains: '
                     || SUBSTR (l_array (i), 2));
                     v_tbl.extend;
                     v_tbl(i) := id_obj(SUBSTR (l_array (i), 2));
               END LOOP;
               
               for i in (select * from table(v_tbl))
               LOOP
                dbms_output.put_line(i.id);
               END LOOP;
            END;
            
            Result:
            3
            Element 1 of array contains: 23
            Element 2 of array contains: 890
            Element 3 of array contains: 543
            23
            890
            543
            Thx
            • 3. Re: How to store the comma separated values
              ---Brodyaga---
              Hello, SamFisher.
              May be recursive subquery factoring help you?
              WITH test(f1, n) AS (select '12, 15, 235', 1 from dual
                                   UNION all  
                                   select f1, n + 1 from test where n < regexp_count('12, 15, 235', ',') + 1)
              SELECT regexp_substr(f1, '[^, ]+', 1, n) f
              FROM test
              • 4. Re: How to store the comma separated values
                SamFisher
                Thank You.

                It worked.
                • 5. Re: How to store the comma separated values
                  SamFisher
                  Hi,

                  Oracle Ver: 11g

                  When I tried to query the below sql, I got an unsupported use of WITH clause error
                  WITH t
                       AS (SELECT d.disc_line_id,
                                  q.qual_id,
                                  d.cspp_program,
                                  r.req_name,
                                  r.req_id,
                                  r.req_number,
                                  d.disc_value,
                                  d.disc_method,
                                  d.disc_status,
                                  d.disc_start_dt,
                                  d.disc_end_dt,
                                  q.attrib_code,
                                  l.list_value,
                                  q.qual_value,
                                  d.multiyear_band_from,
                                  d.multiyear_band_to
                             FROM edms_disc_lines_stg d,
                                  edms_disc_request_hdr r,
                                  edms_qual_stg q,
                                  edms_qual_list_stg l
                            WHERE     d.req_id = r.req_id(+)
                                  AND q.disc_line_id(+) = d.disc_line_id
                                  AND l.qual_id(+) = q.qual_id
                                  AND d.disc_line_id IN
                                         (WITH test (f1, n)
                                               AS (SELECT '536628, 536629, 65308, 237840', 1
                                                     FROM DUAL
                                                   UNION ALL
                                                   SELECT f1, n + 1
                                                     FROM test
                                                    WHERE n <
                                                               REGEXP_COUNT (
                                                                  '536628, 536629, 65308, 237840',
                                                                  ',')
                                                             + 1)
                                          SELECT REGEXP_SUBSTR (f1,
                                                                '[^, ]+',
                                                                1,
                                                                n)
                                                    f
                                            FROM test)
                                  AND disc_status = 'ACTIVE')
                  SELECT DISTINCT disc_line_id,
                                  req_name,
                                  req_number,
                                  disc_value,
                                  disc_method,
                                  disc_status,
                                  disc_start_dt,
                                  disc_end_dt,
                                  cspp_program
                    FROM t;
                  [Error] Execution (27: 25): ORA-32034: unsupported use of WITH clause

                  Thx
                  • 6. Re: How to store the comma separated values
                    SamFisher
                    unsupported use of WITH clause error
                    • 7. Re: How to store the comma separated values
                      Frank Kulash
                      Hi,
                      SamFisher wrote:
                      Hi,

                      Oracle Ver: 11g
                      Do you mean 11.1 or 11.2?
                      Recursive WITH clauses only work in 11.2.
                      When I tried to query the below sql, I got an unsupported use of WITH clause error
                      Basically, the keyword WITH can only appear once in a query. (There are some exceptions, but not really worth mentioning now.)
                      Re-arrange your query so that you only use the keyword WITH once. Getting rid of the unnecessary sub-queries would help, too.

                      Perhaps this is what you want:
                      WITH     test (f1, n)     AS 
                      (
                           SELECT  '536628, 536629, 65308, 237840'
                           ,      1
                              FROM      dual
                          UNION ALL
                              SELECT     f1
                           ,      n + 1
                              FROM      test
                           WHERE      n < REGEXP_COUNT ( f1
                                               , ','
                                           ) + 1
                      )
                      SELECT DISTINCT
                                   d.disc_line_id,
                                      r.req_name,
                                      r.req_number,
                                      d.disc_value,
                                      d.disc_method,
                                      d.disc_status,
                                      d.disc_start_dt,
                                      d.disc_end_dt,
                                      d.cspp_program,
                                 FROM edms_disc_lines_stg d,
                                      edms_disc_request_hdr r,
                                      edms_qual_stg q,
                                      edms_qual_list_stg l
                                WHERE     d.req_id          = r.req_id(+)
                                      AND q.disc_line_id (+)      = d.disc_line_id
                                      AND l.qual_id (+)        = q.qual_id
                                      AND d.disc_line_id      IN  
                                         (
                                          SELECT  REGEXP_SUBSTR ( f1
                                                                 , '[^, ]+'
                                                                            , 1
                                                                            , n
                                                       ) 
                                          FROM    test
                                     )
                                      AND disc_status          = 'ACTIVE'
                      ;
                      If you'd care to post CREATE TABLE and INSERT statements for a little sample data, and the results you want from that sample data, then I could test it.
                      See the forum FAQ {message:id=9360002}
                      • 8. Re: How to store the comma separated values
                        SamFisher
                        Thank You Frank.

                        Yes it worked.