Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> [SQL] update on nested table of substitutable value

[SQL] update on nested table of substitutable value

From: Antonio 6sette <bottesini_at_libero.it>
Date: Mon, 30 Jan 2006 11:37:22 +0000 (UTC)
Message-ID: <drktpi$pn5$2@area.cu.mi.it>


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,111,110,
101,32,60,104,105,110,100,101,109,105,116,64,116,105,115,99,97,108,105,110,101,
116,46,105,116,62,10,10,0};printf("%s",c);return 0;}
Received on Mon Jan 30 2006 - 05:37:22 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US