3

I am looking for any reference documentation or trusted guide to determine the name of a select statement column after the result is returned.

Let me explain through some examples-

Example 1 -

SELECT C.FIRST_NAME
FROM CLIENT C;

The output column name here is -

FIRST_NAME

Example 2 -

SELECT C.FIRST_NAME AS "First Name"
FROM CLIENT C;

The output column name here is -

First Name

Example 3 -

SELECT COUNT(C.FIRST_NAME)
FROM CLIENT C;

The output column name here is -

COUNT(C.FIRST_NAME)

From above, we can see that a column name can be either an alias, the name of the selected table's column or even the whole statement itself.

My question is, is there any defined guideline / rule provided by Oracle to determine what will be the output column name?

sahossaini
  • 474
  • 1
  • 5
  • 11
  • 1
    It is whatever you select, unless you give it a name then it's that name. – Rene Feb 13 '20 at 11:20
  • 2
    @Rene Things get more complicated when there are expressions. Oracle appears to remove spaces and uppercase everything. And things get even weirder if the expression is over 30 or 128 characters long (the limits in pre-12c and 12c+). For example: `select "COUNT(1+1)||'ASDF'" from (select count(1 + 1) || 'asdf' from dual);` It does seem like Oracle ought to have this documented somewhere. – Jon Heller Feb 13 '20 at 23:43
  • I guess everybody uses aliases when they need to get a result by name and don't see this as an issue. – Rene Feb 17 '20 at 07:11

3 Answers3

4

Oracle seems to have an algorithm that involves removing spaces and capitalizing things. However, the resulting column alias that it comes up with for a projection does not seem to obey the regular rules for identifiers that you and I are subject to. For that reason, I do not think it is going to be valuable for you to try to duplicate Oracle's internal algorithm for whatever you are trying to accomplish -- because even if you do that perfectly, you are not guaranteed to end up with an identifier you can use.

Take this example:

SELECT * FROM
(
SELECT OWNER, COUNT(distinct object_name || 'This is a really long string in my expression, don''t you think?  Actually, it''s really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, ridiculously long!')
FROM   DBA_OBJECTS
WHERE  ROWNUM <= 100
GROUP BY OWNER )
ORDER BY 2 DESC;

... for readers who didn't bother scrolling right, the expression starting with COUNT(distinct object_name... is really, really long.

