This discussion is archived
3 Replies Latest reply: Jan 4, 2013 11:04 AM by 903224 RSS

Quote Wrong Output

903224 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points