CODEWITHSUNDEEP
sqloracle11guser-defined-types
BHA Bilel
BHA Bilel

Reputation: 371

How to manipulate an attribute of an object in sql (oracle)?

Supposing i have this hierarchy :

create or replace type tperson as object(
    fname varchar2(20),
    lname tprenom,
    adress tadr,
    phone_num varchar2(10),
    email varchar2(50)
)not final;

create or replace type tuser under tperson(
    username varchar2(20),
    password varchar2(20)
);

create table agent(
id_ag int,
infos tuser not null
);

insert into agent values(1,tuser('name',tprenom('bilel','dani','lastname3')  
,tadr(3,'ain delfa','miliana','hammama',20), 
'2140547854','[email protected]','username','password'));

How could i select, update only a single attribute from agent table ?

I've tried that sql but it didn't work :

select infos.fname, infos.lname, infos.adress, infos.phone_num, infos.email, 
infos.username, infos.password from agent where id_ag=1;

But i'm getting this error :

invalid identifier 00904. 00000 - "%s: invalid identifier"

What am i missing ?

Thanks for your response.

Upvotes: 1

Views: 66

Answers (1)

GMB
GMB

Reputation: 222512

You have a semicolon before the where that should not be there.

When it comes to accessing the user-defined column, use a table prefix and you should be fine.

Here is the syntax for your SELECT query :

select 
    ag.infos.fname,
    ag.infos.lname,
    ag.infos.adress, 
    ag.infos.phone_num, 
    ag.infos.email, 
    ag.infos.username, 
    ag.infos.password 
from agent ag
where ag.id_ag = 1;

If you are looking to perform an UPDATE :

update agent ag
set ag.infos.fname = 'foo', ag.infos.lname = 'bar'
where ag.infos.id_ag = 1

Upvotes: 0

Related Questions