I have a table, let’s name it
The table consists of many columns, and one of the columns is an object, which consist of 3 subcolumns, let’s call them
Let’s say that object type is named
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
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
and the DDL of object:
CREATE OR REPLACE TYPE "MY_SCHEMA"."OBJECT_TYPE_1" is object
You have to because that’s what the docs say 😉
126.96.36.199 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
departmentstable has a
managerobject column with a
managertable has a column
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.