facing issue with clause after 19c upgrade

User_G2K3P

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;

BEDE

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;

Frank Kulash

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

Also asked at OraFAQ