What is the difference in select with alias, and without- oracle 11g

Question

I have a table, let's name it table1.

The table consists of many columns, and one of the columns is an object, which consist of 3 subcolumns, let's call them value1, value2, value3.

Let's say that object type is named object_type1.

I prepared a projection query which looks like this:

Select typed_column.value1 
from table1

This projection won't work on Oracle 11g, it says 'invalid identifier'.

So I tried to add name of a table to selected column:

Select table1.typed_column.value1 
from table1

It also didn't work.

But when I use an alias:

Select tab1.typed_column.value1 
from table1 tab1

It's working.

I also found out an another solution which works aswell (with use of treat function):

Select treat(typed_column as object_type1).value1 
from table1

My question is: what does the alias do, that the database actually knows how to map an object typed column?

Why did my first two queries not work propely?

I prepared a table and type, DDL of table looks like:

CREATE TABLE table1 ( --....lot of other columns before typed_column OBJECT_TYPE_1 )

and the DDL of object:

CREATE OR REPLACE TYPE "MY_SCHEMA"."OBJECT_TYPE_1" is object ( value1 varchar2(100), value2 date, value3 date )

Answer 1

You have to because that's what the docs say ;)

2.1.8.1 When Table Aliases Are Required

Table aliases can be required to avoid problems resolving references.

Oracle Database requires you to use a table alias to qualify any dot-notational reference to subprograms or attributes of objects, to avoid inner capture and similar problems resolving references.

OK, so why does this rule exist?

Consider this scenario:

create type emp_obj as object (
  employee_id   integer,
  employee_name varchar2(100),
  department_id integer
);
/

create table departments (
  department_id integer,
  manager       emp_obj
);
/

create table manager (
  employee_id   integer,
  employee_name varchar2(100),
  department_id integer
);

select * from departments d
where  exists ( 
  select * from manager
  where  manager.department_id = d.department_id --which manager.department_id?
);

In this example

• The departments table has a manager object column with a department_id attribute
• The manager table has a column department_id

So what does where manager.department_id resolve to?!

In the absence of aliases, it could be either table. When you store objects it's possible to have:

<table_name>.<column_name> identical to <object_column_name>.<attribute_name> from another table in the same query!

When you start adding/removing columns from a table or attributes from a type this creates name resolution... surprises.

So to avoid this Oracle Database enforces that you must use aliases.

Just like how you need to use aliases when you use the same table twice in a query:

create table t (
  c1 int, c2 int
);

select * from t, t
where  c1 = 1;

ORA-00918: column ambiguously defined

select * from t t1, t t2
where  t1.c1 = 1;

no rows selected

---

Note there is a refinement to the rule:

Use of a table alias is optional when referencing top-level attributes of an object table directly, without using the dot notation. For example, the following statements define two tables that contain the person_typ object type. person_obj_table is an object table for objects of type person_typ, and contacts is a relational table that contains a column of the object person_typ.

When you create an object table, the attributes are the columns. So the ambiguities above go away.