Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

bug on free database 23c when use a view with two LISTAGG columns

pntDec 19 2023 — edited Dec 19 2023
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
This post has been answered by gsalem-Oracle on Dec 20 2023
Jump to Answer
Comments
Post Details
Added on Dec 19 2023
4 comments
317 views