SQLcl: Release 23.3 Production on Tue Dec 19 12:11:59 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
-- OK
SQL> SELECT
2 LISTAGG(dummy, ',') WITHIN GROUP(ORDER BY 1) a,
3 LISTAGG(dummy, ',') WITHIN GROUP(ORDER BY 1) b
4 FROM
5* dual;
A B
____ ____
X X
-- OK
SQL> create or replace view v1 as
2 SELECT
3 LISTAGG(dummy, ',') WITHIN GROUP(ORDER BY 1) a,
4 LISTAGG(dummy, ',') WITHIN GROUP(ORDER BY 1) b
5 FROM
6* dual;
View V1 created.
-- OK with all columns
SQL> select * from v1;
A B
____ ____
X X
-- Error with one column
SQL> select a from v1;
Error starting at line : 1 in command -
select a from v1
Error at Command Line : 1 Column : 15
Error report -
SQL Error: ORA-00924: missing BY keyword
00924. 00000 - "missing BY keyword"
*Cause: The keyword BY was omitted in a GROUP BY, ORDER BY, or
CONNECT BY clause. In a GRANT statement, the keyword
IDENTIFIED must also be followed by the keyword BY.
*Action: Correct the syntax. Insert the keyword BY where
required and then retry the statement.
More Details :
https://docs.oracle.com/error-help/db/ora-00924/
-- Workaround
-- The same select , modified(added WITH clause) - OK
SQL> create or replace view v2 as
2 with c as (
3 SELECT
4 LISTAGG(dummy, ',') WITHIN GROUP(ORDER BY 1) a,
5 LISTAGG(dummy, ',') WITHIN GROUP(ORDER BY 1) b
6 FROM
7 dual)
8* select * from c;
View V2 created.
-- OK with all columns
SQL> select * from v2;
A B
____ ____
X X
-- OK with one column
SQL> select a from v2;
A
____
X