Forum Stats

  • 3,836,993 Users
  • 2,262,218 Discussions
  • 7,900,174 Comments

Discussions

facing issue with clause after 19c upgrade

User_G2K3P
User_G2K3P Member Posts: 25 Blue Ribbon
edited Feb 17, 2022 7:54AM in SQL & PL/SQL

i am facing issue with below SQL .Into v_chr_sernumdata is causing issue


 WITH t

                                    AS (SELECT v_chr_sernum

                                           AS in_cls

                                      INTO v_chr_sernumdata

                                       FROM DUAL),

                                    t1

                                    AS (  SELECT DISTINCT

                                           REGEXP_SUBSTR (

                                             in_cls,

                                             '[^,]+',

                                             1,

                                             ROWNUM)

                                             names

                                         FROM t

                                      CONNECT BY ROWNUM <=

                                             LENGTH (

                                              REGEXP_REPLACE (

                                                in_cls,

                                                '[^,]'))

                                             + 1)

                                 SELECT RTRIM (

                                      XMLAGG (

                                        XMLELEMENT (

                                         a,

                                         names

                                         || ',').EXTRACT (

                                         '//text()')),

                                      ',')

                                      v_chr_sernumdata

                                  FROM t1;

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,453 Gold Trophy

    Sorry, but I've never seen an into in a common-table expression. And why do you need that into there?

    It could be like the below:


     WITH t

                                        AS (SELECT v_chr_sernum

                                               AS in_cls

                                         

                                           FROM DUAL),

                                        t1

                                        AS (  SELECT DISTINCT

                                               REGEXP_SUBSTR (

                                                 in_cls,

                                                 '[^,]+',

                                                 1,

                                                 ROWNUM)

                                                 names

                                             FROM t

                                          CONNECT BY ROWNUM <=

                                                 LENGTH (

                                                  REGEXP_REPLACE (

                                                    in_cls,

                                                    '[^,]'))

                                                 + 1)

                                     SELECT RTRIM (

                                          XMLAGG (

                                            XMLELEMENT (

                                             a,

                                             names

                                             || ',').EXTRACT (

                                             '//text()')),

                                          ',')

                                          v_chr_sernumdata

     INTO v_chr_sernumdata

                                      FROM t1;

    Jonathan Lewis
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,210 Red Diamond

    Hi, @User_G2K3P

    Whenever you have a question, please post a complete test script so the people who want to help you can re-create the problem and test their ideas. In this case, include a complete PL/SQL block and the exact results you want. Explain why you want those results. Always post your complete Oracle version (e.g. 18.4.0.0.0).

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown