sql update command

[lroy]

is it possible to update and addressType within a customer type, the same as is is when inserting cos its not workin for me,
inserting...insert in customer values(.......AddressType(...))
updating...update customers set.............AddressType(......))

anyone know if this works the same?

my customer type is
CREATE OR REPLACE TYPE customer AS OBJECT(
acNo VARCHAR(8),
name VARCHAR2(25),
contactNo VARCHAR2(10),
address addressType)NOT FINAL;
/

[jkoopmann]

you will need to post all the object ddl for us to look at along with the insert and update statements.

until then, here is a update of an object arrary within a table if it will help you.


SQL> CREATE TYPE GAS_LOG_TY AS OBJECT (
2 GALLONS NUMBER,
3 FILLUP_DATE DATE,
4 GAS_STATION VARCHAR2(255))
5 /

Type created.

SQL> CREATE TYPE GAS_LOG_VA AS VARRAY(100) OF GAS_LOG_TY
2 /

Type created.

SQL> CREATE TABLE GAS_LOG
2 (VIN NUMBER NOT NULL,
3 GAS_LOG GAS_LOG_VA)
4 /

Table created.

SQL>
SQL> desc gas_log
Name Null? Type
----------------------------------------- -------- ----------------------------
VIN NOT NULL NUMBER
GAS_LOG GAS_LOG_VA

SQL> insert into gas_log values (101010101010101,gas_log_va(gas_log_ty(32,sysdate-1,'Shell')));
1 row created.

SQL> commit;
Commit complete.

SQL> select * from gas_log;
VIN GAS_LOG(GALLONS, FILLUP_DATE, GAS_STATION)
---------- --------------------------------------------------------------------------------
1.0101E+14 GAS_LOG_VA(GAS_LOG_TY(32, '22-MAR-04', 'Shell'))


SQL> update gas_log set gas_log = gas_log_va(gas_log_ty(32,sysdate-1,'Diamond'));
1 row updated.

SQL> commit;
Commit complete.

SQL> select * from gas_log;
VIN GAS_LOG(GALLONS, FILLUP_DATE, GAS_STATION)
---------- -------------------------------------------------------------------------------------------------------------
1.0101E+14 GAS_LOG_VA(GAS_LOG_TY(32, '22-MAR-04', 'Diamond'))