Home » SQL & PL/SQL » SQL & PL/SQL » Return ColumnNames in lowercase. (Oracle 11gR2)
Return ColumnNames in lowercase. [message #526600] Tue, 11 October 2011 17:18 Go to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
Hi,

I wonder if there is any way to return the columns of an select with its letters lowercase?

I have a piece of code that creates an script wich returns an SQL result to be confronted with some templates.
My template have the columnnames in lowercase and because It is case sensitive the Uppercase returned by Oracle, it fails me.

Is there any way?

Thanks!
Re: Return ColumnNames in lowercase. [message #526601 is a reply to message #526600] Tue, 11 October 2011 17:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311

>My template have the columnnames in lowercase and because It is case sensitive the Uppercase returned by Oracle, it fails me.

my car fails me
how to make my car go?
Re: Return ColumnNames in lowercase. [message #526603 is a reply to message #526601] Tue, 11 October 2011 17:34 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
Ok, ok ... I got it. There is no need to be so sarcastic! Smile

I'm using XSLT and within my transformations (Case sensitive) it happens what I've mentionated.

select name, id from person;

It returns:
NAME  |   ID
Paul  |    1
Marie |    2
Brian |    3

I need that it returns the column name in lowercase, like this:
name  |   id
Paul  |    1
Marie |    2
Brian |    3


I want my dataset to return the column names in lower cases ...

(Sorry for not being more specific, but I am at home currently)

Thanks!
Re: Return ColumnNames in lowercase. [message #526611 is a reply to message #526603] Tue, 11 October 2011 20:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
You can use quotes around a columnn alias to preserve the case, like so:

select name as "name", id as "id" from person;
Re: Return ColumnNames in lowercase. [message #526649 is a reply to message #526611] Wed, 12 October 2011 04:03 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
First of all, thanks for your answer and help.

I was trying to find some kind of solution within system config, Oracle Parameters or something alike. I was hoping there was an solution to work around this without altering all my SQLs.

Maybe I was hoping too much .. Smile

But, this solution of yours is the one. It gives me some work to do, but it is indeed the answer.

Thanks.

Re: Return ColumnNames in lowercase. [message #526732 is a reply to message #526649] Wed, 12 October 2011 14:01 Go to previous message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
When you create the table, or rename a column, you can preserve the case by enclosing it within double quotation marks. However, this is generally regarded as a bad practice, since it will require you to use the double quotes any time that you refer to the column. Referring to the column without the quotes will raise an error. Please see the demonstration below.

SCOTT@orcl_11gR2> create table person
  2    ("name"	varchar2(15),
  3  	"id"	number)
  4  /

Table created.

SCOTT@orcl_11gR2> insert into person ("name", "id")
  2  values ('John Doe', 1)
  3  /

1 row created.

SCOTT@orcl_11gR2> select * from person
  2  /

name                    id
--------------- ----------
John Doe                 1

1 row selected.

SCOTT@orcl_11gR2> select "name", "id" from person
  2  /

name                    id
--------------- ----------
John Doe                 1

1 row selected.

SCOTT@orcl_11gR2> select name from person
  2  /
select name from person
       *
ERROR at line 1:
ORA-00904: "NAME": invalid identifier


SCOTT@orcl_11gR2> select id from person
  2  /
select id from person
       *
ERROR at line 1:
ORA-00904: "ID": invalid identifier


SCOTT@orcl_11gR2> alter table person rename column "name" to "Name"
  2  /

Table altered.

SCOTT@orcl_11gR2> select * from person
  2  /

Name                    id
--------------- ----------
John Doe                 1

1 row selected.

SCOTT@orcl_11gR2>


Previous Topic: Same object in different schema (2 Merged)
Next Topic: ORA-00904: "DATEADD": invalid identifier
Goto Forum:
  


Current Time: Sat Aug 02 06:43:33 CDT 2025