Q
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.

This was last published in February 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close