5 Replies Latest reply: Feb 8, 2013 4:49 AM by odie_63 RSS

    need help  for XMLAGG

    788343
      For this below query I want single quotes are required for each kintana_number

      and when I increase value <=100 I get error like ORA-19011 how to modify above one get in single quotes and avoid 0ra-19011
      please help

      SELECT DISTINCT RTRIM
      (XMLAGG
      (XMLELEMENT (e, p.kintana_number || ',').EXTRACT
      ('//text()')
      ),
      ','
      )
      FROM project p where p.projectid <=100


      select
      listagg (kintana_number, ',')||''''
      WITHIN GROUP
      (ORDER BY kintana_number)
      FROM
      project where projectid<=100
      I want every kintana_number should be in singlequote after every comma separated

      Edited by: user13301356 on Feb 8, 2013 12:39 AM
        • 1. Re: need help  for XMLAGG
          AlbertoFaenza
          Hi,

          something like this?
          WITH project(kintana_number) AS
          (
             SELECT LEVEL
               FROM DUAL
            CONNECT BY LEVEL <= 10
          )
          SELECT LISTAGG (''''||kintana_number||'''', ',') 
                 WITHIN GROUP (ORDER BY kintana_number) txt
          FROM project;
          
          TXT                                                                             
          ----------------------------------------------
          '1','2','3','4','5','6','7','8','9','10'                                 
          >
          If it is not what you want, please post sample data (CREATE TABLE and INSERT statements) and your expected output.

          Please read SQL and PL/SQL FAQ

          Regards.
          Al

          Edited by: Alberto Faenza on Feb 8, 2013 10:43 AM
          Modified for single quotes
          • 2. Re: need help  for XMLAGG
            stefan nebesnak
            user13301356 wrote:
            and when I increase value <=100 I get error like ORA-19011 how to modify above one get in single quotes and avoid 0ra-19011
            please help

            SELECT DISTINCT RTRIM
            (XMLAGG
            (XMLELEMENT (e, p.kintana_number || ',').EXTRACT
            ('//text()')
            ),
            ','
            )
            FROM project p where p.projectid <=100
            To avoid ORA-19011 you need to add getClobVal() to your XMLType result, before the RTRIM.
            select DISTINCT RTRIM (xmlagg(xmlelement(e, p.kintana_number || chr(44))).extract('//text()').getclobval(), chr(44)) as val
            from project p
            where p.projectid <=100;
            • 3. Re: need help  for XMLAGG
              788343
              this is one but how to bring every value in single quote please do help
              • 4. Re: need help  for XMLAGG
                Karthick_Arp
                user13301356 wrote:
                this is one but how to bring every value in single quote please do help
                Try this
                select rtrim(replace(replace(XMLAGG(xmlelement(e,  p.kintana_number)).getclobval(), '<E>', ''''), '</E>', ''','), ',') clob_str   
                  from project p   
                 where p.projectid <=1000
                • 5. Re: need help  for XMLAGG
                  odie_63
                  SELECT dbms_xmlgen.convert(
                           rtrim(
                             extract(
                               xmlagg(
                                 xmlelement(e, ''''|| p.kintana_number || ''',')
                               )
                             , '//text()'
                             ).getclobval()
                           , ','
                           )
                         , 1
                         )
                  FROM project p ;
                  Or, on Oracle 11 :
                  SELECT rtrim(
                           xmlcast(
                             xmlagg(
                               xmlelement(e, ''''|| p.kintana_number || ''',')
                             ) 
                             as clob
                           )
                         , ','
                         )
                  FROM project p ;