3

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.

2

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.
  • Thank you. I couldn't imagine it was for this reason. One simple alias and nothing works... Absurd! :)) – andrea Jul 21 '17 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.