Oracle: update table with object type udt

datatypesoracleoracle-11g-r2plsqlupdate

I have this Oracle code:

Create type address_ty as object( street varchar2(50),city varchar2(50),prov varchar2(50),cap number);

Create type person_ty as object( name varchar2(50), address address_ty);

Create table client (client_id number,person person_ty);

insert into client
values(1,person_ty('pinco pallo',
         address_ty('Boulevard street' ,'torino','to',10100)));
Commit;

select c.client_id,
     c.person.name,
     c.person.address.street,
     c.person.address.city,
     c.person.address.prov,
     c.person.address.cap
from client c;

Everything works good.

Now i want to update c.person.address.prov

I tried with:

update client
set client.person.address.prov='MI'
where client.client_id=1

But i get an error.

Ora-00904: "client.person.address.prov" invalid identifier.

How can i update the field prov??

Thank you for your answer.

Best Answer

Use an alias. It is mandatory when updating a table with an object type. UPDATE

t_alias

Specify a correlation name (alias) for the table, view, or subquery to be referenced elsewhere in the statement. This alias is required if the DML_table_expression_clause references any object type attributes or object type methods.

Create table client (client_id number,person person_ty);

insert into client 
values(1,person_ty('pinco pallo',
         address_ty('Boulevard street' ,'torino','to',10100)));
Commit;

...

update client c
set c.person.address.prov='MI'
where c.client_id=1;

1 row updated.
Related Question