I’m a HUGE fan of comments on objects in the database. The more the better. Everything is obvious while you are working on it, but later it can be hard to remember what you were doing and why you were doing it. Also I’m not going to be around forever and at some point in the future someone else is going to be looking at my tables, columns, views, operators, indexes and materialized views.

I’ve looked at objects that were created in the mid 1970s. What was in the mind of the developer way back then? I had no idea (no comments on those objects…).

To me the goal of a comment should be this:

We’ve just hired a new developer who doesn’t have much experience. She’s going to be tasked with creating a new application that uses one of my tables and in order to do so, I’m going to have to have a conversation with her about my table. That conversation should already be in a comment in the database.

For example…

Recently I created a very simple lookup table with two columns: Database and URL. Depending on which database our code was in, either development, user acceptance testing or production, a URL associated with a web service that our application needed to call would be looked up and then referenced in our code.

Since I use SQL Developer for development, I created the following comment in SQL Developer. Yes, I know it is long. But it is a lot shorter than the 4000 BYTE maximum!

COMMENT ON TABLE CANCEL_FRAMEWORK_URL  IS ‘CANCEL_FRAMEWORK_URL: This table is used to derive the correct web service URL used to invoke the Cancel Framework built by the Web Services team in each of the lifecycle environments: DEV, UAT or PROD.

Any questions about this table can be directed to Rich Soule at richard.soule@somedomain.com.

Created in February 2016 for the 360 Application.

Description:

Each database is given a name when it is created which can be found by the following query:

  SELECT SYS_CONTEXT(”userenv”,”db_name”) FROM DUAL;

The above query is a bit more flexible than using SELECT NAME FROM V$DATABASE; as a user may not have access to the V$ tables, but they will always have access to their context.

By knowing which database we are currently running in, the correct URL can be used in each environment. This will work through code promotion and environment cloning as long as the database names do not change. If they do (and really, they shouldn”t), then new rows can be added to this table. For example, let”s say we moved to a continuous integration development environment with nightly builds and 7 different development environments, then there could be 7 rows for development.

Currently we have the following values for each of the database names:

  dev – Development
  uat – Test
  prod – Production

Since the values for the endpoint URLs are known during the development process, they should all be entered into the DEV environment and then the DML associated with the rows is included as part of the code promotion process. This is slightly different than things have been done in the past, but we believe it to be more flexible and easier to maintain.’;

Everything worked just fine. I had a nicely formatted multi-line comment in the database.

I passed the code along to the build team so they could build in UAT and I got the following from them:

“Your code doesn’t work. SQL Plus doesn’t handle carriage returns.”

I knew that was wrong, so I did some testing and it turns out that there are two bugs in SQL Plus and one in SQLCL that prevent the above comment from working. While you can have carriage returns, you can’t create totally blank lines in either SQLCL or SQL*PLus. And in SQL*Plus you additionally can’t use a semicolon at the end of the line of a multi-line comment.

Details below:

SQL Developer works just fine:

With SQLCL we can have a semicolon at the end of a line (as we should be able to), but we can NOT enter an entirely blank line in a comment (even though we should be able to).

Notice what happens with the second statement. SQLCL interprets the blank line as “Let’s disregard everything the user was doing and just give them a new prompt.”

With SQL*Plus we can’t do either.

Notice that we first try a blank line and SQL*Plus mimics the behavior of SQLCL (really it’s probably the other way around…).

Notice in our second attempt we try to enter a multi-line comment with SQL*Plus that has a semicolon at the end of the line and SQL*Plus interprets this as “Even though you are in the middle of a quoted literal (string) I’m still going to interpret your semicolon as the end of the statement you are typing because it’s at the end of the line so that must be what you meant so I’m going to tell you that you didn’t finish your string.” Which is a bug.

Development just logged this bug against SQL*Plus: Bug 22887839 : “COMMENT ON” WITH MULTI-LINE COMMENTS FAILS IN SQL*PLUS; WORKS IN OTHER TOOLS. There was a similar bug just about the semicolons logged in 2009, but the SQL*Plus development team labeled it ‘Not a bug’ and turned it into an enhancement request: Bug 7829732 : THE ABILITY TO ESCAPE OR ENTER SEMICOLONS IN COMMENTS.

Note: I spent 5.5 years in Oracle development and getting bugs labeled ‘Not a bug’ by developers was one of my biggest pet peeves.

I believe it’s a bug because the database can certainly hold a multi-line comment with semicolons at the end of lines and blank lines in it. The tools that we use should be able to put things into the database that the database can hold. And some of them do this just fine… Thus it’s a bug. Not an enhancement request. But that’s how I think.

What do you think?

UPDATE: So I’m here at ODTUG KSCOPE16 and Dietmar Aust just showed me that there is a SQL*Plus and SQLCL option: set sqlblanklines on. This means that we are down to a single issue with SQL*Plus: You can’t have a line end in a semicolon.