Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Suggestion: Feedback message for "if not exists" should not say "created"

Franck PachotApr 4 2023 — edited Apr 4 2023

The IF NOT EXISTS gives the “created” feedback message even when the object already exists :

sh-4.4$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue Apr 4 20:39:49 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> create table demo(answer) as select 42;
Table created.

SQL> create table if not exists demo(question) as select 42;
Table created.

SQL> desc demo
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ANSWER                                             NUMBER
SQL>

Then, from the log, it is impossible to know if the table was created by the statement or not.

For better developer experience, it should be preferable to have a warning like PostgreSQL does:

$ psql -p 5433 -d yugabyte
psql (14.5, server 11.2-YB-2.17.2.0-b0)
Type "help" for help.

yugabyte=# create table demo(answer) as select 42;
SELECT 1

yugabyte=# create table if not exists demo(question) as select 42;
NOTICE:  relation "demo" already exists, skipping
CREATE TABLE AS

yugabyte=#
This post has been answered by Gerald Venzl-Oracle on Apr 4 2023
Jump to Answer
Comments
Post Details
Added on Apr 4 2023
13 comments
818 views