SEO

I have a table 'dipendenti' with a substitutable column of object:

SQL> desc dipendenti
Name Null? Type
----------------------------------------- -------- -----------------------
ID NOT NULL NUMBER(4)
NOME VARCHAR2(30)
DESCR VARCHAR2(30)
....
DIP_VAR DIP_VAR_TYPE


the dip_var_type is:

SQL> desc dip_var_type
dip_var_type TABLE OF DIP_VAR_SUPER
DIP_VAR_SUPER is NOT FINAL
DIP_VAR_SUPER is NOT INSTANTIABLE
Name Null? Type
----------------------------------------- -------- ----------------------
ID NUMBER(38)
.....
UTENTE VARCHAR2(20)

and i have four type of objects UNDER dip_var_super ...

OBJECT name | attribute name | data type |
--------------------------------------------------
dip_var_num: VALORE NUMBER(7,2)
dip_var_char: VALORE CHAR(1)
dip_var_vc2: VALORE VARCHAR2(35)
dip_var_date: VALORE DATE

If I want to update the data in an attribute of dip_var_type:

UPDATE TABLE (
SELECT dip_var FROM dipendenti
WHERE id = 1 ) x
SET x.descr = 'addiz_com'
WHERE x.descr = 'addiz_reg';


but if I try to update an attribute of dip_var_vc2:

UPDATE TREAT(VALUE(TABLE(
SELECT dip_var FROM dipendenti
WHERE id = 1 )) AS dip_var_vc2) y
set y.valore = 555.33

update treat(value(table(
*
ERROR at line 1:
ORA-00971: missing SET keyword


tanks in advance ...

--
#include <stdio.h>
int main(void){char c[]={10,65,110,116,111,110,105,111,32,98,97,114,98,11 1,110,
101,32,60,104,105,110,100,101,109,105,116,64,116,1 05,115,99,97,108,105,110,101,
116,46,105,116,62,10,10,0};printf("%s",c);return 0;}

On 2006-01-30, Antonio 6sette <bottesini@libero.it> wrote:

UPDATE (
SELECT TREAT(VALUE(c) AS dip_var_vc2).valore cambiami
FROM TABLE(SELECT dip_var FROM dipendenti
WHERE id=1) c)
SET cambiami = 'XXX';

update (
*
ERROR at line 1:
ORA-01733: virtual column not allowed here

?!

--
#include <stdio.h>
int main(void){char c[]={10,65,110,116,111,110,105,111,32,98,97,114,98,11 1,110,
101,32,60,104,105,110,100,101,109,105,116,64,116,1 05,115,99,97,108,105,110,101,
116,46,105,116,62,10,10,0};printf("%s",c);return 0;}

On 2006-01-30, Antonio 6sette <bottesini@libero.it> wrote:
> UPDATE TABLE (
> SELECT dip_var FROM dipendenti
> WHERE id = 1 ) x
> SET x.descr = 'addiz_com'
> WHERE x.descr = 'addiz_reg';
>
>
> but if I try to update an attribute of dip_var_vc2:

update table(select dip_var
from dipendenti d where id=1) x
set x=dip_var_vc2(x.id,x.descr,
x.data_var,x.utente,'Nuovo Valore')
where treat(value(x) as dip_var_vc2).valore =
'Vecchio valore';

;-)))

--
#include <stdio.h>
int main(void){char c[]={10,65,110,116,111,110,105,111,32,98,97,114,98,11 1,110,
101,32,60,104,105,110,100,101,109,105,116,64,116,1 05,115,99,97,108,105,110,101,
116,46,105,116,62,10,10,0};printf("%s",c);return 0;}

Antonio 6sette wrote:
> I have a table 'dipendenti' with a substitutable column of object:
>
> SQL> desc dipendenti
> Name Null? Type
> ----------------------------------------- -------- -----------------------
> ID NOT NULL NUMBER(4)
> NOME VARCHAR2(30)
> DESCR VARCHAR2(30)
> ...
> DIP_VAR DIP_VAR_TYPE
>
>
> the dip_var_type is:
>
> SQL> desc dip_var_type
> dip_var_type TABLE OF DIP_VAR_SUPER
> DIP_VAR_SUPER is NOT FINAL
> DIP_VAR_SUPER is NOT INSTANTIABLE
> Name Null? Type
> ----------------------------------------- -------- ----------------------
> ID NUMBER(38)
> ....
> UTENTE VARCHAR2(20)
>
> and i have four type of objects UNDER dip_var_super ...

This might be a good place to note that Tom Kyte, and others, have made
persuasive arguments against creating tables such as this but rather
suggest relational tables and creating object views. You might wish to
explore this option.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

On 2006-01-30, DA Morgan <damorgan@psoug.org> wrote:
> This might be a good place to note that Tom Kyte, and others, have made
> persuasive arguments against creating tables such as this but rather
> suggest relational tables and creating object views. You might wish to
> explore this option.

tank you for the suggestion ... the myne was only a "didactical"
question to better understand the inner nested table and the
object substitutable columns/rows and TREAT, VALUE and TABLE
functions ...

I certainly read something about object views ...

tanks again ...

--
#include <stdio.h>
int main(void){char c[]={10,65,110,116,111,110,105,111,32,98,97,114,98,11 1,110,
101,32,60,104,105,110,100,101,109,105,116,64,116,1 05,115,99,97,108,105,110,101,
116,46,105,116,62,10,10,0};printf("%s",c);return 0;}

Antonio 6sette wrote:
> On 2006-01-30, Antonio 6sette <bottesini@libero.it> wrote:
>
> UPDATE (
> SELECT TREAT(VALUE(c) AS dip_var_vc2).valore cambiami
> FROM TABLE(SELECT dip_var FROM dipendenti
> WHERE id=1) c)
> SET cambiami = 'XXX';
>
> update (
> *
> ERROR at line 1:
> ORA-01733: virtual column not allowed here
>
> ?!
>
> --
> #include <stdio.h>
> int main(void){char c[]={10,65,110,116,111,110,105,111,32,98,97,114,98,11 1,110,
> 101,32,60,104,105,110,100,101,109,105,116,64,116,1 05,115,99,97,108,105,110,101,
> 116,46,105,116,62,10,10,0};printf("%s",c);return 0;}