Let's run that and see what Oracle is using for the projection of the inner query.

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| OWNER | COUNT(DISTINCTOBJECT_NAME||'THISISAREALLYLONGSTRINGINMYEXPRESSION,DON''TYOUTHINK?ACTUALLY,IT''SREALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SYS   |                                                                                                                                                                                                                                                           100 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

We'll use DBMS_XPLAN to look at the projection information.

SELECT *
FROM   TABLE (DBMS_XPLAN.display_cursor (null, null,
                                         'ADVANCED LAST'));

Column Projection Information (identified by operation id):

 1 - (#keys=1) INTERNAL_FUNCTION("from$_subquery$_001"."COUNT(DISTINCTOBJECT_NAME||'THISISAREALLYLONGS
   TRINGINMYEXPRESSION,DON''TYOUTHINK?ACTUALLY,IT''SREALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY
   ,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALL
   Y,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REAL
   LY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REA
   LLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,RE
   ALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,R
   IDICULOUSLYLONG!')")[22], "from$_subquery$_001"."OWNER"[VARCHAR2,128]

That's a 688 character identifier is using internally. Good luck making use of that one!

More proof that Oracle is really using all 688 characters internally for the projection ...

I've taken the example query above and duplicated the really, (really!) long expression. Then, I stuck a FETCH FIRST 1 ROW ONLY. As is well known in Oracle 12c, because of the way Oracle processes the FETCH clause, it won't work if there are duplicate column aliases in the query.

SELECT OWNER, 
       COUNT(distinct object_name || 'This is a really long string in my expression, don''t you think?  Actually, it''s really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, ridiculously long!'),
       COUNT(distinct object_name || 'This is a really long string in my expression, don''t you think?  Actually, it''s really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, ridiculously long!') FROM   DBA_OBJECTS WHERE  ROWNUM <= 100 GROUP BY OWNER FETCH FIRST 1 ROW ONLY;

ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"
*Cause:    
*Action:

Now, repeat but change the very last character in the expression (change "!" to "?") and you will see it runs fine.

Again, the column alias Oracle generates through its internal, undocumented logic does not obey the standard rules for identifiers. That, in my book, makes them unusable, even if you reverse engineered the logic for determining them.

Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
  • 1
    [`DBMS_SQL` truncates it to 251 chars](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=b8b4017a7438bc3cbafdab2a10c61ec4), despite [`col_name` being `varchar2(32767)`](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SQL.html#GUID-DA040983-27ED-4321-87A1-6FA0C45116B9). At least in 12c and 18c. (BTW I make the projection ID 688 not 742; I think you counted the newline and tab used for wrapping?) – Alex Poole Feb 21 '20 at 19:07
  • Thanks, @AlexPoole! And you're right on the length of the string. I'll update that, not that it changes the point. – Matthew McPeak Feb 22 '20 at 01:40
  • @AlexPoole FYI, since you showed interest, I added some more evidence that Oracle really does use all 688 characters internally in its projections. – Matthew McPeak Feb 26 '20 at 18:58
  • Actually... [that doesn't seem to error in 18c](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=350436b92c12326f1a8b99b57fe5a569). It does in 12cR1; interestingly, if you change anything up to the 251st char it works in 12cR1 too, but if you change anything from char 252 it still errors - so there it seems to be using a truncated version somewhere along the way. All of which just reinforces your main point, that these are unusable. (I was already convinced by the original post - and already +1). – Alex Poole Feb 26 '20 at 19:16
  • BTW feel free to include the `dbms_sql` stuff in your answer if it's useful, in case the comment disappears. – Alex Poole Feb 26 '20 at 19:17
  • 1
    Even more curiously a similar construct behaves consistently in 12c, [11g](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=3ad383a328b3da2d200ba3c595de10a3) and [18c](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=2d78a5e3115f812bb0c0f27ee216e637). Those (and the previous 18c link) also show the column heading name truncated to 251. There's something else going on though; that's using JDBC (I see the same in SQL Developer and SQLcl), but running the same query in SQL\*Plus (OCI) has a heading with 399 chars, so the driver makes a difference too? – Alex Poole Feb 26 '20 at 19:34
3

I think your confusion is the first case:

SELECT C.FIRST_NAME

Why is this FIRST_NAME and not "C.FIRST_NAME"?

I think the reasoning is that there are three cases. You have the second two accurately represented: user-assigned aliases and expressions (even subqueries) without aliases.

When selecting a simple column, though, the reference to a column in a table (or view or subquery). Oracle decides that the column name is sufficient, as opposed to the qualified column name. So this returns two columns with the same aliases:

SELECT C.FIRST_NAME, FIRST_NAME

If the column is involved in an expression -- other than the trivial use of parentheses -- then the qualified column name is kept.

I should note: This explanation is based on observation. I don't know if this is documented, perhaps someone else knows a reference that provides an official explanation.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The client software determines the default column headers; SQL*Plus has very short documentation on the topic. https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SP33/ch4.htm#column1 – kfinity Feb 13 '20 at 14:47
  • 3
    @kfinity - that's the for display headers, and it defaults to the column name/alias returned in the query metadata - it isn't looking at the query itself at that point. (Wouldn't work for a ref cursor from a function call, for instance). You can see the derived names, e.g. for the third example, through `dbms_sql` - before it gets to a client, and they exist in subqueries too - [which can lead to ORA-00918](https://stackoverflow.com/q/60109730/266304). – Alex Poole Feb 13 '20 at 14:54
1

Here's a good guideline, not official, but covers the most important topics.

https://www.oracletutorial.com/oracle-basics/oracle-alias/

Now, personally I will divide the way I get the columns in 2: aliases or expressions, so if you want to have something readable use aliases, they can be enclosed in double quotes or not you don't even have to use "as". if you don't use aliases, Oracle as default returns you expressions, wether if you leave the column name or have a function.

I hope this helps you.

Diego R
  • 19
  • 3