3 Replies Latest reply: Jan 4, 2013 1:04 PM by 903224 RSS

    Quote Wrong Output

    903224
      i was trying to get expected output but tried in many ways ,,,can anyone help to escape &apos and quote
      
      CREATE TABLE ESC
      (
        C1  NUMBER(10),
        C2  NUMBER(20)
      )
      
      WITH t AS
           (SELECT 'MAX(LENGTH(' || column_name || '))||||' column_name
              FROM all_tab_cols
             WHERE UPPER (table_name) = 'ESC'
                   ORDER BY 1)
      SELECT RTRIM
                (XMLAGG (XMLELEMENT (t, column_name || '')).EXTRACT ('//text()'),
                 ','
                ) column_name
        FROM t
        
        Output:
        MAX(LENGTH(C1))||||MAX(LENGTH(C2))||||
        
        Expected Output:
        MAX(LENGTH(C1))||','||MAX(LENGTH(C2))
        • 1. Re: Quote Wrong Output
          Frank Kulash
          Hi,

          The 2nd argument to RTRIM tells what character(s) you want removed. You're only removing commas, not pipes.
          Try this:
          ...
          SELECT RTRIM ( XMLAGG ( XMLELEMENT (t, column_name || '')
                                        ).EXTRACT ('//text()')
                       , '|'  -- to be removed from the end
                       ) column_name
            FROM t;
          • 2. Re: Quote Wrong Output
            903224
            No this is not the answer i am expecting ,

            I need to escape qoutes from the string ,final output should contatin C1||','||C2

            tried with below sql but getting MAX(LENGTH(C1))||,||MAX(LENGTH(C2))|||| this will not execute without enclosing comma(,) in quotes so tried with '','' then got

            MAX(LENGTH(C1))||&apos,&apos||MAX(LENGTH(C2))|||| is it possible to get out put in MAX(LENGTH(C1))||','||MAX(LENGTH(C2))|||| format ?
            WITH t AS
                 (SELECT 'MAX(LENGTH(' || column_name || '))||','||' column_name
                    FROM all_tab_cols
                   WHERE UPPER (table_name) = 'ESC'
                         ORDER BY 1)
            SELECT RTRIM
                      (XMLAGG (XMLELEMENT (t, column_name || '')).EXTRACT ('//text()'),
                       ','
                      ) column_name
              FROM t
            • 3. Re: Quote Wrong Output
              Frank Kulash
              Hi,

              You're explicity adding 4 pipe characters
              ||||
              after every item. If you want to enter soimething else, such as
              ||','||
              after every item, then concatenate that instead, and change RTRIM to remove all those characters from the very end of the final output.
              The XML functions will covert single-quotes to ' . If possible, use some other character in place of single-quotes, and replace that character with single-quote only after all the XML stuff is finished. For example, if you're sure the data will never include a grave accent ( ` ), then you can do this:
              WITH t AS
                   (SELECT 'MAX(LENGTH(' || column_name || '))||`,`||'     -- CHANGED HERE
                                                            column_name
                      FROM all_tab_cols
                     WHERE UPPER (table_name) = 'ESC'
                           ORDER BY 1)
              SELECT RTRIM ( REPLACE ( XMLAGG ( XMLELEMENT (t, column_name || '')
                                                       ).EXTRACT ('//text()')
                               , '`'
                               , ''''
                               )
                           , ',|'''       -- include all characters used in delimiter 
                           ) column_name
                FROM t
              ;
              You might find it simpler to use some other methiod to do your string aggregation. LISTAGG, for example, doesn't add a delimiter after the last item.
              SELECT     LISTAGG ( 'MAX(LENGTH' || column_name
                                      || '))'
                        , Q'{||','||}'
                        ) WITHIN GROUP (ORDER BY column_name)     AS column_name_list
              FROM    user_tab_cols
              WHERE     table_name     = 'ESC'
              ;
              Edited by: Frank Kulash on Jan 4, 2013 2:10 PM
              Added query using LISTAGG (Oracle 11.2)