Home » SQL & PL/SQL » SQL & PL/SQL » Facing issie in 19c
Facing issie in 19c [message #685607] Thu, 17 February 2022 01:46 Go to next message
nishant87
Messages: 48
Registered: September 2013
Location: india
Member
i am facing facing with clause after 19c upgrade
 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;

I don't know how to handle that?


--moderator update: added [code] tags, please do so yourself in future.

[Updated on: Thu, 17 February 2022 02:02] by Moderator

Report message to a moderator

Re: Facing issie in 19c [message #685608 is a reply to message #685607] Thu, 17 February 2022 02:05 Go to previous messageGo to next message
John Watson
Messages: 8719
Registered: January 2010
Location: Global Village
Senior Member
You didn't say what your "issue" is, which makes it a bit hard to assist. However, you have an obvious syntax error: the INTO in your first CTE is wrong.
Re: Facing issue in 19c [message #685610 is a reply to message #685607] Thu, 17 February 2022 04:10 Go to previous messageGo to next message
nishant87
Messages: 48
Registered: September 2013
Location: india
Member
After 19c upgrade this part of sql not get compiled and throw error because of v_chr_sernumdata.
Re: Facing issue in 19c [message #685611 is a reply to message #685610] Thu, 17 February 2022 04:21 Go to previous messageGo to next message
John Watson
Messages: 8719
Registered: January 2010
Location: Global Village
Senior Member
The error "not get compiled and throw error" is a new one to me. Most Oracle errors are of the form ORA-99999.

Apart from that, I've already told you that you can't use INTO in a CTE. You need to fix your syntax before going any further.
Re: Facing issue in 19c [message #685612 is a reply to message #685611] Thu, 17 February 2022 04:50 Go to previous messageGo to next message
piripicchio
Messages: 18
Registered: April 2018
Location: Rome
Junior Member
It's not clear if this sql is part of a compiled object which fails compilation or you're simply running it as is (in this case, you've already been answered).

[Updated on: Thu, 17 February 2022 04:50]

Report message to a moderator

Re: Facing issue in 19c [message #685613 is a reply to message #685612] Thu, 17 February 2022 08:16 Go to previous message
EdStevens
Messages: 1375
Registered: September 2013
Senior Member
Also asked on OTN
Previous Topic: Putting a row with a particular value on top of a sort
Next Topic: SQL -Report missing string when running a query
Goto Forum:
  


Current Time: Sat Jul 02 20:59:20 CDT 2022