Problem solve Get help with specific problems with your technologies, process and projects.

"Select star" with column reformatting in Oracle

A comment on your "select star" article: If you are using Oracle, the obvious solution is to use alter session. Then all date columns will be displayed in this format.

Comment to your article "Select star" with column reformatting. If you are using Oracle, the obvious solution is to enter:

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'

Then all date columns will be displayed in this format.

We received a ton of responses like the above. Apparently, this solution was obvious to many people, but not me. <sigh />

I guess part of the problem is that I don't work with Oracle on a day-to-day basis. I don't even have access to an Oracle system to test my queries. And before we get additional responses telling me to download some 800-megabyte install file, let me just say that for the time being I am not prepared to do that, sorry.

So that leaves me two choices. I could simply choose not to answer any questions that deal with Oracle. In the original question, the use of the TO_CHAR function was an obvious giveaway. So scenarios in which I try to apply good old ordinary cross-platform basic SQL, for which there happens to be a spiffier Oracle solution, will never see the light of day.

Alternatively, I could do my best and just take my lumps when there's a much more elegant solution that's obvious to you Oracle folks but not to me.

Which do you think is the best strategy?

By the way, several good comments and follow-up questions were also submitted. One person asked "I'm wondering about the AS part of the SELECT statement. Can C be used as a column alias as well as a column name?" This was in reference to the snippet:

to_char(C,"YYYY-MM-DD HH24:MI") as C

To tell you the truth, I don't know. Some databases will let you, some won't. As I said, I don't have Oracle to test on.

Another person commented: "Be aware that [alter session set nls_date_format] is a DDL command that issues a commit."

Finally, one person commented on the "select star" aspect of the question as follows:

In an application the best option is to list the column names. However, it initially sounded to me like the person asking the question was typing the command, maybe in SQL*Plus. In that case, "select *" is often used because typing even a subset of the table column names can be TOO tedious.

I totally agree with that, and I use "select star" when writing quick and dirty queries all the time, too.

So thanks for all the responses, folks. It's quite heartwarming to realize that there are many people out there reading this column. Let's hope I don't mess up too often, eh.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.