Skip to Main Content
Forums

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

Gerald Venzl-Oracle
Answer

Thanks a lot for your feedback!

This behavior is part of the design of the new IF [NOT] EXISTS functionality.

The goal of this behavior modifier is to ignore the existence of the underlying object and report the same output back to the invoker. This is particularly useful for regression test suites that rely on log file "diff" comparison mechanisms where a difference in output than the expected one would flag a potential regression to be looked at. For such environments, providing a different output for IF [NOT] EXISTS behavior modifier depending on the object's existence would defeat the purpose and still cause false positives.

Note that applications will never see a "Table created." statement or similar. This additional feedback statement is added by the command line itself, not the database.

A question may arise what end-users should do, who want to know whether the operation resulted in a successful, in this case, creation of the object or whether it was ignored because there is already an object with the same name in place? In such scenarios, end users should not use the optional IF [NOT] EXISTS clause. By not using this clause, the database will inform users of an already existing object via an error message, for example:

SQLcl: Release 23.1 Production on Tue Apr 04 14:20:55 2023

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Tue Apr 04 2023 14:20:57 -07:00

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 DEMO created.

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

Table DEMO created.

SQL> create table demo(question) as select 42;

Error starting at line : 1 in command -
create table demo(question) as select 42
Error report -
ORA-00955: name is already used by an existing object
00955. 00000 -  "name is already used by an existing object"
*Cause:
*Action:
Marked as Answer by Gerald Venzl-Oracle · Apr 4 2023
1 person found this helpful
L. Fernigrini

Just an additional comment, using SQL Developer 22.2.1.234 (latest version), the message shows “IF” rather than the actual object name.

1 person found this helpful
Loïc Lefèvre-Oracle

Hello @l-fernigrini, this is a known bug indeed and it will be fixed very soon. Thanks a lot!

2 people found this helpful
jgebal

I would then ask for a change. If ghe idea is to keep message consistent, than please make it vague enough so it us not confusing.

Instead of: "Table created", use “Create table statement processed” or somethin equally vague so that the message is not untrue.

I've started a discussion on twitter about it before checking on the forum and Pat Barel was kind enough to disagree.

https://twitter.com/patch72/status/1647494364136972293?t=RTwp-mUMngvJlrpMXI3yRw&s=19

https://twitter.com/patch72/status/1647612796153020419?t=v3TbUF_n5XAHBu1pechVMQ&s=19

1 person found this helpful
Philipp Salvisberg

Instead of: "Table created", use “Create table statement processed” or somethin equally vague so that the message is not untrue.

This is a good idea. The clients SQL*Plus, SQLcl and SQLDev should not have a problem to implement that.

Gerald Venzl-Oracle

Changing the CLI output message would equally cause many false positives for said log file “diff” comparison regression test suites now being dependent on which version of the tool they are using. It would also disrupt every existing test suite, including those that never want to use IF [NOT] EXISTS.

User_3ABCE

It would also disrupt every existing test suite, including those that never want to use IF [NOT] EXISTS.

The message should change only if there is an IF. The previous scripts will not be affected by the change.

Since the "old" sqldeveloper 22 issues "table IF created", but sqlcl 23 issues the correct table name, the work on determining IF has already been done.

Gerald Venzl-Oracle

As stated in the original answer:

The goal of this behavior modifier is to ignore the existence of the underlying object and report the same output back to the invoker. This is particularly useful for regression test suites that rely on log file "diff" comparison mechanisms where a difference in output than the expected one would flag a potential regression to be looked at. For such environments, providing a different output for IF [NOT] EXISTS behavior modifier depending on the object's existence would defeat the purpose and still cause false positives.

User_3ABCE

… depending on the object's existence …

As stated in the other answers, the message "Create table statement processed" does not depend on the object existence.

Philipp Salvisberg

Thank you, @gerald-venzl-oracle . I can understand this reasoning. When we see a log message such as

  • Table created. (SQL*Plus)
  • Table DEMO created. (SQLcl)
  • Table dropped. (SQL*Plus)
  • Table DEMO dropped. (SQLcl)
  • etc.

we should read it as Statement completed successfully. and not assume more.

However, I still think that @franck-pachot1 has a point and @jgebal as well. As a developer, I like precise feedback. Similar to precise error messages. It makes life easier. That's why I like the output of SQLcl better.

In other areas, when introducing new functionality that could break compatibility, the old behaviour is kept as default and the new behaviour can be enabled via a database parameter (e.g. group_by_position_enabled). SQL*Plus and SQLcl have a similar parameter concept (e.g. set echo). So, IMO it would be possible to improve the accuracy of the feedback messages without compromising the compatibility with "regression test suites that rely on log file "diff" comparison mechanisms".

1 person found this helpful
Gerald Venzl-Oracle

we should read it as Statement completed successfully. and not assume more

Users should read it as The table is there and The table is gone. The fact that the user didn't care about whether the table was there or gone before the statement was expressed by IF [NOT] EXISTS as part of the statement.

SQL*Plus and SQLcl have a similar parameter concept (e.g. set echo).

Perhaps, but that's more a question for @thatjeffsmith-oracle to comment on.

2 people found this helpful
jgebal

How about issuing a warning when command was skipped? Other DBs implementing this feature seem to have it.

The nice thing is that it could be added at a later release and the main message doesn't need to change. It woulbe a win-win I suppose.

https://dev.to/aws-heroes/drop-if-exists-create-if-not-exists-in-oracle-mysql-mariadb-postgresql-yugabytedb-pb1

https://twitter.com/GebalJacek/status/1647836600666058755?t=hLNb7Us2iLotXPkq6G_4-g&s=19

Anthony Harper

This bug applies to all create if exists statements and drop if exists statements. Hopefully they are all being addressed.

Regards, Anthony Harper

prompt Feedback is incorrect when using exists for object create and drop
create table if not exists test_23c_exists_table(id number);
create procedure if not exists test_23c_exists_procedure 
is begin null; end test_23c_exists_procedure;
/
create function if not exists test_23c_exists_function return boolean
is begin return true; end test_23c_exists_function;
/
drop table if exists test_23c_exists_table;
drop procedure if exists test_23c_exists_procedure;
drop function if exists test_23c_exists_function;
/* script output:
Table IF created.
Procedure IF compiled
Function IF compiled
Table IF dropped.
Procedure IF dropped.
Function IF dropped.
*/
1 - 13

Post Details

Added on Apr 4 2023
13 comments
823 views
5 mentions